Laravel chunkById vs chunk: Why Your Backfill Skips Rows

If you have ever lost rows to a Laravel batch job and ended up searching for chunkById vs chunk afterward, here are the mechanics behind the skip. Tuesday night I ran a backfill over an orders table: 83,000 rows, mark the imported ones as reconciled, log a count at the end. The command finished clean and reported 41,006 rows updated. The finance report the next morning expected 83,000. No exception, no warning. The query had just kept moving the floor out from under itself.

The fix was one method name. Understanding why took about forty lines of framework source, which I would rather read once than re-litigate in code review every quarter.

What chunk() asks the database for

chunk() pages with OFFSET. Every iteration runs the same query again, only with a bigger OFFSET:

Order::where('reconciled', false)
    ->orderBy('id')
    ->chunk(1000, function ($orders) {
        foreach ($orders as $order) {
            $order->update(['reconciled' => true]);
        }
    });

Here is the part that bit me. OFFSET does not mean "the next thousand rows". It means scan past this many matching rows, then take the next thousand, and the scan runs against the live table. My callback set the same flag my WHERE clause filtered on. After page one, a thousand rows stopped matching. Page two asked for offset 1000 of what was left, which were originally rows 2001 through 3000. Every page advanced 2000 positions in the original table while processing 1000. Half the table, skipped, with arithmetic you can do in your head.

Deletes shift the window too, and inserts shift it the other way. The docs do warn that updating records while chunking can change the results in unexpected ways and point you at chunkById. Most tutorials show the naive version first anyway, and the bug only fires when you update what you filtered on. Which is what backfills do.

What chunkById() does instead

chunkById never computes an offset. It remembers the id of the last row in the previous batch and asks for the rows after it:

-- page 3 of chunk(1000) after half the rows stopped matching
select * from orders where reconciled = 0 limit 1000 offset 2000;

-- page 3 of chunkById(1000)
select * from orders
where reconciled = 0 and id > 2000
order by id asc limit 1000;

That id > $lastId condition comes from forPageAfterId() in the query builder. The first page has no cursor yet, so it filters with whereNotNull; every page after that is a strict greater-than on the value the chunk's last row carried. Marking rows reconciled shrinks the result set but never moves the id cursor, so nothing behind it can vanish. There is a bonus, too: later pages stop paying the OFFSET tax. A page at offset 999000 walks the whole index before returning anything, while id > 999000 goes straight there. I got into that cost in the MySQL slow query workflow post.

Two traps waiting after you switch

Trap one: drop the chunk column from your select and chunkById aborts. I "optimized" by selecting only the columns the callback touched, forgot the id, and got this:

RuntimeException: The chunkById operation was aborted because
the [id] column is not present in the query result.

After a silent skip, an exception that names the problem felt almost friendly. The reason is that the framework reads the next cursor value off the last row of every batch, and that read comes back null when the column was never selected. The fourth argument exists for exactly this case: chunkById(1000, $cb, 'created_at', 'ts') pairs with select('created_at as ts').

Trap two: the comparison is strict, so the column you page by has to hold unique values. Legacy imports left me with second-precision created_at stamps, thousands of rows per second. Paging by created_at with chunkById skips every row that shares a timestamp with a batch's last row. A random UUIDv4 key technically works since every value differs. The order is just meaningless lexicographic noise.

About the advice you see everywhere, "never combine orderBy with chunkById": the source explains it in one line. forPageAfterId strips existing orders on the chunk column only and appends its own. Your orderBy('status') survives, the final query orders by status then id, and the id cursor now pages across an ordering it never assumed. The advice is right. The reason is one line of code.

Which one to reach for

Any job that writes while it reads gets chunkById or lazyById. They share the same id-cursor paging underneath; lazyById just yields rows into a foreach instead of handing your closure a batch. lazy() is not a substitute, by the way. Its implementation pages with offset and limit exactly like chunk() does.

For a single read-only pass, cursor() is fine and runs one query. Just know that PHP buffers the entire MySQL result set on the client side by default, so "one row at a time" is true in PHP land, not on the wire. And a small asymmetry I found in the source while writing this: Order::chunk() with no orderBy runs fine, because Eloquent quietly appends one on the primary key. DB::table('orders')->chunk() with no orderBy throws "You must specify an orderBy clause when using this function." Same method name, different rules depending on the builder.

The backfill now logs the row count it plans to touch before it starts and compares it against the count it actually marked. That check alone would have caught Tuesday's bug on the first run. But chunkById is the real fix, and those forty lines of BuildsQueries.php have settled every version of this argument I have had since. I keep the annotated source snippet around in Snippet Ark for the next time someone proposes chunk() in a migration.