Postgres FM

Latest Episodes:

Monitoring from Postgres AI Jul 25, 2025

Show notes

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_ai
DB Lab 4.0 announcement https://github.com/postgres-ai/database-lab-engine/releases/tag/v4.0.0
pganalyze https://pganalyze.com
postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup
Percona Monitoring and Management (PMM) https://github.com/percona/pmm
pgwatch https://github.com/cybertec-postgresql/pgwatch
pgwatch Postgres AI Edition https://gitlab.com/postgres-ai/pgwatch2
libpg_query https://github.com/pganalyze/libpg_query
The Four Golden Signals https://sre.google/sre-book/monitoring-distributed-systems/#xref_monitoring_golden-signals
logerrors 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 pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With credit to:

Jessie Draws for the elephant artwork

MultiXact member space exhaustion Jul 18, 2025

Show notes

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

Multigres Jul 11, 2025

Show notes

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

Multi-tenant options Jun 20, 2025

Show notes

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

Mean vs p99 Jun 13, 2025

Show notes

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

What to log Jun 06, 2025

Show notes

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!

~~~

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

How to move off RDS May 30, 2025

Show notes

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

Locks May 23, 2025

Show notes

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

Top ten dangerous issues May 09, 2025

Show notes

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 contention
Bloat control and index maintenance
Lightweight lock contention
Transaction ID wraparound
4-byte integer PKs hitting the limit
Replication limits
Hard limits
Data loss
Poor HA choice (split brain)
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

synchronous_commit May 02, 2025

Show notes

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: