Postgres Storytelling: Support in the Darkest Hour
summary
A Postgres instance hits transaction ID wraparound risk: a 4 TB, insert-only table (≈7 billion rows) drives oldest XID past autovacuum_freeze_max_age (~200M); a single 8-hour-long transaction and a cron job that requests new XIDs every minute block freezing. Investigation uses pg_database, pg_class, pg_stat_user_tables, pg_stat_activity and pg_locks; fixes include killing the stale backend, tuning autovacuum_cost_delay and autovacuum_vacuum_cost_limit, and upgrading from v13 to v14/15 so autovacuum can keep up.
AI-written summary · about
chapters (10)
- 0:00Introducing Boriss
- 1:40Boriss’ talk begins: Postgres storytelling
- 3:10About Monica de Bea
- 4:29The transaction wraparound problem
- 9:37Mammoths & frozen Postgres transactions
- 12:52Monitoring & table age
- 15:18Vacuum & autovacuum
- 16:23pg_stat_activity
- 18:16Investigating pg_locks
- 29:10Q&A
related talks
38mWhen it All Goes Wrong: Database Incident Response Dos and Don'ts
31m5 Wrong Hypotheses about PostgreSQL Multi-Transaction Locks
01h 07mNavigating Challenging Customer Escalations in Postgres
27mMaintaining Large Tables in PostgreSQL
33mPostgreSQL Observed—and Explained
42mPostgres: stories that will scare you
49mConcurrency Deep-Dive
32mOptimizing autovacuum: PostgreSQL’s vacuum cleaner