Postgres FM

Nikolay and Michael celebrate the podcast's 1 year anniversary by going through several questions and suggestions received over the year. Here are the questions and some links to things we mentioned: Question 1: Effect of wal_log_hints=on after bulk Deletes: Why next select runs slow and generated tons of WAL? https://twitter.com/dmx551/status/1598253188926570496 wal_log_hints https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LOG-HINTS Exploring how SELECT Queries can produce disk writes https://blog.okmeter.io/postgresql-exploring-how-select-queries-can-produce-disk-writes-f36c8bee6b6fQuestion 2: How to get started reading PostgreSQL source code. Maybe a PostgreSQL style C reference guide to consult with for non C programmers https://twitter.com/andatki/status/1578088843940593678 So, you want to be a developer? https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3FGitHub search https://github.com/search?q=repo%3Apostgres%2Fpostgres+wal_log_hints&type=code The Internals of PostgreSQL (by Hironobu SUZUKI) https://www.interdb.jp/pg/ PostgreSQL 14 Internals (by Egor Rogov) https://postgrespro.com/community/books/internals Question 3: Isolation Levels Primer/Strategies — their uses in different scenarios, battle tested strategies and insights, performance tradeoffs, edge cases to consider at scale (with replication and sharding, etc.) I remember reading some interesting stuff on the jepsen analysis https://jepsen.io/analyses/postgresql-12.3 about Postgres's Serializable Isolation Level behaving more like Snapshot Isolation. Has this type of behavior or another one similar to this affected you or your clients in any significant way?Transaction Isolation https://www.postgresql.org/docs/current/transaction-iso.htmlWhat developers find surprising about Postgres transactions https://blog.lawrencejones.dev/isolation-levels/  Question 4: Data encryption in PostgresCybertec Transparent Data Encryption https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/ EDB Transparent Data Encryption https://www.enterprisedb.com/docs/tde/latest/Question 5: Migration from other DBMSsPostgreSQL transition/migration guide https://github.com/postgresql-transition-guide/guide  Question 6: Latest failover best practicesPatroni https://github.com/zalando/patroni~~~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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

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=storyUnexpected 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=36429986Additional math by me (converting to bytes): https://twitter.com/samokhvalov/status/1671962111092850689Updated RFC4122 (proposal): https://github.com/ietf-wg-uuidrev/rfc4122bisStatus 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.aiMichael Christofides, founder of pgMustardWith 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.htmlAndres 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-usageTuning 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.htmlPostgreSQL 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.aiMichael Christofides, founder of pgMustardWith 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/plrustZomboDB https://github.com/zombodb/zombodb Why is Postgres popular episode https://postgres.fm/episodes/why-is-postgres-popular Citus https://github.com/citusdata/citusTimescaleDB https://github.com/timescale/timescaledb OrioleDB https://github.com/orioledb/orioledbPostGIS 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/stackgrespg_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.aiMichael Christofides, founder of pgMustardWith 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_timeoutFast 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 guidedblab ~~~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.aiMichael Christofides, founder of pgMustardWith 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 maintenanceOur 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.aiMichael Christofides, founder of pgMustardWith 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 memeData 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)Checksumspg_checksumsOriginal pg_checksums (by Credativ)amcheckOur episode on index maintenance14.4 release notes about create index / reindex concurrently issue and fixamcheck 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 talkChristophe Pettus slidespg_hexeditpageinspectpg_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.aiMichael Christofides, founder of pgMustardWith 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: ChatGPTNikolay’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 amplificationDon’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.aiMichael Christofides, founder of pgMustardWith 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 PhridayObserver 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_monitorPGConOur episode on query analysisMarginalia ~~~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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork