When upserts don’t update but still write: Debugging Postgres performance at scale | Datadog

This post details the debugging of an upsert query that unexpectedly doubled disk writes and quadrupled WAL syncs by writing to WAL even when no data changed, due to row locking. The solution involved creating a dedicated table for `last_ingested` timestamps and optimizing the upsert query to leverage Heap-Only Tuples (HOT) updates by ensuring updates only modify unindexed columns and by setting an appropriate fillfactor, thereby reducing write amplification and WAL activity.