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

What this post added

This post describes the debugging process for a Postgres performance issue caused by an upsert query. It details how an upsert, even when not modifying data, was generating significant Write-Ahead Logging (WAL) activity and increasing disk writes. The post explains the use of the `pg_walinspect` extension to analyze WAL records and identify the root cause. It then presents the optimized table schema and upsert query designed to leverage Heap-Only (HOT) updates by ensuring updates only affected unindexed columns and by setting a `fillfactor` of 80% to encourage in-place updates. The solution effectively reduced WAL syncs and write IOPS without sacrificing correctness.