Cursor Pagination and Lazy Collections at Scale in Laravel

Why Offset Pagination Fails at Scale

Every time you call ->paginate(50) with OFFSET 50000, the database scans and discards 50,000 rows before returning your page. On a table with millions of records, that cost compounds with every page request. Query time grows linearly, index scans become full-table scans, and your users notice.

Laravel ships two better tools for this: cursor pagination for user-facing pages and lazy collections for background processing.

Cursor Pagination

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, which the database can satisfy with a simple index seek.

// Controller
$orders = Order::query()
    ->where('tenant_id', $tenantId)
    ->orderBy('created_at')
    ->orderBy('id') // tie-breaker — must be unique
    ->cursorPaginate(50);

return OrderResource::collection($orders);

The response includes next_cursor and prev_cursor tokens. Pass them back as ?cursor=<token> and Laravel decodes them automatically.

What the Generated SQL Looks Like

-- First page
SELECT * FROM orders
WHERE tenant_id = 1
ORDER BY created_at ASC, id ASC
LIMIT 51;

-- Second page (cursor decoded)
SELECT * FROM orders
WHERE tenant_id = 1
  AND (created_at > '2024-06-01 12:00:00'
    OR (created_at = '2024-06-01 12:00:00' AND id > 9823))
ORDER BY created_at ASC, id ASC
LIMIT 51;

The composite (tenant_id, created_at, id) index satisfies this seek in microseconds regardless of how deep into the dataset you are.

Caveats

• Cursor pagination cannot jump to an arbitrary page — it is forward/backward only.
• Your orderBy columns must be stable and unique (always add id as a tie-breaker).
• Avoid nullable columns in the cursor key; NULL comparisons break the seek logic.

Lazy Collections for Batch Processing

When you need to process every row — exports, re-indexing, data migrations — chunk() is the classic approach, but it fires a new query per chunk and holds an entire chunk in memory. lazy() streams rows through a PHP generator, keeping memory flat.

// Bad: loads 1,000 rows into memory, then another 1,000, etc.
Order::where('status', 'pending')->chunk(1000, function ($orders) {
    $orders->each(fn ($o) => dispatch(new ProcessOrder($o)));
});

// Good: one query, cursor-driven, constant memory
Order::where('status', 'pending')
    ->lazy()
    ->each(fn ($order) => dispatch(new ProcessOrder($order)));

Under the hood, lazy() uses PDO::FETCH_LAZY via a cursor, pulling one row at a time from the database driver buffer.

Combining lazy() with Collection Pipelines

Order::where('status', 'pending')
    ->lazy()
    ->filter(fn ($o) => $o->total > 100)
    ->map(fn ($o) => new ProcessOrder($o))
    ->pipe(fn ($jobs) => Bus::batch($jobs->all())->dispatch());

Because LazyCollection is a generator-backed collection, filter and map are also lazy — nothing is evaluated until all() forces iteration.

lazyById() for Long-Running Processes

If your process modifies rows mid-iteration (e.g., updating status), the cursor can drift. Use lazyById() instead — it re-queries in chunks ordered by primary key, safe against mutations:

Order::where('status', 'pending')
    ->lazyById(500, 'id')
    ->each(function ($order) {
        $order->update(['status' => 'processing']);
    });

Choosing the Right Tool

| Scenario | Tool | |---|---| | User-facing paginated API | cursorPaginate() | | Read-only export / reporting | lazy() | | Mutating rows during iteration | lazyById() | | Random page access required | paginate() (accept the cost) |

Key Takeaways

• OFFSET pagination degrades linearly; cursor pagination uses index seeks and stays fast at any depth.
• Always include a unique tie-breaker column in your orderBy for cursor pagination.
• lazy() streams rows via a generator — memory stays constant regardless of result set size.
• Use lazyById() when the loop body mutates the rows being iterated.
• A composite index covering your filter + order columns is non-negotiable for both techniques.