TechPrufer
Sign inGet started

Blogs›Datadog›Postgres Upsert Performance Optimization

Postgres Upsert Performance Optimization

Postgres Upsert Performance Optimization

1
posts
2026

This post details the debugging and resolution of a performance issue in Datadog's Postgres database related to an upsert query for tracking host ingestion times. The problem stemmed from unexpected Write-Ahead Logging (WAL) activity and increased disk writes, even when upserts resulted in no actual data modification. The solution involved analyzing WAL records using pg_walinspect and optimizing the query to leverage Heap-Only (HOT) updates by ensuring updates only modified unindexed columns and by setting an appropriate fillfactor for the table. This significantly reduced write amplification and WAL syncs, improving overall database performance.

2026

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

3/23/2026

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.