Postgres FM

Michael is joined by Jonathan Katz, PostgreSQL Core Team member, Principle Product Manager at AWS, and also pgvector contributor, to discuss pgvector. They cover what it is, why it's so popular all of a sudden, some tuning and tradeoff guidance, and a look to the future.
 
Here are some links to things they mentioned:

• pgvector https://github.com/pgvector/pgvector
• Our episode on companion databases https://postgres.fm/episodes/companion-databases
• Vectors are the new JSON (talk by Jonathan) https://www.youtube.com/watch?v=D_1zunKblAU
• pgvector 0.5.0 feature highlights and howtos (blog post by Jonathan) https://jkatz05.com/post/postgres/pgvector-overview-0.5.0
• HNSW indexing and searching (blog post by Jonathan) https://aws.amazon.com/blogs/database/accelerate-hnsw-indexing-and-searching-with-pgvector-on-amazon-aurora-postgresql-compatible-edition-and-amazon-rds-for-postgresql
• Our episode on TOAST https://postgres.fm/episodes/toast
• pgvector changelog https://github.com/pgvector/pgvector/blob/master/CHANGELOG.md
• HQANN paper https://arxiv.org/abs/2207.07940
• HNSW fast build branch https://github.com/pgvector/pgvector/tree/hnsw-fast-build 
• pgconf.dev https://2024.pgconf.dev 

~~~

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

Michael is joined by Alicja Kucharczyk, Program Manager for Azure Database for PostgreSQL at Microsoft and organiser of the Warsaw PostgreSQL User Group, to discuss Alicja's favorite tool: pgBadger. They cover what it is, how and when it's useful, and some guidance for getting the most out of it (without causing an outage).
 
Here are some links to things they mentioned:

• pgBadger website https://pgbadger.darold.net 
• pgBadger repo https://github.com/darold/pgbadger
• An example pgBadger report https://pgbadger.darold.net/examples/sample.html 
• pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
• Azure Guide https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/how-to-generate-pgbadger-report-from-azure-database-for/ba-p/3756328
• Google Cloud SQL guide https://cloud.google.com/blog/products/databases/guide-to-the-database-observability-with-cloud-sql
• RDS guide https://aws.amazon.com/blogs/database/optimizing-and-tuning-queries-in-amazon-rds-postgresql-based-on-native-and-external-tools/
• Community Insights on pgBadger (PGSQL Phriday Recap) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/community-insights-on-pgbadger-a-pgsql-phriday-010-recap/ba-p/3880911 
• PGSQL Phriday #010: Log analysis (blog post by Lætitia Avrot) https://mydbanotebook.org/post/log-analysis/ 
• Nothing Compares To VACUUM/The Ballad of Bloat https://www.youtube.com/watch?v=2NxIngqq1y0 
• Explain Analyze (Feliz Navidad cover) https://www.youtube.com/watch?v=qznnzYZPdkM 

~~~

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

With Nikolay taking a well-earned break, Michael covers the basics of EXPLAIN — what it is, the parameters available, and a process he recommends for using it.
 
Here are some links to the things mentioned:

• EXPLAIN (docs) https://www.postgresql.org/docs/current/sql-explain.html
• Using EXPLAIN (docs) https://www.postgresql.org/docs/current/using-explain.html
• EXPLAIN Explained (talk by Josh Berkus https://www.youtube.com/watch?app=desktop&v=mCwwFAl1pBU
• Beyond joins and indexes (slides from talk by Bruce Momjian) https://momjian.us/main/writings/pgsql/beyond.pdf
• Depesz https://explain.depesz.com
• Dalibo https://explain.dalibo.com
• pgMustard https://www.pgmustard.com/
• A beginners guide to explain (talk by Michael) https://www.youtube.com/watch?v=31EmOKBP1PY&t=24s
• Explain beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4
• auto_explain episode https://postgres.fm/episodes/auto_explain
• Row estimates episode https://postgres.fm/episodes/row-estimates
• Slow queries and slow transactions episode https://postgres.fm/episodes/slow-queries-and-slow-transactions
• Benchmarking episode https://postgres.fm/episodes/benchmarking
• EXPLAIN glossary by Michael (forgot to mention, oops!) https://www.pgmustard.com/docs/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 amazing artwork 

Happy holidays! Today's topic is work_mem — how it's used and how to tune it.
 
Related post: https://x.com/samokhvalov/status/1740813478150189172?s=46&t=HSxrW86NZqzPQP9vXVJEWg.

Nikolay and Michael discuss partitioning by ULID — revisiting some of the old UUID vs bigint key debate in light of some new UUID specs, and how these can be used for partitioning (by time). 
 
Here are some links to things they mentioned:

• Nikolay’s episode on UUID (for primary keys) https://postgres.fm/episodes/uuid 
• Postgres TV hacking session with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8
• UUIDv7 patch https://commitfest.postgresql.org/43/4388/ 
• Use ULID Idempotency Keys (tip 6 in this blog post from Shopify) https://shopify.engineering/building-resilient-payment-systems 
• Nikolay’s howto post on UUID v7 and partitioning with TimescaleDB https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.md 
• Hussein Nasser’s YouTube video about the above https://www.youtube.com/watch?v=f53-Iw_5ucA 
• UUID proposal that includes UUIDv7 and UUIDv8 (work in progress): https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/history/  
• James Blackwood-Sewell advice on more elegant solution using TimescaleDB https://twitter.com/jamessewell/status/1730125437903450129 
• ULIDs and Stripe IDs (section of Identity Crisis blog post by Brandur) https://brandur.org/nanoglyphs/026-ids#ulids 
• Crunchy Bridge changed their default random_page_cost to 1.1 🙌 https://twitter.com/brandur/status/1720477470116422028 
• UUIDs vs serials for keys (newer post from Christophe Pettus) https://thebuild.com/blog/2023/02/16/uuids-vs-serials-for-keys/ 

~~~

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 hash indexes in Postgres — what they are, some brief history, their pros and cons vs btrees, and whether or when they recommend using them.

Update: the idea Nikolay mentioned at the end of this episode turns out to be a little fraught (and as such, inadvisable). 
 
Here are some links to things they mentioned:

• Index types (docs) https://www.postgresql.org/docs/current/indexes-types.html  
• Re-introducing hash indexes in PostgreSQL (blog post by Haki Benita and Michael) https://hakibenita.com/postgresql-hash-index 
• Hash indexes intro (docs) https://www.postgresql.org/docs/current/hash-intro.html 
• Hash indexes implementation (docs) https://www.postgresql.org/docs/current/hash-implementation.html

~~~

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 PostgreSQL constraints — the six different types we have, how to add and modify them, including on large tables with high TPS. 
 
Here are some links to things they mentioned:

• Constraints (docs) https://www.postgresql.org/docs/current/ddl-constraints.html
• Unique index vs unique constraint issue (2017) https://www.postgresql.org/message-id/flat/CANNMO%2B%2B7%2BuVMj%2BZPGKB_zuRpKE0sNthy2Z-_oKSS%3DoE3_rN7QA%40mail.gmail.com 
• Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
• Advanced Int to Bigint Conversions (talk by Robert Treat) https://www.youtube.com/watch?v=_IieyXzdmcM 
• Use BIGINT in Postgres (blog post by Ryan Lambert) https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default 
• How to add a foreign key (Tweet by Nikolay) https://twitter.com/samokhvalov/status/1732056107483636188 
• Bloat, pg_repack, and deferred constraints (blog post by Nikolai Averin from Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec 

~~~

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 PostgreSQL events — whether in-person or online, large conferences or small meet-ups, as well as some strong opinions based on their experiences attending, speaking, and organising them. 
 
Here are some links to things they mentioned:

• PGSQL Phriday #014: PostgreSQL Events https://www.cybertec-postgresql.com/en/pgsql-phriday-014-postgresql-events/ 
• PGCon https://www.pgcon.org/ 
• Highload https://highload.rs/ 
• The San Francisco Bay Area PostgreSQL Meetup Group https://www.meetup.com/postgresql-1/ 
• Our episode on “Why is Postgres popular?” https://postgres.fm/episodes/why-is-postgres-popular 
• PGConf EU https://pgconf.eu/ 
• Open talks series on Postgres TV https://www.youtube.com/playlist?list=PLH8y1BNPAKjJCuZiDRl0qUEDaKLBpFvZ9 
• Rails World (including videos!) https://rubyonrails.org/world 
• Upcoming events https://www.postgresql.org/about/events/ 

~~~

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 PostgreSQL subtransactions — what they are, and four issues they can cause at scale. 
 
Here are some links to things they mentioned:

• Subtransactions (docs) https://www.postgresql.org/docs/current/subxacts.html  
• SAVEPOINT (docs) https://www.postgresql.org/docs/current/sql-savepoint.html  
• PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful  
• Subtransactions and performance in PostgreSQL (blog post by Laurenz at Cybertec) https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/  
• Notes on some PostgreSQL implementation details (blog post by Nelson Elhage) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/ 
• Why we spent the last month eliminating PostgreSQL subtransactions (GitLab blog post) https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions/  

~~~

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 companion databases — when and why you might want to add another database management system to your stack (or not), and some specifics for analytics, timeseries, search, and vectors. 
 
Here are some links to things they mentioned:

• Heap were using Postgres + Citus for analytics as of 2022 https://www.heap.io/blog/juggling-state-machines-incident-response-and-data-soup-a-glimpse-into-heaps-engineering-culture 
• Heap recently moved their core analytics to SingleStore (we only spotted this after recording 🤦‍♂️) https://www.heap.io/blog/heaps-next-generation-data-platform 
• Posthog moved their analytics from Postgres to Clickhouse https://posthog.com/blog/clickhouse-vs-postgres 
• Timescale https://www.timescale.com/ 
• Citus https://www.citusdata.com/ 
• Hydra https://www.hydra.so/ 
• Our episode on real-time analytics https://postgres.fm/episodes/real-time-analytics 
• Our episode on queues https://postgres.fm/episodes/queues-in-postgres 
• Our episode on logical replication https://postgres.fm/episodes/logical-replication 
• Full Text Search in PostgreSQL (docs) https://www.postgresql.org/docs/current/textsearch.html 
• ZomboDB https://www.zombodb.com/ 
• ParadeDB https://www.paradedb.com/ 
• PeerDB https://www.peerdb.io/ 
• RUM indexes https://github.com/postgrespro/rum 
• Debezium https://debezium.io/ 
• pgmq https://github.com/tembo-io/pgmq 
• Tembo https://tembo.io/ 
• pgvector vs Pinecone (Supabase blog post) https://supabase.com/blog/pgvector-vs-pinecone 
• Jepsen analyses https://jepsen.io/analyses 

~~~

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