MultiXact member space exhaustion

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 postgres.fm/people/nate-brennand
• Andrew Johnson postgres.fm/people/andrew-johnson
• Metronome metronome.com
• Root Cause Analysis: PostgreSQL MultiXact member exhaustion incidents (blog post by Metronome) metronome.com/blog/root-cause-analysis-postgre…
• Multixacts and Wraparound (docs) postgresql.org/docs/current/routine-vacuuming.…
• multixact.c source code github.com/postgres/postgres/blob/master/src/b…
• Add pg_stat_multixact view for multixact membership usage monitoring (patch proposal by Andrew, needing review!) commitfest.postgresql.org/patch/5869
• PostgreSQL subtransactions considered harmful (blog post by Nikolay) postgres.ai/blog/20210831-postgresql-subtransa…
• vacuum_multixact_failsafe_age doesn't account for MultiXact member exhaustion (thread started by Peter Geoghegan) postgresql.org/message-id/flat/CAH2-WzmLPWJk3g…
• Amazon S3 Vectors aws.amazon.com/blogs/aws/introducing-amazon-s3…
• MultiXacts in PostgreSQL: usage, side effects, and monitoring (blog post by Shawn McCoy and Divya Sharma from AWS) aws.amazon.com/blogs/database/multixacts-in-po…
• Postgres Aurora multixact monitoring queries gist.github.com/natebrennand/0924f723ff61fa897…
• And finally an apology and a correction, the membership space is ~4B, not ~2B as said by Michael in the episode! Definition here:
github.com/postgres/postgres/blob/f6ffbeda00e0…
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, (postgres.fm/people) or by commenting on our Google doc (docs.google.com/document/d/1PNGSn_d0A7gTR4C0p6…)!

~~~
Postgres FM is produced by:
• Michael Christofides, founder of pgMustard (pgmustard.com)
• Nikolay Samokhvalov, founder of Postgres.ai (postgres.ai)

With special thanks to:
• Jessie Draws for the elephant artwork