IL-0311 BattleMetrics · 2022-03-27database
BattleMetrics' primary PostgreSQL went read-only at transaction ID wraparound, breaking the website and RCON updates
BattleMetrics runs 5 Postgres servers, one primary and four read replicas, with autovacuum policies that had worked for years, but without a health check on the size of transaction IDs. About a month before the outage, for a reason BattleMetrics does not know, the index on its player identifiers table, built on the identifier type and the identifier and used heavily to match players joining a server, became corrupted. Site performance did not change, so nobody noticed, but from then on every vacuum job errored out, and transaction IDs climbed until the primary went read-only at 03:23 UTC on 27 March. The corruption had also reached the backup servers and the downloadable backups.
After a failover to a replica did not help, the team tried three recoveries in parallel: a full vacuum in single-user mode on the old primary, a restore from backup on a wiped server, and, following Google Cloud documentation, a vacuum without single-user mode on a third server with Postgres settings raised to give vacuum as many resources as possible. The third vacuum was cancelled after about 30 minutes with "failed to re-find parent key in index "player_identifiers_type_identifier_index" for deletion target page 615856", which explained why autovacuum had been failing. Dropping the index failed with the same wraparound error, so the team shut the database down, dropped the corrupted index in single-user mode, started it again and ran a vacuum that succeeded, after which the database accepted writes. The repair took about 3 hours, and rebuilding the index took about two hours.
BattleMetrics then shut down its query workers, gave RCON updates priority and pointed services at the new primary. Even so, the single server's CPU, disk and network were fully saturated: P50 RCON processing delay was two and a half minutes, P90 and P99 sat at their 5-minute limit, and some servers saw delays of up to two hours. The first new replica took 3 hours and 44 minutes to bootstrap because of the load on the primary and came online at 12:40 UTC, when 660 thousand updates were waiting. With more replicas the backlog caught up at 16:30 UTC, queries were restarted with throttling, player search indexing caught up at 16:46 UTC and Steam lookups at 17:53 UTC, and at 00:42 all replicas were back and queries ran at full speed.
- Severity
- –
- Scope
- The website, API and backend services that need Postgres, including RCON updates and queries; RCON updates ran heavily delayed for hours after the database was repaired
- Users affected
- BattleMetrics users; most servers kept RCON delay below 5 minutes on average, larger servers saw up to two hours
- Region
- –
- Time to detect
- –
- Time to resolve
- 21h 19m
Symptoms
At 03:23 UTC on 27 March 2022, BattleMetrics' primary PostgreSQL database, which holds all account, server and player information, went into read-only mode. Users first saw the website fail to load, and soon RCON updates and queries began to fail as well.
The Postgres logs repeated the following error thousands of times: "ERROR: database is not accepting commands to avoid wraparound data loss in database "battlemetrics"", with "HINT: Stop the postmaster and vacuum that database in single-user mode." and "You might also need to commit or roll back old prepared transactions, or drop stale replication slots." Postgres can take only about 2.1 billion transactions before it refuses write commands, and vacuuming normally keeps it away from that limit.
The failure surfaced gradually. Some systems kept working for a while without being able to write: the website uses API keys issued to users for up to 15 minutes, and while a key was valid the website still loaded and showed information. Reports grew as the keys expired.
About 30 minutes before the first problems, BattleMetrics' DDoS protection had activated against a small attack, so the website failures and then the failing RCON updates and queries were first taken to be part of that attack. Once the read-only database was found, a manual failover to a replica had no effect, because the transaction IDs close to wraparound and the index corruption, not yet found, had been replicated too.
Symptom Writes rejectedService outage
Root cause
Root cause Data loss or corruption
- Trigger
- A corruption of the index on the player identifiers table, which BattleMetrics' logs place at about 26 February 2022, made every vacuum job fail, so transaction IDs kept climbing until Postgres stopped accepting writes on 27 March.
Detection
Users failed to load the website and reported it as their API keys expired, then RCON updates and queries began to fail, and the team found that the main database no longer accepted writes.
Remediation
- Shut the database down and dropped the corrupted player identifiers index in single-user mode
- Ran the vacuum again, which succeeded and let the database accept writes
- Rebuilt the player identifier index, which took about two hours
- Shut down the query workers and gave RCON updates priority
- Pointed services at the new Postgres primary
- Bootstrapped new replicas one after another to take load off the primary
- Restarted queries with throttling, prioritised by server population and rank, after RCON updates had caught up
Action items
- Monitor errors in the Postgres logs
- Monitor and alert on transaction IDs
- Set up a logical replica in addition to physical replication
- Build a faster network to speed up recovery from failure
Lessons
BattleMetrics names skipping health checks on transaction ID size as its critical mistake: the index corruption silently broke vacuum for a month, and without monitoring of transaction IDs nobody saw the database approach wraparound. Because the corruption had been replicated to the replicas, failing over did not help, and the backup servers and downloadable backups carried the same corruption. The team also found that, contrary to the error message, a full vacuum on a server in wraparound protection does not need single-user mode, and chose to run a slow, delayed service over no service while replicas were rebuilt.
Sources
- https://learn.battlemetrics.com/article/64-march-27-2022-postgres-transacton-id-wraparound · blog · archived copy
References
- https://blog.sentry.io/2015/07/23/transaction-id-wraparound-in-postgres
- https://devcenter.heroku.com/articles/postgresql-concurrency
- https://dataegret.com/2017/10/deep-dive-into-postgres-stats-pg_stat_progress_vacuum/
- https://blog.crunchydata.com/blog/managing-transaction-id-wraparound-in-postgresql
- https://cloud.google.com/sql/docs/postgres/txid-wraparound
Related cases
IL-0516matrix.org homeserver rooms stopped working with "No create event in auth events" as a cleanup job deleted live stateMatrix.org Foundation · 2025same root cause · shared Database
Check your cluster for this
For AI tools and scripts
- This case as JSON: https://incidentlib-api.benchlet.com/cases/IL-0311
- The cases most like it: https://incidentlib-api.benchlet.com/cases/IL-0311/similar
- MCP server (Streamable HTTP, no key):
https://incidentlib-mcp.benchlet.com/mcp
The API needs no key. The MCP server offers search_incidents, get_incident, list_incidents, get_topic and vocab; both are explained on API and MCP.