How to Update Large Eloquent Tables Without Skipping Rows with lazyById

When a Laravel job updates many database rows, an update can make a row stop meeting the query’s conditions. If the job reads rows in batches by offset, each update shrinks the set of matching rows. The next batch can then skip rows that still need work.

Eloquent is Laravel’s object-relational mapper: it lets PHP code read database rows as model objects. Its lazyById method reads those objects in batches, moving forward by a unique key such as the primary key. Because each batch starts after the last key it read, changing a row’s status does not shift later rows out of reach.

Use lazyById when a long-running job updates records while its selection condition changes. Keep the key stable, and make sure the query’s conditions select the intended rows.

Why offset-based batches skip rows

Suppose a job selects users whose status is pending, then changes each selected user’s status to processed. With offset-based chunk() pagination, the first batch leaves the matching result set as soon as the job updates those users. The next query skips the first batch’s offset within that smaller set, so some pending users never get read.

For example, updating the earliest matches in a batch removes them from the query results. If the next query skips an offset within that smaller result set, it can skip rows that have not yet been processed.

Reusing a query builder, the Laravel object that assembles database queries, does not preserve the original set of matching rows. The builder applies its conditions again, so an update can make a record stop matching them; a Laravel discussion of repeated updates describes this behavior.

How lazyById keeps moving forward

lazyById fetches records in batches ordered by a key, then uses the last key it read to find the next batch. When it reads keys from low to high, the next query looks for keys greater than the last one. The method returns a lazy collection, which fetches successive batches as the job reads them. This lets the job handle records without loading the whole table into memory. Reading large result sets in chunks is also a common way to limit memory use, as described in this Laravel query optimization overview.

A job that updates each model could look like this:

foreach (
    User::query()
        ->where('status', 'pending')
        ->lazyById() as $user
) {
    $user->status = 'processed';
    $user->save();
}

After the job processes a user, that user no longer matches status = 'pending'. The next batch still looks for IDs greater than the last ID read, so the job continues to later users rather than skipping them because the result set got smaller.

Choose a stable key and keep conditions clear

Use a unique, stable key to move through the rows, usually the table’s primary key. If you choose a different column, make sure its values are unique and do not change while the job runs. If multiple rows share the key, moving past that value can leave some rows unread.

Also group OR conditions so the key condition applies to the intended selection. For example, put alternative status checks inside a closure:

User::query()
    ->where(function ($query) {
        $query->where('status', 'pending')
            ->orWhere('status', 'retry');
    })
    ->lazyById();

Without grouping, the database can combine the status checks and key condition in a different order. The query can then return rows that do not meet the intended status and key checks.

If new records enter the table while the job runs, later batches can include them when their IDs are greater than the last ID read and they match the query. If the job must process only records that existed when it started, set a boundary or record the IDs of those records. Querying by a saved set of IDs is one way to address the problem of a changing match condition, as shown in the Laravel discussion about updating the same records twice.

Choose between per-model updates and one bulk update

Use save() inside the loop when each row needs its own calculation or handling through its model. The job then sends one update for each model, creating more database writes than a single bulk statement.

If every matching row should receive the same value, one query-builder update() can change them all at once. A bulk update can also use SQL expressions to calculate values from each row’s existing data. Laravel supports raw expressions through DB::raw(), as described in the Query Builder documentation. For different per-row values, calculate and save each model, or construct a database statement with per-row logic such as CASE.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com