Postgres FM

Lonely Nikolay discusses the performance aspects of using UUID for primary keys. 

Here are links to a few things I mentioned: 

• "postgresql" posts on HN, most popular last week (Algolia search): https://hn.algolia.com/?dateRange=pastWeek&page=0&prefix=true&query=postgresql&sort=byPopularity&type=story
• Unexpected downsides of UUID keys in PostgreSQL (a post by Ants Aasma, Cybertec): https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-keys-in-postgresql/
• HN discussion of that post: https://news.ycombinator.com/item?id=36429986
• Additional math by me (converting to bytes): https://twitter.com/samokhvalov/status/1671962111092850689
• Updated RFC4122 (proposal): https://github.com/ietf-wg-uuidrev/rfc4122bis
• Status of that proposal: https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/history/
• Patch UUID v7 (commitfest record): https://commitfest.postgresql.org/43/4388/
• Postgres hacking with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8 (where that patch was developed)

~~~

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!

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 memory in PostgreSQL — how it works, important settings, and how to go about tuning them.
 
Here are links to a few things we mentioned: 

• Resource Consumption (PostgreSQL docs) https://www.postgresql.org/docs/current/runtime-config-resource.html
• Andres Freud tweet about shared_buffers https://twitter.com/AndresFreundTec/status/1438912583554113537 
• Henrietta (Hettie) Dombrovskaya https://hdombrovskaya.wordpress.com/about-the-author/
• annotated.conf (by ash Berkus) https://github.com/jberkus/annotated.conf 
• Our episode about checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning 
• Our episode about BUFFERS https://postgres.fm/episodes/buffers-by-default 
• Analyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266#memory-usage
• Tuning memory parameters for Aurora PostgreSQL https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.BestPractices.Tuning-memory-parameters.html 
• RDS for PostgreSQL memory https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.concepts.html#PostgreSQL.Tuning.concepts.memory 
• EDB guide by Via Fearing https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization#resourceusage 
• pg_stat_kcache https://github.com/powa-team/pg_stat_kcache 
• pg_buffercache https://www.postgresql.org/docs/current/pgbuffercache.html 
• Process and Memory Architecture chapter (from Hironobu SUZUKI) https://www.interdb.jp/pg/pgsql02.html
• PostgreSQL 14 internals PDF book from Egor Rogov (pages 37, 184)  https://edu.postgrespro.com/postgresql_internals-14_en.pdf 
• src/backend/storage/buffer/README https://github.com/postgres/postgres/blob/master/src/backend/storage/buffer/README 
• pg_backend_memory_contexts (PostgreSQL 14+) https://www.postgresql.org/docs/current/view-pg-backend-memory-contexts.html 
• pg_stat_io (coming in PostgreSQL 16) https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW 
• pg_prewarm https://www.postgresql.org/docs/current/pgprewarm.html 
• Configuring work_mem blog post https://www.pgmustard.com/blog/work-mem

~~~

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!

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 extensions — what they are, how they affect your decisions around Postgres, and some things to keep in mind when using them.
 
Here are links to a few things we mentioned: 

• Extensions (docs) https://www.postgresql.org/docs/current/external-extensions.html 
• Extension (pgPedia) https://pgpedia.info/e/extension.html 
• pgvector https://github.com/pgvector/pgvector 
• PL/Rust https://github.com/tcdi/plrust
• ZomboDB https://github.com/zombodb/zombodb 
• Why is Postgres popular episode https://postgres.fm/episodes/why-is-postgres-popular 
• Citus https://github.com/citusdata/citus
• TimescaleDB https://github.com/timescale/timescaledb 
• OrioleDB https://github.com/orioledb/orioledb
• PostGIS https://trac.osgeo.org/postgis/ 
• “There’s an extension for that” (tweet from Robert Treat) https://twitter.com/robtreat2/status/1665735485883314178  
• RDS supported extensions https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html 
• RUM https://github.com/postgrespro/rum 
• pg_repack https://github.com/reorg/pg_repack 
• PGXN https://pgxn.org/ 
• pgTrunk by CoreDB https://pgtrunk.io/ 
• Dbdev by Supabase https://supabase.com/blog/dbdev 
• StackGres https://github.com/ongres/stackgres
• pg_tle by AWS https://github.com/aws/pg_tle 
• Modern Postgres monitoring (slides from Nikolay’s tutorial) https://twitter.com/samokhvalov/status/1664686535562625034 
• Awesome Postgres https://github.com/dhamaniasad/awesome-postgres 

~~~

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!

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 zero-downtime schema migrations — why they're a challenge, a variety of different cases, and some things you can do to achieve them.
 
Here are links to a few things we mentioned: 

• Comparison of JOINS: MongoDB vs. PostgreSQL (blog post by Michael Stonebraker and Álvaro Hernández)
• Common DB schema change mistakes (blog post by Nikolay)
• lock_timeout and retries (blog post by Nikolay)
• lock_timeout
• Fast Column Creation with Defaults (blog post by Brandur)
• Database Schema Changes Without Downtime (new version of blog post by Braintree)
• Zero-downtime Postgres migrations - the hard parts (blog post from GoCardless)
• GitLab migration_helpers.rb 
• GitLab migration style guide
• dblab 

~~~

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!

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 parallelism — both parallel query execution and other parallel operations.
 
Here are links to a few things we mentioned: 

• Parallel query (docs)
• Parallelism in PostgreSQL 11 (talk by Thomas Munro)
• Parallelism in PostgreSQL 15 (talk by Thomas Munro)
• Towards Millions TPS (blog post by Alexander Korotkov)
• Memory resource consumption (docs)
• Our episode about index maintenance
• Our episode about partitioning 
• Patch to make postgres_fdw parallel-safe (by Swarm64) 
• PostgreSQL Parallelism Do’s and Don’ts (talk by Sebastian Dressler)
• Increasing max_parallel_workers_per_gather (blog post by Michael)

~~~

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!

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 database corruption — various types, how they can come about, and what to do (and not do) if you come across it.
  
Here are links to a few things we mentioned: 

• The dangers of streaming across versions of glibc (TripAdvisor mailing list thread)
• The floor is Java meme
• Data Corruption talk by Sebastian Webber (on Postgres TV) 
• Data corruption monitoring & troubleshooting talk by Andrey Borodin (on Postgres TV)
• Postgres data corruption (document from Nikolay) 
• Data Corruption and Bugs Runbook (document from Nikolay)
• Corruption (Postgres wiki)
• Checksums
• pg_checksums
• Original pg_checksums (by Credativ)
• amcheck
• Our episode on index maintenance
• 14.4 release notes about create index / reindex concurrently issue and fix
• amcheck to check unique constraints in btree indexes (Commitfest entry)
• amcheck verification of GiST and GIN (Commitfest entry)
•  How to corrupt your Postgres database (blog post from Cybertec)
• Christophe Pettus talk
• Christophe Pettus slides
• pg_hexedit
• pageinspect
• pg_catcheck

~~~

What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 using ChatGPT for Postgres tasks — should you, if so what for, and some things to be mindful of!
 
Here are links to a few things we mentioned: 

• ChatGPT
• Nikolay’s polls on Twitter and on LinkedIn 
• The Art of PostgreSQL (book by Dimitri Fontaine)
• SQL Performance Explained (book by Markus Winand)
• Nikolay’s YouTube correction about deletes and index amplification
• Don’t use ChatGPT to solve problems (blog post by Christophe Pettus)
• Query optimization session with ChatGPT, Michael, and Nikolay (on YouTube)
• DBeaver SmartAssistance feature  
• Depesz anonymization feature

~~~

What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 pg_stat_statements — why everyone should use it, but also some downsides!
 
Here are links to a few things we mentioned: 

• pg_stat_statements (docs)
• pg_stat_statements (PgPedia)
• PGSQL Phriday
• Observer effect in pg_stat_statements and pg_stat_kcache (Postgres Hacking session on Postgres TV) 
• track_io_timing (docs)
• Overhead comment (by Tom Kate, via Jeremy Schneider) 
• pg_stat_monitor
• PGCon
• Our episode on query analysis
• Marginalia 

~~~

What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 auto_explain — what it is, how it can help, and how to check it's overhead.
 
Here are links to a few things we mentioned: 

• auto_explain (docs)
• ScaleGrid guide to auto_explain 
• Can auto_explain, with timing, have low overhead? (Blog post by Michael)
• pgBadger 
• pg_stat_monitor
• EXPLAIN ANALYZE may be lying to you (blog post by Álvaro from Ongres)
• pg_test_timing
• Our episode on benchmarking
• Database Lab Engine

~~~

What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

~~~

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 queues in Postgres — the pros and cons vs dedicated queuing tools, and some tips for scaling.
 
A couple of apologies-in-advance: 

1. Near the end, we incorrectly say "idempotent" when we meant "stateless", and also 50 TPS instead of 500 TPS
2. We also had a couple of audio issues, sorry!

Here are links to a few things we mentioned: 

• Recent discussion on Hacker News
• PgQ
• What is SKIP LOCKED (blog post by Craig Ringer) 
• autovacuum
• Postgres queues (blog post by Brandur)
• pg_repack
• Our episode on partitioning
• Nikolay’s Twitter poll
• Subtransactions Considered Harmful (blog post by Nikolay)

~~~

What did you like or not like? What should we discuss next time? Let us know on social media, or by commenting on our Google doc.

If you would like to share this episode, here's a good link (and thank you!)

~~~

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