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

Why Offset Pagination Fails 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 no covering index, this becomes a full or near-full index scan on every page load. The deeper the page, the slower the query — linearly.

Laravel gives you three tools to escape this trap: cursor pagination, chunked iteration, and lazy collections. They solve different problems and are not interchangeable.

Cursor Pagination for User-Facing APIs

Cursor pagination encodes the last-seen row's ordered column value into an opaque token. The next query uses a WHERE clause instead of OFFSET:

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

return OrderResource::collection($orders);

The generated SQL looks like:

SELECT * FROM orders WHERE id > 1482930 ORDER BY id ASC LIMIT 50;

With an index on id (always true for PKs), this is an O(log n) seek regardless of depth. The cursor itself is a base64-encoded JSON payload containing the column values — it is not a page number.

Multi-column ordering

Cursor pagination works with compound order keys, but every column in orderBy must be included in the cursor:

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

Laravel encodes both created_at and id into the cursor token and generates the correct WHERE (created_at, id) > (?, ?) inequality. Ensure a composite index exists:

$table->index(['created_at', 'id']);

What cursor pagination cannot do

• No random page access (you cannot jump to page 47).
• No total count (avoid withCount unless you genuinely need it).
• Ordering must be deterministic — avoid nullable columns without a tiebreaker.

Chunked Iteration for Background Processing

When you need to process every row in a table — exports, migrations, recalculations — chunk() and chunkById() are your tools. They are not for pagination; they are for batch processing.

// Dangerous: chunk() with mutations can skip rows
Order::where('status', 'pending')->chunk(500, function ($orders) {
    foreach ($orders as $order) {
        $order->update(['status' => 'processing']); // shifts the result set
    }
});

Use chunkById() whenever you mutate rows inside the callback. It re-anchors each chunk using the last seen primary key:

Order::where('status', 'pending')
    ->chunkById(500, function ($orders) {
        foreach ($orders as $order) {
            ProcessOrder::dispatch($order);
        }
    });

The underlying SQL uses WHERE id > ? ORDER BY id LIMIT 500 — the same cursor-style seek as cursor pagination, but driven internally.

Lazy Collections for Memory-Efficient Streaming

LazyCollection wraps a PHP generator. Eloquent's cursor() method streams rows one at a time from the database, keeping only the current model in memory:

Order::where('status', 'pending')
    ->cursor()
    ->each(function (Order $order) {
        ProcessOrder::dispatch($order);
    });

This uses a single unbuffered query. Memory stays flat regardless of result set size. The trade-off: the database connection is held open for the entire iteration. For long-running processes or large tables, this can exhaust connection pool slots.

Combining lazy collections with chunking

Order::cursor()
    ->chunk(200)
    ->each(function ($chunk) {
        Order::whereIn('id', $chunk->pluck('id'))
            ->update(['processed_at' => now()]);
    });

This streams rows lazily but batches the writes — a useful hybrid when you need both low memory and efficient bulk updates.

Choosing the Right Tool

| Scenario | Tool | |---|---| | User-facing paginated API | cursorPaginate() | | Background batch processing | chunkById() | | Memory-constrained streaming | cursor() + LazyCollection | | Bulk mutations on large tables | chunkById() | | Piping results through a pipeline | cursor() |

Key Takeaways

• Offset pagination degrades linearly; replace it with cursorPaginate() for any user-facing list.
• Always prefer chunkById() over chunk() when mutating rows inside the callback.
• cursor() streams one model at a time via a generator — ideal for memory-sensitive pipelines but holds the DB connection open.
• Compound order keys in cursor pagination require matching composite indexes.
• Lazy collections compose with standard Collection methods, making them easy to integrate into existing pipelines.