When upserts don't update but still write: Debugging Postgres performance at scale
Datadog needed to track when ephemeral hosts were last seen so inactive hosts could be deleted after seven days. A seemingly inexpensive PostgreSQL upsert caused disk writes to double and WAL syncs to quadruple, despite most operations not changing any data. Investigating the WAL revealed that conflict-handling upserts still lock conflicting rows and generate WAL activity, consuming the database’s limited write capacity. ## Tracking Host Activity Efficiently - Hosts stop reporting telemetry when they terminate, but Datadog has no direct termination signal. - Hosts inactive for seven days can be safely removed from the metadata store. - Updating the main host table on every observation would be too expensive because: - Large data centers generate more than 25,000 observations per second. - PostgreSQL MVCC creates a new row version for every update. - Updating the main table would rewrite all host metadata. - Datadog created a separate `host_last_ingested` table containing: - `host_id` as the primary key - `last_ingested` with a default timestamp - The table used `fillfactor=80` to leave page space for future updates. - No index was created on `last_ingested`, allowing updates to use Heap-Only Tuples (HOT) and avoid additional index writes. - Because only daily freshness was required, the timestamp needed to change at most once per day. ## The Conditional Upsert The initial query inserted a host if it did not exist and otherwise updated `last_ingested` only when the previous value was more than a day old: ```sql INSERT INTO host_last_ingested AS t VALUES ($1, now()) ON CONFLICT (host_id) DO UPDATE SET last_ingested = EXCLUDED.last_ingested WHERE t.last_ingested < EXCLUDED.last_ingested - '1 day'::interval; ``` - New hosts produced an insert. - Recently seen hosts matched the conflict but were expected to be no-ops because of the `WHERE` clause. - The team therefore expected most queries to avoid meaningful writes. ## Unexpected Disk and WAL Activity - During a gradual rollout at roughly 500 upserts per second: - Insertions initially increased as expected. - Actual updates remained mostly flat. - Write IOPS more than doubled. - WAL syncs increased by approximately the same amount. - This showed that the absence of an applied update did not mean the query was free. - Since PostgreSQL must flush WAL records at transaction commit, additional WAL activity directly increased disk pressure. - A PostgreSQL cluster’s single-writer design makes this write budget particularly important. ## Inspecting PostgreSQL WAL - PostgreSQL records database changes in its Write-Ahead Log, including table changes, index modifications, and related transaction activity. - The team used the `pg_walinspect` extension, available starting in PostgreSQL 15: ```sql CREATE EXTENSION pg_walinspect; ``` - Its `pg_get_wal_records_info` function allows inspection of WAL records between two Log Sequence Numbers (LSNs). - Examining the WAL helped explain why the conditional upsert generated writes even when the `WHERE` condition prevented the row update. - The underlying issue was that `ON CONFLICT DO UPDATE` still locks the conflicting row, and that locking activity is recorded in the WAL. The key lesson is that a PostgreSQL upsert that reports zero processed rows is not necessarily a true no-op. Conditional conflict updates can still create substantial WAL and locking overhead, so WAL inspection is essential when database write metrics do not match apparent update volume.
Read original(opens in new tab)