Queries & Eloquent

Why chunking can skip half your rows

The mistake

chunk() looks like a memory-safe get(). Same query, same rows, handed to you a page at a time so a million records do not have to fit in memory at once. So updating each row inside the loop feels no different from updating it afterwards.

It is different, because chunk() does not hold one result set. It runs the query again for every page, and it finds each page by counting rows from the start: LIMIT 3 OFFSET 0, then OFFSET 3, then OFFSET 6. That counting is done by the database, on the rows that match now. The moment your loop makes a row stop matching, every row behind it shifts forward one place, and the next offset lands past rows that were never visited. They are not reprocessed later. They are not reported. The loop ends early, believing it is finished.

The machine

Simulator · chunking method size

3 of 8 rows visited

Post::where('status', 'todo')->chunk(3, function ($posts) {
    foreach ($posts as $post) {
        $post->update(['status' => 'done']);
    }
});

Three rows handled, and each one no longer matches the filter. The next query asks for the same result set at offset 3, but the result set is three rows shorter than it was.

the loop writes

Every query and every row runs on the tested reducer, measured against Laravel 13.31 on PHP 8.4.

3 of 8 rows visited after 1 queries.

Drive it

Eight rows, three to a page, and a loop that marks each row done as it goes. One query has already run.

Run the next query. It asks for the same result set at offset 3, but three rows have left that set, so it comes back with rows 7 and 8. It is short, so chunking stops. Rows 4, 5 and 6 were never visited, and nothing said so.
Press the featured button to switch to chunkById. Same loop, same writes, and this time every row is reached. Read the SQL: where id > 3 instead of offset 3.
Turn off “the loop writes” and run it again. All eight, every time. This is the version your test does, which is why the bug survives review.

The mechanism

chunk() runs a loop of its own. It takes a page, hands it to your callback, then asks for the next page, and it stops when a page comes back with fewer rows than the chunk size. That stopping rule is the second half of the bug: a short page normally means the end of the table, but it also happens when rows have disappeared from the result set, and chunking cannot tell the difference.

Walk the eight rows through it. Page one is LIMIT 3 OFFSET 0 and returns rows 1, 2, 3. The loop marks them done, so they no longer match status = 'todo'. Page two is LIMIT 3 OFFSET 3, but the result set is now rows 4 to 8, and skipping three of those five lands on rows 7 and 8. Two rows, fewer than three, so the loop ends. Rows 4, 5 and 6 sit there still waiting, and the run reports nothing wrong.

chunkById() fixes it by not counting. It remembers the highest key it has seen and asks for where id > that, so the page it gets next cannot depend on how many rows are in front of it. Rows leaving the result set behind the cursor change nothing. It costs one extra query at the end, and it requires the column to be a unique, sortable key, which the primary key is.

The same split runs through the rest of the family. each() and lazy() page the same way chunk() does, so they skip rows in exactly the same conditions. eachById() and lazyById() page by key like chunkById(). If the loop writes to the filtered column, only the ById half is safe.

In your code

The rule is short: if the loop writes to anything the query filters or sorts on, page by key.

// skips rows: the offset counts a result set that is shrinking underneath it
Post::where('status', 'todo')->chunk(500, function ($posts) {
    foreach ($posts as $post) {
        $post->update(['status' => 'done']);
    }
});

// safe: each page carries on from the last key, whatever left the set
Post::where('status', 'todo')->chunkById(500, function ($posts) {
    foreach ($posts as $post) {
        $post->update(['status' => 'done']);
    }
});

There is a second fix worth knowing, because it is sometimes the better one: take the ids first, then work from that fixed list.

$ids = Post::where('status', 'todo')->pluck('id');

foreach ($ids->chunk(500) as $chunk) {
    Post::whereKey($chunk)->update(['status' => 'done']);
}

The list of ids is decided once, so nothing can shift. It costs one pass over the keys up front, which is cheap, and it is the shape to reach for when the update is a mass update anyway.

The fine print

The queries, the row counts and the stopping behaviour were measured against Laravel 13.31 on PHP 8.4 with the query log on. At size 3 the run visits five of eight rows in two queries; at size 2 it visits four; at size 4 it visits four. The panel replays those exact runs.
The final query that comes back empty is really sent. The panel shows it because it is what ends the run.
chunkById() does not replace an orderBy you wrote. It appends the key, so ordering by something else and paging by id gives order by status desc, id asc, and the paging is only reliable if that first sort does not move rows around. If you need a specific order, sort by the key.
The key has to be unique and it has to be sortable. A UUID primary key works only if it sorts in insertion order, which random UUIDs do not. Ordered UUIDs and ULIDs do.
Deleting rows in the loop has the same problem, for the same reason, and the same fix.
None of this is about memory. chunk() is still the right tool for not loading a million rows at once. The question is only how it finds the next page.