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 a composite index, this becomes a full or partial index scan that grows linearly with page depth. You feel it in query times that balloon from 5 ms on page 1 to 800 ms on page 500.

Laravel ships with three tools that sidestep this entirely: cursor pagination, chunked iteration, and lazy collections. Each solves a different problem.

Cursor Pagination

Cursor pagination encodes the last-seen value of an ordered column into an opaque token. The next query uses a WHERE clause instead of OFFSET, making every page equally fast regardless of depth.

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

// In a subsequent request
$users = User::orderBy('id')->cursorPaginate(50, ['*'], 'cursor', $request->cursor);

The generated SQL looks like:

SELECT * FROM users WHERE id > 84321 ORDER BY id ASC LIMIT 50;

That id > 84321 predicate hits the primary key index directly — O(log n) regardless of depth.

Constraints to know

• The cursor column must be unique and ordered. Composite cursors (e.g., created_at + id) work but require both columns in orderBy.
• You cannot jump to an arbitrary page — cursor pagination is strictly sequential.
• cursorPaginate returns a CursorPaginator, not a LengthAwarePaginator, so there is no total count.

User::orderBy('created_at')->orderBy('id')->cursorPaginate(25);

This produces a stable, tie-breaking cursor even when created_at has duplicates.

Chunked Iteration: chunk() vs chunkById()

For background jobs or exports where you need to process every row, chunk() is the entry point most developers reach for. But it has a subtle bug: if rows are deleted or inserted during iteration, the offset shifts and you skip or double-process records.

// Dangerous with concurrent writes
User::where('active', true)->chunk(500, function ($users) {
    // process
});

Use chunkById() instead. It re-anchors each batch using the last seen primary key:

User::where('active', true)->chunkById(500, function ($users) {
    foreach ($users as $user) {
        ProcessUser::dispatch($user);
    }
});

The generated SQL per batch:

SELECT * FROM users WHERE active = 1 AND id > 7450 ORDER BY id ASC LIMIT 500;

Safe under concurrent writes, index-friendly, and consistent.

Lazy Collections and lazyById()

When you want to iterate a result set with PHP generators — pulling rows one at a time without loading the full batch into memory — reach for lazy() or lazyById().

User::where('active', true)->lazyById(500)->each(function (User $user) {
    // Hydrated one model at a time, fetched in batches of 500
    $user->sendWeeklyDigest();
});

Under the hood, lazyById() uses chunkById() and yields each model through a PHP generator. Your memory footprint stays flat — you hold at most one model at a time in your application layer, while the database still returns batches of 500 for network efficiency.

Combining with Collection pipelines

Lazy collections are first-class Enumerable implementations, so you can chain higher-order operations without materialising the full dataset:

User::lazyById(1000)
    ->filter(fn($u) => $u->isEligibleForPromotion())
    ->each(fn($u) => PromoteUser::dispatch($u));

The filter callback runs per-model as the generator yields, never accumulating a full collection in memory.

Choosing the Right Tool

| Scenario | Tool | |---|---| | API pagination, deep pages | cursorPaginate() | | Background export, safe under writes | chunkById() | | Memory-sensitive pipeline processing | lazyById() | | Small datasets, total count needed | paginate() |

Key Takeaways

• Never use chunk() on tables with concurrent writes — use chunkById() instead.
• Cursor pagination is O(log n) per page; offset pagination degrades linearly.
• lazyById() gives you generator-based streaming with Eloquent model hydration and a flat memory profile.
• Always pair cursor/chunk columns with a database index — without one, you trade an offset scan for a full-table seek.
• Composite cursors (created_at + id) handle non-unique sort columns safely.