Postgres FM

Latest Episodes:

Should we use foreign keys? Jun 21, 2024

Show notes

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://postgres.fm/episodes/constraints
GitLab migration helper add_concurrent_foreign_key https://github.com/gitlabhq/gitlabhq/blob/master/rubocop/cop/migration/add_concurrent_foreign_key.rb
Adding a foreign key without downtime (tweet by Nikolay) https://x.com/samokhvalov/status/1732056107483636188
Bloat, pg_repack, and deferred constraints (blog post by Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec
Postgres 17 draft release notes, server configuration section https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-CONFIG
Our 100th episode https://postgres.fm/episodes/to-100tb-and-beyond
Stop! Trigger Time (blog post by Michael) https://www.pgmustard.com/blog/trigger-time
Should I Create an Index on Foreign Keys? (Blog post by Percona) https://www.percona.com/blog/should-i-create-an-index-on-foreign-keys-in-postgresql
Avoid Postgres performance cliffs with MultiXact IDs and foreign keys (5 min video by Lukas Fittl) https://pganalyze.com/blog/5mins-postgres-multiXact-ids-foreign-keys-performance
Experiment to see basic overhead of foreign keys https://v2.postgres.ai/chats/01902ee6-8ed1-70ec-9345-5606305012f4
Experiment showing an extreme contention case https://v2.postgres.ai/chats/018fb28d-865f-788d-adb7-efa7ed3a48c4
Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
Notes on some PostgreSQL implementation details (blog post by Nelson Elhage that mentions “subtransactions are cursed”) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details

~~~

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 Google doc!

~~~

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the amazing artwork

pgvectorscale Jun 14, 2024

Show notes

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:

~~~

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 Google doc!

~~~

Postgres FM is produced by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the elephant artwork

To 100TB, and beyond! Jun 07, 2024

Show notes

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-ganguli
Sammy Steele from Figma https://postgres.fm/people/sammy-steele
Derk van Veen from Adyen https://postgres.fm/people/derk-van-veen
Thank you to yerrysherry on Reddit for the idea! https://www.reddit.com/r/PostgreSQL/comments/1cn8ajh/what_should_we_do_for_episode_100_of_postgres_fm
Lessons learned from sharding Postgres at Notion (October 2021) https://www.notion.so/blog/sharding-postgres-at-notion
Adding Postgres capacity (again) with zero downtime (July 2023) https://www.notion.so/blog/the-great-re-shard
How Figma’s databases team lived to tell the scale (March 2024) https://www.figma.com/blog/how-figmas-databases-team-lived-to-tell-the-scale
Updating a 50 terabyte PostgreSQL database (March 2018) https://www.adyen.com/knowledge-hub/updating-a-50-terabyte-postgresql-database
Part 1: Introduction to Table Partitioning (July 2023) https://www.adyen.com/knowledge-hub/introduction-to-table-partioning
Part 2: Partitioning at Adyen (October 2023) https://www.adyen.com/knowledge-hub/partitioning-at-adyen
Part 3: Maintenance Under Pressure (January 2024) https://www.adyen.com/knowledge-hub/maintenance-under-pressure
Fighting PostgreSQL write amplification with HOT updates (May 2022) https://www.adyen.com/knowledge-hub/postgresql-hot-updates
Tracking HOT updates and tuning FillFactor (October 2022) https://www.adyen.com/knowledge-hub/postgresql-hot-updates-part2
Partitioning your Postgres tables for 20X better performance (upcoming talk by Derk at Posette) https://www.citusdata.com/posette/speakers/derk-van-veen
Figma, including Sammy’s team, is hiring https://www.figma.com/careers
Notion’s engineering team is hiring https://www.notion.so/careers
Adyen’s engineering team is hiring https://careers.adyen.com

~~~

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 Google doc!

~~~

Postgres FM is produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Sponsoring the community May 31, 2024

Show notes

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-database-for-postgresql/what-s-new-with-postgres-at-microsoft-2024-edition/ba-p/4140085
Citus https://github.com/citusdata/citus
Fibonacci Spirals and 21 Ways to Contribute to Postgres Beyond Code (talk by Claire) https://archive.fosdem.org/2020/schedule/event/postgresql_fibonacci_spirals_and_21_ways_to_contribute_to_postgres_beyond_code/
How to contribute to PostgreSQL or, 50 Ways To Love Your Project (talk slides by Josh Berkus) https://berkus.org/pdf_presos/50_ways.pdf
pgconf dot dev https://2024.pgconf.dev/
POSETTE: An Event for Postgres https://www.citusdata.com/posette
About Talk Selection for POSETTE https://www.citusdata.com/blog/2024/04/22/about-talk-selection-for-posette-an-event-for-postgres-2024/
Claire’s video about how to say Postgres, PostgreSQL, Citus, and a few questionably named Microsoft things https://x.com/clairegiordano/status/1503784151614320640
Citus goes fully open source https://www.citusdata.com/blog/2024/04/22/about-talk-selection-for-posette-an-event-for-postgres-2024/
Contributor Profiles https://www.postgresql.org/community/contributors/
Mastodon post by Álvaro Herrera (of EDB) https://lile.cl/@alvherre/112444579030481334
Talking Postgres podcast (formerly called Path To Citus Con): https://talkingpostgres.com/

~~~

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 Google doc!

~~~

Postgres FM is produced by:

Michael Christofides, founder of pgMustard
Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

Jessie Draws for the elephant artwork

Full text search May 24, 2024

Show notes

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.postgresql.org/docs/current/textsearch-dictionaries.html
RUM index https://github.com/postgrespro/rum
Okapi BM25 https://en.wikipedia.org/wiki/Okapi_BM25
tf–idf https://en.wikipedia.org/wiki/Tf%E2%80%93idf
unaccent https://www.postgresql.org/docs/current/unaccent.html
tsvector and tsquery https://www.postgresql.org/docs/current/datatype-textsearch.html
GiST indexes https://www.postgresql.org/docs/current/gist.html
GIN indexes https://www.postgresql.org/docs/current/gin.html
Controlling Text Search (including setweight function) https://www.postgresql.org/docs/current/textsearch-controls.html
pg_trgrm https://www.postgresql.org/docs/current/pgtrgm.html
btree_gist https://www.postgresql.org/docs/current/btree-gist.html
btree_gin https://www.postgresql.org/docs/current/btree-gin.html
websearch_to_tsquery https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES
pgvector https://github.com/pgvector/pgvector
Our previous episode on search https://postgres.fm/episodes/search

~~~

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 Google doc!

~~~

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the amazing artwork

Minor releases May 17, 2024

Show notes

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 https://www.postgresql.org/support/versioning/
PostgreSQL 14.4 release notes (most recent minor release not on the usual schedule) https://www.postgresql.org/docs/release/14.4/
Minor release roadmap https://www.postgresql.org/developer/roadmap/
Our last episode on upgrades (major and minor) https://postgres.fm/episodes/upgrades
All versions of Postgres https://bucardo.org/postgres_all_versions.html
Why upgrade? (Useful tool by depesz) https://why-upgrade.depesz.com/
Stop and start Postgres faster https://postgres.fm/episodes/stop-and-start-postgres-faster
WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Postgres CVE-2024-4317 and how to fix the system views (5 mins of Postgres by Lukas Fittl) https://youtube.com/watch?v=fLwVvJ3fKdA
Our episode on NULL https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
What should we do for episode 100? https://www.reddit.com/r/PostgreSQL/comments/1cn8ajh/what_should_we_do_for_episode_100_of_postgres_fm/

~~~

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 Google doc!

~~~

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the amazing artwork

Custom vs generic plan May 10, 2024

Show notes

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/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE
plan_cache_mode https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE
EXPLAIN (GENERIC_PLAN) https://www.postgresql.org/docs/current/sql-explain.html#id-1.9.3.148.8
EXPLAIN (GENERIC_PLAN) in PostgreSQL 16 (blog post by Laurenz from Cybertec) https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/
Running EXPLAIN on any query, even with $1 parameters (blog post and video by Lukas Fittl of pganalyze) https://pganalyze.com/blog/5mins-postgres-explain-generic-plan
EXPLAIN from pg_stat_statements, how to get the generic plan (blog post by Franck Pashto of Yugabyte) https://dev.to/yugabyte/explain-from-pgstatstatements-normalized-queries-how-to-always-get-the-generic-plan-in--5cfi
Rework query relation permission checking (commit by Amit Langote) https://git.postgresql.org/gitweb/postgres.git?p=postgresql.git;a=commit;h=a61b1f74823c9c4f79c95226a461f1e7a367764b
Partition pruning, prepared statements and generic vs custom query plans (a follow up blog post and video by Lukas) https://pganalyze.com/blog/5mins-postgres-partition-pruning-prepared-statements-generic-vs-custom-query-plans
Our episode on over-indexing (inc LWLock discussion) https://postgres.fm/episodes/over-indexing
“The year of the lock manager’s revenge” (from blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/

~~~

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 Google doc!

~~~

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the amazing artwork

LIMIT vs Performance May 03, 2024

Show notes

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/docs/current/queries-limit.html
No OFFSET (by Markus Winand) https://use-the-index-luke.com/no-offset
LIMIT clause (docs) https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT

~~~

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 Google doc!

~~~

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the amazing artwork

Buffers II (the sequel) Apr 26, 2024

Show notes

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 (5 mins) https://www.youtube.com/watch?v=WfY-mSpUzaQ&t=1470s
Waiting for SERIALIZE https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/

~~~

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 Google doc!

~~~

Postgres FM is brought to you by:

Nikolay Samokhvalov, founder of Postgres.ai
Michael Christofides, founder of pgMustard

With special thanks to:

Jessie Draws for the elephant artwork

Massive DELETEs Apr 19, 2024

Show notes

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=true
Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Egor Rogov’s book on Postgres Internals (chapter 10 on WAL) https://edu.postgrespro.com/postgresql_internals-14_en.pdf
full_page_writes https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-FULL-PAGE-WRITES
TRUNCATE https://www.postgresql.org/docs/current/sql-truncate.html
Our episode on partitioning https://postgres.fm/episodes/partitioning
Our episode on bloat https://postgres.fm/episodes/bloat
Our episode on index maintenance https://postgres.fm/episodes/index-maintenance

~~~

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 Google doc!

~~~

Postgres FM is brought to you by: