Postgres FM

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-COMPRESSION Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkp...

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.html pgcompacttable https://github.com/dataegret/pgcompacttable   Our episode on massi...

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/1808972166752580039 OtterTune shut down https://x.com/andy_pavlo/status/1801687420330770841 Snaplet shu...

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

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-deletion Easy alternative soft deletion (blog post by Brandur) https://brandur.org/fragments/delete...

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-FK Our episode about constraints: https...

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/pgvectorscale https://www.timescale.com/blog/pgvector-vs-pinecone https://postgres.fm/people/matvey-arye https://postgres.fm/people/john-pruitt ~~~ What did you like or not like? What sh...

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

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 to things they mentioned: What’s new with Postgres at Microsoft (blog post by Claire) https://techcommunity.microsoft.com/t5/azure-d...

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 mentioned: Full Text Search https://www.postgresql.org/docs/current/textsearch.html tsearch2 https://www.postgresql.org/docs/9.6/tsearch2.html Dictionaries https://www.postgres...

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) https://www.postgresql.org/about/news/postgresql-163-157-1412-1315-and-1219-released-2858/   PostgreSQL versioning policy h...

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 things they mentioned: PREPARE https://www.postgresql.org/docs/current/sql-prepare.html   track_activity_query_size https://www.postgresql.org/docs/curr...

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 thread by Christophe Pettus) https://twitter.com/Xof/status/1413542818673577987   LIMIT and OFFSET (docs) https://www.postgresql.org...

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 are some links to things they mentioned: BUFFERS by default (episode 4) https://postgres.fm/episodes/buffers-by-default   Lightning talk by Michael at pgDay Paris...

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 implementation (translated to English) https://habr-com.translate.goog/en/articles/523536/?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hist=tru...

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 well.   Here are some links to things they mentioned: PeerDB https://www.peerdb.io/ Our episode on logical replication https://postgres.fm...

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 (PostgreSQL wiki page) https://wiki.postgresql.org/wiki/Don't_Do_This How to get into trouble using some Postgres features (how to by Nikolay) https://gith...

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 complexity vs user experience.   Here are some links to things they mentioned: Simon Riggs https://www.linkedin.com/feed/update/urn:li:...

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 in the UK) https://en.wikipedia.org/wiki/MOT_test   Let's make PostgreSQL multi-threaded (discussion started by Heikki) https://www.postgresql.or...

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 things they mentioned: superuser (docs) https://www.postgresql.org/docs/current/role-attributes.html#id-1.6.9.6.2.1.2.1.1 Crunchy Data PostgreSQL Security Technical Implementatio...

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 to things they mentioned: transaction_timeout (devel docs) https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-TRANSACTION-TI...

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 we can do as a Postgres community to make it even better.   Here are some links to things they mentioned: Planet Argon survey https://rails-hosting.com/2022/#dat...

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 using my functional index? (Stack Exchange question from Brent Ozar) https://dba.stackexchange.c...

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.html pg_stat_kcache https://github.com/powa-team/pg_stat_kca...

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 should be on a t-shirt.   Here are some links to things they mentioned: Modern SQL (site) https://modern-sql.com Use the index, Luke! (site)...