אודות פרק זה
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
אנגלית
ארצות הברית
בפרק זה
תעתיק 🔗
1
00:00:00,060 --> 00:00:02,060
Nikolay: Hello, hello, this is
Postgres.FM.
2
00:00:02,260 --> 00:00:04,700
This is Nik from Postgres.AI.
3
00:00:05,020 --> 00:00:06,820
And as usual, co-host Michael.
4
00:00:06,820 --> 00:00:07,580
Hi, Michael.
5
00:00:07,900 --> 00:00:08,520
Michael: Hi, Nik.
6
00:00:08,520 --> 00:00:08,720
Nikolay: Yeah.
7
00:00:08,720 --> 00:00:12,380
And we invited a couple of guests
today, Nate and Andrew.
8
00:00:12,660 --> 00:00:17,880
And we invited because the c…
00:00:00,060 --> 00:00:02,060
Nikolay: Hello, hello, this is
Postgres.FM.
2
00:00:02,260 --> 00:00:04,700
This is Nik from Postgres.AI.
3
00:00:05,020 --> 00:00:06,820
And as usual, co-host Michael.
4
00:00:06,820 --> 00:00:07,580
Hi, Michael.
5
00:00:07,900 --> 00:00:08,520
Michael: Hi, Nik.
6
00:00:08,520 --> 00:00:08,720
Nikolay: Yeah.
7
00:00:08,720 --> 00:00:12,380
And we invited a couple of guests
today, Nate and Andrew.
8
00:00:12,660 --> 00:00:17,880
And we invited because the c…
הצהרה: הפודקאסט והגרפיקה המוטבעים בדף זה הם מ-Nikolay Samokhvalov and Michael Christofides, שהוא רכושו של בעליו ואינו קשור או מאושר על ידי Listen Notes, Inc.