Postgres FM

Postgres FM episodes:

• 06.28.2024

  Soft delete

  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...

  
• 06.21.2024

  Should we use foreign keys?

  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...

  
• 06.14.2024

  pgvectorscale

  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?...

  
• 06.07.2024

  To 100TB, and beyond!

  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...

  
• 05.31.2024

  Sponsoring the community

  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...

  
• 05.24.2024

  Full text search

  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...

  
• 05.17.2024

  Minor releases

  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)...

  
• 05.10.2024

  Custom vs generic plan

  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...

  
• 05.03.2024

  LIMIT vs Performance

  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...

  
• 04.26.2024

  Buffers II (the sequel)

  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...

  
• 04.19.2024

  Massive DELETEs

  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...

  
• 04.12.2024

  Logical replication common issues

  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...

  
• 04.05.2024

  Don't do this

  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...

  
• 03.29.2024

  Search

  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...

  
• 03.22.2024

  Health check

  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...

  
• 03.15.2024

  superuser

  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...

  
• 03.08.2024

  transaction_timeout

  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...

  
• 03.01.2024

  Rails + Postgres

  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...

  
• 02.23.2024

  Why isn't Postgres using my index?

  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...

  
• 02.16.2024

  Overhead of pg_stat_statements and pg_stat_kcache

  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...

  
• 02.02.2024

  Modern SQL

  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...

  
• 01.26.2024

  Bloat

  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...

  
• 01.19.2024

  pgvector

  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,...

  
• 01.12.2024

  pgBadger

  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...

  
• 01.05.2024

  EXPLAIN

  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...

  
• 12.31.2023

  work_mem

  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.

  
• 12.22.2023

  Partitioning by ULID

  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...

  
• 12.15.2023

  Hash indexes

  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...

  
• 12.08.2023

  Constraints

  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...

  
• 12.01.2023

  Events

  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:...

  
• 11.24.2023

  Subtransactions

  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...

  
• 11.17.2023

  Companion databases

  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...

  
• 11.10.2023

  Blue-green deployments

  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...

  
• 11.03.2023

  Data model trade-offs

  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...

  
• 10.27.2023

  Under-indexing

  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...

  
• 10.20.2023

  Over-indexing

  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...

  
• 10.13.2023

  Query hints

  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...

  
• 10.07.2023

  Stop and start Postgres faster

  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...

  
• 09.29.2023

  Backups

  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...

  
• 09.22.2023

  Postgres 16

  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...

  
• 09.15.2023

  Logical replication

  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...

  
• 09.08.2023

  Our favourite v16 feature

  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...

  
• 09.01.2023

  Connections

  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)...

  
• 08.25.2023

  Decoupled storage and compute

  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...

  
• 08.18.2023

  Self-managing

  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...

  
• 08.11.2023

  Sharding

  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...

  
• 08.04.2023

  Data types

  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...

  
• 07.28.2023

  High availability

  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...

  
• 07.21.2023

  Beginner tips

  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...

  
• 07.14.2023

  Connection poolers

  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...