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

The Problem With Offset Pagination 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 load, this degrades predictably. The query plan cannot use an index seek for the offset portion — it must materialise the skipped rows first.

Laravel gives you three better tools depending on your use case: cursor pagination, lazy collections, and chunked iteration. They are not interchangeable.

Cursor Pagination

Cursor pagination encodes the last-seen value of an ordered column into an opaque token. The next page is fetched with a WHERE id > :last_id clause, which the database resolves with a single index seek.

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

// In your API resource:
return [
    'data' => UserResource::collection($users),
    'next_cursor' => $users->nextCursor()?->encode(),
    'prev_cursor' => $users->previousCursor()?->encode(),
];

The generated SQL looks like:

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

Constraints to know:

• The sort column must be unique or combined with a tiebreaker (orderBy('created_at')->orderBy('id')).
• You cannot jump to an arbitrary page — cursors are sequential.
• Ideal for infinite scroll, feeds, and API consumers that process pages linearly.

Lazy Collections

LazyCollection wraps a PHP generator. Eloquent's cursor() method returns one, hydrating one model at a time from a forward-only database cursor.

User::where('active', true)
    ->orderBy('id')
    ->cursor()
    ->each(function (User $user) {
        ProcessUser::dispatch($user);
    });

Memory stays flat because only one model is alive at a time. The database connection, however, stays open for the duration of the iteration. On long-running processes this can exhaust connection pool slots.

// Combine with lazy() for collection pipeline operations without loading all rows
User::cursor()
    ->filter(fn (User $u) => $u->isEligible())
    ->map(fn (User $u) => $u->toExportArray())
    ->each(fn (array $row) => $csv->writeRow($row));

The filter and map here are lazy — they do not buffer the full result set.

When to prefer cursor(): transformation pipelines, exports, and read-only processing where you need collection methods but cannot afford to load everything into memory.

Chunked Iteration

chunk() and chunkById() issue multiple queries, each fetching a fixed number of rows. The connection is released between chunks.

// chunk() uses OFFSET — fine for small tables, degrades at scale
User::chunk(500, function (Collection $users) {
    foreach ($users as $user) {
        // ...
    }
});

// chunkById() uses WHERE id > :last_id — scales correctly
User::orderBy('id')->chunkById(500, function (Collection $users) {
    foreach ($users as $user) {
        // ...
    }
});

Always prefer chunkById(). It avoids the offset scan and is safe when rows are inserted or deleted mid-iteration because it anchors on the primary key, not a row offset.

// Custom key column for tables without a sequential `id`
User::chunkById(500, function ($users) { /* ... */ }, column: 'uuid');

Choosing the Right Tool

| Scenario | Best fit | |---|---| | API with next/prev navigation | cursorPaginate() | | Export / ETL pipeline | cursor() + LazyCollection | | Background job processing all rows | chunkById() | | Small table, arbitrary page jumps | paginate() (offset is fine) |

Practical Gotchas

• Eager loading with cursor(): with() is ignored — Eloquent cannot batch eager loads across a generator. Use chunkById() if you need relationships.
• Transactions and chunk(): wrapping a chunk callback in a transaction is safe; wrapping the entire chunk() call is not — it holds a transaction open across all chunks.
• lazyById(): a convenience wrapper that combines chunkById() with a LazyCollection facade, giving you collection methods without holding an open cursor.

User::lazyById(500, column: 'id')
    ->filter(fn ($u) => $u->plan === 'pro')
    ->each(fn ($u) => SendRenewalReminder::dispatch($u));

Takeaways

• Replace paginate() with cursorPaginate() for any API endpoint that processes pages sequentially.
• Use cursor() / LazyCollection for memory-sensitive pipelines, but watch open connection duration.
• Always use chunkById() over chunk() on large tables — offset scans are a silent performance killer.
• lazyById() is the pragmatic middle ground: chunked queries with a lazy collection interface.
• Eager loading requires chunkById(); cursor() cannot batch relationship queries.