Nikolay and Michael discuss some Postgres Gotchas, things you might expect to work one way in fact working another way. Here are some links to things they mentioned:Out episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknownPostgres Gotchas (list by Ian Barwick) https://sql-info.de/postgresql/postgres-gotchas.htmlOur episode on slow count https://postgres.fm/episodes/slow-countDiscussion on X about major version upgrades and statistics https://x.com/samokhvalov/status/1844593601638260850Our episode on upgrades https://postgres.fm/episodes/upgradesStatistics Import and Export (commitfest entry which link to email thread) https://commitfest.postgresql.org/50/4538/vacuumdb https://www.postgresql.org/docs/current/app-vacuumdb.htmlvacuum_cost_delay https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-VACUUM-COST-DELAYZero-downtime Postgres schema migrations need lock_timeout and retries (blog post by Nikolay) https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retriesPostgres Hacking 101: adding params, prevent_unqualified_deletes and prevent_unqualified_updates https://www.youtube.com/watch?v=samLkrC5xQA PostgREST suggestion to use pg-safeupdate https://docs.postgrest.org/en/v12/integrations/pg-safeupdate.html#pg-safeupdateDBOS (new company co-founded by Michael Stonebraker) https://www.dbos.dev~~~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 some more advanced topics around EXPLAIN, including some tips for complex query plans, some recent improvements, and an idea or two that might make it even better. Here are some links to things they mentioned:Michael’s solo episode on EXPLAIN basics https://postgres.fm/episodes/explainOur episode on auto_explain https://postgres.fm/episodes/auto_explainWhich cloud providers support auto_explain? (Blog post by Michael) https://www.pgmustard.com/blog/which-cloud-providers-support-auto-explainpsychopg https://www.psycopg.orgOur first episode on BUFFERS https://postgres.fm/episodes/buffers-by-defaultOur second episode on BUFFERS https://postgres.fm/episodes/buffers-ii-the-sequeltrack_io_timing https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMINGcompute_query_id https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID EXPLAIN: beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4~~~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 

Michael and Nikolay are joined by Alexander Kukushkin, PostgreSQL contributor and maintainer of Patroni, to discuss all things Patroni — what it is, how it works, recent improvements, and more.Here are some links to things they mentioned:Alexander Kukushkin https://postgres.fm/people/alexander-kukushkinPatroni https://github.com/patroni/patroniSpilo https://github.com/zalando/spilo Zalando Postgres Operator https://github.com/zalando/postgres-operatorCrunchy Data Postgres Operator https://github.com/CrunchyData/postgres-operatorSplit-brain https://en.wikipedia.org/wiki/Split-brain_(computing)repmgr https://github.com/EnterpriseDB/repmgrCloudNativePG https://github.com/cloudnative-pg/cloudnative-pgPatroni release notes https://patroni.readthedocs.io/en/latest/releases.htmlCitus & Patroni talk and demo by Alexander (at Citus Con 2023) https://www.youtube.com/watch?v=Mw8O9d0ez7E~~~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 fresh new Postgres 17 release! They cover several performance improvements, favourite new features, and some considerations for upgrading. Here are some links to things they mentioned:Postgres 17 release notes https://www.postgresql.org/docs/17/release-17.htmltransaction_timeout episode https://postgres.fm/episodes/transaction_timeoutVACUUM improvements discussed towards end of episode with Melanie Plageman https://postgres.fm/episodes/getting-started-with-benchmarkingB-tree improvements discussed in episdode with Peter Geoghegan https://postgres.fm/episodes/skip-scanAs Rails developers, why we are excited about PostgreSQL 17 (blog post by Benoit Tigeot) https://benoittgt.github.io/blog/postgres_17_rails/ Real World Performance Gains With Postgres 17 B-tree Bulk Scans (blog post by Brandur Leach) https://www.crunchydata.com/blog/real-world-performance-gains-with-postgres-17-btree-bulk-scansMERGE RETURNING came up towards end of episode with Haki Benita https://postgres.fm/episodes/get-or-createuuid_extract_timestamp and uuid_extract_version functions https://www.postgresql.org/docs/current/functions-uuid.htmlEpisode on UUID https://postgres.fm/episodes/uuidPartitioning by ULID https://postgres.fm/episodes/partitioning-by-ulidWhy Upgrade? (site by depesz) https://why-upgrade.depesz.comWhy we spent the last month eliminating PostgreSQL subtransactions (GitLab blog post) https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactionsSynchronization of sequences to subscriber (patch that needs review!)  https://commitfest.postgresql.org/49/5111~~~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 planning time in Postgres — what it is, how to spot issues, its relationship to things like partitioning, and some tips for avoiding issues. Here are some links to things they mentioned:Query Planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.htmlAre there limits to partition counts? (Blog post by depesz) https://www.depesz.com/2021/01/17/are-there-limits-to-partition-countsNikolays recent experiment https://postgres.ai/chats/01920004-a982-7896-b8cb-dfd2406359b0PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0“The year of the lock manager’s revenge” (from blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalabilitypg_stat_statements.track_planning https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.42.9.2.4.1.3pg_hint_plan https://github.com/ossc-db/pg_hint_plan~~~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 counting can be slow in Postgres, and what the options are for counting things quickly at scale. Here are some links to things they mentioned:Aggregate functions (docs) https://www.postgresql.org/docs/current/functions-aggregate.htmlPostgREST https://github.com/PostgREST/postgrest Get rid of count by default in PostgREST https://github.com/PostgREST/postgrest/issues/273 Faster PostgreSQL Counting (by Joe Nelson on the Citus blog) https://www.citusdata.com/blog/2016/10/12/count-performance Our episode on Index-Only Scans https://postgres.fm/episodes/index-only-scansPostgres HyperLogLog https://github.com/citusdata/postgresql-hllOur episode on Row estimates https://postgres.fm/episodes/row-estimates Our episode about dangers of NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown Aggregate expressions, including FILTER https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATESSpread writes for counter cache (tip from Tobias Petry) https://x.com/tobias_petry/status/1475870220422107137pg_ivm extension (Incremental View Maintenance) https://github.com/sraoss/pg_ivm pg_duckdb announcement https://motherduck.com/blog/pg_duckdb-postgresql-extension-for-duckdb-motherduckOur episode on Queues in Postgres https://postgres.fm/episodes/queues-in-postgresOur episode on Real-time analytics https://postgres.fm/episodes/real-time-analyticsClickHouse acquired PeerDB https://clickhouse.com/blog/clickhouse-acquires-peerdb-to-boost-real-time-analytics-with-postgres-cdc-integrationTimescale Continuous Aggregates https://www.timescale.com/blog/materialized-views-the-timescale-wayTimescale editions https://docs.timescale.com/about/latest/timescaledb-editionsLoose indexscan https://wiki.postgresql.org/wiki/Loose_indexscan~~~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 

Michael and Nikolay are joined by Peter Geoghegan, major contributor and committer to Postgres, to discuss adding skip scan support to PostgreSQL over versions 17 and 18. Here are some links to things they mentioned:Peter Geoghegan https://postgres.fm/people/peter-geogheganPeter’s previous (excellent) interview on Postgres TV https://www.youtube.com/watch?v=iAPawr1DxhMEfficient Search of Multidimensional B-Trees (1995 paper by Harry Leslie, Rohit Jain, Dave Birdsall, and Hedieh Yaghmai) https://vldb.org/conf/1995/P710.PDFIndex Skip Scanning in Oracle https://oracle-base.com/articles/9i/index-skip-scanningPeter’s introductory email to the hackers mailing list about adding skip scan https://www.postgresql.org/message-id/CAH2-Wzmn1YsLzOGgjAQZdn1STSG_y8qP__vggTaPAYXJP+G4bw@mail.gmail.comLoose Indexscan versus Index Skip Scan (PostgreSQL wiki) https://wiki.postgresql.org/wiki/Loose_indexscanTom Lane will be on the Talking Postgres podcast on October 9th https://aka.ms/TalkingPostgres-Ep20-calBenoit Tigeot feedback and repro (originally reported via Slack) https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d?permalink_comment_id=4597410#gistcomment-4597410Summary video and blog post about the v17 work by Lukas from pganalyze (not mentioned but great) https://pganalyze.com/blog/5mins-postgres-17-faster-btree-index-scansUnderstanding HNSW + filtering (pgvector repo discussion) https://github.com/pgvector/pgvector/issues/259btree_gin https://www.postgresql.org/docs/current/btree-gin.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 special thanks to:Jessie Draws for the elephant artwork 

Nikolay and Michael discuss PostgreSQL emergencies — both the psychological side of incident management, and some technical aspects too. Here are some links to things they mentioned:Site Reliability Engineering resources from Google https://sre.googleGitLab Handbook SRE https://handbook.gitlab.com/job-families/engineering/infrastructure/site-reliability-engineerKeeping Customers Streaming — The Centralized Site Reliability Practice at Netflix https://netflixtechblog.com/keeping-customers-streaming-the-centralized-site-reliability-practice-at-netflix-205cc37aa9fbOur monitoring checklist episode https://postgres.fm/episodes/monitoring-checklistHannu Krosing talk on Postgres TV — Do you vacuum everyday? https://www.youtube.com/watch?v=JcRi8Z7rkPgOur episode on corruption https://postgres.fm/episodes/corruptionNikolay’s episode on stopping and starting Postgres faster https://postgres.fm/episodes/stop-and-start-postgres-fasterOur episode on out of disk https://postgres.fm/episodes/out-of-diskThe USE method (Brendan Gregg) https://www.brendangregg.com/usemethod.html Thundering herd problem https://en.wikipedia.org/wiki/Thundering_herd_problem~~~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 

Michael and Nikolay are joined by Haki Benita, a technical lead and database enthusiast who writes an excellent blog and gives popular talks and training sessions too, to discuss the surprisingly complex topic of trying to implement “get or create” in PostgreSQL — handling issues around idempotency, concurrency, and bloat. Here are some links to things they mentioned:Haki Benita https://hakibenita.com How to Get or Create in PostgreSQL (blog post by Haki) https://hakibenita.com/postgresql-get-or-create "Find-or-insert" using a single query (how-to guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0036_find-or-insert_using_a_single_query.md?ref_type=heads Is SELECT or INSERT in a function prone to race conditions? (Answer by Erwin Brandstetter)  https://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions/15950324#15950324 get_or_create() in Django https://docs.djangoproject.com/en/5.1/ref/models/querysets/#get-or-create Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful MERGE (Postgres documentation) https://www.postgresql.org/docs/current/sql-merge.htmlHidden dangers of duplicate key violations in PostgreSQL and how to avoid them (blog post by Divya Sharma and Shawn McCoy from the RDS team) https://aws.amazon.com/blogs/database/hidden-dangers-of-duplicate-key-violations-in-postgresql-and-how-to-avoid-them/ One, Two, Skip a Few... (blog post by Pete Hamilton from Incident) https://incident.io/blog/one-two-skip-a-few ~~~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 

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 

Nikolay and Michael discuss foreign keys in Postgres — what they are, their benefits, their overhead, some edge cases to be aware of, some improvements coming, and whether or not they generally recommend using them. Here are some links to things they mentioned:Foreign keys (docs) https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FKOur episode about constraints: https://postgres.fm/episodes/constraintsGitLab migration helper add_concurrent_foreign_key https://github.com/gitlabhq/gitlabhq/blob/master/rubocop/cop/migration/add_concurrent_foreign_key.rbAdding a foreign key without downtime (tweet by Nikolay) https://x.com/samokhvalov/status/1732056107483636188Bloat, pg_repack, and deferred constraints (blog post by Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ecPostgres 17 draft release notes, server configuration section https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-CONFIGOur 100th episode https://postgres.fm/episodes/to-100tb-and-beyondStop! Trigger Time (blog post by Michael) https://www.pgmustard.com/blog/trigger-timeShould I Create an Index on Foreign Keys? (Blog post by Percona) https://www.percona.com/blog/should-i-create-an-index-on-foreign-keys-in-postgresqlAvoid Postgres performance cliffs with MultiXact IDs and foreign keys (5 min video by Lukas Fittl) https://pganalyze.com/blog/5mins-postgres-multiXact-ids-foreign-keys-performanceExperiment to see basic overhead of foreign keys https://v2.postgres.ai/chats/01902ee6-8ed1-70ec-9345-5606305012f4Experiment showing an extreme contention case https://v2.postgres.ai/chats/018fb28d-865f-788d-adb7-efa7ed3a48c4Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmfulNotes on some PostgreSQL implementation details (blog post by Nelson Elhage that mentions “subtransactions are cursed”) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

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 

Michael is joined by Claire Giordano, Head of Postgres Open Source Community Initiatives at Microsoft, to discuss several ways to contribute to the Postgres community — from core contributions, to extensions, to events, and (of course) podcasts. Here are some links to things they mentioned:What’s new with Postgres at Microsoft (blog post by Claire) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/what-s-new-with-postgres-at-microsoft-2024-edition/ba-p/4140085 Citus https://github.com/citusdata/citus Fibonacci Spirals and 21 Ways to Contribute to Postgres Beyond Code (talk by Claire) https://archive.fosdem.org/2020/schedule/event/postgresql_fibonacci_spirals_and_21_ways_to_contribute_to_postgres_beyond_code/ How to contribute to PostgreSQL or, 50 Ways To Love Your Project (talk slides by Josh Berkus) https://berkus.org/pdf_presos/50_ways.pdf pgconf dot dev https://2024.pgconf.dev/ POSETTE: An Event for Postgres https://www.citusdata.com/posette About Talk Selection for POSETTE https://www.citusdata.com/blog/2024/04/22/about-talk-selection-for-posette-an-event-for-postgres-2024/ Claire’s video about how to say Postgres, PostgreSQL, Citus, and a few questionably named Microsoft things https://x.com/clairegiordano/status/1503784151614320640 Citus goes fully open source https://www.citusdata.com/blog/2024/04/22/about-talk-selection-for-posette-an-event-for-postgres-2024/Contributor Profiles https://www.postgresql.org/community/contributors/ Mastodon post by Álvaro Herrera (of EDB) https://lile.cl/@alvherre/112444579030481334 Path To Citus Con podcast (soon to be renamed to Talking Postgres) https://www.citusdata.com/podcast/path-to-citus-con/ ~~~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 full text search in Postgres — some of the history, some of the features, and whether it now makes sense to try to replace or combine it with semantic search. Here are some links to things they mentioned:Full Text Search https://www.postgresql.org/docs/current/textsearch.htmltsearch2 https://www.postgresql.org/docs/9.6/tsearch2.htmlDictionaries https://www.postgresql.org/docs/current/textsearch-dictionaries.html RUM index https://github.com/postgrespro/rum Okapi BM25 https://en.wikipedia.org/wiki/Okapi_BM25 tf–idf https://en.wikipedia.org/wiki/Tf%E2%80%93idf unaccent https://www.postgresql.org/docs/current/unaccent.html tsvector and tsquery https://www.postgresql.org/docs/current/datatype-textsearch.html GiST indexes https://www.postgresql.org/docs/current/gist.html GIN indexes https://www.postgresql.org/docs/current/gin.html Controlling Text Search (including setweight function) https://www.postgresql.org/docs/current/textsearch-controls.html pg_trgrm https://www.postgresql.org/docs/current/pgtrgm.html btree_gist https://www.postgresql.org/docs/current/btree-gist.html btree_gin https://www.postgresql.org/docs/current/btree-gin.html websearch_to_tsquery https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES  pgvector https://github.com/pgvector/pgvector Our previous episode on search https://postgres.fm/episodes/search ~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss Postgres minor releases — how the schedule works, options for upgrading to them, and the importance of reading the release notes. Here are some links to things they mentioned:PostgreSQL 16.3, 15.7, 14.12, 13.15, and 12.19 released (announcement) https://www.postgresql.org/about/news/postgresql-163-157-1412-1315-and-1219-released-2858/ PostgreSQL versioning policy https://www.postgresql.org/support/versioning/ PostgreSQL 14.4 release notes (most recent minor release not on the usual schedule) https://www.postgresql.org/docs/release/14.4/ Minor release roadmap https://www.postgresql.org/developer/roadmap/ Our last episode on upgrades (major and minor) https://postgres.fm/episodes/upgrades  All versions of Postgres https://bucardo.org/postgres_all_versions.html Why upgrade? (Useful tool by depesz) https://why-upgrade.depesz.com/ Stop and start Postgres faster https://postgres.fm/episodes/stop-and-start-postgres-faster WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning  Postgres CVE-2024-4317 and how to fix the system views (5 mins of Postgres by Lukas Fittl) https://youtube.com/watch?v=fLwVvJ3fKdA Our episode on NULL https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown What should we do for episode 100? https://www.reddit.com/r/PostgreSQL/comments/1cn8ajh/what_should_we_do_for_episode_100_of_postgres_fm/ ~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss custom and generic planning in prepared statements — how it works, how issues can present themselves, some ways to view the generic plan, and some benefits of avoiding planning (not just time).  Here are some links to things they mentioned:PREPARE https://www.postgresql.org/docs/current/sql-prepare.html track_activity_query_size https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE plan_cache_mode https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE EXPLAIN (GENERIC_PLAN) https://www.postgresql.org/docs/current/sql-explain.html#id-1.9.3.148.8 EXPLAIN (GENERIC_PLAN) in PostgreSQL 16 (blog post by Laurenz from Cybertec)  https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/ Running EXPLAIN on any query, even with $1 parameters (blog post and video by Lukas Fittl of pganalyze) https://pganalyze.com/blog/5mins-postgres-explain-generic-plan EXPLAIN from pg_stat_statements, how to get the generic plan (blog post by Franck Pashto of Yugabyte) https://dev.to/yugabyte/explain-from-pgstatstatements-normalized-queries-how-to-always-get-the-generic-plan-in--5cfi Rework query relation permission checking (commit by Amit Langote) https://git.postgresql.org/gitweb/postgres.git?p=postgresql.git;a=commit;h=a61b1f74823c9c4f79c95226a461f1e7a367764b Partition pruning, prepared statements and generic vs custom query plans (a follow up blog post and video by Lukas) https://pganalyze.com/blog/5mins-postgres-partition-pruning-prepared-statements-generic-vs-custom-query-plans Our episode on over-indexing (inc LWLock discussion) https://postgres.fm/episodes/over-indexing “The year of the lock manager’s revenge” (from blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/  ~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss LIMIT in Postgres — what it does, how it can help with performance, and an interesting example where adding it can actually hurt performance(!) Here are some links to things they mentioned:LIMIT considered harmful in PostgreSQL (Twitter thread by Christophe Pettus) https://twitter.com/Xof/status/1413542818673577987 LIMIT and OFFSET (docs) https://www.postgresql.org/docs/current/queries-limit.html No OFFSET (by Markus Winand) https://use-the-index-luke.com/no-offset LIMIT clause (docs) https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT ~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael return to the topic of using the buffers explain parameter — with a new analogy, some (conspiracy) theories of why it's still not on by default, and some related chat about the serialize parameter coming in 17. Here are some links to things they mentioned:BUFFERS by default (episode 4) https://postgres.fm/episodes/buffers-by-default Lightning talk by Michael at pgDay Paris (5 mins) https://www.youtube.com/watch?v=WfY-mSpUzaQ&t=1470s Waiting for SERIALIZE https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/ ~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss doing massive DELETE operations in Postgres — what can go wrong, how to prevent major issues, and some ideas to minimise their impact. Here are some links to things they mentioned:Article based on Nikolay’s talk, including batching implementation (translated to English) https://habr-com.translate.goog/en/articles/523536/?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hist=true Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningEgor Rogov’s book on Postgres Internals (chapter 10 on WAL) https://edu.postgrespro.com/postgresql_internals-14_en.pdf full_page_writes https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-FULL-PAGE-WRITES TRUNCATE https://www.postgresql.org/docs/current/sql-truncate.html Our episode on partitioning https://postgres.fm/episodes/partitioning Our episode on bloat https://postgres.fm/episodes/bloat Our episode on index maintenance https://postgres.fm/episodes/index-maintenance ~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael are joined by Sai Srirampur, CEO and Co-founder of PeerDB, to discuss how to overcome several logical replication issues. They discuss the protocol versions, minimising slot growth, minimising lag, and some tips and tricks for scaling things well. Here are some links to things they mentioned:PeerDB https://www.peerdb.io/Our episode on logical replication https://postgres.fm/episodes/logical-replicationExploring versions of the Postgres logical replication protocol (blog post by Sai) https://blog.peerdb.io/exploring-versions-of-the-postgres-logical-replication-protocol Logical Streaming Replication Protocol https://www.postgresql.org/docs/current/protocol-logical-replication.htmlREPLICA IDENTITY FULL https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITYOn the performance impact of REPLICA IDENTITY FULL in Postgres (blog post by Xata) https://xata.io/blog/replica-identity-full-performance max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE Active Active in Postgres 16 (blog post by Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16 pgEdge https://www.pgedge.com/DistSQL (term used by Mark Callaghan) https://smalldatum.blogspot.com/2023/07/keeping-up-with-sql-dbms-market.html Five tips on Postgres logical decoding (blog post by Sai) https://blog.peerdb.io/five-tips-on-postgres-logical-decodingPG Slot Notify: Monitor Postgres Slot Growth in Slack (blog post by PeerDB) https://blog.peerdb.io/pg-slot-notify-monitor-postgres-slot-growth-in-slack~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss several "Don't do this" lists about Postgres — picking out their favourite items, as well as some contentious ones that could be clearer, or not included. Here are some links to things they mentioned:Don’t do this (PostgreSQL wiki page) https://wiki.postgresql.org/wiki/Don't_Do_ThisHow to get into trouble using some Postgres features (how to by Nikolay) https://github.com/postgres-ai/postgres-howtos/blob/main/0016_how_to_get_into_trouble_using_some_postgres_features.mdDon’t do this (jOOQ list) http://www.jooq.org/doc/3.19/manual/reference/dont-do-thisDon’t use NOT IN (jOOQ) https://www.jooq.org/doc/3.19/manual/reference/dont-do-this/dont-do-this-sql-not-in/ Our episode about NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown Our episode on timestamps https://postgres.fm/episodes/timestamps~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael have a high-level discussion on all things search — touching on full-text search, semantic search, and faceted search. They discuss what comes in Postgres core, what is possible via extensions, and some thoughts on performance vs implementation complexity vs user experience. Here are some links to things they mentioned:Simon Riggs https://www.linkedin.com/feed/update/urn:li:activity:7178702287740022784/Companion databases episode https://postgres.fm/episodes/companion-databasespgvector episode https://postgres.fm/episodes/pgvectorFull Text Search https://www.postgresql.org/docs/current/textsearch.htmlSemantic search https://en.wikipedia.org/wiki/Semantic_searchFaceted search https://en.wikipedia.org/wiki/Faceted_searchFaceting large result sets in PostgreSQL https://www.cybertec-postgresql.com/en/faceting-large-result-sets/RUM index https://github.com/postgrespro/rum Hybrid search (Supabase guide) https://supabase.com/docs/guides/ai/hybrid-search Elastic https://www.elastic.co/ GiST indexes https://www.postgresql.org/docs/current/gist.html GIN indexes https://www.postgresql.org/docs/current/gin.html btree_gist https://www.postgresql.org/docs/current/btree-gist.html btree_gin https://www.postgresql.org/docs/current/btree-gin.html pg_trgrm https://www.postgresql.org/docs/current/pgtrgm.html Text Search Types (tsvector and tsquery) https://www.postgresql.org/docs/current/datatype-textsearch.html Postgres full text search with the “websearch” syntax (blog post by Adam Johnson) https://adamj.eu/tech/2024/01/03/postgresql-full-text-search-websearch/Understanding Postgres GIN Indexes: The Good and the Bad (blog post by Lukas Fittl) https://pganalyze.com/blog/gin-index ParadeDB https://www.paradedb.com/ ZomboDB https://www.zombodb.com/ Introduction to Information Retrieval (book by Manning, Raghavan, and Schütze) https://www.amazon.co.uk/Introduction-Information-Retrieval-Christopher-Manning/dp/0521865719 How to build a search engine with Ruby on Rails (blog post by Justin Searls) https://blog.testdouble.com/posts/2021-09-09-how-to-build-a-search-engine-with-ruby-on-rails/~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss Postgres health checks — what they are, things to include, how often makes sense, and whether improvements to Postgres would increase or decrease the need for them. Here are some links to things they mentioned:MOT (car test in the UK) https://en.wikipedia.org/wiki/MOT_test Let's make PostgreSQL multi-threaded (discussion started by Heikki) https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4%40iki.fi postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup Why upgrade https://why-upgrade.depesz.com/  Related episodes: Default configuration https://postgres.fm/episodes/default-configurationIndex maintenance https://postgres.fm/episodes/index-maintenanceBloat https://postgres.fm/episodes/bloatMonitoring checklist https://postgres.fm/episodes/monitoring-checklistpg_stat_statements https://postgres.fm/episodes/pg_stat_statementsBackups https://postgres.fm/episodes/backups~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss the superuser role in PostgreSQL — what it is, how and when it shouldn’t be used, and whether most cloud providers are right to not give us it (no prizes for guessing). Here are some links to things they mentioned:superuser (docs) https://www.postgresql.org/docs/current/role-attributes.html#id-1.6.9.6.2.1.2.1.1Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/crunchy-data-postgresql-security-technical-implementation-guide-now-availableSupabase docs (unsupported operations) https://supabase.com/docs/guides/database/postgres/roles-superuserCrunchy Data docs https://docs.crunchybridge.com/concepts/usersRDS docs https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.htmlCloud SQL docs https://cloud.google.com/sql/docs/postgres/usersAzure docs https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-serversRoles, Privileges, and Security (talk by Ryan Booz) https://www.youtube.com/watch?v=mtPM3iZFE04~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss transaction_timeout (a recently committed addition for Postgres 17) — what it's for, how to get around not having it already, and whether it will replace the need to set statement_timeout globally in future. Here are some links to things they mentioned:transaction_timeout (devel docs) https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-TRANSACTION-TIMEOUTCommit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5 Mailing list thread: https://www.postgresql.org/message-id/flat/CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ%40mail.gmail.com  Hacking Postgres session on Postgres TV where work started https://www.youtube.com/live/WLoMpg8A4WU?t=50 Our first ever episode, on slow queries and slow transactions https://postgres.fm/episodes/slow-queries-and-slow-transactions statement_timeout (v7.3) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT idle_in_transaction_session_timeout (v9.6) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT idle_session_timeout (v14) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Michael and Nikolay are joined by Andrew Atkinson, author of High Performance PostgreSQL for Rails, to discuss how Rails and Postgres work together — where the limits are, how people use the ORM, things that are improving, and some things we can do as a Postgres community to make it even better. Here are some links to things they mentioned:Planet Argon survey https://rails-hosting.com/2022/#databasesActive Record https://guides.rubyonrails.org/active_record_basics.htmlPostgreSQL specific usage of Active Record https://guides.rubyonrails.org/active_record_postgresql.htmlMultiple Databases with Active Record https://guides.rubyonrails.org/active_record_multiple_databases.htmlschema.rb vs structure.sql https://blog.appsignal.com/2020/01/15/the-pros-and-cons-of-using-structure-sql-in-your-ruby-on-rails-application.htmlactiverecord-clean-db-structure (Ruby gem by Lukas Fittl) https://github.com/lfittl/activerecord-clean-db-structureGitLab’s migration_helpers.rb https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/migration_helpers.rbSQLite https://www.sqlite.orgPlanetScale’s foreign key support announcement video https://twitter.com/PlanetScale/status/1732070818958500083DoorDash Engineering Blog https://doordash.engineering/blograils-pg-extras https://github.com/pawurb/rails-pg-extrasBenoit Tigeot testing Peter Geoghegan improvement for large IN lists https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04dHigh Performance PostgreSQL for Rails (Andy’s book, 35% discount code “postgres.fm”) https://pragprog.com/titles/aapsql/high-performance-postgresql-for-railsAndy’s blog and website https://andyatkinson.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 a common question — why Postgres isn't using an index, and what you can do about it! Here are some links to things they mentioned:Why isn’t Postgres using my index? (blog post by Michael) https://www.pgmustard.com/blog/why-isnt-postgres-using-my-index Why isn’t Postgres using my functional index? (Stack Exchange question from Brent Ozar) https://dba.stackexchange.com/questions/336019/why-isnt-postgres-using-my-functional-index  enable_seqscan (and similar parameters) https://www.postgresql.org/docs/current/runtime-config-query.html Crunchy Bridge changed random_page_cost to 1.1 https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1 Make indexes invisible (trick from Haki Benita) https://hakibenita.com/sql-tricks-application-dba#make-indexes-invisible ANALYZE https://www.postgresql.org/docs/current/sql-analyze.htmlStatistics used by the planner https://www.postgresql.org/docs/current/planner-stats.html Our episode on query hints https://postgres.fm/episodes/query-hints transaction_timeout (commit for Postgres 17) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5 What’s new in the Postgres 16 query planner / optimizer (blog post by David Rowley) https://www.citusdata.com/blog/2024/02/08/whats-new-in-postgres-16-query-planner-optimizer/ ~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Nikolay and Michael discuss the overhead of pg_stat_statements and pg_stat_kcache — mostly focusing on some interesting things Nikolay found while stress-testing some large spot instances up to and beyond 2m TPS(!)  Here are some links to things they mentioned:pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlpg_stat_kcache https://github.com/powa-team/pg_stat_kcachepg_stat_statements overhead estimate in pganalyze FAQs https://pganalyze.com/faqWhat is the performance impact of pg_stat_statements? (DBA Stack Exchange answer with several links) https://dba.stackexchange.com/questions/303503/what-is-the-performance-impact-of-pg-stat-statementsTowards Millions TPS (blog post by Alexander Korotkov) https://akorotkov.github.io/blog/2016/05/09/scalability-towards-millions-tpsPostgres.ai blog post with links to benchmarks https://postgres.ai/blog/20240127-postges-ai-botpostgresql_cluster https://github.com/vitabaks/postgresql_clusterpg_wait_sampling https://github.com/postgrespro/pg_wait_samplingpg_stat_kcache issue, discussion, and fast resolution https://github.com/powa-team/pg_stat_kcache/issues/41 log_statement_sample_rate https://postgresqlco.nf/doc/en/param/log_statement_sample_rate/ auto_explain.sample_rate https://www.postgresql.org/docs/current/auto-explain.html#id-1.11.7.14.5.3.13.1.3pg_stat_statements performance penalty on Intel much higher than on AMD https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/52~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Michael is joined by Markus Winand, creator of use-the-index-luke.com and modern-sql.com, and author of SQL Performance Explained, to discuss Modern SQL — what Markus means by it, why it's important, some benefits, some examples, and at least one phrase that should be on a t-shirt. Here are some links to things they mentioned:Modern SQL (site) https://modern-sql.comUse the index, Luke! (site) https://use-the-index-luke.comSQL Performance Explained (book) https://sql-performance-explained.comOur episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknownIS DISTINCT FROM (null-safe not equals comparison) https://modern-sql.com/caniuse/is-distinct-fromUNIQUE NULLS DISTINCT https://modern-sql.com/caniuse/unique-nulls-distinctModernes SQL ist mehr als SELECT * FROM (a German language podcast Markus was on recently) https://engineeringkiosk.dev/podcast/episode/99-modernes-sql-ist-mehr-als-select-from-mit-markus-winandMarkus’ website https://winand.at~~~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 

Michael is joined by Chelsea Dole, Staff Software Engineer and Tech Lead of the Data Storage Team at Brex, to discuss bloat. They cover what it is, why it's a problem, strategies for minimising it, and the options when it's really bad. Here are some links to things they mentioned:Managing your tuple graveyard (talk by Chelsea) https://www.youtube.com/watch?v=gAgbzvGT6ckpgstattuple https://www.postgresql.org/docs/current/pgstattuple.html pg_class https://www.postgresql.org/docs/current/catalog-pg-class.html Bloat in PostgreSQL: a taxonomy (talk by Peter Geoghegan) https://youtube.com/watch?v=JDG4bMHxCH8 It's Not You, It's Me: Breaking Up with Massive Tables via Partitioning (talk by Chelsea) https://youtube.com/watch?v=TafwSuLNxe8 pg_repack https://github.com/reorg/pg_repackpg_squeeze https://github.com/cybertec-postgresql/pg_squeeze VACUUM https://www.postgresql.org/docs/current/sql-vacuum.htmlautovacuum https://www.postgresql.org/docs/current/runtime-config-autovacuum.html CLUSTER https://www.postgresql.org/docs/current/sql-cluster.html HOT updates https://www.postgresql.org/docs/current/storage-hot.html Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default Our episode on TOAST https://postgres.fm/episodes/toast Our episode on index maintenance https://postgres.fm/episodes/index-maintenance Chelsea's website: https://chelseadole.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 amazing artwork 

Michael is joined by Jonathan Katz, PostgreSQL Core Team member, Principle Product Manager at AWS, and also pgvector contributor, to discuss pgvector. They cover what it is, why it's so popular all of a sudden, some tuning and tradeoff guidance, and a look to the future. Here are some links to things they mentioned:pgvector https://github.com/pgvector/pgvectorOur episode on companion databases https://postgres.fm/episodes/companion-databasesVectors are the new JSON (talk by Jonathan) https://www.youtube.com/watch?v=D_1zunKblAUpgvector 0.5.0 feature highlights and howtos (blog post by Jonathan) https://jkatz05.com/post/postgres/pgvector-overview-0.5.0HNSW indexing and searching (blog post by Jonathan) https://aws.amazon.com/blogs/database/accelerate-hnsw-indexing-and-searching-with-pgvector-on-amazon-aurora-postgresql-compatible-edition-and-amazon-rds-for-postgresqlOur episode on TOAST https://postgres.fm/episodes/toastpgvector changelog https://github.com/pgvector/pgvector/blob/master/CHANGELOG.mdHQANN paper https://arxiv.org/abs/2207.07940HNSW fast build branch https://github.com/pgvector/pgvector/tree/hnsw-fast-build pgconf.dev https://2024.pgconf.dev ~~~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 amazing artwork 

Michael is joined by Alicja Kucharczyk, Program Manager for Azure Database for PostgreSQL at Microsoft and organiser of the Warsaw PostgreSQL User Group, to discuss Alicja's favorite tool: pgBadger. They cover what it is, how and when it's useful, and some guidance for getting the most out of it (without causing an outage). Here are some links to things they mentioned:pgBadger website https://pgbadger.darold.net pgBadger repo https://github.com/darold/pgbadgerAn example pgBadger report https://pgbadger.darold.net/examples/sample.html pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.htmlAzure Guide https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/how-to-generate-pgbadger-report-from-azure-database-for/ba-p/3756328Google Cloud SQL guide https://cloud.google.com/blog/products/databases/guide-to-the-database-observability-with-cloud-sqlRDS guide https://aws.amazon.com/blogs/database/optimizing-and-tuning-queries-in-amazon-rds-postgresql-based-on-native-and-external-tools/Community Insights on pgBadger (PGSQL Phriday Recap) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/community-insights-on-pgbadger-a-pgsql-phriday-010-recap/ba-p/3880911 PGSQL Phriday #010: Log analysis (blog post by Lætitia Avrot) https://mydbanotebook.org/post/log-analysis/ Nothing Compares To VACUUM/The Ballad of Bloat https://www.youtube.com/watch?v=2NxIngqq1y0 Explain Analyze (Feliz Navidad cover) https://www.youtube.com/watch?v=qznnzYZPdkM ~~~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 amazing artwork 

With Nikolay taking a well-earned break, Michael covers the basics of EXPLAIN — what it is, the parameters available, and a process he recommends for using it. Here are some links to the things mentioned:EXPLAIN (docs) https://www.postgresql.org/docs/current/sql-explain.htmlUsing EXPLAIN (docs) https://www.postgresql.org/docs/current/using-explain.html EXPLAIN Explained (talk by Josh Berkus https://www.youtube.com/watch?app=desktop&v=mCwwFAl1pBU Beyond joins and indexes (slides from talk by Bruce Momjian) https://momjian.us/main/writings/pgsql/beyond.pdf Depesz https://explain.depesz.com Dalibo https://explain.dalibo.com pgMustard https://www.pgmustard.com/ A beginners guide to explain (talk by Michael) https://www.youtube.com/watch?v=31EmOKBP1PY&t=24s  Explain beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4 auto_explain episode https://postgres.fm/episodes/auto_explain Row estimates episode https://postgres.fm/episodes/row-estimates Slow queries and slow transactions episode https://postgres.fm/episodes/slow-queries-and-slow-transactionsBenchmarking episode https://postgres.fm/episodes/benchmarking  EXPLAIN glossary by Michael (forgot to mention, oops!) https://www.pgmustard.com/docs/explain ~~~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 brought to you by:Nikolay Samokhvalov, founder of Postgres.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Happy holidays! Today's topic is work_mem — how it's used and how to tune it.Related post: https://x.com/samokhvalov/status/1740813478150189172?s=46&t=HSxrW86NZqzPQP9vXVJEWg.

Nikolay and Michael discuss partitioning by ULID — revisiting some of the old UUID vs bigint key debate in light of some new UUID specs, and how these can be used for partitioning (by time).  Here are some links to things they mentioned: Nikolay’s episode on UUID (for primary keys) https://postgres.fm/episodes/uuid  Postgres TV hacking session with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8 UUIDv7 patch https://commitfest.postgresql.org/43/4388/  Use ULID Idempotency Keys (tip 6 in this blog post from Shopify) https://shopify.engineering/building-resilient-payment-systems  Nikolay’s howto post on UUID v7 and partitioning with TimescaleDB https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.md  Hussein Nasser’s YouTube video about the above https://www.youtube.com/watch?v=f53-Iw_5ucA  UUID proposal that includes UUIDv7 and UUIDv8 (work in progress): https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/history/   James Blackwood-Sewell advice on more elegant solution using TimescaleDB https://twitter.com/jamessewell/status/1730125437903450129  ULIDs and Stripe IDs (section of Identity Crisis blog post by Brandur) https://brandur.org/nanoglyphs/026-ids#ulids  Crunchy Bridge changed their default random_page_cost to 1.1 🙌 https://twitter.com/brandur/status/1720477470116422028  UUIDs vs serials for keys (newer post from Christophe Pettus) https://thebuild.com/blog/2023/02/16/uuids-vs-serials-for-keys/  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss hash indexes in Postgres — what they are, some brief history, their pros and cons vs btrees, and whether or when they recommend using them. Here are some links to things they mentioned: Index types (docs) https://www.postgresql.org/docs/current/indexes-types.html   Re-introducing hash indexes in PostgreSQL (blog post by Haki Benita and Michael) https://hakibenita.com/postgresql-hash-index  Hash indexes intro (docs) https://www.postgresql.org/docs/current/hash-intro.html  Hash indexes implementation (docs) https://www.postgresql.org/docs/current/hash-implementation.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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss PostgreSQL constraints — the six different types we have, how to add and modify them, including on large tables with high TPS.  Here are some links to things they mentioned: Constraints (docs) https://www.postgresql.org/docs/current/ddl-constraints.html Unique index vs unique constraint issue (2017) https://www.postgresql.org/message-id/flat/CANNMO%2B%2B7%2BuVMj%2BZPGKB_zuRpKE0sNthy2Z-_oKSS%3DoE3_rN7QA%40mail.gmail.com  Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown Advanced Int to Bigint Conversions (talk by Robert Treat) https://www.youtube.com/watch?v=_IieyXzdmcM  Use BIGINT in Postgres (blog post by Ryan Lambert) https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default  How to add a foreign key (Tweet by Nikolay) https://twitter.com/samokhvalov/status/1732056107483636188  Bloat, pg_repack, and deferred constraints (blog post by Nikolai Averin from Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss PostgreSQL events — whether in-person or online, large conferences or small meet-ups, as well as some strong opinions based on their experiences attending, speaking, and organising them.  Here are some links to things they mentioned: PGSQL Phriday #014: PostgreSQL Events https://www.cybertec-postgresql.com/en/pgsql-phriday-014-postgresql-events/  PGCon https://www.pgcon.org/  Highload https://highload.rs/  The San Francisco Bay Area PostgreSQL Meetup Group https://www.meetup.com/postgresql-1/  Our episode on “Why is Postgres popular?” https://postgres.fm/episodes/why-is-postgres-popular  PGConf EU https://pgconf.eu/  Open talks series on Postgres TV https://www.youtube.com/playlist?list=PLH8y1BNPAKjJCuZiDRl0qUEDaKLBpFvZ9  Rails World (including videos!) https://rubyonrails.org/world  Upcoming events https://www.postgresql.org/about/events/  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss PostgreSQL subtransactions — what they are, and four issues they can cause at scale.  Here are some links to things they mentioned: Subtransactions (docs) https://www.postgresql.org/docs/current/subxacts.html   SAVEPOINT (docs) https://www.postgresql.org/docs/current/sql-savepoint.html   PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful   Subtransactions and performance in PostgreSQL (blog post by Laurenz at Cybertec) https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/   Notes on some PostgreSQL implementation details (blog post by Nelson Elhage) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/  Why we spent the last month eliminating PostgreSQL subtransactions (GitLab blog post) https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/   ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss companion databases — when and why you might want to add another database management system to your stack (or not), and some specifics for analytics, timeseries, search, and vectors.  Here are some links to things they mentioned: Heap were using Postgres + Citus for analytics as of 2022 https://www.heap.io/blog/juggling-state-machines-incident-response-and-data-soup-a-glimpse-into-heaps-engineering-culture  Heap recently moved their core analytics to SingleStore (we only spotted this after recording 🤦‍♂️) https://www.heap.io/blog/heaps-next-generation-data-platform  Posthog moved their analytics from Postgres to Clickhouse https://posthog.com/blog/clickhouse-vs-postgres  Timescale https://www.timescale.com/  Citus https://www.citusdata.com/  Hydra https://www.hydra.so/  Our episode on real-time analytics https://postgres.fm/episodes/real-time-analytics  Our episode on queues https://postgres.fm/episodes/queues-in-postgres  Our episode on logical replication https://postgres.fm/episodes/logical-replication  Full Text Search in PostgreSQL (docs) https://www.postgresql.org/docs/current/textsearch.html  ZomboDB https://www.zombodb.com/  ParadeDB https://www.paradedb.com/  PeerDB https://www.peerdb.io/  RUM indexes https://github.com/postgrespro/rum  Debezium https://debezium.io/  pgmq https://github.com/tembo-io/pgmq  Tembo https://tembo.io/  pgvector vs Pinecone (Supabase blog post) https://supabase.com/blog/pgvector-vs-pinecone  Jepsen analyses https://jepsen.io/analyses  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss blue-green deployments — specifically an RDS blog post, how similar this is (or not) to what they understand to be blue-green deployments, and how applicable the methodology might be in the database world more generally.  Here are some links to things they mentioned: Fully managed Blue/Green Deployment in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL https://aws.amazon.com/blogs/database/new-fully-managed-blue-green-deployment-in-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/   Blue-green deployment (blog post by Martin Fowler) https://martinfowler.com/bliki/BlueGreenDeployment.html   Our episode on logical replication https://postgres.fm/episodes/logical-replication  pgroll https://github.com/xataio/pgroll  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss data model trade-offs — some cases where you might want to (gasp) de-normalize, and which PostgreSQL internals are at play.  Here are some links to things they mentioned: Our episode on JSON https://postgres.fm/episodes/json  PostgreSQL limits https://www.postgresql.org/docs/current/limits.html Boyce–Codd normal form https://en.wikipedia.org/wiki/Boyce–Codd_normal_form  Our episode on over-indexing https://postgres.fm/episodes/over-indexing  Heap-Only Tuples (HOT) optimisation https://www.postgresql.org/docs/current/storage-hot.html  The Surprising Impact of Medium-Size Texts on PostgreSQL Performance https://hakibenita.com/sql-medium-text-performance  Query planner settings (collapse limits and Genetic Query Optimizer) https://www.postgresql.org/docs/current/runtime-config-query.html  WITH Queries (Common Table Expressions) https://www.postgresql.org/docs/current/queries-with.html  Our episode on benchmarking https://postgres.fm/episodes/benchmarking  The tests and benchmarks Nikolay mentioned https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss under-indexing — how to tell if this applies to you, some general and specific cases, as well as some tricky ones.  Here are some links to things they mentioned: Indexes (docs) https://www.postgresql.org/docs/current/indexes.html  random_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/ Sequential scans https://www.pgmustard.com/docs/explain/sequential-scan   pg_stat_user_tables https://pgpedia.info/p/pg_stat_user_tables.html  pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html auto_explain https://www.postgresql.org/docs/current/auto-explain.html  Our episode on slow queries https://postgres.fm/episodes/slow-queries-and-slow-transactions  Our episode on query macro analysis https://postgres.fm/episodes/macro-query-analysis-intro Running 10 Million PostgreSQL Indexes In Production (And Counting) https://www.heap.io/blog/running-10-million-postgresql-indexes-in-production  Faceting large result sets in PostgreSQL https://www.cybertec-postgresql.com/en/faceting-large-result-sets/  Our episode on over-indexing https://postgres.fm/episodes/over-indexing  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss over-indexing — what we mean by it, the regular issues people discuss about it, as well as a novel one Nikolay has come across and benchmarked recently.  Here are some links to things they mentioned: Nikolay’s tweet on over-indexing https://twitter.com/samokhvalov/status/1713101666629927112  Heap-Only Tuples (HOT) optimization https://www.postgresql.org/docs/current/storage-hot.html  Our episode on index maintenance https://postgres.fm/episodes/index-maintenance  PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0  Our episode on connection poolers https://postgres.fm/episodes/connection-poolers  Configurable FP_LOCK_SLOTS_PER_BACKEND (Hackers mailing list discussion) https://www.postgresql.org/message-id/flat/CAM527d-uDn5osa6QPKxHAC6srOfBH3M8iXUM%3DewqHV6n%3Dw1u8Q%40mail.gmail.com  LWLock:lock_manager (Amazon RDS docs) https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.lw-lock-manager.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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss query hints — what they are, what we do and don't have in PostgreSQL, and some other things in and around the ecosystem to be aware of.  Here are some links to some extra things they mentioned: Query planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.html  Statistics used by the planner (docs) https://www.postgresql.org/docs/current/planner-stats.html  default_statistics_target  https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET  Optimiser hints discussion (wiki) https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion   An example mailing list thread from 2006 https://www.postgresql.org/message-id/flat/20061012151439.GT28647%40nasby.net   Peter Geoghegan tweet regarding invalid index https://twitter.com/petervgeoghegan/status/1599191964045672449  plantuner http://sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=blob;hb=HEAD;f=README.plantuner pg_hint_plan https://github.com/ossc-db/pg_hint_plan  Aurora PostgreSQL query plan management https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.overview.html  Building automatic adviser & performance tuning tools - Julien Rouhaud & Tatsuro Yamada: PGCon 2020 (the completely misremembered presentation Michael mentioned 🙈) https://www.youtube.com/watch?v=LQZK6p3SwwA  hypopg https://github.com/HypoPG/hypopg  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

In this episode (recorded live on YouTube), Nikolay discusses Postgres shutdown and startup times – how to troubleshoot them and, when needed, optimize.  Some extra things mentioned in this episode: #PostgresMarathon series – every day, Nikolay posts a new howto-style article https://twitter.com/hashtag/PostgresMarathon Day 2: Postgres shutdown and restart attempts https://twitter.com/samokhvalov/status/1707147450044297673 Day 3: How to troubleshoot long Postgres startup https://twitter.com/samokhvalov/status/1707466169245171773 GitLab repo with #PostgresMarathon posts (markdown): https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos Episode 010 "WAL and checkpoint tuning": https://postgres.fm/episodes/wal-and-checkpoint-tuning ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss Postgres backups — why we need them, what the options are, whether a dump is a backup or not, and some considerations for lowering RPO and RTO at scale.  Here are some links to some extra things they mentioned: pg_dump https://www.postgresql.org/docs/current/app-pgdump.html  pg_basebackup https://www.postgresql.org/docs/current/app-pgbasebackup.html pgBackRest https://github.com/pgbackrest/pgbackrest  WAL-G https://github.com/wal-g/wal-g  Barman https://github.com/EnterpriseDB/barman  Data loss at GitLab (2017) https://about.gitlab.com/blog/2017/02/01/gitlab-dot-com-database-incident/  Dev Deletes Entire Production Database, Chaos Ensues (YouTube video) https://www.youtube.com/watch?v=tLdRBsuvVKc  Our episode on corruption https://postgres.fm/episodes/corruption  DBLab Engine https://github.com/postgres-ai/database-lab-engine  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss the release of PostgreSQL 16 — the most important new features, what they mean for us as users, whether and when to upgrade, and more. Here are some links to some extra things they mentioned: Release notes https://www.postgresql.org/docs/current/release-16.html New Features With Examples (PDF from Noriyoshi Shinoda of Hewlett Packard Enterprise Japan) https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL16Beta1_New_Features_en_20230528_1.pdf   Why Upgrade? (site by depesz) https://why-upgrade.depesz.com/ Waiting for PostgreSQL 16 (blog post series from Depesz) https://www.depesz.com/tag/pg16/ Our episode on favourite features https://postgres.fm/episodes/our-favourite-v16-feature   Our episode on logical replication https://postgres.fm/episodes/logical-replication  Active Active in Postgres 16 (blog post from Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16  AlloyDB adaptive autovacuum https://cloud.google.com/blog/products/databases/alloydb-for-postgresql-under-the-hood-adaptive-autovacuum  Visualizing Postgres I/O Performance (talk by Melanie Plageman at PGCon) https://www.youtube.com/watch?v=CxyPZHG5beI  Our monitoring checklist episode https://postgres.fm/episodes/monitoring-checklist  pgvector https://github.com/pgvector/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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss logical replication — some history, initialization, change data capture, how to scale it, some limitiations, and ways that it is getting better. Here are some links to some things they mentioned: Logical replication https://www.postgresql.org/docs/current/logical-replication.html  GitLab upgraded multi-terabyte, heavily-loaded clusters with zero-downtime https://twitter.com/samokhvalov/status/1700574156222505276  pg_waldump https://www.postgresql.org/docs/current/pgwaldump.html  pg_dump and external snapshots (blog post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/  Failover of logical replication slots in Patroni (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=SllJsbPVaow  Our episode on replication https://postgres.fm/episodes/replication  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss their favourite feature each from the upcoming PostgreSQL 16 release. Here are some links to some things they mentioned: v16 draft release notes https://www.postgresql.org/docs/16/release-16.html PGSQL Phriday #012 invitation from Ryan Booz https://www.pgsqlphriday.com/2023/08/pgsql-phriday-012/  Subscribe options for the podcast https://postgres.fm/subscribe A recent closed source ClickHouse feature https://github.com/ClickHouse/ClickHouse/issues/44767#issuecomment-1683293218   Postgres TV hacking session with Andrey Borodin on \watch with limited number of loops (v16) https://www.youtube.com/watch?v=vTV8XhWf3mo  Allow \watch queries to stop on minimum rows returned (v17) https://github.com/postgres/postgres/commit/f347ec76e2a227e5c5b5065cce7adad16d58d209  pg_stat_io commit mentioning the op_bytes column (v16) https://github.com/postgres/postgres/commit/a9c70b46dbe152e094f137f7e6ba9cd3a638ee25  pg_size_pretty function https://www.postgresql.org/docs/current/functions-admin.html#id-1.5.8.33.9.3.2.2.7.1.1.1  Visualizing Postgres I/O Performance (talk by Melanie Plageman at PGCon) https://www.youtube.com/watch?v=CxyPZHG5beI  Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default  EXPLAIN (GENERIC_PLAN) blog post by Laurenz Albe https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/  Running EXPLAIN on any query (video by Lukas Fittl) https://www.youtube.com/watch?v=CMftYJnqou0  PostgreSQL 16 Beta 1 New Features with Examples.(English Version) by Noriyoshi Shinoda https://twitter.com/nori_shinoda/status/1664481483355226114  Have auto_explain's log_verbose mode honor the value of compute_query_id (commitfest entry) https://commitfest.postgresql.org/42/4136/  Make auto_explain print the query identifier in verbose mode (commit) https://github.com/postgres/postgres/commit/9d2d9728b8d546434aade4f9667a59666588edd6 ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss connections — the options, security and performance tradeoffs, and a few other things to be aware of. Here are some links to some things they mentioned: Episode on connection poolers https://postgres.fm/episodes/connection-poolers  listen_addresses https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-LISTEN-ADDRESSES  SSLMODE Explained (blog post by Andrew Kane) https://ankane.org/postgres-sslmode-explained  pg_hba.conf https://www.postgresql.org/docs/current/auth-pg-hba-conf.html Timing a query (blog post by Bruce Momjian) https://momjian.us/main/blogs/pgblog/2012.html#June_6_2012  How to connect (blog post by Lætitia Avrot) https://mydbanotebook.org/post/cant-connect/  Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462  idle_session_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT  Do you vacuum everyday? (talk by Hannu Krosing) https://www.youtube.com/watch?v=JcRi8Z7rkPg  Don’t use now() with pg_stat_activity (tweet by Nikolay) https://twitter.com/samokhvalov/status/1664981076014690304  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss a listener question — about products that take Postgres and transform it to something that decouples compute from storage (RDS Aurora, GC AlloyDB, Neon etc.) and whether they see something like this landing upstream in the medium term.Here are some links to some things they mentioned: Amazon Aurora https://aws.amazon.com/rds/aurora/ Google Cloud AlloyDB for PostgreSQL https://cloud.google.com/alloydb Neon https://neon.tech/  Google Cloud Spanner https://cloud.google.com/spanner Is Aurora PostgreSQL really faster and cheaper than RDS PostgreSQL? (blog post by Avinash Vallarapu from MigOps) https://www.migops.com/blog/is-aurora-postgresql-really-faster-and-cheaper-than-rds-postgresql-benchmarking/  Deep dive on Amazon Aurora with PostgreSQL compatibility (presentation by Grant McAllister) https://www.youtube.com/watch?v=HQg8wqlxefo  Intro to Aurora PostgreSQL Query Plan Management https://aws.amazon.com/blogs/database/introduction-to-aurora-postgresql-query-plan-management/  Michael Stonebraker Turing Award Lecture  Interview with Stas Kelvich from Neon on Postgres TV https://www.youtube.com/watch?v=4PUKNznq_eM  Interview with Ben Vandiver from Google Cloud Spanner on Postgres TV https://www.youtube.com/watch?v=BW-Uexhv-bk  Timescale Cloud bottomless storage feature (data tiering to Amazon S3) https://www.timescale.com/blog/expanding-the-boundaries-of-postgresql-announcing-a-bottomless-consumption-based-object-storage-layer-built-on-amazon-s3/  Testing Database Changes the Right Way (Heap Analytics article) https://www.heap.io/blog/testing-database-changes-right-way   ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss self-managing Postgres — both the practicalities of doing so, as well as some managed-service style tooling.  Here are some links to some things they mentioned: Our episode on Managed services vs. DIY https://postgres.fm/episodes/managed-services-vs-diy  WAL-G https://github.com/wal-g/wal-g  pgBackRest https://pgbackrest.org/  Barman https://github.com/EnterpriseDB/barman  Dead Man’s Snitch https://deadmanssnitch.com/  Netdata https://www.netdata.cloud/  Upgrades https://postgres.fm/episodes/upgrades   High availability https://postgres.fm/episodes/high-availability  Configuration https://postgres.fm/episodes/default-configuration  Corruption https://postgres.fm/episodes/corruption  Connection poolers https://postgres.fm/episodes/connection-poolers  Index maintenance https://postgres.fm/episodes/index-maintenance  StackGres supported extensions (Michael was wrong, it also has a timescale_tls extension!) https://stackgres.io/extensions/  postgresql_cluster https://github.com/vitabaks/postgresql_cluster  Supabase self-hosting https://supabase.com/docs/guides/self-hosting Tembo https://github.com/tembo-io/tembo  Open source licenses, clouds, Postgres (Postgres TV discussion) https://www.youtube.com/watch?v=1rcbyIjA4gI&t=149s  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss sharding Postgres — what it means, why and when it's needed, and the available options right now.  Here are some links to some things they mentioned: PGSQL Friday monthly blogging event https://www.pgsqlphriday.com/ Did “sharding” come from Ultima Online? https://news.ycombinator.com/item?id=23438399  Our episode on partitioning: https://postgres.fm/episodes/partitioning Vitess https://vitess.io/ Citus https://www.citusdata.com/  Lessons learned from sharding Postgres (Notion 2021) https://www.notion.so/blog/sharding-postgres-at-notion  The Great Re-shard (Notion 2023) https://www.notion.so/blog/the-great-re-shard  The growing pains of database architecture (Figma 2023) Timescale multi-node https://docs.timescale.com/self-hosted/latest/multinode-timescaledb/about-multinode/  PgCat https://github.com/postgresml/pgcat  SPQR https://github.com/pg-sharding/spqr  PL/Proxy https://plproxy.github.io/  Sharding GitLab by top-level namespace https://about.gitlab.com/handbook/engineering/development/enablement/data_stores/database/doc/root-namespace-sharding.html  Loose foreign keys (GitLab) https://docs.gitlab.com/ee/development/database/loose_foreign_keys.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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss data types in PostgreSQL — including system types, choosing between types, types provided by extensions, and more.  Here are some links to some things they mentioned: Data Types (docs) https://www.postgresql.org/docs/current/datatype.html  10 tips for beginners https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners  Tid Scan (explain glossary) https://www.pgmustard.com/docs/explain/tid-scan  Don’t do this (wiki) https://wiki.postgresql.org/wiki/Don't_Do_This  Boundless `text` and back again https://brandur.org/text  UUID episode https://postgres.fm/episodes/uuid  I use ENUM (30min talk by Boriss Mejías) https://archive.fosdem.org/2021/schedule/event/postgresql_i_use_enum_vindicating_the_underdog_of_data_types/  Peter Geoghegan tweet https://twitter.com/petervgeoghegan/status/1680275871905775616  JSON episode https://postgres.fm/episodes/json  pg_repack reorder columns discussion https://github.com/reorg/pg_repack/issues/101  Use bigint https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss HA (high availability) — what it means, tools and techniques for maximising it, while going through some of the more common causes of downtime.  Here are some links to some things they mentioned: https://en.wikipedia.org/wiki/High_availability https://postgres.fm/episodes/upgrades    https://github.com/shayonj/pg_easy_replicate/  pg_easy_replicate discussion on Hacker News https://news.ycombinator.com/item?id=36405761  https://postgres.fm/episodes/connection-poolers  https://www.postgresql.org/docs/current/libpq.html   Support load balancing in libpq (new feature in Postgres 16) https://commitfest.postgresql.org/42/3679/  target_session_attrs options for high availability and scaling (2021; a post by Laurenz Albe) https://www.cybertec-postgresql.com/en/new-target_session_attrs-settings-for-high-availability-and-scaling-in-postgresql-v14/ Postgres 10 highlight - read-write and read-only mode of libpq (2016, a post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/  Postgres 10 highlight - Quorum set of synchronous standbys (2017, a post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-10-quorum-sync/ https://github.com/zalando/patroni  https://postgres.fm/episodes/replication  https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default  Zero-downtime Postgres schema migrations need this: lock_timeout and retries (2021) https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries  A fix in Patroni to mitigate a very long shutdown attempt when archive_command has a lot of WALs to archive https://github.com/zalando/patroni/pull/2067  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss 10 beginner tips Nikolay recently shared — they go into a bit more detail on each, and even disagree a little on one or two!  Here are some links to some things they mentioned: Nikolay’s tweet with all 10 tips: https://twitter.com/samokhvalov/status/1679953049899642880  Tip 1: tuples are physical versions of rows Related episodes: https://postgres.fm/episodes/how-to-become-a-dba , https://postgres.fm/episodes/hot-updates  Tip 2: always use EXPLAIN (ANALYZE, BUFFERS) Related episode: https://postgres.fm/episodes/buffers-by-default  Tip 3: throw away pgAdmin Related episode: https://postgres.fm/episodes/psql-vs-guis   Tip 4: enable as much logging as you can afford Related episode: https://postgres.fm/episodes/default-configuration  Tip 5: install pg_stat_statements Related episodes: https://postgres.fm/episodes/pg_stat_statements and https://postgres.fm/episodes/auto_explain Tip 6: run experiments on realistic data sets (use thin cloning and branching) Related episode: https://postgres.fm/episodes/database-branching  Tip 7: make sure data checksums are enabled  Related episode: https://postgres.fm/episodes/corruption   Tip 8: tune autovacuum to run frequently and move faster Related episode: https://postgres.fm/episodes/vacuum Tip 9: query optimization will eventually be more important than configuration tuning Related episodes: https://postgres.fm/episodes/intro-to-query-optimization and https://postgres.fm/episodes/102-query-optimization Tip 10: indexes need to be rebuilt, unfortunately, since their health decline over time Related episode: https://postgres.fm/episodes/index-maintenance   Bonus tip: the official documentation, release notes, and source code comments/readmes are your friends!  ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss Postgres connection poolers — when and why we need them, the king that is PgBouncer, and the many new pretenders to the throne. Here are links to a few things they mentioned:  max_connections https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS  Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462  PgBouncer https://github.com/pgbouncer/pgbouncer Odyssey https://github.com/yandex/odyssey PgCat https://github.com/postgresml/pgcat  Adopting PgCat: A Nextgen Postgres Proxy https://www.instacart.com/company/how-its-made/adopting-pgcat-a-nextgen-postgres-proxy/  Supavisor https://github.com/supabase/supavisor  pgagroal https://github.com/agroal/pgagroal PgBouncer is useful, important, and fraught with peril (blog post from JP Camara) https://jpcamara.com/2023/04/12/pgbouncer-is-useful.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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael celebrate the podcast's 1 year anniversary by going through several questions and suggestions received over the year. Here are the questions and some links to things we mentioned:  Question 1: Effect of wal_log_hints=on after bulk Deletes: Why next select runs slow and generated tons of WAL? https://twitter.com/dmx551/status/1598253188926570496  wal_log_hints https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LOG-HINTS  Exploring how SELECT Queries can produce disk writes https://blog.okmeter.io/postgresql-exploring-how-select-queries-can-produce-disk-writes-f36c8bee6b6f Question 2: How to get started reading PostgreSQL source code. Maybe a PostgreSQL style C reference guide to consult with for non C programmers https://twitter.com/andatki/status/1578088843940593678  So, you want to be a developer? https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F GitHub search https://github.com/search?q=repo%3Apostgres%2Fpostgres+wal_log_hints&type=code  The Internals of PostgreSQL (by Hironobu SUZUKI) https://www.interdb.jp/pg/  PostgreSQL 14 Internals (by Egor Rogov) https://postgrespro.com/community/books/internals  Question 3: Isolation Levels Primer/Strategies — their uses in different scenarios, battle tested strategies and insights, performance tradeoffs, edge cases to consider at scale (with replication and sharding, etc.) I remember reading some interesting stuff on the jepsen analysis https://jepsen.io/analyses/postgresql-12.3 about Postgres's Serializable Isolation Level behaving more like Snapshot Isolation. Has this type of behavior or another one similar to this affected you or your clients in any significant way? Transaction Isolation https://www.postgresql.org/docs/current/transaction-iso.html What developers find surprising about Postgres transactions https://blog.lawrencejones.dev/isolation-levels/   Question 4: Data encryption in Postgres Cybertec Transparent Data Encryption https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/  EDB Transparent Data Encryption https://www.enterprisedb.com/docs/tde/latest/ Question 5: Migration from other DBMSs PostgreSQL transition/migration guide https://github.com/postgresql-transition-guide/guide   Question 6: Latest failover best practices Patroni https://github.com/zalando/patroni ~~~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 brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay (alone, again) reveals some issues that might hit those who perform major PostgreSQL upgrades with minimal downtime.Links:- "Upgrades" – PostgresFM episode 037: https://postgres.fm/episodes/upgrades- recovery_target_lsn: https://postgresqlco.nf/doc/en/param/recovery_target_lsn/- recovery_target_action: https://postgresqlco.nf/doc/en/param/recovery_target_action/- pg_easy_replicate https://github.com/shayonj/pg_easy_replicate/- HN discussion of this tool: https://news.ycombinator.com/item?id=36405761- Waiting for Postgres 16: Logical decoding on standbys: https://pganalyze.com/blog/5mins-postgres-16-logical-decoding- pg_upgrade and logical replication (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud- allow upgrading publisher node (proposal to natively support running pg_upgrade on publisher, WIP): https://commitfest.postgresql.org/43/4273/- ❓🤔 pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption? (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/CAM527d8heqkjG5VrvjU3Xjsqxg41ufUyabD9QZccdAxnpbRH-Q%40mail.gmail.com

Lonely Nikolay discusses the performance aspects of using UUID for primary keys. Here are links to a few things I mentioned:  "postgresql" posts on HN, most popular last week (Algolia search): https://hn.algolia.com/?dateRange=pastWeek&page=0&prefix=true&query=postgresql&sort=byPopularity&type=story Unexpected downsides of UUID keys in PostgreSQL (a post by Ants Aasma, Cybertec): https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-keys-in-postgresql/ HN discussion of that post: https://news.ycombinator.com/item?id=36429986 Additional math by me (converting to bytes): https://twitter.com/samokhvalov/status/1671962111092850689 Updated RFC4122 (proposal): https://github.com/ietf-wg-uuidrev/rfc4122bis Status of that proposal: https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/history/ Patch UUID v7 (commitfest record): https://commitfest.postgresql.org/43/4388/ Postgres hacking with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8 (where that patch was developed) ~~~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!If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss memory in PostgreSQL — how it works, important settings, and how to go about tuning them. Here are links to a few things we mentioned:  Resource Consumption (PostgreSQL docs) https://www.postgresql.org/docs/current/runtime-config-resource.html Andres Freud tweet about shared_buffers https://twitter.com/AndresFreundTec/status/1438912583554113537  Henrietta (Hettie) Dombrovskaya https://hdombrovskaya.wordpress.com/about-the-author/ annotated.conf (by ash Berkus) https://github.com/jberkus/annotated.conf  Our episode about checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning  Our episode about BUFFERS https://postgres.fm/episodes/buffers-by-default  Analyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266#memory-usage Tuning memory parameters for Aurora PostgreSQL https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.BestPractices.Tuning-memory-parameters.html  RDS for PostgreSQL memory https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.concepts.html#PostgreSQL.Tuning.concepts.memory  EDB guide by Via Fearing https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization#resourceusage  pg_stat_kcache https://github.com/powa-team/pg_stat_kcache  pg_buffercache https://www.postgresql.org/docs/current/pgbuffercache.html  Process and Memory Architecture chapter (from Hironobu SUZUKI) https://www.interdb.jp/pg/pgsql02.html PostgreSQL 14 internals PDF book from Egor Rogov (pages 37, 184)  https://edu.postgrespro.com/postgresql_internals-14_en.pdf  src/backend/storage/buffer/README https://github.com/postgres/postgres/blob/master/src/backend/storage/buffer/README  pg_backend_memory_contexts (PostgreSQL 14+) https://www.postgresql.org/docs/current/view-pg-backend-memory-contexts.html  pg_stat_io (coming in PostgreSQL 16) https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW  pg_prewarm https://www.postgresql.org/docs/current/pgprewarm.html  Configuring work_mem blog post https://www.pgmustard.com/blog/work-mem ~~~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!If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss Postgres extensions — what they are, how they affect your decisions around Postgres, and some things to keep in mind when using them. Here are links to a few things we mentioned:  Extensions (docs) https://www.postgresql.org/docs/current/external-extensions.html  Extension (pgPedia) https://pgpedia.info/e/extension.html  pgvector https://github.com/pgvector/pgvector  PL/Rust https://github.com/tcdi/plrust ZomboDB https://github.com/zombodb/zombodb  Why is Postgres popular episode https://postgres.fm/episodes/why-is-postgres-popular  Citus https://github.com/citusdata/citus TimescaleDB https://github.com/timescale/timescaledb  OrioleDB https://github.com/orioledb/orioledb PostGIS https://trac.osgeo.org/postgis/  “There’s an extension for that” (tweet from Robert Treat) https://twitter.com/robtreat2/status/1665735485883314178   RDS supported extensions https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html  RUM https://github.com/postgrespro/rum  pg_repack https://github.com/reorg/pg_repack  PGXN https://pgxn.org/  pgTrunk by CoreDB https://pgtrunk.io/  Dbdev by Supabase https://supabase.com/blog/dbdev  StackGres https://github.com/ongres/stackgres pg_tle by AWS https://github.com/aws/pg_tle  Modern Postgres monitoring (slides from Nikolay’s tutorial) https://twitter.com/samokhvalov/status/1664686535562625034  Awesome Postgres https://github.com/dhamaniasad/awesome-postgres  ~~~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!If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss zero-downtime schema migrations — why they're a challenge, a variety of different cases, and some things you can do to achieve them. Here are links to a few things we mentioned:  Comparison of JOINS: MongoDB vs. PostgreSQL (blog post by Michael Stonebraker and Álvaro Hernández) Common DB schema change mistakes (blog post by Nikolay) lock_timeout and retries (blog post by Nikolay) lock_timeout Fast Column Creation with Defaults (blog post by Brandur) Database Schema Changes Without Downtime (new version of blog post by Braintree) Zero-downtime Postgres migrations - the hard parts (blog post from GoCardless) GitLab migration_helpers.rb  GitLab migration style guide dblab  ~~~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!If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss parallelism — both parallel query execution and other parallel operations. Here are links to a few things we mentioned:  Parallel query (docs) Parallelism in PostgreSQL 11 (talk by Thomas Munro) Parallelism in PostgreSQL 15 (talk by Thomas Munro) Towards Millions TPS (blog post by Alexander Korotkov) Memory resource consumption (docs) Our episode about index maintenance Our episode about partitioning  Patch to make postgres_fdw parallel-safe (by Swarm64)  PostgreSQL Parallelism Do’s and Don’ts (talk by Sebastian Dressler) Increasing max_parallel_workers_per_gather (blog post by Michael) ~~~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!If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss database corruption — various types, how they can come about, and what to do (and not do) if you come across it.  Here are links to a few things we mentioned:  The dangers of streaming across versions of glibc (TripAdvisor mailing list thread) The floor is Java meme Data Corruption talk by Sebastian Webber (on Postgres TV)  Data corruption monitoring & troubleshooting talk by Andrey Borodin (on Postgres TV) Postgres data corruption (document from Nikolay)  Data Corruption and Bugs Runbook (document from Nikolay) Corruption (Postgres wiki) Checksums pg_checksums Original pg_checksums (by Credativ) amcheck Our episode on index maintenance 14.4 release notes about create index / reindex concurrently issue and fix amcheck to check unique constraints in btree indexes (Commitfest entry) amcheck verification of GiST and GIN (Commitfest entry)  How to corrupt your Postgres database (blog post from Cybertec) Christophe Pettus talk Christophe Pettus slides pg_hexedit pageinspect pg_catcheck ~~~What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss using ChatGPT for Postgres tasks — should you, if so what for, and some things to be mindful of! Here are links to a few things we mentioned:  ChatGPT Nikolay’s polls on Twitter and on LinkedIn  The Art of PostgreSQL (book by Dimitri Fontaine) SQL Performance Explained (book by Markus Winand) Nikolay’s YouTube correction about deletes and index amplification Don’t use ChatGPT to solve problems (blog post by Christophe Pettus) Query optimization session with ChatGPT, Michael, and Nikolay (on YouTube) DBeaver SmartAssistance feature   Depesz anonymization feature ~~~What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss pg_stat_statements — why everyone should use it, but also some downsides! Here are links to a few things we mentioned:  pg_stat_statements (docs) pg_stat_statements (PgPedia) PGSQL Phriday Observer effect in pg_stat_statements and pg_stat_kcache (Postgres Hacking session on Postgres TV)  track_io_timing (docs) Overhead comment (by Tom Kate, via Jeremy Schneider)  pg_stat_monitor PGCon Our episode on query analysis Marginalia  ~~~What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss auto_explain — what it is, how it can help, and how to check it's overhead. Here are links to a few things we mentioned:  auto_explain (docs) ScaleGrid guide to auto_explain  Can auto_explain, with timing, have low overhead? (Blog post by Michael) pgBadger  pg_stat_monitor EXPLAIN ANALYZE may be lying to you (blog post by Álvaro from Ongres) pg_test_timing Our episode on benchmarking Database Lab Engine ~~~What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss queues in Postgres — the pros and cons vs dedicated queuing tools, and some tips for scaling. A couple of apologies-in-advance:  Near the end, we incorrectly say "idempotent" when we meant "stateless", and also 50 TPS instead of 500 TPS We also had a couple of audio issues, sorry! Here are links to a few things we mentioned:  Recent discussion on Hacker News PgQ What is SKIP LOCKED (blog post by Craig Ringer)  autovacuum Postgres queues (blog post by Brandur) pg_repack Our episode on partitioning Nikolay’s Twitter poll Subtransactions Considered Harmful (blog post by Nikolay) ~~~What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss a listener request — special considerations for databases that are used in a read-only mode all day, and get an update at night with additional data.Here are links to a few things we mentioned:  Index-only scans Vacuum UK Covid-19 dashboard pg_repack Partitioning Our episode on BRIN indexes Always load sorted data (blog post by Haki Benita) GIN indexes: the good and the bad (blog post by Lukas Fittl) Our episode on materialised views pg_buffercache Towards Millions TPS (blog post by Alexander Korotkov) Postgres WASM (by Snaplet and Supabase) Yugabyte AWS Aurora  Continuous Archiving and Point-in-Time Recovery (docs) Our episode on checkpoint tuning Our episode on partitioning PgQ Neon branching Database Lab Engine Cluster ~~~What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss table partitioning — what it is, why and when it's helpful, and some considerations for your partition key. Here are links to a few things we mentioned:  Partitioning docs pg_partman Index maintenance episode  Timescale partitioning pg_cron Xtreme PostgreSQL (talk by Christophe Pettus) Database Antipatterns (also by Christophe, slides 46-49) Understanding an outage (blog post by Duffel) ~~~What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)~~~Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

This week we're sharing an edited version of Nikolay's recent interview with Peter Zaitsev from Percona — they discuss MySQL vs Postgres, Percona’s success, open source licenses, FerretDB, and databases on Kubernetes… phew! And here are some links to a few things mentioned:  Percona pgCloudHacker browser extension  PMM Percona Distribution for PostgreSQL FerretDB Peter's Twitter profile Peter's LinkedIn profile ------------------------What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss command line and graphical user interfaces for Postgres — what they are, some tips and tricks for learning, and what we each use and prefer. Here are links to a few things we mentioned:  psql (docs) psql is awesome! (talk by Lætitia Avrot) psql tips (site by Lætitia Avrot) pgAdmin Postico  DBeaver DataGrip PgManage (new Command Prompt fork of OmniDB)  PopSQL postgres_dba pspg Materialized views episode pgcli ------------------------What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss major and minor version Postgres upgrades — what they are, how often they come out, and how regularly we should be upgrading. Here are links to a few things we mentioned:  Postgres versioning policy  why-upgrade (by depesz) postgresqlco.nf (by Ongres) postgresql.conf comparison (by Rustproof Labs)  pg_upgrade Logical replication  CHECKPOINT amcheck Locale data changes (e.g. glibc upgrades) ANALYZE Upgrades are hard (summary of panel discussion by Andreas 'ads' Scherbaum) spilo Recent pgsql hackers discussion about using logical and pg_upgrade together ------------------------What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss wait events — what they are, why we have them, and how to use them to help diagnose performance issues. Here are links to a few things we mentioned:  Wait events table (docs) 9.6 release notes PostgreSQL Scalability (blog post by Alexander Korotkov) Wait event analysis in pganalyze auto_explain Database Lab Engine track_io_timing pg_test_timing pgBadger Coroot Okmeter pgwatch2 Postgres.ai Edition pg_wait_sampling pgsentinel Datadog AWS RDS docs for PostgreSQL wait events pgMustard newsletter PASH Viewer pgCenter Intro to query optimisation episode Monitoring checklist episode ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss TOAST (The Oversized-Attribute Storage Technique) — what it is, how it works, and some general things to be aware of. Here are links to a few things we mentioned:  TOAST docs TOAST wiki Hussein Nasser on rows per page (Twitter) Toasting in action (dbi services blog) Interview with Peter Zaitsev (Postgres TV) Building columnar compression in a row-oriented database (Timescale blog post) The Surprising Impact of Medium-Size Texts on PostgreSQL Performance (blog post by Haki Benita) PostgreSQL at Scale: Saving Space Basically for Free (blog post by Braintree on column Tetris) postgres_dba alignment padding query  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss JSON — our options for storing it in Postgres, whether or when we should, as well as a brief intro and some tips for JSON functions available. Here are links to a few things we mentioned:  hstore XML type XML functions JSON types JSON functions JSONB indexing NULLS episode Why Postgres is popular episode PostgreSQL 12 release notes What’s New in SQL:2016 (blog post by Markus Winand) SQL/JSON is postponed (blog post by depesz)  JSON[b] Roadmap (talk by Oleg Bartunov) Slides, with benchmarks RUM access method JSON in PostgreSQL: how to use it right (blog post by Laurenz Albe from Cybertec) pg_jsonschema TOAST_TUPLE_TARGET and TOAST_TUPLE_THRESHOLD  The Surprising Impact of Medium-Size Texts on PostgreSQL Performance (blog post by Haki Benita)  Aggregate functions How to store and index json data (blog post by ScaleGrid) When to avoid JSONB (blog post by Heap) FerretDB ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss real-time analytics — what it means, what the options are, and some tips if you're trying to implement it within Postgres. Here are links to a few things we mentioned:  Loose index scan / skip scan with recursive CTE (wiki) Zheap (wiki) cstore_fdw (now part of Citus) Timescale compression docs Hydra founders interview (on Postgres TV) Materialised views episode  pg_ivm Timescale continuous aggregates docs Clickhouse Snowflake Replication episode Timescale bottomless storage on S3 (blog post) pg_partman Querying Postgres from DuckDB (blog post) Heap blog (filter by “Engineering”) Incremental View Maintenance (wiki) PostgreSQL HyperLogLog   Faster counting (by Joe Nelson on the Citus blog) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss benchmarking — reasons to do it, and some approaches, tools, and resources that can help. Here are links to a few things we mentioned:  Towards Millions TPS (blog post by Alexander Korotkov) Episode on testing  Episode on buffers  pgbench sysbench Improving Postgres Connection Scalability (blog post by Andres Freund) pgreplay pgreplay-go JMeter pg_qualstats pg_query Database experimenting/benchmarking (talk by Nikolay, 2018) Database testing (talk by Nikolay at PGCon, 2022) Systems Performance (Brendan Gregg’s book, chapter 12) fio Netdata Subtransactions Considered Harmful (blog post by Nikolay including Netdata exports) WAL compression benchmarks (by Vitaly from Postgres.ai) Dumping/restoring a 1 TiB database benchmarks (by Vitaly from Postgres.ai) PostgreSQL on EXT3/4, XFS, BTRFS and ZFS (talk slides from Tomas Vondra) Insert benchmark on ARM and x86 cloud servers (blog post by Mark Callaghan) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Nikolay and Michael discuss the default config — some tools and principles you can use to customise it, as well as several parameters you probably always want to change.Here are links to a few things we mentioned:  shared_buffers Andres Freund tweets about shared_buffers  PGTune Leopard  Cybertec Configurator pg_stat_statements JIT configuration postgresqlco.nf annotated.conf OtterTune work_mem random_page_cost max_connections What to log max_wal_size WAL and checkpoint tuning episode  effective_cache_size Intro to Performance Tuning and Optimization (EDB guide) max_parallel_workers_per_gather  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  The Cost of Cloud, a Trillion Dollar Paradox (blog post from Andreessen Horowitz) OVH Hetzner postgresql_cluster Why we're leaving the cloud (blog post by DHH from Basecamp) Managed services vs. DIY episode ec2instances.info Vantage Postgres TV episode with Everett Berry from Vantage Migrating to Aurora: easy except the bill (blog post by Kimberly Nicholls from Gridium) Database Lab Engine Postgres.ai consulting Netdata sysbench fio Query macro analysis episode Top queries by buffers (Gist from Michael) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) pgAudit (site) pgAudit (repo) noset (extension) Splunk Kibana Timescale CREATE TRIGGER docs Transition table triggers (blog post by David Fetter) Table Audit (blog post by Lorenzo Alberton) Row change auditing options (blog post by CYBERTEC) Hydra founders interview (on Postgres TV) max_slot_wal_keep_size eBPF Building a perf-like tool for PostgreSQL (talk by Ronan Dunklau) Party tricks for PostgreSQL: perf, ftrace and bpftrace (talk by Dmitry Dolgov) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  pg_dump  pg_restore   pgcopydb (tool by Dimitri Fontaine)   pg_dump and external snapshots (blog post by Michael Paquier)  Systems Performance (book by Brendan Gregg)   Performance troubleshooting methodologies (two part talk by Brendan Gregg)   pg_basebackup   wal-g  pgBackRest  Database Lab Engine   Database branching episode   ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  The Internals of PostgreSQL chapter 5 (book by Hironobu SUZUKI)  PostgreSQL 14 internals chapter 7 (book by Egor Rogov)   Transaction ID Wraparound (blog post from Sentry)  What We Learned from the Recent Mandrill Outage (blog post from Mailchimp)  How to simulate transaction ID wraparound (blog post by Prashant Dixit) Add 64-bit XIDs into PostgreSQL 16 (commitfest entry)   Partitioning (docs)   Consider using VACUUM’s INDEX_CLEANUP option (tweet by Peter Geoghan)  Add wraparound failsafe to VACUUM (commit)  Do you vacuum everyday? (talk by Hannu Krosing)  Multixacts wraparound monitoring (mailing list thread)  Subtransactions Considered Harmful (blog post by Nikolay)  Buffer management in PostgreSQL (talk by Alexander Korotkov)  OrioleDB  pageinspect pg_hexedit   pg_visibility   Visibility Map (docs) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: 1. Startups building momentum Aiven raised $210m   Timescale raised $110m   Hasura raised $100m   Supabase raised $80m   Neon raised $30m  Hydra   OrioleDB   2. Educational resources Postgres FM started 👋 Postgres TV became more active (including topic playlists)   Tobias Petry tips on Twitter and SQL for Devs   Hussein Nasser YouTube channel (backend engineering)  Postgres Weekly (newsletter)  3. Sharding progress Citus goes fully open source   SPQR   pgcat  Sharding Postgres at Notion (blog post)  PlanetScale (MySQL)  4. Database branching is coming Database Lab Engine   Neon branching  OrioleDB branching   Crunchy Bridge  Database branching episode   5. Postgres is everywhere All cloud providers AlloyDB announced  Kubernetes operators (comparison blog post)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  ANALYZE (docs) Autovacuum config (docs) Statistics used by the planner (docs)  CREATE STATISTICS (docs)  Row count estimates (pgMustard blog post)  pg_hint_plan  Optimizer methodology (talk by Robert Haas)  Tomáš Vondra on statistics and hints (an excellent interview we forgot to mention, sorry!)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Replication (docs)  9.0 release notes (including built-in replication)  Slony Londiste pg_is_in_recovery (pgPedia page)  Patroni  Multiple Databases with Active Record hot_standby_feedback (docs)  max_standby_archive_delay or max_standby_streaming_delay (for WAL-shipping and WAL-streaming respectively) synchronous_commit Synchronous replicas Database Lab Engine  Neon Branching   Past, Present, and Future of Logical Replication (Postgres TV with Amit Kapila)   Failover of logical replication slots in Patroni (Postgres TV with Alexander Kukushkin)   PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay)  Why we spent the last month eliminating PostgreSQL subtransactions (blog post by GitLab)  Fivetran  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Date/Time Types (docs)  Don’t use timestamp without time zone (wiki)   Date/Time functions and operators (docs)   Postgres AT TIME ZONE explained (blog post by Bruce Momjian)  Our episode on BRIN indexes   RUM indexes  allballs (mailing list thread)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  PostgREST documentation Supabase No offset (blog post by Markus Winand) JSON functions and operators (PostgreSQL docs) Stored procedures (a previous Postgres FM episode) PostGraphile  Hasura  Parse, Inc.  Firebase PostgresML PgQ  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Apologies, Michael's audio is not great in this, we'll hopefully be back to normal next week!Here are links to a few things we mentioned:  Materialized views (docs) Refresh materialized view (docs) Timescale blog post PlanetScale Boost (content warning: MySQL)  Incremental Materialized Views with pg_ivm (video by Lukas Fittl)  Articles on how to do your own incremental updates(?) Materialize (company)  Materialize talk Incremental View Maintenance (Postgres wiki)   Implementing Incremental View Maintenance (mailing list thread)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Heap-only tuple updates (docs) README file (source code) Heap Only Tuple chapter (Internals of PostgreSQL by Hironobu SUZUKI)  How partial, covering, and multicolumn indexes may slow down UPDATEs (blog post by Nikolay)  Why Uber switched from Postgres to MySQL (blog post)   Fighting write amplification with HOT updates (Adyen blog post)  HOT Updates vs Bottom-Up Index Deletion (video by Lukas Fittl from pganalyze)  Reduced Bloat with Bottom-Up Deletion (blog post by Hamid Akthar from Percona)  What is fillfactor and how does it affect performance? (blog post by Kaarel Moppel from Cybertec) ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Database Lab Engine Overview of Aurora cloning  PlanetScale branching Common DB schema change mistakes (blog post by Nikolay) Supabase vision Neon branching Shift-left testing ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  A brief history of Version Control Systems (blog post by Mehran Hajirajabi) Redgate Source Control for Oracle (and SQL Server) Flyway  Sqitch  Liquibase  pgAdmin Schema Diff  Migra  PostgresCompare  Schema based versioning and deployment (blog post by Valentine Gogichashvili) Change management tools and techniques (PostgreSQL Wiki) GitLab migration_helpers.rb  Database schema changes without downtime (blog post by James Coleman from Braintree/PayPal)  Zero-downtime Postgres schema migrations need lock_timeout and retries (blog post by Nikolay)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  PostgreSQL 14 coin (upside down!) Intro to Planner Hacking (talk by Melanie Plageman) Demystifying contributing to PostgreSQL (talk by Lætitia Avrot) How to become a PostgreSQL contributor (blog post by Aleksander Alekseev) Compile and install from source code (PostgreSQL Wiki) PostgreSQL mailing lists GitHub PostgreSQL mirror GitLab PostgreSQL mirror Commitfests So, you want to be a developer? (PostgreSQL Wiki) Resolving the search engine issue (mailing list thread) Planet PostgreSQL pg_stat_monitor (extension by Percona) ZomboDB pgx (framework for developing extensions) Awesome Postgres Depesz blog  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Extending SQL  PostgREST   PL/pgSQL  EdgeDB   pgTAP   Sqitch  Flyway   Liquibase   client_min_messages   log_min_messages RAISE DEBUG Simplify: move code into database functions (blog post by Derek Sivers)   PL/sh   Supabase  auto_explain.log_nested_statements   Hasura   ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  PostgreSQL 15 release notes Speeding up sort performance in Postgres 15 (blog post by David Rowley) Past, Present, and Future of Logical Replication (talk by Amit Kapila)  Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert)  Do you vacuum everyday? (talk by Hannu Krosing)  Why upgrade PostgreSQL? (by depesz) What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas Google doc.If you would like to share this episode, here's a good link (and thank you!)------------------------Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  pg_plan_advsr  How partial, covering, and multicolumn indexes may slow down UPDATEs (blog post by Nikolay) Why Uber Switched from Postgres to MySQL (blog post) pganalyze index advisor Nancy bot (project is not active) pgreplay pgreplay go   Real Application Testing on YugabyteDB with pgreplay (blog post by Franck Pachot)  pg_query   Database Lab thin clones  Migrating to Aurora: easy except the bill (blog post by Kimberley Nicholls)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

This episode was badly affected by internet issues. Hopefully the edit came out ok, but the quality should be back to a better level from next week.Here are links to a few things we mentioned:  Monthly blog event, PGSQL Phriday (blog post from Ryan Booz)  Who or what made Postgres cool? (tweet from Kenneth Cassel)  PostGIS  Acquisition of Sun by Oracle  DB-Engines trend Hacker News hiring trends  Supabase on GitHub (nearly 40k stars) How I Built This (podcast)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Important correction from this episode: amcheck promises no false positives, not no false negatives, sorry!Here are links to a few things we mentioned:  pgstattuple pg_repack postgres-checkup  Workload Analysis for GitLab.com Bloat Analysis (also from GitLab) Bloat, pg_repack, and deferred constraints (blog post by Miro)  amcheck Peter Geoghegan interview (on Postgres TV)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  pg_stat_statements pgFouine pgBadger pg_query pg_stat_activity auto_explain Can auto_explain (with timing) have low overhead? (blog post by Michael) track_io_timing pgbench PgHero pgCenter pgwatch2 (Postgres AI edition) pg_stat_kcache PASH Viewer ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  LSN (log sequence number) On the impact of full-page writes (blog post by Tomas Vondra) Deep dive on Aurora Postgres (talk by Grant McAlister)  Netdata monitoring  PostgresqlCO.NF  pgPedia  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Using EXPLAIN (PostgreSQL documentation) explain.depesz.com explain.dalibo.com pgMustard  EverSQL pganalyze pg_stat_monitor (Extension by Percona) Recent thread on hackers mailing list about plan_id in pg_stat_activity auto_explain EXPLAIN observer effect (Ongres blog post by Álvaro Hernández) auto_explain overhead (blog post by Michael)   pg_test_timing Database Lab Engine (for thin clones) Our previous episode on BUFFERS EXPLAIN Explained (talk by Josh Berkus) A beginner's guide to EXPLAIN (talk by Michael) A deeper dive into EXPLAIN (talk by Michael) EXPLAIN glossary (pgMustard docs) Topic suggestions document ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our Google doc.If you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

And few things we mentioned:  Topic request on Reddit — thanks HerbyHoover! Haki Benita's blog  PostgreSQL documentation (table of contents)  Planet PostgreSQL (blog aggregator)  MVCC Unmasked (by Bruce Momjian)  The Internals of PostgreSQL (by Hironobu SUZUKI) PostgreSQL 14 Internals — parts I and II (by Egor Rogov) Cybertec blog modern-sql.com (by Markus Winand) use-the-index-luke.com (by Markus Winand) The Art of PostgreSQL (by Dimitri Fontaine)  explain.depesz.com explain.dalibo.com  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork  

Monitoring checklist (dashboard 1): TPS and (optional but also desired) QPS Latency (query duration) — at least average. Better: histogram, percentiles Connections (sessions) — stacked graph of session counts by state (first of all: active and idle-in-transaction; also interesting: idle, others) and how far the sum is from max_connection (+pool size for PgBouncer). Longest transactions (max transaction age or top-n transactions by age), excluding autovacuum activity Commits vs rollbacks — how many transactions are rolled back Transactions left till transaction ID wraparound Replication lags / bytes in replication slot / unused replication slots Count of WALs waiting to be archived (archiving lag) WAL generation rates Locks and deadlocks Basic query analysis graph (top-n by total_time or by mean_time?) Basic wait event analysis (a.k.a. “active session analysis” or “performance insights”) And links to a few things we mentioned:  Postgres monitoring review checklist (community document)  pgstats.dev  Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund)  Transaction ID Wraparound in Postgres (blog post by David Cramer)  Subtransactions Considered Harmful (blog post by Nikolay) datadoghq.com   pgwatch2 (Postgres.ai Edition)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Do you vacuum everyday? (talk by Hannu Krosing) Autovacuum tuning (EDB guide)  When autovacuum does not vacuum (2ndQuadrant blog post by Tomas Vondra)  Autovacuum tuning basics (old 2ndQuadrant blog post) Discussion with Anastasia Lubennikova (on RuPostgres, in Russian)   B-tree indexes (talk by Anastasia Lubennikova, in English)  Discussion with Peter Geoghegan (on Postgres TV) pg_repack  pg_squeeze  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  Three-valued_logic (Wikipedia) Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert)  Practical SQL for Data Analysis — Interpolation (blog post by Haki Benita)  What is the deal with NULLs? (blog post by Jeff Davis)  NULL in SQL: Indicating the Absence of Data (Markus Winand on Modern SQL)  The Art of PostgreSQL (book by Dimitri Fontaine)  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned:  EXPLAIN parameters (PostgreSQL documentation) EXPLAIN (ANALYZE) needs BUFFERS (blog post by Nikolay) Using BUFFERS for query optimization (blog post by Michael)   H3 indexes on PostGIS data (blog post by Ryan Lambert) Turning BUFFERS on by default (latest patch) pgMustard  explain.depesz.com explain.dalibo.com Database Lab Engine ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

Here are links to the two main resources we mentioned:  Paul Ramsey's recent blog post on BRIN indexes Tomas Vondra's slides on BRIN index improvements A few other things we mentioned: B-tree Wikipedia page  pg_repack  pg_squeeze  ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

A well as discussing pros and cons, we mentioned a LOT of different providers and tools, and a few good articles/videos too. 😅Here are links to most of them, roughly in the order they came up:  How Auto Trader migrated its on-prem databases to Cloud SQL PostgreSQL Community Panel: Upgradability Postgres TV Open Talks PostgreSQL Conference Europe Hannu Krosing — excellent vacuum talk pg_docs_bot — browser extension for getting to the current docs Amazon RDS for PostgreSQL Google Cloud SQL for PostgreSQL Heroku Postgres Crunchy Bridge Spilo: HA PostgreSQL Clusters with Docker Aiven for PostgreSQL AlloyDB for PostgreSQL Neon Yugabyte ScaleGrid PostgreSQL Hosting StackGres Timescale OrioleDB Citus Supabase PlanetScale pg_stat_kcache pg_wait_sampling EDB BigAnimal Azure Database for PostgreSQL ------------------------What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork 

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofidesIf you would like to share this episode, here's a good link (and thank you!)Postgres FM is brought to you by: Nikolay Samokhvalov, founder of Postgres.ai Michael Christofides, founder of pgMustard With special thanks to: Jessie Draws for the amazing artwork