Postgres FM

Nikolay and Michael discuss a couple of surprising ways read queries (selects) can cause writes (shared buffers dirtied) in Postgres.   Here are some links to things they mentioned: Reads causing writes in Postgres (post by Alex Jesipow) https://jesipow.com/blog/postgres-reads-cause-writes/ Exploring how SELECT queries can produce disk writes (post by Nikolay Sivko) https://blog.okmeter.io/postgre...

Nikolay and Michael discuss adding constraints in an online fashion, using NOT VALID and then VALIDATE CONSTRAINT.   Here are some links to things they mentioned: ADD table_constraint NOT VALID  https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT Our episode on zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations VALIDATE C...

Michael and Nikolay are joined by Antonín Houska to discuss pg_squeeze — what it is, how it started, some of its features, and hopes of getting the functionality into core.   Here are some links to things they mentioned: Antonín Houska https://postgres.fm/people/antonin-houska pg_squeeze https://github.com/cybertec-postgresql/pg_squeeze Cybertec https://www.cybertec-postgresql.com pg_repack https:...

Michael and Nikolay are joined by Joe Sciarrino and Jelte Fennema-Nio to discuss pg_duckdb — what it is, how it started, what early users are using it for, and what they're working on next.   Here are some links to things they mentioned: Joe Sciarrino https://postgres.fm/people/joe-sciarrino Jelte Fennema-Nio https://postgres.fm/people/jelte-fennema-nio pg_duckdb https://github.com/duckdb/pg_duckd...

Nikolay and Michael discuss Row Level Security in Postgres, focussing on the performance side effects and some tips to avoid (or minimize) them.   Here are some links to things they mentioned: Row Security Policies (docs) https://www.postgresql.org/docs/current/ddl-rowsecurity.html 7+ million Postgres tables (recent talk by Kailash Nadh) https://www.youtube.com/watch?v=xhi5Q_wL9i0 Row Level Securi...

Michael and Nikolay are joined by Lukas Eder, the creator of jOOQ, to discuss what it is, some nice developer experience features it has, and some fun things he's come across from a Postgres perspective.   Here are some links to things they mentioned: Lukas Eder https://postgres.fm/people/lukas-eder jOOQ https://www.jooq.org/   DSL https://en.wikipedia.org/wiki/Domain-specific_language   SQL Diale...

Nikolay and Michael discuss "Column Tetris" — what it is, why it matters, how to order columns for new tables, and how to re-organise existing ones.   Here are some links to things they mentioned: “Column Tetris” by Erwin Brandstetter on Stack Overflow  https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468 Data Types https://www.postgresql.org/docs/...

Nikolay and Michael discuss the track_planning parameter of pg_stat_statements — what it is, how it affects performance, and when or whether you should switch it on.   Here are some links to things they mentioned: pg_stat_statements.track_planning https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.40.9.2.4.1.3 Our episode about pg_stat_statements https://postgres.fm/episodes/p...

Michael and Nikolay are joined by Gülçin Yıldırım Jelínek and Robert Haas to discuss both the technical question of whether or not pg_dump is a backup tool, as well as the tone and intent behind the statement "pg_dump is not a backup tool".   Here are some links to things they mentioned: Gülçin Yıldırım Jelínek https://postgres.fm/people/gulcin-yildirim-jelinek Robert Haas https://postgres.fm/peop...

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-only Our episode on BRIN indexes https://postgres.fm/episodes/brin-indexes Tips to Improve Insert Performance http...

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/Denormalization Our episode on materialized views https://postgres.fm/episodes/materialized-views Our episode on data model trade-offs https://postgre...

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/community Mailing lists https://www.postgresql.org/list IRC https://www.postgresql.org/community/irc Slack https://pgtreats.info/slack-invite Stack Overf...

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-guis postgres_dba https://github.com/NikolayS/postgres_dba Our episode on massive deletes https://postgres.fm/e...

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-unknown Postgres Gotchas (list by Ian Barwick) https://sql-info.de/postgresql/postgres-gotchas.html Our episode on slow count https://postgres...

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/explain Our episode on auto_explain https://postgres.fm/episodes/auto_explain Which cloud...

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-kukushkin Patroni https://github.com/patroni/patroni Spilo https://github.com/zalando/spilo   Zalando P...

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.html transaction_timeout episode https://postgres.fm/episodes/transaction_timeout VACUUM improvements discuss...

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.html Are there limits to partition counts? (Blog post by depesz) https://www.depesz.com/2021/01/17/a...

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.html PostgREST https://github.com/PostgREST/postgrest   Get rid of count by default in PostgREST https://github.com/PostgREST/postgrest/...

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-geoghegan Peter’s previous (excellent) interview on Postgres TV https://www.youtube.com/watch?v=iAPawr1DxhM Efficient Search of Mult...

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.google GitLab Handbook SRE https://handbook.gitlab.com/job-families/engineering/infrastructure/site-reliability-engineer Keeping Customers Streaming — T...

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

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

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.html Discussion on Twitter about JIT and Parallel Query defaults https://x.com/jer_s/status/181974968818437...

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-popular Database Systems: The Complete Book (by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom) http://infolab.stanford.edu/~ull...