Postgres FM

Nik and Michael discuss when not to use Postgres — specifically use cases where it still makes sense to store data in another system.   Here are some links to things they mentioned: Just use Postgres (blog post by Ethan McCue) https://mccue.dev/pages/8-16-24-just-use-postgres Just Use Postgres for Everything (blog post by Stephan Schmidt) https://www.amazingcto.com/postgres-for-everything Real-tim...

Nik and Michael discuss disks in relation to Postgres — why they matter, how saturation can happen, some modern nuances, and how to prepare to avoid issues.   Here are some links to things they mentioned: Nik’s tweet demonstrating a NOTIFY hot spot https://x.com/samokhvalov/status/1959468091035009245 Postgres LISTEN/NOTIFY does not scale (blog post by Recall ai) https://www.recall.ai/blog/postgres...

Nik and Michael discuss multi-column indexes in Postgres — what they are, how to think about them, and some guidance around using them effectively.   Here are some links to things they mentioned: Multicolumn Indexes (docs) https://www.postgresql.org/docs/current/indexes-multicolumn.html Our episode on Index-only scans https://postgres.fm/episodes/index-only-scans Combining Multiple Indexes (docs)...

Nikolay and Michael discuss self-driving Postgres — what it could mean, using self-driving cars as a reference, and ideas for things to build and optimize for in this area.   Here are some links to things they mentioned: Nikolay’s blog post on Self-driving Postgres https://postgres.ai/blog/20250725-self-driving-postgres SAE J3016 levels of driving automation https://www.sae.org/news/2019/01/sae-up...

Nikolay and Michael discuss case-insensitive data — when we want to treat columns as case-insensitive, and the pros and cons of using citext, functions like lower(), or a custom collation.   Here are some links to things they mentioned: citext https://www.postgresql.org/docs/current/citext.html Our episode on over-indexing https://postgres.fm/episodes/over-indexing Nondeterministic collations http...

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-c...

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://me...

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://githu...

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/...

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/...

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 ht...

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.d...

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-block...

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 l...

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/synchr...

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 dep...

Nikolay and Michael discuss time-series considerations for Postgres — including when it matters, some tips for avoiding issues, performance considerations, and more.   Here are some links to things they mentioned: Time series data https://en.wikipedia.org/wiki/Time_series TimescaleDB https://github.com/timescale/timescaledb 13 Tips to Improve PostgreSQL Insert Performance https://www.timescale.com...

Nikolay and Michael are joined by Tomas Vondra to discuss single query performance cliffs — what they are, why they happen, some things we can do to make them less likely or less severe, and some potential improvements to Postgres that could help.   Here are some links to things they mentioned: Tomas Vondra https://postgres.fm/people/tomas-vondra Where do performance cliffs come from? (Talk by Tom...

Nikolay and Michael are joined by Lev Kokotov to discuss PgDog — including whether or when sharding is needed, the origin story (via PgCat), what's already supported, and what's coming next.      Here are some links to things they mentioned: Lev Kokotov https://postgres.fm/people/lev-kokotov PgDog https://github.com/pgdogdev/pgdog PgCat https://github.com/postgresml/pgcat Adopting PgCat (Instacart...

Nikolay talks Michael through using cloud snapshots — how they can be used to reduce RTO for huge Postgres setups, also to improve provisioning time, and some major catches to be aware of.   Here are some links to things they mentioned: Snapshots on RDS https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html pgBackRest https://pgbackrest.org WAL-G https://github.com/wal-g/w...

Nikolay and Michael discuss GIN indexes in Postgres — what they are, what they're used for, and some limitations to be aware of.   Here are some links to things they mentioned: GIN Indexes https://www.postgresql.org/docs/current/gin.html Generalized Search Trees for Database Systems (Hellerstein, Naughton, Pfeffer) https://dsf.berkeley.edu/papers/vldb95-gist.pdf   RUM extension https://pgxn.org/di...

Nikolay and Michael use a recent "best practices" article as a prompt — giving a few tips each on the topics mentioned, like schema design, performance, backups, and more.   Here are some links to things they mentioned: 7 Crucial PostgreSQL Best Practices (recent blog post) https://speakdatascience.com/postgresql-best-practices “Don't do this” episode https://postgres.fm/episodes/dont-do-this Arti...

Nikolay and Michael discuss the CREATE STATISTICS feature in Postgres — what it's for, how often it's used, and how to spot cases where it would help.    Here are some links to things they mentioned: CREATE STATISTICS https://www.postgresql.org/docs/current/sql-createstatistics.html citext https://www.postgresql.org/docs/current/citext.html Statistics Used by the Planner https://www.postgresql.org...

Nikolay and Michael are joined by Franck Pachot to discuss SQL vs NoSQL — did Franck change teams by joining MongoDB, normalisation vs denormalisation, developer experience, NULLs, and more!   Here are some links to things they mentioned: Franck Pachot https://postgres.fm/people/franck-pachot Franck's workshop at PGConf India https://pgconf.in/conferences/pgconfin2025/program/proposals/958   Postg...

Nikolay and Michael return to the topic of BUFFERS for the third (and final?) time! They discuss the news that it'll be on by default with EXPLAIN ANALYZE in Postgres 18, and what effect that might have.    Here are some links to things they mentioned: Our first BUFFERS episode https://postgres.fm/episodes/buffers-by-default Our second BUFFERS episode https://postgres.fm/episodes/buffers-ii-the-se...