At twenty past eleven on a Tuesday in June our order database stopped accepting writes. Reads worked. Every insert, update and delete failed with database is not accepting commands to avoid wraparound data loss.
Postgres numbers transactions with a 32 bit counter and compares them on a circle, about two billion either way. Old rows must be frozen by vacuum, marked as visible to everyone, before the counter comes round to them again. Autovacuum does this routinely, and urgently once a table's oldest unfrozen transaction is two hundred million old. If freezing still cannot keep up, Postgres refuses new transactions a few million short of the limit rather than risk corrupting anything.
Vacuum cannot freeze rows newer than the oldest transaction still open, because that transaction may still need to see them as they were. Ours had been open for twenty three days. A reporting service took a connection from its pool, began a transaction, hit an exception on a path that never rolled back, and returned the connection idle in transaction. The pool held twenty connections for a service that rarely used three, so that one sat at the back, kept alive by a health check every thirty seconds. At around a thousand transactions a second, twenty three days is close to two billion.
Autovacuum had been running anti wraparound vacuums on our largest tables for weeks, each one finishing and achieving nothing. Postgres had been logging warnings for eleven hours: database must be vacuumed within so many transactions. They went to the log, and nobody alerted on Postgres warnings.
We terminated the session, watched vacuum work through the four largest tables, and had writes back after two hours and ten minutes. Orders waited in the checkout's outbox in the meantime, so we lost none of them, only the time.
idle_in_transaction_session_timeout is now five minutes for every application role. The pool rolls back every connection before reuse. We alert when the oldest open transaction passes one hour, and when any database's transaction age passes six hundred million, which is weeks of warning instead of eleven hours. And Postgres log lines are counted by severity as a metric.
One idle connection used no CPU, no memory and no lock worth noticing. What it held was the database's ability to forget the past.
– Sergey Shinder
Top comments (1)
The health check detail is the part that hurts. A liveness ping kept that connection looking healthy, so every dashboard stayed green while the wraparound count kept going down. Did you go through the other services for the same exception path that skips the rollback, or only the reporting one?