Insert, update, delete, upsert

INSERT has nothing to find — it just builds the new row and stops. The plan is Insert on users straight over a Result node, cost close to 0.00..0.01.

UPDATE and DELETE have to locate the row before they can touch it. Because email is UNIQUE, PostgreSQL already has an index for it, so both plans show an Index Scan on that index underneath Update on users / Delete on users, costed around 0.15..8.17. That's higher than the insert, but still cheap — the same index-vs-scan tradeoff from the indexes lesson, just triggered by a write instead of a plain SELECT. With PostgreSQL's default cost constants (random_page_cost = 4), the 8.17 is mostly two page reads: one to read the matching entry in the email index, one to fetch the actual row from the table — 4 + 4, plus a sliver of cpu_tuple_cost for evaluating the condition and handing back the one row. The tiny 0.15 startup cost is just descending the index to find that first matching entry — small here because the table (and its index) only spans a page or two.

PostgreSQL has no dedicated UPSERT keyword. MySQL has INSERT ... ON DUPLICATE KEY UPDATE; SQL Server and Oracle have MERGE. PostgreSQL instead extends INSERT itself: ON CONFLICT lets that single statement insert a new row or update the existing one, depending on whether it already exists — atomically, with no race condition between a separate check and a follow-up write. The absence of this feature from Postgres has been a long-standing complaint from Postgres users [2] [3] [4] [5].

Refreshing an existing row is often written as a DELETE followed by an INSERT. It reaches the same end state as the upsert above, but not the same way under the hood — deleting marks the old row dead and writes the replacement somewhere new, while ON CONFLICT updates the existing row in place. A full comparison walks through the difference in detail, including the VACUUM cost DELETE + INSERT leaves behind.

A VACUUM is a cleanup process for rows marked as deleted — either run by hand (VACUUM users;) or triggered automatically in the background by autovacuum. It scans the table for those leftover rows and marks their space reusable, so the table doesn't just keep growing with every write.

VACUUM is needed because PostgreSQL internally implements MVCC (Multi-Version Concurrency Control): it never overwrites a row version in place, because some other transaction might still need to see the version that was there before. A DELETE just marks that row's version as no longer current — the bytes stay on the disk page as a dead tuple until VACUUM reclaims the space. An UPDATE (what ON CONFLICT DO UPDATE runs) leaves a dead tuple too — MVCC doesn't make an exception for it — but when the new version fits on the same page and no indexed column changed, it qualifies as a HOT (Heap-Only Tuple) update: no other index has to be touched, and PostgreSQL can often clean up that dead space on its own without waiting for a full VACUUM pass. A DELETE followed by a separate INSERT never gets that shortcut. Learn more in the dedicated lesson about MVCC.

Same scenario as the upsert above — deactivate Katherine Johnson — done two ways, with EXPLAIN (ANALYZE, BUFFERS) instead of a plain EXPLAIN. This actually runs the statement and reports real timing and page reads, not just an estimate.

Look at the Buffers line in each plan — it counts real page touches, not an estimate. DELETE + INSERT pays for two statements: a lookup through the email index to find the old row, then a whole new tuple written elsewhere in the table — with a new id, since the sequence never rewinds after a delete — and every index loses an entry and gains a new one. UPSERT pays for the same conflict-checking lookup, but updates the tuple it already found instead: same id, one statement, and (usually) no other index touched. Run this enough times and that gap compounds — here's what the article linked above measured on a 100k-row table, single-row operations:

In their benchmark, the UPSERT approach is ~2.9x faster and uses 50% fewer buffer hits than DELETE + INSERT.