PostgreSQL gets atomic UPSERT
Summary
Peter Geoghegan implements INSERT ON CONFLICT, combining insertion and conflict handling in one concurrency-safe statement. UPSERT arrived with PostgreSQL 9.5. The syntax is INSERT ON CONFLICT.
Ideas
- A unique index detects colliding keys atomically.
- ON CONFLICT DO NOTHING discards expected duplicates.
- DO UPDATE changes the row that already exists.
- The database resolves races between parallel writers.
Insights
- Atomic database operations are safer than separate check-then-write sequences.
- Concurrency makes seemingly simple application logic error-prone.
- Unique constraints are integrity rules and synchronisation primitives at the same time.
Facts
- Conflict targets can use unique indexes.
Recommendations
- Define business uniqueness as a database constraint.
- Test concurrent writes rather than only individual cases.
References
Links to the original source and the Web Archive open in a new tab.