Amey-Thakur Upsert patterns
The naive approach, checking whether a row exists and then inserting or updating, is a race in every concurrent system: two callers both check, both find nothing, and one insert fails or duplicates. Upsert exists so the database resolves that atomically.
Method
- Use the engine's conflict clause. ON CONFLICT, MERGE, or the equivalent performs the decision inside one statement, which is what makes it safe under concurrency.
- Target a real unique constraint. Conflict handling needs a constraint to detect against, so the uniqueness must exist in the schema rather than in your intent.
- Decide what update means on conflict. Overwrite everything, merge selected columns, or do nothing. Blindly overwriting can discard concurrent changes made between read and write (see transactions-isolation).
- Preserve creation metadata. created_at and similar columns should not be overwritten by the update branch, which is an easy detail to miss and hard to notice later.
- Make bulk upserts deterministic. A batch containing two rows with the same key needs a defined winner, since some engines error and others pick arbitrarily.
- Consider idempotency for retried writes. An upsert keyed on a natural or client-supplied key makes a retried request safe, which is what you want in any queue or API path (see idempotency).
Boundaries
- Upsert resolves existence races; it does not resolve semantic conflicts about which version of the data is correct.
- Conflict syntax and behaviour differ substantially between engines, so this is one of the less portable parts of SQL.
- Heavy upsert traffic on the same keys creates contention, which is a throughput problem rather than a correctness one.