SQL · Queries that survive scale
How do you perform an upsert safely?
Short answer
Use the database's atomic upsert, such as INSERT ON CONFLICT or MERGE, backed by a unique constraint. Avoid SELECT then INSERT or UPDATE, which races under concurrency.
An upsert inserts a row or updates it if a conflict occurs. The conflict target must be backed by a unique constraint or unique index. The operation is atomic, so two concurrent requests cannot both insert the same key.
INSERT INTO counters (user_id, action, count)
VALUES (42, 'login', 1)
ON CONFLICT (user_id, action)
DO UPDATE SET count = counters.count + 1
RETURNING count;- The conflict target must match a unique constraint or unique index
- DO NOTHING is useful when an existing row should be left unchanged
- DO UPDATE can reference the existing row through the table name
- MERGE is the SQL standard alternative supported by SQL Server, Oracle, and newer PostgreSQL versions
- An upsert does not replace the need to reason about concurrent increments
- Beware of triggers and returning clauses that fire on conflict paths
If the update is relative, such as count = count + 1, the database applies it atomically to the current row. If the update is absolute, last writer wins unless you add a version check. Choose based on whether the operation is commutative.
Follow-ups you should expect
Interviewers use these to check whether the first answer was understanding or memorisation.
- 1What does the conflict target require?
- 2When would you use DO NOTHING?
- 3How does MERGE differ from ON CONFLICT?
- 4What happens if two requests upsert the same key concurrently?
Say it in the interview
I use the database's atomic upsert and rely on a unique constraint to define the conflict. In PostgreSQL that is INSERT ON CONFLICT, and in other engines it may be MERGE. The unique constraint is essential because it prevents concurrent inserts from both succeeding. For counters I increment relative to the current value, and for absolute updates I add a version check if last-writer-wins is not acceptable.
Use this as a model, not a script. Saying it in your own words is what makes it survive follow-up pressure.
Related questions
Reading is step one
Now say it out loud.
Understanding an answer and delivering it under pressure are different skills. Rehearse this topic in a live SQL mock interview and get scored feedback on depth, clarity and confidence.