Postgres FM

Nikolay talks to Michael about Postgres AI's new monitoring tool — what it is, how its different to other tools, and some of the thinking behind it. Here are some links to things they mentioned:postgres_ai monitoring https://gitlab.com/postgres-ai/postgres_aiDB Lab 4.0 announcement https://github.com/postgres-ai/database-lab-engine/releases/tag/v4.0.0pganalyze https://pganalyze.compostgres-checkup https://gitlab.com/postgres-ai/postgres-checkupPercona Monitoring and Management (PMM) https://github.com/percona/pmmpgwatch https://github.com/cybertec-postgresql/pgwatchpgwatch Postgres AI Edition https://gitlab.com/postgres-ai/pgwatch2libpg_query https://github.com/pganalyze/libpg_queryThe Four Golden Signals https://sre.google/sre-book/monitoring-distributed-systems/#xref_monitoring_golden-signalslogerrors https://github.com/munakoiso/logerrors~~~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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork

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-brennandAndrew Johnson https://postgres.fm/people/andrew-johnsonMetronome https://metronome.comRoot Cause Analysis: PostgreSQL MultiXact member exhaustion incidents (blog post by Metronome) https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025Multixacts and Wraparound (docs) https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUNDmultixact.c source code https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.cAdd 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-harmfulvacuum_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.comAmazon 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#L31And 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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith 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-sougoumaraneSupabase https://supabase.comAnnouncing Multigres https://supabase.com/blog/multigres-vitess-for-postgresVitess https://github.com/vitessio/vitessSPQR https://github.com/pg-sharding/spqrCitus https://github.com/citusdata/citusPgDog https://github.com/pgdogdev/pgdogMyths and Truths about Synchronous Replication in PostgreSQL (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=PFn9qRGzTMcConsensus algorithms at scale (8 part series by Sugu) https://planetscale.com/blog/consensus-algorithms-at-scale-part-1A More Flexible Paxos (blog post by Sugu) https://www.sougou.io/a-more-flexible-paxoslibpg_query https://github.com/pganalyze/libpg_queryPL/Proxy https://github.com/plproxy/plproxyPlanetScale Postgres Benchmarking https://planetscale.com/blog/benchmarking-postgresMultiXact 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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith 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-shapiraNile https://www.thenile.devSaaS 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.htmlCitus https://github.com/citusdata/citusPostgres.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-Z5JjwvCase 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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith 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-pgstatstatementspg_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_monitorstatement_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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith 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-WHATOur 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_checkpointslog_autovacuum_min_duration log_statementlog_connections and log_disconnectionslog_lock_waitslog_temp_fileslog_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.infohttps://postgresqlco.nfhttps://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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork

The discussion centers around moving away from managed PostgreSQL services like RDS, weighing the pros and cons of vendor lock-in versus the flexibility of open-source alternatives. Key migration strategies and tools, like logical replication and Change Data Capture, are highlighted to ease the process. Insights on managing large tables and minimizing downtime during transitions are shared. The speakers also touch on the evolution of managed services and emerging solutions in the open-source landscape.

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.htmlPostgres 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-WAITSHow 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.mdLock management (docs) https://www.postgresql.org/docs/current/runtime-config-locks.htmlOur 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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith 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:Heavy lock contentionBloat control and index maintenance  Lightweight lock contentionTransaction ID wraparound4-byte integer PKs hitting the limitReplication limitsHard limitsData lossPoor HA choice (split brain)Corruption of various kindsSome previous episodes they mentioned that cover the issues in more detail: PgDog https://postgres.fm/episodes/pgdogPerformance cliffs https://postgres.fm/episodes/performance-cliffsZero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations Queues in Postgres https://postgres.fm/episodes/queues-in-postgresBloat https://postgres.fm/episodes/bloatIndex maintenance https://postgres.fm/episodes/index-maintenanceSubtransactions https://postgres.fm/episodes/subtransactionsFour million TPS https://postgres.fm/episodes/four-million-tpsTransaction ID wraparound https://postgres.fm/episodes/transaction-id-wraparoundpg_squeeze https://postgres.fm/episodes/pg_squeeze synchronous_commit https://postgres.fm/episodes/synchronous_commitManaged 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=JcRi8Z7rkPgpg_stat_wal https://pgpedia.info/p/pg_stat_wal.htmlThe 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.htmlSplit-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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith 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-COMMITsynchronous_commit history on pgPedia https://pgpedia.info/s/synchronous_commit.htmlPatroni’s maximum_lag_on_failover setting https://patroni.readthedocs.io/en/master/replication_modes.html#asynchronous-mode-durabilitywal_writer_delay https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-WRITER-DELAYSelective 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.htmlsynchronous_standby_names https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMESJepson 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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork