Michael and Nikolay are joined by Melanie Plageman, database internals engineer at Microsoft and major contributor and committer to PostgreSQL, to discuss getting started with benchmarking — how it differs for users and developers of Postgres, how and when it comes up during development, some tools and lessons, as well as what she's working on at the moment. Here are some links to things they mentioned:Melanie Plageman https://postgres.fm/people/melanie-plagemanMeanie’s Introduction to Benchmarking With pgbench talk slides https://postgresql.us/events/pgconfnyc2023/schedule/session/1410-introduction-to-benchmarking-with-pgbench/#slidesMelanie’s Visualizing Postgres I/O Performance for Development talk recording https://www.youtube.com/watch?v=CxyPZHG5beIMelanie’s Visualizing Postgres I/O Performance for Development talk slides https://speakerdeck.com/melanieplageman/o-performance-for-developmentpgbench https://www.postgresql.org/docs/current/pgbench.htmlMark Callaghan’s blog https://smalldatum.blogspot.comSome of Tomas Vondra’s blog posts https://www.2ndquadrant.com/en/blog/author/tomas-vondraSome of Andres Freund’s blog posts https://www.citusdata.com/blog/authors/andres-freund/An example of Alexander Lakhin’s work https://www.postgresql.org/message-id/b32bed1b-0746-9b20-1472-4bdc9ca66d52%40gmail.comSimplifying the TPC Benchmark C, an OLTP Workload (talk by Mark Wong) https://www.youtube.com/watch?v=qi0I74urLoYMatplotlib https://matplotlib.orgpandas https://pandas.pydata.orgpg_stat_io https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEWpg_stat_io commit https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a9c70b46dbe152e094f137f7e6ba9cd3a638ee25 pg_buffercache https://www.postgresql.org/docs/current/pgbuffercache.htmltmpfs https://docs.kernel.org/filesystems/tmpfs.htmlEager page freeze criteria mailing list thread https://www.postgresql.org/message-id/CAAKRu_b3tpbdRPUPh1Q5h35gXhY%3DspH2ssNsEsJ9sDfw6%3DPEAg%40mail.gmail.comThe path to using AIO in postgres (talk by Andres Freund) https://www.youtube.com/watch?v=qX50xrHwQa4Improve dead tuple storage for lazy vacuum (Masahiko Sawada) https://www.postgresql.org/message-id/flat/CAD21AoAfOZvmfR0j8VmZorZjL7RhTiQdVttNuC4W-Shdc2a-AA@mail.gmail.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 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 Constitutional Peasants from Monty Python and the Holy Grail https://www.youtube.com/watch?v=t2c-X8HiBngThe 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