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