chunk() vs. lazyById() in Laravel: Processing Millions of Records Safely
Why ->get() Fails at Scale
Calling Message::where('status', 'pending')->get() works fine during local development when your database holds a few hundred rows. Once that table grows to 5 million records in production, PHP exhausts its memory_limit trying to hydrate millions of Eloquent models simultaneously into a single Collection.
Laravel provides four primary methods for streaming large datasets: chunk(), chunkById(), cursor(), and lazyById(). Choosing the wrong one can either skip records silently or degrade query speed exponentially as the job progresses.
1. The Two Hidden Traps of chunk()
Under the hood, chunk(1000) executes standard LIMIT 1000 OFFSET N queries. That causes two severe problems on large tables:
- O(N²) database scan degradation: When MySQL executes
LIMIT 1000 OFFSET 4500000, the storage engine still reads and discards the first 4.5 million rows on every single page fetch. - Skipped rows during updates: If your callback updates the column you are filtering on (for example, changing
statusfrom'pending'to'processed'), the remaining rows shift upward, causingOFFSETto skip half of your records.
2. Keyset Pagination with chunkById() and lazyById()
Instead of using OFFSET, chunkById() and lazyById() track the primary key of the last retrieved record and generate an indexed range scan:
SELECT * FROM messages WHERE status = 'pending' AND id > 1450000 ORDER BY id ASC LIMIT 1000;
Because id > ? jumps directly to the B-Tree leaf node in MySQL, page 5,000 executes just as fast as page 1—and mutating status inside the loop never skips rows.
<?php
use App\Models\Message;
// Clean, flat iteration using LazyCollection + keyset pagination
Message::query()
->select(['id', 'recipient', 'payload', 'status'])
->where('status', 'pending')
->lazyById(1000)
->each(function (Message $message) {
$message->dispatchToGateway();
});
3. When to Choose cursor() vs. lazyById()
While cursor() executes only one SQL query and hydrates a single Eloquent model at a time using PHP generators, it relies on an unbuffered PDO cursor. That introduces two important constraints:
- No eager loading: You cannot chain
->with('campaign')ontocursor()because eager loading requires batching IDs across multiple rows.lazyById(1000)supports eager loading cleanly per 1,000-row chunk. - Connection lock-up: While an unbuffered
cursor()stream is open, you cannot run additional queries on the same PDO connection until the stream finishes.
Quick Decision Matrix
- Updating records or calling APIs per row: Use
lazyById(1000)(orchunkById(1000)when you want to perform bulkwhereIn('id', $ids)->update(...)operations). - Read-only CSV exports without relations: Use
cursor()paired withtoBase()to skip Eloquent model hydration altogether and stream millions of rows in under 15 MB of RAM. - Never use
chunk()on tables with an auto-incrementing or ULID primary key—always preferchunkById()orlazyById().
Written by Gulfaraz Arshad
Software Developer at Cruise Brains specializing in Laravel, PHP, MySQL performance optimization, and high-throughput API architectures.