Postgres FM

Latest Episodes:

Bloat Jan 26, 2024

Show notes

Michael is joined by Chelsea Dole, Staff Software Engineer and Tech Lead of the Data Storage Team at Brex, to discuss bloat. They cover what it is, why it's a problem, strategies for minimising it, and the options when it's really bad.
Here are some links to things they mentioned:

Managing your tuple graveyard (talk by Chelsea) https://www.youtube.com/watch?v=gAgbzvGT6ck
pgstattuple https://www.postgresql.org/docs/current/pgstattuple.html
pg_class https://www.postgresql.org/docs/current/catalog-pg-class.html
Bloat in PostgreSQL: a taxonomy (talk by Peter Geoghegan) https://youtube.com/watch?v=JDG4bMHxCH8
It's Not You, It's Me: Breaking Up with Massive Tables via Partitioning (talk by Chelsea) https://youtube.com/watch?v=TafwSuLNxe8
pg_repack https://github.com/reorg/pg_repack
pg_squeeze https://github.com/cybertec-postgresql/pg_squeeze
VACUUM https://www.postgresql.org/docs/current/sql-vacuum.html
autovacuum https://www.postgresql.org/docs/current/runtime-config-autovacuum.html
CLUSTER https://www.postgresql.org/docs/current/sql-cluster.html
HOT updates https://www.postgresql.org/docs/current/storage-hot.html
Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default
Our episode on TOAST https://postgres.fm/episodes/toast
Our episode on index maintenance https://postgres.fm/episodes/index-maintenance
Chelsea's website: https://chelseadole.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 amazing artwork

pgvector Jan 19, 2024

Show notes

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

pgBadger Jan 12, 2024

Show notes

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

EXPLAIN Jan 05, 2024

Show notes

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

work_mem Dec 31, 2023

Show notes

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.

Partitioning by ULID Dec 22, 2023

Show notes

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

Hash indexes Dec 15, 2023

Show notes

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

Constraints Dec 08, 2023

Show notes

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

Events Dec 01, 2023

Show notes

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

Subtransactions Nov 24, 2023

Show notes

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: