TechPrufer
Get started
Datadog
Datadog logoDatadog · Feature trail

Postgres Upsert Performance Optimization

1 post · Jun 2025 – Jun 2026

What this trail covers

Datadog's PostgreSQL high-availability architecture on Kubernetes has been re-architected for failover safety. The system now uses synchronous replication for failover candidates, coordinated by Patroni, to ensure durability and prevent data loss during network partitions or zonal failures. This involves reconfiguring the leader pool to prioritize data consistency over immediate availability when network latency spikes, ensuring that replicas are sufficiently up-to-date before a failover can occur. 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.

All Datadog trails
  1. When failover isn’t safe: Building high-availability PostgreSQL on Kubernetes | DatadogThe post details a gameday-induced failure mode where network latency between PostgreSQL primary and replica nodes caused replication lag, making failover unsafe and risking data loss. To address this, Datadog rearchitected its Kubernetes-based PostgreSQL clusters to use synchronous replication for failover candidates, coordinated by Patroni. This change prioritizes data consistency over immediate availability during network latency spikes, ensuring replicas are sufficiently up-to-date before failover. The baseline architecture, Patroni's role with ZooKeeper for coordination, and the trade-offs between availability and durability are discussed.
  2. When upserts don’t update but still write: Debugging Postgres performance at scale | DatadogThis 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.
  3. Breaking up a monolith: How we’re unwinding a shared database at scale | DatadogThis post details Datadog's strategy and process for unwinding a large, shared PostgreSQL database into independently owned instances. It outlines the challenges of shared databases, the prerequisites for successful decomposition (platform investments like Rapid and OrgStore, and a PG Proxy for traffic shifting), and a three-phase migration plan: logical separation (identifying data domains via DBM query samples and team collaboration), access reduction, and physical separation. The post emphasizes the importance of defining clear data domain boundaries and ensuring teams have the necessary tooling and platform support to manage their own database instances safely and scalably.