When upserts don't update but still write: Debugging Postgres performance at scale
Datadog tracks last-seen time for millions of ephemeral hosts so inactive ones can be deleted after 7 days. An ON CONFLICT DO UPDATE upsert meant to touch each host at most once a day still locked the conflicting row and wrote Heap LOCK plus COMMIT WAL records even when the WHERE clause skipped the update—at 500 upserts/sec that roughly doubled write IOPS and quadrupled WAL syncs, and at 25,000/sec it would overwhelm a single writer. Using pg_walinspect and a debugger on heap_lock_tuple, they replaced it with an ON CONFLICT DO NOTHING CTE plus a conditional UPDATE that generates no WAL on true no-ops, keeps HOT updates when a day has passed, and preserves correctness for coarse host tracking.
- Published
- 2026-03-23
- Scale
- 25,000 upserts/sec target in largest DCs, 500 upserts/sec doubled write IOPS and ~4x WAL syncs in a small DC rollout, gp3 EBS ~1,000 WAL syncs/sec ceiling via pg_test_fsync
- Techniques
- query optimization HOT updates WAL inspection write amplification
- Stack
- PostgreSQL pg_walinspect
Try this stack yourself
- Railway · Deploy something like this on Railway: spin up PostgreSQL plus your app in a few clicks. New accounts get $20 in credit (applied when you pick a paid plan).
Railway isn't part of Datadog's setup in this story. It's just an easy place to try the same building blocks.
Some links above are affiliate links: if you sign up, How They Scaled may earn a commission at no extra cost to you. We only list tools that fit the stack in the story.