MultiXacts in PostgreSQL: usage, side effects, and monitoring
summary
PostgreSQL creates MultiXacts when multiple transactions lock the same row and stores them in pg_multixact (offsets + members) on disk and SLRU cache; they are 32‑bit and hit wraparound at ~2.1 billion or when storage reaches ~20 GB (aggressive autovacuum starts near 10 GB). Common causes: foreign keys, select for share/for update, subtransactions and JDBC autosave. Monitor with age queries, pgstat_slru, aurora_stat_utils / rds extensions; mitigation is manual or forced vacuum freeze, reduce long transactions, tune multixact buffers and upgrade to PG17.
AI-written summary · about
chapters (12)
- 0:00Introduction
- 1:24Why monitor max used XIDs
- 3:12Tuple header and MultiXacts
- 6:05Operations using MultiXacts
- 8:15MultiXact storage and wraparound
- 12:29Autovacuum for MultiXacts
- 16:42Monitoring MultiXact usage
- 23:10SLRU optimizations in PG17
- 26:42MultiXacts vs standard XIDs
- 29:18Managing MultiXacts
- 33:16Summary and best practices
- 35:06Q&A session
related talks
31m5 Wrong Hypotheses about PostgreSQL Multi-Transaction Locks
49mConcurrency Deep-Dive
48mProblem in PostgreSQL SLRU - And how we are optimizing it
27mImplementing Strict Serializability with pg_xact
50mSupersized Postgres: Postgres-XL for OLTP and Big Data Analytics
33mPostgres Storytelling: Support in the Darkest Hour
48mInside Postgres: why VACUUM matters
28mUnderstanding Transaction Isolation for App Developers