Cursor Pagination, Chunked Iteration, and Lazy Collections at Scale in Laravel

The Problem With Offset at Scale

Offset-based pagination (LIMIT 100 OFFSET 50000) forces the database to scan and discard 50,000 rows before returning your page. On a table with millions of rows and a busy write workload, that scan gets expensive fast — and the query plan degrades non-linearly.

Laravel gives you three tools to escape this trap: cursor pagination, chunked iteration, and lazy collections. Each solves a different problem. Reaching for the wrong one wastes either memory or database round-trips.

Cursor Pagination

Cursor pagination encodes the last-seen row's ordered column value into an opaque token. The next page becomes a WHERE clause rather than an OFFSET.

// Route handler
$users = User::orderBy('id')
    ->cursorPaginate(50);

return UserResource::collection($users);

The generated SQL looks like:

SELECT * FROM users
WHERE id > 3847
ORDER BY id ASC
LIMIT 51; -- one extra to detect next page

The cursor itself is a base64-encoded JSON payload containing the column values at the boundary. Laravel's CursorPaginator handles encoding and decoding transparently.

Gotchas

• You must order by a unique, indexed column (or a combination that is effectively unique). Non-unique cursors produce inconsistent pages.
• Cursor pagination does not support jumping to arbitrary pages — it is forward/backward only. If your UI needs page numbers, stick with paginate().
• When ordering by a non-unique column (e.g., created_at), add id as a tiebreaker: ->orderBy('created_at')->orderBy('id').

$orders = Order::orderBy('created_at')->orderBy('id')
    ->cursorPaginate(25);

Laravel will encode both columns into the cursor token automatically.

Chunked Iteration

When you need to process every row in a table — exports, migrations, backfills — loading everything into memory at once is fatal. chunk() issues repeated queries, each fetching a fixed batch.

User::orderBy('id')->chunk(1000, function (Collection $users) {
    foreach ($users as $user) {
        ProcessUser::dispatch($user);
    }
});

Under the hood this is still offset-based, so on very large tables the later chunks slow down. Use chunkById() instead — it rewrites each batch as a keyed WHERE id > ? query:

User::chunkById(1000, function (Collection $users) {
    $users->each(fn ($u) => ProcessUser::dispatch($u));
});

chunkById() requires an ordered, unique column (defaults to the model's primary key). It is safe to modify rows inside the callback because the cursor advances by ID, not by offset.

Lazy Collections

LazyCollection wraps a PHP generator, pulling one row at a time from the database cursor. Memory usage stays flat regardless of result set size.

User::cursor()->each(function (User $user) {
    // Only one User model in memory at a time
    $user->recalculateScore();
    $user->saveQuietly();
});

The underlying PDO fetch mode is set to PDO::FETCH_LAZY, and Eloquent hydrates one model per iteration. This is ideal for read-heavy pipelines where you do not need to batch database writes.

Combining Lazy Collections With Chunking

For write-heavy pipelines, combine cursor() with chunk() on the LazyCollection to batch inserts while keeping memory low:

User::cursor()
    ->chunk(500)
    ->each(function (LazyCollection $batch) {
        $records = $batch->map(fn ($u) => [
            'user_id' => $u->id,
            'score'   => $u->computeScore(),
        ])->all();

        Score::upsert($records, ['user_id'], ['score']);
    });

This pattern keeps one chunk (500 models) in memory at a time while issuing a single UPSERT per batch.

Choosing the Right Tool

| Scenario | Tool | |---|---| | API pagination with forward/back navigation | cursorPaginate() | | Full-table processing, safe to modify rows | chunkById() | | Read-only streaming pipeline, minimal memory | cursor() / LazyCollection | | Batched writes over a large result set | cursor()->chunk() |

Key Takeaways

• OFFSET pagination degrades at scale; cursor pagination replaces it with an indexed WHERE clause.
• Always pair cursor pagination with a unique ordered column or a composite tiebreaker.
• chunkById() is safer than chunk() when modifying rows inside the callback.
• LazyCollection::cursor() holds one model in memory at a time — ideal for streaming reads.
• Combine cursor()->chunk() for memory-efficient batched writes over millions of rows.