Upserts at Scale with ON CONFLICT
Implement efficient merge logic for large batches while avoiding lock contention and bloat.
Why Upserts Need Care at Scale
An upsert inserts a row, but if it would collide with an existing key, it updates the existing row instead. PostgreSQL spells this INSERT ... ON CONFLICT.
For small workloads it is trivial. For ETL-sized batches (tens of thousands to millions of rows) the naive approach causes three problems:
- Lock contention — concurrent writers fighting over the same rows or index pages.
- Table bloat — every UPDATE writes a new row version (dead tuple) that VACUUM must later reclaim.
- WAL and round-trip overhead — row-by-row upserts multiply network and transaction cost.
This lesson builds a merge that is both correct and throughput-friendly.
The ON CONFLICT Shape
ON CONFLICT requires a conflict target: the column(s) or constraint that define a duplicate. PostgreSQL needs a unique or exclusion constraint on that target to arbitrate.
The two actions are DO NOTHING (skip the colliding row) and DO UPDATE (merge new values in).
Inside DO UPDATE, the incoming row is exposed through the special EXCLUDED pseudo-table.
INSERT INTO products (sku, name, price)
VALUES ('A-100', 'Widget', 9.99)
ON CONFLICT (sku) DO UPDATE
SET name = EXCLUDED.name,
price = EXCLUDED.price;All lessons in this course
- COPY vs Multi-Row INSERT Throughput
- Deferring Indexes and Constraints During Load
- Tuning WAL and Checkpoints for Ingestion
- Upserts at Scale with ON CONFLICT