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

The Problem With Naive Bulk Processing

Pulling 500,000 rows into a standard Eloquent get() call is a fast path to an OOM kill. Laravel ships several mechanisms to handle large datasets, but each has a distinct performance profile and a set of non-obvious failure modes that bite you in production.

chunk() — Reliable But Offset-Dependent

User::orderBy('id')->chunk(1000, function (Collection $users) {
    foreach ($users as $user) {
        ProcessUser::dispatch($user);
    }
});

chunk() issues repeated LIMIT / OFFSET queries. The fatal flaw: if you mutate the table inside the callback — deleting or updating rows that affect the ORDER BY column — the offset shifts and you silently skip records.

Use chunkById() instead whenever you touch the dataset mid-iteration:

User::orderBy('id')->chunkById(1000, function (Collection $users) {
    $users->each(fn ($u) => $u->update(['synced_at' => now()]));
});

chunkById() uses a keyset cursor (WHERE id > ?) rather than OFFSET, making it safe for mutations and dramatically faster on large tables because it avoids a full index scan to reach the offset.

lazy() and lazyById() — Generators Over Eloquent

Introduced in Laravel 8, lazy() wraps chunk() in a PHP generator, yielding individual models one at a time:

foreach (User::lazy(500) as $user) {
    // Only 500 rows hydrated at a time, but you iterate one-by-one
    SyncUser::run($user);
}

This is ergonomically cleaner than a closure-based chunk() and composes naturally with LazyCollection methods:

User::lazyById(500)
    ->filter(fn ($u) => $u->needs_sync)
    ->each(fn ($u) => SyncUser::run($u));

lazyById() carries the same keyset advantage as chunkById(). Prefer it over lazy() for any table you might write to during iteration.

Memory Profile

Both lazy() variants keep at most one chunk hydrated at a time. For 1 M rows with a 500-row chunk size, you hold ~500 model instances in memory simultaneously — a predictable, bounded footprint regardless of dataset size.

cursor() — One Row at a Time, One Query Total

foreach (User::cursor() as $user) {
    // Single unbuffered query; one model hydrated per iteration
    CsvExporter::write($user);
}

cursor() issues a single SQL query and streams results via PDO's unbuffered cursor. Memory usage is minimal — one hydrated model at a time — but the database connection is held open for the entire iteration.

When cursor() is the wrong choice:

• Long-running jobs (connection timeout risk)
• MySQL with PDO::MYSQL_ATTR_USE_BUFFERED_QUERY forced on (some hosting stacks)
• Any code path that opens a second query inside the loop (you'll exhaust the connection)

Cursor Pagination for APIs

For paginated API responses, offset-based pagination degrades as page numbers grow. Cursor pagination encodes the last-seen keyset into an opaque token:

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

return UserResource::collection($page);
// Response includes next_cursor / prev_cursor tokens

The generated SQL uses WHERE id > ? (or a composite keyset for multi-column sorts), giving consistent O(log n) performance regardless of how deep into the dataset the client is.

Gotcha: cursorPaginate() requires a unique, stable sort column. Sorting by created_at alone on a high-write table produces ties that corrupt the cursor. Always append id as a tiebreaker:

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

Choosing the Right Tool

| Scenario | Recommended API | |---|---| | Background job, no mutations | lazy() / lazyById() | | Background job, mutates rows | chunkById() or lazyById() | | CSV/stream export, short-lived | cursor() | | REST API pagination | cursorPaginate() | | Simple one-off script | chunk() (with caution) |

Key Takeaways

• chunk() with OFFSET silently skips rows when you mutate during iteration — always prefer chunkById() or lazyById() for write-heavy loops.
• lazy() / lazyById() give you generator ergonomics with bounded memory; they compose with LazyCollection pipelines cleanly.
• cursor() is fastest for read-only streaming but ties up the DB connection and breaks if you open nested queries.
• cursorPaginate() requires a unique sort; append id to any non-unique column to prevent cursor drift.
• Profile with DB::listen() or Telescope before assuming any approach is fast enough — chunk size and index coverage matter more than the API choice.