Eloquent upsert and updateOrCreate at Scale

A nightly import runs 400,000 rows through Eloquent, and for the first time two workers overlap. Rows that should have been updated get inserted twice, the unique index throws, and the job dies halfway. The fix is rarely a bigger timeout; it is choosing the right write primitive. This guide compares an eloquent upsert with updateOrCreate and insertOrIgnore, and shows which one survives concurrent writers.

What does an Eloquent upsert actually do?

An eloquent upsert compiles to a single INSERT ... ON DUPLICATE KEY UPDATE statement on MySQL and INSERT ... ON CONFLICT ... DO UPDATE on PostgreSQL. It sends every row in one query, so the database resolves conflicts atomically per statement. The second argument lists the columns to match on, and the third lists the columns to overwrite.

The critical constraint is that the conflict columns must have a unique or primary key index. Without one, MySQL runs the update against whatever row it finds, and PostgreSQL rejects the statement with there is no unique or exclusion constraint matching the ON CONFLICT specification. Verify with:

SHOW INDEX FROM products WHERE Non_unique = 0;

On PostgreSQL the equivalent is \d products in psql, which prints every index and its definition. Registration is implicit: you do not declare anything to Eloquent, because the guarantee comes from the schema.

Two behaviours surprise people. First, an eloquent upsert does not fire model events: no creating, no saved, no observers, no timestamps unless you set them. Second, on MySQL an ON DUPLICATE KEY UPDATE consumes auto-increment values for conflicting rows, so a table with a small integer primary key can exhaust its range faster than the row count suggests. PostgreSQL sequences behave the same way. Plan for it on wide imports.

Product::upsert(
    $rows,                              // array of arrays
    ['sku'],                            // conflict target
    ['price', 'stock', 'updated_at']    // columns to overwrite
);

Why updateOrCreate breaks under concurrency

Eloquent updateOrCreate runs a SELECT, then either an UPDATE or an INSERT. Those are separate round trips with no lock held between them, so two processes can both find nothing, both insert, and one hits the unique index. It is a read-modify-write race, and the smaller the gap between requests, the more often it fires.

You can see the window directly in the generated SQL:

select * from products where sku = 'A-100' limit 1;
insert into products (sku, price) values ('A-100', 19.99);

Between those two statements any other connection can run the same pair. On MySQL with the default REPEATABLE READ isolation, the SELECT is a non-locking consistent read, so it cannot even see an uncommitted insert from a competing transaction; the conflict only surfaces when the second statement tries to write. On PostgreSQL the same non-locking read gives identical behaviour.

The observable failure is a QueryException with SQLSTATE 23000 on MySQL or 23505 on PostgreSQL, thrown from the insert. Retrying the whole call helps, but only if the retry re-reads the row, which is why a naive retry(3, ...) around updateOrCreate sometimes appears to work and sometimes does not.

updateOrCreate is safe when a single writer owns the table: a console command, a queue with one worker, a migration. It is not safe when two HTTP requests can touch the same natural key.

Comparing the three write primitives

The count differences matter. On MySQL, an eloquent upsert returns 1 for an insert and 2 for an update, so you cannot use it to distinguish new rows. On PostgreSQL the number is a plain affected-row count. Neither tells you which rows were new, so if downstream logic depends on that, use a RETURNING clause through DB::select or split the batch by querying existing keys first.

Insert-or-ignore is not the same as upsert

insertOrIgnore silently drops conflicting rows, which is correct for idempotent ingestion where the first write wins. It is wrong when a later payload carries fresher data. The dangerous version of this bug is subtle: the import reports success, no exception is thrown, and the row keeps stale values because a retry of an older payload arrived first.

// First writer wins. Later payloads are discarded, not merged.
Product::insertOrIgnore($rows);

Choose an eloquent upsert when the newest payload should overwrite, and insert-or-ignore only when duplicates are guaranteed identical or intentionally frozen.

Deadlocks under concurrent upserts

Two transactions inserting the same set of keys in different orders can deadlock even with an eloquent upsert, because each statement acquires row locks as it walks the batch. MySQL reports error 1213; PostgreSQL reports 40P01. The fix is to sort each batch by the conflict key before writing so all writers take locks in the same order.

collect($rows)
    ->sortBy('sku')
    ->chunk(2000)
    ->each(fn ($chunk) => Product::upsert(
        $chunk->values()->all(),
        ['sku'],
        ['price', 'stock']
    ));

Keep transactions short around these batches. A long transaction holding thousands of row locks blocks unrelated readers on the same pages and turns one slow import into a site-wide stall. On PostgreSQL you can also watch waiting locks live with pg_locks joined against pg_stat_activity when you need to confirm the diagnosis.

Filling created_at and updated_at

Eloquent's automatic timestamps do not run inside an eloquent upsert. If your columns are NOT NULL without a default, the statement fails on insert. Set both keys yourself in the payload, or give the columns a database default such as CURRENT_TIMESTAMP. Setting them per row also lets you distinguish newly created records later, since the overwrite list controls which columns change on conflict:

$now = now();
$rows = array_map(fn ($r) => $r + [
    'created_at' => $now,
    'updated_at' => $now,
], $rows);

Note that including created_at in the overwrite list would reset it on every conflict, which is almost never what you want.

What to measure before and after

Wrap the change in a query-count assertion so a regression shows up in CI rather than in production. If you use DB::listen or Laravel's query log, an eloquent upsert import should log roughly one statement per chunk instead of two per row. A 10,000-row updateOrCreate loop logs at least 10,000 statements, often 20,000 if every row is a miss retried once.

The same query-count discipline applies to reads; see fixing Eloquent N+1 in Laravel for the read-side version of this problem. For background jobs that drive imports, pair the write primitive with the retry semantics described in Laravel queue best practices, since an upsert that is safe on its own is still vulnerable to a job that runs twice without an idempotency key.

Verify the behaviour on your own schema rather than trusting a general rule: run two PHP processes against the same key set, with the unique index in place, and check the row count and column values afterwards. The two references that describe the generated SQL across versions are the Laravel upsert documentation and the PostgreSQL INSERT page, which documents the ON CONFLICT clauses your statement compiles to.

Frequently asked questions

Does an Eloquent upsert fire model events?

No. It bypasses the model lifecycle entirely, including observers, mutators, and automatic timestamps. If downstream logic depends on created or saved events, dispatch an explicit event after the batch commits, or fall back to updateOrCreate when concurrency is not a concern.

Can I use an Eloquent upsert without a unique index?

No. PostgreSQL requires a unique or primary key index matching the conflict target and rejects the statement otherwise. MySQL relies on any unique index but behaves unpredictably without one, potentially updating unintended rows. Add the index before switching the write path.

How many rows should one upsert call handle?

Chunk between 1,000 and 5,000 rows. Larger statements risk packet-size limits and produce errors that do not identify the offending row, while very small chunks multiply round trips. Sorting within each chunk by conflict key also reduces deadlock frequency between concurrent writers.