Postgres FM

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 

Nikolay and Michael are joined by Sugu Sougoumarane to discuss Multigres — a project he's joined Supabase to lead, building an adaptation of Vitess for Postgres!
 
Here are some links to things they mentioned:

• Sugu Sougoumarane https://postgres.fm/people/sugu-sougoumarane
• Supabase https://supabase.com
• Announcing Multigres https://supabase.com/blog/multigres-vitess-for-postgres
• Vitess https://github.com/vitessio/vitess
• SPQR https://github.com/pg-sharding/spqr
• Citus https://github.com/citusdata/citus
• PgDog https://github.com/pgdogdev/pgdog
• Myths and Truths about Synchronous Replication in PostgreSQL (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=PFn9qRGzTMc
• Consensus algorithms at scale (8 part series by Sugu) https://planetscale.com/blog/consensus-algorithms-at-scale-part-1
• A More Flexible Paxos (blog post by Sugu) https://www.sougou.io/a-more-flexible-paxos
• libpg_query https://github.com/pganalyze/libpg_query
• PL/Proxy https://github.com/plproxy/plproxy
• PlanetScale Postgres Benchmarking https://planetscale.com/blog/benchmarking-postgres
• MultiXact member exhaustion incidents (blog post by Cosmo Wolfe / Metronome) https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025

~~~

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 

Nikolay and Michael are joined by Gwen Shapira to discuss multi-tenant architectures — the high level options, the pros and cons of each, and how they're trying to help with Nile.
 
Here are some links to things they mentioned:

• Gwen Shapira https://postgres.fm/people/gwen-shapira
• Nile https://www.thenile.dev
• SaaS Tenant Isolation Strategies (AWS whitepaper) https://docs.aws.amazon.com/whitepapers/latest/saas-tenant-isolation-strategies/saas-tenant-isolation-strategies.html 
• Row Level Security https://www.postgresql.org/docs/current/ddl-rowsecurity.html
• Citus https://github.com/citusdata/citus
• Postgres.AI Bot https://postgres.ai/blog/20240127-postgres-ai-bot 
• RLS Performance and Best Practices https://supabase.com/docs/guides/troubleshooting/rls-performance-and-best-practices-Z5Jjwv
• Case Gwen mentioned about the planner thinking an optimisation was unsafe 
• Re-engineering Postgres for Millions of Tenants (Gwen’s recent talk at PGConf.dev) https://www.youtube.com/watch?v=EfAStGb4s88 
• Multi-tenant database the good, the bad, the ugly (talk by Pierre Ducroquet at PgDay Paris) https://www.youtube.com/watch?v=4uxuPfSvTGU

~~~

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 

Nikolay and Michael discuss looking at queries by mean time — when it makes sense, why ordering by a percentile (like p99) might be better, and the merits of approximating percentiles in pg_stat_statements using the standard deviation column.
 
Here are some links to things they mentioned:

• Approximate the p99 of a query with pg_stat_statements (blog post by Michael) https://www.pgmustard.com/blog/approximate-the-p99-of-a-query-with-pgstatstatements
• pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html 
• Our episode about track_planning https://postgres.fm/episodes/pg-stat-statements-track-planning 
• pg_stat_monitor https://github.com/percona/pg_stat_monitor
• statement_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nikolay and Michael discuss logging in Postgres — mostly what to log, and why changing quite a few settings can pay off big time in the long term.
 
Here are some links to things they mentioned:

• What to log https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
• Our episode about Auditing https://postgres.fm/episodes/auditing 
• Our episode on auto_explain https://postgres.fm/episodes/auto_explain 

Here are the parameters they mentioned changing:

• log_checkpoints
• log_autovacuum_min_duration 
• log_statement
• log_connections and log_disconnections
• log_lock_waits
• log_temp_files
• log_min_duration_statement 
• log_min_duration_sample and log_statement_sample_rate 

And finally, some very useful tools they meant to mention but forgot to!   

• https://pgpedia.info
• https://postgresqlco.nf
• https://why-upgrade.depesz.com/show?from=16.9&to=17.5 

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nikolay and Michael discuss moving off managed services — when and why you might want to, and some tips on how for very large databases.
 
Here are some links to things they mentioned:

• Patroni https://github.com/patroni/patroni
• pgBackRest https://github.com/pgbackrest/pgbackrest
• WAL-G https://github.com/wal-g/wal-g
• Hetzner Cloud https://www.hetzner.com/cloud
• Postgres Extensions Day https://pgext.day
• pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
• pg_stat_kcache https://github.com/powa-team/pg_stat_kcache
• auto_explain https://www.postgresql.org/docs/current/auto-explain.html
• Fivetran https://www.fivetran.com
• pgcopydb https://github.com/dimitri/pgcopydb
• Kafka https://kafka.apache.org
• Debezium https://debezium.io
• max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE
• log_statement DDL https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT
• PgBouncer pause/resume https://www.pgbouncer.org/usage.html#pause-db

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nikolay and Michael discuss heavyweight locks in Postgres — how to think about them, why you can't avoid them, and some tips for minimising issues.
 
Here are some links to things they mentioned:

• Locking (docs) https://www.postgresql.org/docs/current/explicit-locking.html
• Postgres rocks, except when it blocks (blog post by Marco Slot) https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/
• Lock Conflicts (tool by Hussein Nasser) https://pglocks.org/
• log_lock_waits (docs) https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LOCK-WAITS
• How to analyze heavyweight lock trees (guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0042_how_to_analyze_heavyweight_locks_part_2.md
• Lock management (docs) https://www.postgresql.org/docs/current/runtime-config-locks.html
• Our episode on zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nikolay and Michael discuss ten dangerous Postgres related issues — ones that might be painful enough to get onto the CTO and even CEOs desk, and then what you can do proactively.
 
The ten issues discussed are:

1. Heavy lock contention
2. Bloat control and index maintenance  
3. Lightweight lock contention
4. Transaction ID wraparound
5. 4-byte integer PKs hitting the limit
6. Replication limits
7. Hard limits
8. Data loss
9. Poor HA choice (split brain)
10. Corruption of various kinds

Some previous episodes they mentioned that cover the issues in more detail: 

• PgDog https://postgres.fm/episodes/pgdog
• Performance cliffs https://postgres.fm/episodes/performance-cliffs
• Zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations 
• Queues in Postgres https://postgres.fm/episodes/queues-in-postgres
• Bloat https://postgres.fm/episodes/bloat
• Index maintenance https://postgres.fm/episodes/index-maintenance
• Subtransactions https://postgres.fm/episodes/subtransactions
• Four million TPS https://postgres.fm/episodes/four-million-tps
• Transaction ID wraparound https://postgres.fm/episodes/transaction-id-wraparound
• pg_squeeze https://postgres.fm/episodes/pg_squeeze 
• synchronous_commit https://postgres.fm/episodes/synchronous_commit
• Managed service support https://postgres.fm/episodes/managed-service-support

And finally, some other things they mentioned: 

• A great recent SQL Server-related podcast episode on tuning techniques https://kendralittle.com/2024/05/20/erik-darling-and-kendra-little-rate-sql-server-performance-tuning-techniques/
• Postgres Indexes, Partitioning and LWLock:LockManager Scalability (blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/
• Do you vacuum everyday? (talk by Hannu Krosing) https://www.youtube.com/watch?v=JcRi8Z7rkPg
• pg_stat_wal https://pgpedia.info/p/pg_stat_wal.html
• The benefit of lz4 and zstd for Postgres WAL compression (Small Datum blog, Mark Callaghan) https://smalldatum.blogspot.com/2022/05/the-benefit-of-lz4-and-zstd-for.html
• Split-brain in case of network partition (CloudNativePG issue/discussion) https://github.com/cloudnative-pg/cloudnative-pg/discussions/7462
   

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nikolay and Michael discuss synchronous_commit — what it means on single node setups, for synchronous replication setups, and the pros and cons of the different options for each.
 
Here are some links to things they mentioned:

• synchronous_commit https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
• synchronous_commit history on pgPedia https://pgpedia.info/s/synchronous_commit.html
• Patroni’s maximum_lag_on_failover setting https://patroni.readthedocs.io/en/master/replication_modes.html#asynchronous-mode-durability
• wal_writer_delay https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-WRITER-DELAY
• Selective asynchronous commits in PostgreSQL - balancing durability and performance (blog post by Shayon Mukherjee) https://www.shayon.dev/post/2025/75/selective-asynchronous-commits-in-postgresql-balancing-durability-and-performance/
• Asynchronous Commit https://www.postgresql.org/docs/current/wal-async-commit.html
• synchronous_standby_names https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES
• Jepson article about Amazon RDS multi-AZ clusters (by Kyle Kingsbury, aka "Aphyr”) https://jepsen.io/analyses/amazon-rds-for-postgresql-17.4

~~~

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 credit to:

• Jessie Draws for the elephant artwork

Nikolay and Michael discuss managed service support — some tips on how to handle cases that aren't going well, tips for requesting features, whether to factor in support when choosing service provider, and whether to use one at all.
 
Here are some links to things they mentioned:

• YugabyteDB’s new upgrade framework https://www.yugabyte.com/blog/postgresql-upgrade-framework
• Episode on Blue-green deployments https://postgres.fm/episodes/blue-green-deployments
• pg_createsubscriber https://www.postgresql.org/docs/current/app-pgcreatesubscriber.html

~~~

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 credit to:

• Jessie Draws for the elephant artwork