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:

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:

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.