Show Notes
Nikolay and Michael are joined by Andrew Johnson and Nate Brennand from Metronome to discuss MultiXact member space exhaustion — what it is, how they managed to hit it, and some tips to prevent running into it at scale.
Here are some links to things they mentioned: Nate Brennand https://postgres.fm/people/nate-brennand Andrew Johnson https://postgres.fm/people/andrew-johnson Metronome https://metronome.com Root Cause Analysis: PostgreSQL MultiXact member exhaustion incidents (blog post by Metronome) https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025 Multixacts and Wraparound (docs) https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND multixact.c source code https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.c Add pg_stat_multixact view for multixact membership usage monitoring (patch proposal by Andrew, needing review!) https://commitfest.postgresql.org/patch/5869/ PostgreSQL subtransactions considered harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful vacuum_multixact_failsafe_age doesn't account for MultiXact member exhaustion (thread started by Peter Geoghegan) https://www.postgresql.org/message-id/flat/CAH2-WzmLPWJk3gbAxy8dHY%2BA-Juz_6uGwfe6DkE8B5-dTDvLcw%40mail.gmail.com Amazon S3 Vectors https://aws.amazon.com/blogs/aws/introducing-amazon-s3-vectors-first-cloud-storage-with-native-vector-support-at-scale/ MultiXacts in PostgreSQL: usage, side effects, and monitoring (blog post by Shawn McCoy and Divya Sharma from AWS) https://aws.amazon.com/blogs/database/multixacts-in-postgresql-usage-side-effects-and-monitoring/ Postgres Aurora multixact monitoring queries https://gist.github.com/natebrennand/0924f723ff61fa897c4106379fc7f3dc And finally an apology and a correction, the membership space is ~4B, not ~2B as said by Michael in the episode! Definition here: https://github.com/postgres/postgres/blob/f6ffbeda00e08c4c8ac8cf72173f84157491bfde/src/include/access/multixact.h#L31 And here's the formula discussed for calculating how the member space can grow quadratically by the number of overlapping transactions: Members can be calculated via: aₙ = 2 + [sum from k=3 to n+1 of k] This simplifies to: aₙ = (((n+1)(n+2))/2) - 1
~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our Google doc!
~~~ Postgres FM is produced by: Michael Christofides, founder of pgMustard Nikolay Samokhvalov, founder of Postgres.ai
With special thanks to: Jessie Draws for the elephant artwork
More from Postgres FM
Nik is joined by Kirk Wolak, Software Architect at KiraSoft, to discuss what a database still needs after pg_restore finishes and before it takes production traffic — what pg_dump and pg_restore leave behind, why autovacuum may not catch up in time, and how Postgres itself could do better.
Nik and Michael are joined by Álvaro Hernández to discuss sharding Postgres with Citus and StackGres, especially the new query router support that has been added very recently.
Nik and Michael are joined by Salma El-Sayed and Kirk Wolak to discuss their in-progress and very ambitious Google Summer of Code project around adding Btree Merge support to Postgres.
Nik and Michael are joined by Sai Srirampur to discuss ClickHouse's new managed Postgres service.
Nik and Michael discuss hot_standby_feedback — what it controls, whether and when read replicas should be changed to on, and other settings you can change to help too.