Postgres FM

Nikolay and Michael discuss Index-Only Scans in Postgres — what they are, how they help, some things to look out for, and some advice. Here are some links to things they mentioned:Index-Only Scans and Covering Indexes (docs) https://www.postgresql.org/docs/current/indexes-index-only-scans.htmlDiscussion on Twitter about JIT and Parallel Query defaults https://x.com/jer_s/status/1819749688184373742Postgres Wiki on Index-Only Scans https://wiki.postgresql.org/wiki/Index-only_scansHeap Fetches https://www.pgmustard.com/docs/explain/heap-fetchesRows Removed By Filter https://www.pgmustard.com/docs/explain/rows-removed-by-filterrandom_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/pg_dump docs change https://x.com/samokhvalov/status/1820539826363588755Crunchy Bridge changed random_page_cost to 1.1 for new servers https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1Autovacuum Tuning Basics (updated blog post by Tomas Vondra) https://www.enterprisedb.com/blog/autovacuum-tuning-basicsOur episode on over-indexing https://postgres.fm/episodes/over-indexingOur episode on HOT updates https://postgres.fm/episodes/hot-updatesOur episode on partitioning https://postgres.fm/episodes/partitioning~~~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 why they chose Postgres — as users, for their businesses, for their careers, as well as some doubts. Here are some links to things they mentioned:Our episode on why Postgres become popular https://postgres.fm/episodes/why-is-postgres-popularDatabase Systems: The Complete Book (by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom) http://infolab.stanford.edu/~ullman/dscb.htmlOur episode on the Postgres startup ecosystem https://postgres.fm/episodes/postgres-startup-ecosystemWill Postgres Live Forever? (talk by Bruce Momjian) https://www.youtube.com/watch?v=iYVxWpyaGpA "Anarcho-Syndicalist Commune" from Monty Python and the Holy Grail https://www.youtube.com/watch?v=R7qT-C-0ajIThe Cathedral and the Bazaar: Musings on Linux and Open Source by an Accidental Revolutionary (book by Eric S. Raymond) https://en.wikipedia.org/wiki/The_Cathedral_and_the_Bazaar ~~~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 compression in Postgres — what's available natively, newer algorithms in recent versions, and several extensions with compression features. Here are some links to things they mentioned:wal_compression https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-COMPRESSIONOur episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningSynthetic wal_compression and streaming replication wal size test https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/11default_toast_compression https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION ZFS https://en.wikipedia.org/wiki/ZFSOur episode on TOAST https://postgres.fm/episodes/toastOn compression of everything in Postgres (talk by Andrey Borodin) https://learn.microsoft.com/en-us/shows/cituscon-an-event-for-postgres-2023/on-compression-of-everything-in-postgres-citus-con-2023cstore_fdw https://citusdata.github.io/cstore_fdw/Using Hydra columnar https://columnar.docs.hydra.so/concepts/using-hydra-columnarAbout compression in Timescale https://docs.timescale.com/use-timescale/latest/compression/about-compression/pg_tier https://github.com/tembo-io/pg_tierpgBackRest https://pgbackrest.org/WAL-G https://github.com/wal-g/wal-gpg_dump https://www.postgresql.org/docs/current/app-pgdump.htmlpg_dump compression specifications in PostgreSQL 16 (article by Pablo Glob from Cybertec) https://www.cybertec-postgresql.com/en/pg_dump-compression-specifications-postgresql-16/Our episode on pgvector (with Jonathan Katz) https://postgres.fm/episodes/pgvector ~~~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 Postgres running out of disk space — including what happens, what can cause it, how to recover, and most importantly, how to prevent it from happening in the first place. Here are some links to things they mentioned:Disk Full (docs) https://www.postgresql.org/docs/current/disk-full.htmlpgcompacttable https://github.com/dataegret/pgcompacttable Our episode on massive deletes https://postgres.fm/episodes/massive-deletes Getting Rid of Data (slides from VLDB 2019 keynote by Tova Milo)pg_tier https://github.com/tembo-io/pg_tier Data tiering in Timescale Cloud https://docs.timescale.com/use-timescale/latest/data-tiering/ Postgres is Out of Disk and How to Recover (blog post by Elizabeth Christensen) https://www.crunchydata.com/blog/postgres-is-out-of-disk-and-how-to-recover-the-dos-and-donts max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE Our episode on checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning Aiven docs on full disk issues https://aiven.io/docs/products/postgresql/howto/prevent-full-disk  ~~~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 the Postgres startup ecosystem — some recent closures, some recent fundraising announcements, and their thoughts on where things are going and what they'd like to see. Here are some links to things they mentioned:Prediction from Dax Raad https://x.com/thdxr/status/1808972166752580039OtterTune shut down https://x.com/andy_pavlo/status/1801687420330770841Snaplet shutting down https://www.snaplet.dev/post/snaplet-is-shutting-downbit.io shut down https://blog.bit.io/whats-next-for-bit-io-joining-databricks-ace9a40bce0d?gi=8ef885454eefTimescale acquired PopSQL https://www.timescale.com/blog/best-postgresql-gui-popsql-joins-timescaleAiven https://aiven.ioHasura https://hasura.ioSupabase https://supabase.comNeon https://neon.techTembo https://tembo.ioFerretDB https://www.ferretdb.comHydra https://www.hydra.sopgEdge https://www.pgedge.comTembo raised $14m https://techcrunch.com/2024/07/08/database-startup-tembo-lands-new-cash-to-expandRy Walker’s Cybertruck with STARTUP license plate https://x.com/rywalker/status/1810061804380557516 Supabase acquired OrioleDB https://supabase.com/blog/supabase-acquires-orioleMichael Stonebraker Turing Award Lecture https://www.youtube.com/watch?v=BbGeKi6T6QIMicrosoft acquired Citus https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquires-citus-data-re-affirming-its-commitment-to-open-source-and-accelerating-azure-postgresql-performance-and-scaleCrunchy Bridge https://www.crunchydata.com/products/crunchy-bridgePeerDB https://www.peerdb.ioParadeDB https://www.paradedb.compganalyze https://pganalyze.comDBeaver https://dbeaver.ioPostico / Egger Apps https://eggerapps.at/postico2Postgres Compare https://www.postgrescompare.comCoroot https://coroot.comokmeter https://okmeter.ioSlides from Nikolay’s talk on monitoring https://bit.ly/pg-monitoring Nile https://www.thenile.devUbicloud https://www.ubicloud.com~~~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 talks Michael through a recent experiment to find the current maximum transactions per second single-node Postgres can achieve — why he was looking into it, what bottlenecks occurred along the way, and ideas for follow up experiments. Here are some links to things they mentioned:How many TPS can we get from a single Postgres node? (Article by Nikolay) https://www.linkedin.com/pulse/how-many-tps-can-we-get-from-single-postgres-node-nikolay-samokhvalov-yu0rcChat history with Postgres AI bot https://postgres.ai/chats/01905a83-4573-7dca-b47a-bb60ce30fe6cOur episode on the overhead of pg_stat_statements and pg_stat_kcache https://postgres.fm/episodes/overhead-of-pg_stat_statements-and-pg_stat_kcachePostgreSQL 17 beta 2 is out https://www.postgresql.org/about/news/postgresql-17-beta-2-released-2885/ PostgreSQL and MySQL: Millions of Queries per Second (about the work by Sveta and Alexander) https://www.percona.com/blog/millions-queries-per-second-postgresql-and-mysql-peaceful-battle-at-modern-demanding-workloadspostgresql_cluster https://github.com/vitabaks/postgresql_clusterTrack on CPU events for pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling/pull/74The year of the Lock Manager’s Revenge (post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability Pluggable cumulative statistics (Postgres hackers thread started by Michael Paquier) https://www.postgresql.org/message-id/flat/Zmqm9j5EO0I4W8dx%40paquier.xyz ~~~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 soft deletion in Postgres — what it means, several use cases, some implementation options, and which implementations suit which use cases. Here are some links to things they mentioned:Soft deletion probably isn't worth it (blog post by Brandur) https://brandur.org/soft-deletionEasy alternative soft deletion (blog post by Brandur) https://brandur.org/fragments/deleted-record-insertOur episode on auditing https://postgres.fm/episodes/auditingCREATE FUNCTION … SECURITY DEFINER (docs) https://www.postgresql.org/docs/current/sql-createfunction.htmlPrinciple of least privilege https://en.wikipedia.org/wiki/Principle_of_least_privilege~~~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 

Dive into the world of foreign keys in PostgreSQL! The discussion highlights their crucial role in maintaining data integrity while weighing the benefits against potential performance issues. Strategies for managing foreign keys during heavy loads and upcoming Postgres 17 features take center stage. Real-world performance tests show minimal impact in typical scenarios. The podcast also tackles the complexities of managing inserts and updates, and addresses the dual nature of foreign keys in transaction management, emphasizing latency over mere transaction counts.

Nikolay is joined by Mat Arye and John Pruitt, from Timescale, to discuss their new extension pgvectorscale and high-performance vector search in Postgres more generally. Main links:https://github.com/timescale/pgvectorscalehttps://www.timescale.com/blog/pgvector-vs-pineconehttps://postgres.fm/people/matvey-aryehttps://postgres.fm/people/john-pruitt~~~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:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the elephant artwork 

Michael and Nikolay are joined by three special guests for episode 100 who have all scaled Postgres to significant scale — Arka Ganguli from Notion, Sammy Steele from Figma, and Derk van Veen from Adyen. They cover how their setup has evolved, what their plans are for the future, and get into the weeds of some fun and interesting challenges along the way!Links to some of the things discussed: Arka Ganguli from Notion https://postgres.fm/people/arka-ganguliSammy Steele from Figma https://postgres.fm/people/sammy-steeleDerk van Veen from Adyen https://postgres.fm/people/derk-van-veenThank you to yerrysherry on Reddit for the idea! https://www.reddit.com/r/PostgreSQL/comments/1cn8ajh/what_should_we_do_for_episode_100_of_postgres_fmLessons learned from sharding Postgres at Notion (October 2021) https://www.notion.so/blog/sharding-postgres-at-notionAdding Postgres capacity (again) with zero downtime (July 2023) https://www.notion.so/blog/the-great-re-shardHow Figma’s databases team lived to tell the scale (March 2024) https://www.figma.com/blog/how-figmas-databases-team-lived-to-tell-the-scaleUpdating a 50 terabyte PostgreSQL database (March 2018) https://www.adyen.com/knowledge-hub/updating-a-50-terabyte-postgresql-databasePart 1: Introduction to Table Partitioning (July 2023) https://www.adyen.com/knowledge-hub/introduction-to-table-partioningPart 2: Partitioning at Adyen (October 2023) https://www.adyen.com/knowledge-hub/partitioning-at-adyenPart 3: Maintenance Under Pressure (January 2024) https://www.adyen.com/knowledge-hub/maintenance-under-pressureFighting PostgreSQL write amplification with HOT updates (May 2022) https://www.adyen.com/knowledge-hub/postgresql-hot-updatesTracking HOT updates and tuning FillFactor (October 2022) https://www.adyen.com/knowledge-hub/postgresql-hot-updates-part2Partitioning your Postgres tables for 20X better performance (upcoming talk by Derk at Posette) https://www.citusdata.com/posette/speakers/derk-van-veenFigma, including Sammy’s team, is hiring https://www.figma.com/careers Notion’s engineering team is hiring https://www.notion.so/careersAdyen’s engineering team is hiring https://careers.adyen.com~~~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