When upserts don't update but still write: Debugging Postgres performance at scale

The Datadog team introduced a new upsert query to track inactive hosts, but it unexpectedly increased disk writes and WAL syncs due to row locking. By digging into Postgres's Write-Ahead Logging (WAL) and rewriting the query using a Common Table Expression (CTE), they avoided unnecessary overhead and improved performance. The new query eliminated the need for locking and minimized WAL activity and allowed for more efficient host tracking without sacrificing correctness.