Nikolay and Michael discuss append-only tables in Postgres — what they are, some unique challenges they bring, and some options for compressing / removing / offloading the data eventually. Here are some links to things they mentioned: Append-only https://en.wikipedia.org/wiki/Append-onlyOur episode on BRIN indexes https://postgres.fm/episodes/brin-indexesTips to Improve Insert Performance https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance/ Our episode on WAL and...

Nikolay and Michael discuss denormalization in Postgres — when and why to denormalize things, and a couple of specific cases Nikolay came across recently.  Here are some links to things they mentioned: Denormalization https://en.wikipedia.org/wiki/DenormalizationOur episode on materialized views https://postgres.fm/episodes/materialized-viewsOur episode on data model trade-offs https://postgres.fm/episodes/data-model-trade-offsOur episode with Markus Winand...

Nikolay and Michael discuss online Postgres communities — the ones they prefer, the types of conversations in each, and some other places to ask questions or follow news.  Here are some links to things they mentioned: https://www.postgresql.org/communityMailing lists https://www.postgresql.org/listIRC https://www.postgresql.org/community/ircSlack https://pgtreats.info/slack-inviteStack Overflow https://stackoverflow.com/questions/tagged/postgresDBA Stack Exchange...

Nikolay and Michael discuss some cool things you can do with psql, the official CLI that ships with Postgres. Here are some links to things they mentioned: psql docs https://www.postgresql.org/docs/current/app-psql.html Our episode on psql vs GUIs https://postgres.fm/episodes/psql-vs-guispostgres_dba https://github.com/NikolayS/postgres_dbaOur episode on massive deletes https://postgres.fm/episodes/massive-deletesPostgres hacking session on \watch with limited number of loops...

Nikolay and Michael discuss some Postgres Gotchas, things you might expect to work one way in fact working another way. Here are some links to things they mentioned: Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknownPostgres Gotchas (list by Ian Barwick) https://sql-info.de/postgresql/postgres-gotchas.htmlOur episode on slow count https://postgres.fm/episodes/slow-countDiscussion on X about major version upgrades and statistics...

Nikolay and Michael discuss some more advanced topics around EXPLAIN, including some tips for complex query plans, some recent improvements, and an idea or two that might make it even better. Here are some links to things they mentioned: Michael’s solo episode on EXPLAIN basics https://postgres.fm/episodes/explainOur episode on auto_explain https://postgres.fm/episodes/auto_explainWhich cloud providers support auto_explain? (Blog post by Michael)...

Michael and Nikolay are joined by Alexander Kukushkin, PostgreSQL contributor and maintainer of Patroni, to discuss all things Patroni — what it is, how it works, recent improvements, and more. Here are some links to things they mentioned: Alexander Kukushkin https://postgres.fm/people/alexander-kukushkinPatroni https://github.com/patroni/patroniSpilo https://github.com/zalando/spilo Zalando Postgres Operator https://github.com/zalando/postgres-operatorCrunchy Data Postgres Operator...

Nikolay and Michael discuss the fresh new Postgres 17 release! They cover several performance improvements, favourite new features, and some considerations for upgrading. Here are some links to things they mentioned: Postgres 17 release notes https://www.postgresql.org/docs/17/release-17.htmltransaction_timeout episode https://postgres.fm/episodes/transaction_timeoutVACUUM improvements discussed towards end of episode with Melanie Plageman...

Nikolay and Michael discuss planning time in Postgres — what it is, how to spot issues, its relationship to things like partitioning, and some tips for avoiding issues. Here are some links to things they mentioned: Query Planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.htmlAre there limits to partition counts? (Blog post by depesz) https://www.depesz.com/2021/01/17/are-there-limits-to-partition-countsNikolays recent experiment...

Nikolay and Michael discuss why counting can be slow in Postgres, and what the options are for counting things quickly at scale. Here are some links to things they mentioned: Aggregate functions (docs) https://www.postgresql.org/docs/current/functions-aggregate.htmlPostgREST https://github.com/PostgREST/postgrest Get rid of count by default in PostgREST https://github.com/PostgREST/postgrest/issues/273 Faster PostgreSQL Counting (by Joe Nelson on the Citus blog)...

Michael and Nikolay are joined by Peter Geoghegan, major contributor and committer to Postgres, to discuss adding skip scan support to PostgreSQL over versions 17 and 18. Here are some links to things they mentioned: Peter Geoghegan https://postgres.fm/people/peter-geogheganPeter’s previous (excellent) interview on Postgres TV https://www.youtube.com/watch?v=iAPawr1DxhMEfficient Search of Multidimensional B-Trees (1995 paper by Harry Leslie, Rohit Jain, Dave Birdsall, and Hedieh Yaghmai)...

Nikolay and Michael discuss PostgreSQL emergencies — both the psychological side of incident management, and some technical aspects too. Here are some links to things they mentioned: Site Reliability Engineering resources from Google https://sre.googleGitLab Handbook SRE https://handbook.gitlab.com/job-families/engineering/infrastructure/site-reliability-engineerKeeping Customers Streaming — The Centralized Site Reliability Practice at Netflix...

Michael and Nikolay are joined by Haki Benita, a technical lead and database enthusiast who writes an excellent blog and gives popular talks and training sessions too, to discuss the surprisingly complex topic of trying to implement “get or create” in PostgreSQL — handling issues around idempotency, concurrency, and bloat. Here are some links to things they mentioned: Haki Benita https://hakibenita.com How to Get or Create in PostgreSQL (blog post by Haki)...

Michael and Nikolay are joined by Melanie Plageman, database internals engineer at Microsoft and major contributor and committer to PostgreSQL, to discuss getting started with benchmarking — how it differs for users and developers of Postgres, how and when it comes up during development, some tools and lessons, as well as what she's working on at the moment. Here are some links to things they mentioned: Melanie Plageman https://postgres.fm/people/melanie-plagemanMeanie’s Introduction to...

Nikolay and Michael discuss Index-Only Scans in Postgres — what they are, how they help, some things to look out for, and some advice. Here are some links to things they mentioned: Index-Only Scans and Covering Indexes (docs) https://www.postgresql.org/docs/current/indexes-index-only-scans.htmlDiscussion on Twitter about JIT and Parallel Query defaults https://x.com/jer_s/status/1819749688184373742Postgres Wiki on Index-Only Scans https://wiki.postgresql.org/wiki/Index-only_scansHeap Fetches...

Nikolay and Michael discuss why they chose Postgres — as users, for their businesses, for their careers, as well as some doubts. Here are some links to things they mentioned: Our episode on why Postgres become popular https://postgres.fm/episodes/why-is-postgres-popularDatabase Systems: The Complete Book (by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom) http://infolab.stanford.edu/~ullman/dscb.htmlOur episode on the Postgres startup ecosystem...

Nikolay and Michael discuss compression in Postgres — what's available natively, newer algorithms in recent versions, and several extensions with compression features. Here are some links to things they mentioned: wal_compression https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-COMPRESSIONOur episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuningSynthetic wal_compression and streaming replication wal size test...

Nikolay and Michael discuss Postgres running out of disk space — including what happens, what can cause it, how to recover, and most importantly, how to prevent it from happening in the first place. Here are some links to things they mentioned: Disk Full (docs) https://www.postgresql.org/docs/current/disk-full.htmlpgcompacttable https://github.com/dataegret/pgcompacttable Our episode on massive deletes https://postgres.fm/episodes/massive-deletes Getting Rid of Data (slides from VLDB 2019...

Nikolay and Michael discuss the Postgres startup ecosystem — some recent closures, some recent fundraising announcements, and their thoughts on where things are going and what they'd like to see. Here are some links to things they mentioned: Prediction from Dax Raad https://x.com/thdxr/status/1808972166752580039OtterTune shut down https://x.com/andy_pavlo/status/1801687420330770841Snaplet shutting down https://www.snaplet.dev/post/snaplet-is-shutting-downbit.io shut down...

Nikolay talks Michael through a recent experiment to find the current maximum transactions per second single-node Postgres can achieve — why he was looking into it, what bottlenecks occurred along the way, and ideas for follow up experiments. Here are some links to things they mentioned: How many TPS can we get from a single Postgres node? (Article by Nikolay) https://www.linkedin.com/pulse/how-many-tps-can-we-get-from-single-postgres-node-nikolay-samokhvalov-yu0rcChat history with Postgres...

Nikolay and Michael discuss soft deletion in Postgres — what it means, several use cases, some implementation options, and which implementations suit which use cases. Here are some links to things they mentioned: Soft deletion probably isn't worth it (blog post by Brandur) https://brandur.org/soft-deletionEasy alternative soft deletion (blog post by Brandur) https://brandur.org/fragments/deleted-record-insertOur episode on auditing https://postgres.fm/episodes/auditingCREATE FUNCTION …...

Nikolay and Michael discuss foreign keys in Postgres — what they are, their benefits, their overhead, some edge cases to be aware of, some improvements coming, and whether or not they generally recommend using them. Here are some links to things they mentioned: Foreign keys (docs) https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FKOur episode about constraints: https://postgres.fm/episodes/constraintsGitLab migration helper add_concurrent_foreign_key...

Nikolay is joined by Mat Arye and John Pruitt, from Timescale, to discuss their new extension pgvectorscale and high-performance vector search in Postgres more generally. Main links: https://github.com/timescale/pgvectorscalehttps://www.timescale.com/blog/pgvector-vs-pineconehttps://postgres.fm/people/matvey-aryehttps://postgres.fm/people/john-pruitt ~~~ What did you like or not like? What should we discuss next time? Let us know via a YouTube comment, on social media, or by commenting on our...

Michael and Nikolay are joined by three special guests for episode 100 who have all scaled Postgres to significant scale — Arka Ganguli from Notion, Sammy Steele from Figma, and Derk van Veen from Adyen. They cover how their setup has evolved, what their plans are for the future, and get into the weeds of some fun and interesting challenges along the way! Links to some of the things discussed:  Arka Ganguli from Notion https://postgres.fm/people/arka-ganguliSammy Steele from Figma...