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

Summary

At Datadog, we track the life cycle of millions of ephemeral hosts that report telemetry data to our platform. When a host stops emitting data, we eventually need to clean it up to avoid bloating our metadata store. To detect inactive hosts, the Datadog team that manages the host metadata store introduced a new upsert to track the last time a host was seen. We expected this new query to have minimal impact. Each host would be updated at most once a day, so even at 25,000 upserts per second, most queries should have been no-ops. But when we rolled out the new query, disk writes doubled and Write-Ahead Logging (WAL) syncs quadrupled. We discovered that even when an upsert doesn’t change any values, it still locks the conflicting row, which is recorded in the WAL. Given that a Postgres cluster can only have a single writer, there’s a hard limit to how many writes it can handle. The increase in disk writes introduced by the new query was consuming too much of this limited budget and had to be fixed. In this post, we’ll walk through how we diagnosed the unexpected overhead by inspecting Postgres’s WAL and how we rewrote the query to eliminate the cost without sacrificing correctness. When the Datadog Agent runs, it collects host metadata—such as host_id, availability zone, and tags—and sends it to Datadog, where we store it in a Postgres database. Because hosts have a limited lifetime, they eventually terminate and stop emitting metrics. At that point, we can safely delete them from the database to keep it from growing indefinitely. However, we don’t have a specific signal when a host has been terminated, other than the fact that it has stopped sending data. We decided that if a host hasn’t emitted metrics for 7 days, we can safely delete it. To support that, we needed a way to track the last time a host was seen. Updating a row every time we see a host is prohibitively expensive: In our largest data centers, this means more than 25,000 updates per second. We needed to d...