Cursor Pagination at Scale with Laravel Lazy Collections and Chunked Iteration

Why Offset Pagination Fails at Scale

Every senior engineer has seen it: a LIMIT 25 OFFSET 500000 query that takes seconds because the database must scan and discard half a million rows before returning your page. Offset-based pagination is convenient but fundamentally broken for large tables.

Laravel ships with three complementary tools that, used together, eliminate this problem: cursor pagination, lazy collections, and chunked iteration. Each solves a different layer of the problem.

Cursor Pagination: Stable, Index-Friendly Pages

Cursor pagination replaces the numeric offset with an opaque pointer derived from the last seen row's ordered column(s). The query becomes a WHERE clause rather than an OFFSET, so the database can satisfy it with a simple index seek.

// routes/api.php
Route::get('/events', function (Request $request) {
    return EventResource::collection(
        Event::orderBy('id')
            ->cursorPaginate(perPage: 50, cursor: $request->cursor())
    );
});

The response includes next_cursor and prev_cursor strings. The client passes ?cursor=eyJpZCI6MTAwMH0 on the next request — no page numbers, no drift when rows are inserted mid-scroll.

Composite Cursor Columns

When ordering by a non-unique column you must include a tiebreaker:

Event::orderBy('created_at')->orderBy('id')->cursorPaginate(50);

Laravel encodes both columns into the cursor automatically. Without the tiebreaker, rows with identical created_at values can appear on multiple pages or be skipped entirely.

Lazy Collections: Streaming Without Loading Everything

LazyCollection wraps a PHP generator, pulling rows from the database one at a time (or in small internal chunks) rather than hydrating the entire result set into memory.

use Illuminate\Support\LazyCollection;

Event::query()
    ->where('processed', false)
    ->orderBy('id')
    ->lazy() // returns LazyCollection; PDO cursor under the hood
    ->each(function (Event $event) {
        ProcessEvent::dispatch($event);
    });

lazy() uses PDO::FETCH_LAZY / unbuffered queries on MySQL, so peak memory stays roughly constant regardless of table size. The trade-off: the database connection is held open for the duration of the iteration. Keep the work inside the loop fast, or dispatch jobs instead of doing heavy lifting inline.

Filtering and Mapping Without Materialising

Event::lazy()
    ->filter(fn (Event $e) => $e->score > 0.9)
    ->map(fn (Event $e) => new EventExportRow($e))
    ->each(fn (EventExportRow $row) => $exporter->write($row));

Every operator in the chain is evaluated lazily — nothing is pulled into an array until each (or toArray) forces evaluation.

Chunked Iteration: Batched Processing with Isolated Connections

When you need to dispatch jobs, write to a secondary store, or perform work that benefits from transaction batching, chunk() and chunkById() are safer than lazy().

// chunkById is preferred — it resets the query per chunk using the last seen ID
Event::where('processed', false)
    ->chunkById(500, function (Collection $events) {
        $events->each(fn (Event $e) => ProcessEvent::dispatch($e));
    });

chunkById issues a fresh query per chunk (WHERE id > :last_id LIMIT 500), so it releases the connection between batches and is safe even if rows are updated or deleted during iteration. Plain chunk() uses OFFSET internally and can skip rows when the underlying data changes — avoid it on mutable datasets.

Putting It Together: Export Pipeline

final class ExportEventsAction
{
    public function handle(ExportRequest $dto): void
    {
        Event::query()
            ->whereBetween('created_at', [$dto->from, $dto->to])
            ->orderBy('id')
            ->chunkById(1000, function (Collection $chunk) use ($dto): void {
                $rows = $chunk->map(fn (Event $e) => [
                    'id' => $e->id,
                    'payload' => $e->payload,
                    'created_at' => $e->created_at->toIso8601String(),
                ]);

                $dto->writer->writeRows($rows->all());
            });
    }
}

This pattern keeps memory flat, uses index seeks for each chunk, and composes cleanly inside a single-responsibility action class.

Key Takeaways

• Cursor pagination replaces OFFSET with a WHERE seek — always pair it with an indexed, ordered column plus a unique tiebreaker.
• lazy() streams rows via a database cursor; ideal for read-only pipelines where connection hold time is acceptable.
• chunkById() re-queries per batch; safer for mutable data and long-running jobs because it releases the connection between chunks.
• Never use plain chunk() on a dataset that changes during iteration — use chunkById() instead.
• For API responses, cursor pagination is the correct default for any resource that can grow beyond a few thousand rows.