Postgres FM

Nik and Michael are joined by Simon Eskildsen from turbopuffer — among other things, they discuss ANN index types, tradeoffs that can make sense for search workloads, and when it can make sense to move search out of Postgres. Here are some links to things they mentioned:Simon Eskildsen https://postgres.fm/people/simon-eskildsenturbopuffer https://turbopuffer.comUse ULID Idempotency Keys (tip 6 in this blog post from Shopify) https://shopify.engineering/building-resilient-payment-systemsPostgreSQL 18 Release Candidate 1 https://www.postgresql.org/about/news/postgresql-18-rc-1-released-3130Understanding DiskANN (blog post by Junaid Ahmed) https://www.tigerdata.com/blog/understanding-diskannSPFresh: Incremental In-Place Update for Billion-Scale Vector Search (paper) https://arxiv.org/abs/2410.14452Amazon S3 adds new functionality for conditional writes https://aws.amazon.com/about-aws/whats-new/2024/11/amazon-s3-functionality-conditional-writesAmazon S3 Vectors https://aws.amazon.com/s3/features/vectors~~~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

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-postgresJust Use Postgres for Everything (blog post by Stephan Schmidt) https://www.amazingcto.com/postgres-for-everythingReal-time analytics episode https://postgres.fm/episodes/real-time-analyticsCrunchy Data Joins Snowflake https://www.crunchydata.com/blog/crunchy-data-joins-snowflakeTwo sizes fit most: PostgreSQL and Clickhouse (blog post by Sid Sijbrandij) https://about.gitlab.com/blog/two-sizes-fit-most-postgresql-and-clickhousepg_duckdb episode https://postgres.fm/episodes/pg_duckdbCloudberry https://github.com/apache/cloudberryTime-series considerations episode https://postgres.fm/episodes/time-series-considerationsQueues in Postgres episode https://postgres.fm/episodes/queues-in-postgresLarge Objects https://www.postgresql.org/docs/current/largeobjects.html PGlite https://pglite.devParadeDB https://www.paradedb.comZomboDB https://github.com/zombodb/zombodbturbopuffer https://turbopuffer.comHNSW vs. DiskANN (blog post by Haziqa Sajid) https://www.tigerdata.com/learn/hnsw-vs-diskannSPANN: Highly-efficient Billion-scale Approximate Nearest Neighbor Search (paper) https://www.microsoft.com/en-us/research/wp-content/uploads/2021/11/SPANN_finalversion1.pdfAmazon S3 Vectors https://aws.amazon.com/s3/features/vectorsIterative Index Scans added to pgvector in 0.8.0 https://github.com/pgvector/pgvector/issues/678S3 FDW from Supabase https://github.com/supabase/wrappers/tree/main/wrappers/src/fdw/s3_fdw~~~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

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/1959468091035009245Postgres LISTEN/NOTIFY does not scale (blog post by Recall ai) https://www.recall.ai/blog/postgres-listen-notify-does-not-scaletrack_io_timing https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMINGpg_test_timing https://www.postgresql.org/docs/current/pgtesttiming.html PlanetScale for Postgres https://planetscale.com/blog/planetscale-for-postgresOut of disk episode https://postgres.fm/episodes/out-of-disk100TB episode https://postgres.fm/episodes/to-100tb-and-beyond Latency Numbers Every Programmer Should Know https://gist.github.com/jboner/2841832Fio https://github.com/axboe/fio~~~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

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.htmlOur episode on Index-only scans https://postgres.fm/episodes/index-only-scansCombining Multiple Indexes (docs) https://www.postgresql.org/docs/current/indexes-bitmap-scans.htmlEnable BUFFERS with EXPLAIN ANALYZE by default https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07“PostgreSQL includes an implementation of the standard btree […] The only limitation is that an index entry cannot exceed approximately one-third of a page” https://www.postgresql.org/docs/current/btree.htmlOur episode on HOT updates https://postgres.fm/episodes/hot-updatesOur episode on LIMIT vs Performance https://postgres.fm/episodes/limit-vs-performance~~~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 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-postgresSAE J3016 levels of driving automation https://www.sae.org/news/2019/01/sae-updates-j3016-automated-driving-graphicOracle Autonomous Database https://www.oracle.com/uk/autonomous-database/Self-Driving Database Management Systems (2017 paper) https://db.cs.cmu.edu/papers/2017/p42-pavlo-cidr17.pdfPGTune https://pgtune.leopard.in.ua/pg_index_pilot https://gitlab.com/postgres-ai/pg_index_pilot/[Vibe] Hacking Postgres with Andrey, Kirk, Nik – index bloat, btree page merge https://www.youtube.com/watch?v=D1PEdDcvZTw~~~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 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.htmlOur episode on over-indexing https://postgres.fm/episodes/over-indexingNondeterministic collations https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTICHow to migrate from Django’s PostgreSQL CI Fields to use a case-insensitive collation (blog post by Adam Johnson) https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collationThe collation versioning problem with ICU 73 (blog post by Daniel Vérité) https://postgresql.verite.pro/blog/2023/10/20/icu-73-versioning.htmlamcheck https://www.postgresql.org/docs/current/amcheck.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 pgMustardNikolay Samokhvalov, founder of Postgres.aiWith credit to:Jessie Draws for the elephant artwork

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