Postgres FM

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

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 

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 

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 

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 

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 

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 

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

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:

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

With special thanks to:

• Jessie Draws for the amazing artwork 

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/episodes/logical-replication
• Exploring versions of the Postgres logical replication protocol (blog post by Sai) https://blog.peerdb.io/exploring-versions-of-the-postgres-logical-replication-protocol 
• Logical Streaming Replication Protocol https://www.postgresql.org/docs/current/protocol-logical-replication.html
• REPLICA IDENTITY FULL https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY
• On the performance impact of REPLICA IDENTITY FULL in Postgres (blog post by Xata) https://xata.io/blog/replica-identity-full-performance 
• max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE 
• Active Active in Postgres 16 (blog post by Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16 
• pgEdge https://www.pgedge.com/
• DistSQL (term used by Mark Callaghan) https://smalldatum.blogspot.com/2023/07/keeping-up-with-sql-dbms-market.html 
• Five tips on Postgres logical decoding (blog post by Sai) https://blog.peerdb.io/five-tips-on-postgres-logical-decoding
• PG Slot Notify: Monitor Postgres Slot Growth in Slack (blog post by PeerDB) https://blog.peerdb.io/pg-slot-notify-monitor-postgres-slot-growth-in-slack

~~~

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