Postgres FM

Latest Episodes:

pg_upgrade: the tricky and dangerous parts Jun 30, 2023

Show notes

Nikolay (alone, again) reveals some issues that might hit those who perform major PostgreSQL upgrades with minimal downtime.

Links:
- "Upgrades" – PostgresFM episode 037: https://postgres.fm/episodes/upgrades
- recovery_target_lsn: https://postgresqlco.nf/doc/en/param/recovery_target_lsn/
- recovery_target_action: https://postgresqlco.nf/doc/en/param/recovery_target_action/
- pg_easy_replicate https://github.com/shayonj/pg_easy_replicate/
- HN discussion of this tool: https://news.ycombinator.com/item?id=36405761
- Waiting for Postgres 16: Logical decoding on standbys: https://pganalyze.com/blog/5mins-postgres-16-logical-decoding
- pg_upgrade and logical replication (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud
- allow upgrading publisher node (proposal to natively support running pg_upgrade on publisher, WIP): https://commitfest.postgresql.org/43/4273/
- ❓🤔 pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption? (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/CAM527d8heqkjG5VrvjU3Xjsqxg41ufUyabD9QZccdAxnpbRH-Q%40mail.gmail.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!

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

UUID Jun 23, 2023

Show notes

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

Memory Jun 16, 2023

Show notes

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

Extensions Jun 09, 2023

Show notes

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

Zero-downtime migrations Jun 02, 2023

Show notes

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

Parallelism May 26, 2023

Show notes

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

Corruption May 19, 2023

Show notes

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

ChatGPT x  PostgreSQL May 12, 2023

Show notes

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: