Postgres FM

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.htmlEXPLAIN Explained (talk by Josh Berkus https://www.youtube.com/watch?app=desktop&v=mCwwFAl1pBUBeyond joins and indexes (slides from talk by Bruce Momjian) https://momjian.us/main/writings/pgsql/beyond.pdfDepesz https://explain.depesz.comDalibo https://explain.dalibo.compgMustard https://www.pgmustard.com/A beginners guide to explain (talk by Michael) https://www.youtube.com/watch?v=31EmOKBP1PY&t=24sExplain beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4auto_explain episode https://postgres.fm/episodes/auto_explainRow estimates episode https://postgres.fm/episodes/row-estimatesSlow queries and slow transactions episode https://postgres.fm/episodes/slow-queries-and-slow-transactionsBenchmarking episode https://postgres.fm/episodes/benchmarkingEXPLAIN 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-N8UUIDv7 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.aiMichael Christofides, founder of pgMustardWith 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.Update: the idea Nikolay mentioned at the end of this episode turns out to be a little fraught (and as such, inadvisable). 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.aiMichael Christofides, founder of pgMustardWith 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.htmlUnique 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-unknownAdvanced 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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith 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.htmlBoyce–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.aiMichael Christofides, founder of pgMustardWith 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.htmlauto_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-introRunning 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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith 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.plantunerpg_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.aiMichael Christofides, founder of pgMustardWith 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/PostgresMarathonDay 2: Postgres shutdown and restart attempts https://twitter.com/samokhvalov/status/1707147450044297673Day 3: How to troubleshoot long Postgres startup https://twitter.com/samokhvalov/status/1707466169245171773GitLab repo with #PostgresMarathon posts (markdown): https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtosEpisode 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.aiMichael Christofides, founder of pgMustardWith 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.htmlpgBackRest 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.aiMichael Christofides, founder of pgMustardWith 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.htmlNew 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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the elephant 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.htmlPGSQL Phriday #012 invitation from Ryan Booz https://www.pgsqlphriday.com/2023/08/pgsql-phriday-012/ Subscribe options for the podcast https://postgres.fm/subscribeA 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.aiMichael Christofides, founder of pgMustardWith 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.htmlTiming 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.aiMichael Christofides, founder of pgMustardWith 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/alloydbNeon https://neon.tech/ Google Cloud Spanner https://cloud.google.com/spannerIs 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 https://www.youtube.com/watch?v=BbGeKi6T6QIInterview 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.aiMichael Christofides, founder of pgMustardWith 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-hostingTembo 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.aiMichael Christofides, founder of pgMustardWith 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/partitioningVitess 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) https://www.figma.com/blog/how-figma-scaled-to-multiple-databases/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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith 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_availabilityhttps://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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the elephant 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 rowsRelated episodes: https://postgres.fm/episodes/how-to-become-a-dba and 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 pgAdminRelated episode: https://postgres.fm/episodes/psql-vs-guis Tip 4: enable as much logging as you can affordRelated episode: https://postgres.fm/episodes/default-configuration Tip 5: install pg_stat_statementsRelated episodes: https://postgres.fm/episodes/pg_stat_statements and https://postgres.fm/episodes/auto_explain, and https://postgres.fm/episodes/macro-query-analysis-introTip 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 fasterRelated episode: https://postgres.fm/episodes/vacuumTip 9: query optimization will eventually be more important than configuration tuningRelated episodes: https://postgres.fm/episodes/intro-to-query-optimization and https://postgres.fm/episodes/102-query-optimizationTip 10: indexes need to be rebuilt, unfortunately, since their health decline over timeRelated 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.aiMichael Christofides, founder of pgMustardWith 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/pgbouncerOdyssey https://github.com/yandex/odysseyPgCat 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/pgagroalPgBouncer 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.aiMichael Christofides, founder of pgMustardWith 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-f36c8bee6b6fQuestion 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%3FGitHub 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.htmlWhat developers find surprising about Postgres transactions https://blog.lawrencejones.dev/isolation-levels/ Question 4: Data encryption in PostgresCybertec 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 DBMSsPostgreSQL transition/migration guide https://github.com/postgresql-transition-guide/guide Question 6: Latest failover best practicesPatroni 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.aiMichael Christofides, founder of pgMustardWith 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~~~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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork

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=storyUnexpected 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=36429986Additional math by me (converting to bytes): https://twitter.com/samokhvalov/status/1671962111092850689Updated RFC4122 (proposal): https://github.com/ietf-wg-uuidrev/rfc4122bisStatus 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.aiMichael Christofides, founder of pgMustardWith 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.htmlAndres 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-usageTuning 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.htmlPostgreSQL 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.aiMichael Christofides, founder of pgMustardWith 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/plrustZomboDB https://github.com/zombodb/zombodb Why is Postgres popular episode https://postgres.fm/episodes/why-is-postgres-popular Citus https://github.com/citusdata/citusTimescaleDB https://github.com/timescale/timescaledb OrioleDB https://github.com/orioledb/orioledbPostGIS 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/stackgrespg_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.aiMichael Christofides, founder of pgMustardWith 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_timeoutFast 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 guidedblab ~~~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.aiMichael Christofides, founder of pgMustardWith 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 maintenanceOur 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.aiMichael Christofides, founder of pgMustardWith 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 memeData 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)Checksumspg_checksumsOriginal pg_checksums (by Credativ)amcheckOur episode on index maintenance14.4 release notes about create index / reindex concurrently issue and fixamcheck 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 talkChristophe Pettus slidespg_hexeditpageinspectpg_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.aiMichael Christofides, founder of pgMustardWith 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: ChatGPTNikolay’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 amplificationDon’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.aiMichael Christofides, founder of pgMustardWith 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 PhridayObserver 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_monitorPGConOur episode on query analysisMarginalia ~~~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.aiMichael Christofides, founder of pgMustardWith 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_monitorEXPLAIN ANALYZE may be lying to you (blog post by Álvaro from Ongres)pg_test_timingOur episode on benchmarkingDatabase 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.aiMichael Christofides, founder of pgMustardWith 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 TPSWe also had a couple of audio issues, sorry!Here are links to a few things we mentioned: Recent discussion on Hacker NewsPgQWhat is SKIP LOCKED (blog post by Craig Ringer) autovacuumPostgres queues (blog post by Brandur)pg_repackOur episode on partitioningNikolay’s Twitter pollSubtransactions 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.aiMichael Christofides, founder of pgMustardWith 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 scansVacuumUK Covid-19 dashboardpg_repackPartitioningOur episode on BRIN indexesAlways load sorted data (blog post by Haki Benita)GIN indexes: the good and the bad (blog post by Lukas Fittl)Our episode on materialised viewspg_buffercacheTowards Millions TPS (blog post by Alexander Korotkov)Postgres WASM (by Snaplet and Supabase)YugabyteAWS Aurora Continuous Archiving and Point-in-Time Recovery (docs)Our episode on checkpoint tuningOur episode on partitioningPgQNeon branchingDatabase Lab EngineCluster~~~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.aiMichael Christofides, founder of pgMustardWith 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 docspg_partmanIndex maintenance episode Timescale partitioningpg_cronXtreme 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.aiMichael Christofides, founder of pgMustardWith 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: PerconapgCloudHacker browser extension PMMPercona Distribution for PostgreSQLFerretDBPeter's Twitter profilePeter'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.aiMichael Christofides, founder of pgMustardWith 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)pgAdminPostico DBeaverDataGripPgManage (new Command Prompt fork of OmniDB) PopSQLpostgres_dbapspgMaterialized views episodepgcli------------------------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.aiMichael Christofides, founder of pgMustardWith 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_upgradeLogical replication CHECKPOINTamcheckLocale data changes (e.g. glibc upgrades)ANALYZEUpgrades are hard (summary of panel discussion by Andreas 'ads' Scherbaum)spiloRecent 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.aiMichael Christofides, founder of pgMustardWith 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 notesPostgreSQL Scalability (blog post by Alexander Korotkov)Wait event analysis in pganalyzeauto_explainDatabase Lab Enginetrack_io_timingpg_test_timingpgBadgerCorootOkmeterpgwatch2 Postgres.ai Editionpg_wait_samplingpgsentinelDatadogAWS RDS docs for PostgreSQL wait eventspgMustard newsletterPASH ViewerpgCenterIntro to query optimisation episodeMonitoring 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.aiMichael Christofides, founder of pgMustardWith 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 docsTOAST wikiHussein 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.aiMichael Christofides, founder of pgMustardWith 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: hstoreXML typeXML functionsJSON typesJSON functionsJSONB indexingNULLS episodeWhy Postgres is popular episodePostgreSQL 12 release notesWhat’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 benchmarksRUM access methodJSON in PostgreSQL: how to use it right (blog post by Laurenz Albe from Cybertec)pg_jsonschemaTOAST_TUPLE_TARGET and TOAST_TUPLE_THRESHOLD The Surprising Impact of Medium-Size Texts on PostgreSQL Performance (blog post by Haki Benita) Aggregate functionsHow 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.aiMichael Christofides, founder of pgMustardWith 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 docsHydra founders interview (on Postgres TV)Materialised views episode pg_ivmTimescale continuous aggregates docsClickhouseSnowflakeReplication episodeTimescale bottomless storage on S3 (blog post)pg_partmanQuerying 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.aiMichael Christofides, founder of pgMustardWith 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 pgbenchsysbenchImproving Postgres Connection Scalability (blog post by Andres Freund)pgreplaypgreplay-goJMeterpg_qualstatspg_queryDatabase experimenting/benchmarking (talk by Nikolay, 2018)Database testing (talk by Nikolay at PGCon, 2022)Systems Performance (Brendan Gregg’s book, chapter 12)fioNetdataSubtransactions 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.aiMichael Christofides, founder of pgMustardWith 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_buffersAndres Freund tweets about shared_buffers PGTune Leopard Cybertec Configuratorpg_stat_statementsJIT configurationpostgresqlco.nfannotated.confOtterTunework_memrandom_page_costmax_connectionsWhat to logmax_wal_sizeWAL and checkpoint tuning episode effective_cache_sizeIntro 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.aiMichael Christofides, founder of pgMustardWith 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)OVHHetznerpostgresql_clusterWhy we're leaving the cloud (blog post by DHH from Basecamp)Managed services vs. DIY episodeec2instances.infoVantagePostgres TV episode with Everett Berry from VantageMigrating to Aurora: easy except the bill (blog post by Kimberly Nicholls from Gridium)Database Lab EnginePostgres.ai consultingNetdatasysbenchfioQuery macro analysis episodeTop 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork