mediumSystem Design

Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?

Q: Advanced UPSERT patterns — conditional updates, xmax detection, and soft-delete-aware indexes?

Add `DO UPDATE … WHERE EXCLUDED.updated_at > users.updated_at` to keep newest-wins, use `RETURNING (xmax=0)` to tell insert from update, and match conflict targets to **partial unique indexes** for soft-delete tables. Advanced UPSERT: conditional update, RETURNING xmax trick, partial unique index Conditional DO UPDATE DO UPDATE SET … WHERE EXCLUDED.updated_at > t.updated_at keep newest wins avoid stale overwrite <rect x="290" y="35" width="240" heigh

1.2k views

Understand the problem

Build idempotent pipelines with WHERE on DO UPDATE, RETURNING to detect insert vs update, and partial indexes.

upsertidempotencyconstraints

Attempt it yourself

Sketch your approach before reading the solution — that's what interviews test.

Nudge consolestandby

Stuck? Beam a request up — the console returns a conceptual nudge that guides your logic without spoiling the implementation.

Study the solution

Add DO UPDATE … WHERE EXCLUDED.updated_at > users.updated_at to keep newest-wins, use RETURNING (xmax=0) to tell insert from update, and match conflict targets to partial unique indexes for soft-delete tables.

Solution ready — 2 min read

Classified // press E to declassify

Join the discussion

Discussion (0)

No responses yet. Be the first to share what you think.

Transmission complete // awaiting log

KEEP THE
STREAK ALIVE.

Dossier 15 of 70 decoded in the SQL track. One more won't hurt.

Back to track

Next up→

Covering indexes (INCLUDE) and Bitmap Heap Scans — how do you eliminate heap fetches in 2025?

Continue