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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 and https://postgres.fm/episodes/hot-updates  Tip 2: always use EXPLAIN (ANALYZE,...

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

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