Postgres FM

Nikolay and Michael discuss blue-green deployments — specifically an RDS blog post, how similar this is (or not) to what they understand to be blue-green deployments, and how applicable the methodology might be in the database world more generally. 
 
Here are some links to things they mentioned:

• Fully managed Blue/Green Deployment in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL https://aws.amazon.com/blogs/database/new-fully-managed-blue-green-deployment-in-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/  
• Blue-green deployment (blog post by Martin Fowler) https://martinfowler.com/bliki/BlueGreenDeployment.html  
• Our episode on logical replication https://postgres.fm/episodes/logical-replication 
• pgroll https://github.com/xataio/pgroll 

~~~

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 data model trade-offs — some cases where you might want to (gasp) de-normalize, and which PostgreSQL internals are at play. 
 
Here are some links to things they mentioned:

• Our episode on JSON https://postgres.fm/episodes/json 
• PostgreSQL limits https://www.postgresql.org/docs/current/limits.html
• Boyce–Codd normal form https://en.wikipedia.org/wiki/Boyce–Codd_normal_form 
• Our episode on over-indexing https://postgres.fm/episodes/over-indexing 
• Heap-Only Tuples (HOT) optimisation https://www.postgresql.org/docs/current/storage-hot.html 
• The Surprising Impact of Medium-Size Texts on PostgreSQL Performance https://hakibenita.com/sql-medium-text-performance 
• Query planner settings (collapse limits and Genetic Query Optimizer) https://www.postgresql.org/docs/current/runtime-config-query.html
•  WITH Queries (Common Table Expressions) https://www.postgresql.org/docs/current/queries-with.html 
• Our episode on benchmarking https://postgres.fm/episodes/benchmarking 
• The tests and benchmarks Nikolay mentioned https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues

~~~

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 under-indexing — how to tell if this applies to you, some general and specific cases, as well as some tricky ones. 
 
Here are some links to things they mentioned:

• Indexes (docs) https://www.postgresql.org/docs/current/indexes.html 
• random_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/
• Sequential scans https://www.pgmustard.com/docs/explain/sequential-scan  
• pg_stat_user_tables https://pgpedia.info/p/pg_stat_user_tables.html 
• pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
• auto_explain https://www.postgresql.org/docs/current/auto-explain.html 
• Our episode on slow queries https://postgres.fm/episodes/slow-queries-and-slow-transactions 
• Our episode on query macro analysis https://postgres.fm/episodes/macro-query-analysis-intro
• Running 10 Million PostgreSQL Indexes In Production (And Counting) https://www.heap.io/blog/running-10-million-postgresql-indexes-in-production 
• Faceting large result sets in PostgreSQL https://www.cybertec-postgresql.com/en/faceting-large-result-sets/ 
• Our episode on over-indexing https://postgres.fm/episodes/over-indexing 

~~~

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 over-indexing — what we mean by it, the regular issues people discuss about it, as well as a novel one Nikolay has come across and benchmarked recently. 
 
Here are some links to things they mentioned:

• Nikolay’s tweet on over-indexing https://twitter.com/samokhvalov/status/1713101666629927112 
• Heap-Only Tuples (HOT) optimization https://www.postgresql.org/docs/current/storage-hot.html 
• Our episode on index maintenance https://postgres.fm/episodes/index-maintenance 
• PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0 
• Our episode on connection poolers https://postgres.fm/episodes/connection-poolers 
• Configurable FP_LOCK_SLOTS_PER_BACKEND (Hackers mailing list discussion) https://www.postgresql.org/message-id/flat/CAM527d-uDn5osa6QPKxHAC6srOfBH3M8iXUM%3DewqHV6n%3Dw1u8Q%40mail.gmail.com 
• LWLock:lock_manager (Amazon RDS docs) https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.lw-lock-manager.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 query hints — what they are, what we do and don't have in PostgreSQL, and some other things in and around the ecosystem to be aware of. 
 
Here are some links to some extra things they mentioned:

• Query planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.html 
• Statistics used by the planner (docs) https://www.postgresql.org/docs/current/planner-stats.html 
• default_statistics_target  https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET 
• Optimiser hints discussion (wiki) https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion  
• An example mailing list thread from 2006 https://www.postgresql.org/message-id/flat/20061012151439.GT28647%40nasby.net  
• Peter Geoghegan tweet regarding invalid index https://twitter.com/petervgeoghegan/status/1599191964045672449 
• plantuner http://sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=blob;hb=HEAD;f=README.plantuner
• pg_hint_plan https://github.com/ossc-db/pg_hint_plan 
• Aurora PostgreSQL query plan management https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.overview.html 
• Building automatic adviser & performance tuning tools - Julien Rouhaud & Tatsuro Yamada: PGCon 2020 (the completely misremembered presentation Michael mentioned 🙈) https://www.youtube.com/watch?v=LQZK6p3SwwA 
• hypopg https://github.com/HypoPG/hypopg 

~~~

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 

In this episode (recorded live on YouTube), Nikolay discusses Postgres shutdown and startup times – how to troubleshoot them and, when needed, optimize. 
 
Some extra things mentioned in this episode:

• #PostgresMarathon series – every day, Nikolay posts a new howto-style article https://twitter.com/hashtag/PostgresMarathon
• Day 2: Postgres shutdown and restart attempts https://twitter.com/samokhvalov/status/1707147450044297673
• Day 3: How to troubleshoot long Postgres startup https://twitter.com/samokhvalov/status/1707466169245171773
• GitLab repo with #PostgresMarathon posts (markdown): https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos
• Episode 010 "WAL and checkpoint tuning": https://postgres.fm/episodes/wal-and-checkpoint-tuning

~~~

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 Postgres backups — why we need them, what the options are, whether a dump is a backup or not, and some considerations for lowering RPO and RTO at scale. 
 
Here are some links to some extra things they mentioned:

• pg_dump https://www.postgresql.org/docs/current/app-pgdump.html 
• pg_basebackup https://www.postgresql.org/docs/current/app-pgbasebackup.html
• pgBackRest https://github.com/pgbackrest/pgbackrest 
• WAL-G https://github.com/wal-g/wal-g 
• Barman https://github.com/EnterpriseDB/barman 
• Data loss at GitLab (2017) https://about.gitlab.com/blog/2017/02/01/gitlab-dot-com-database-incident/ 
• Dev Deletes Entire Production Database, Chaos Ensues (YouTube video) https://www.youtube.com/watch?v=tLdRBsuvVKc 
• Our episode on corruption https://postgres.fm/episodes/corruption 
• DBLab Engine https://github.com/postgres-ai/database-lab-engine 

~~~

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 the release of PostgreSQL 16 — the most important new features, what they mean for us as users, whether and when to upgrade, and more. 

Here are some links to some extra things they mentioned:

• Release notes https://www.postgresql.org/docs/current/release-16.html
• New Features With Examples (PDF from Noriyoshi Shinoda of Hewlett Packard Enterprise Japan) https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL16Beta1_New_Features_en_20230528_1.pdf  
• Why Upgrade? (site by depesz) https://why-upgrade.depesz.com/
• Waiting for PostgreSQL 16 (blog post series from Depesz) https://www.depesz.com/tag/pg16/
• Our episode on favourite features https://postgres.fm/episodes/our-favourite-v16-feature  
• Our episode on logical replication https://postgres.fm/episodes/logical-replication 
• Active Active in Postgres 16 (blog post from Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16 
• AlloyDB adaptive autovacuum https://cloud.google.com/blog/products/databases/alloydb-for-postgresql-under-the-hood-adaptive-autovacuum 
• Visualizing Postgres I/O Performance (talk by Melanie Plageman at PGCon) https://www.youtube.com/watch?v=CxyPZHG5beI 
• Our monitoring checklist episode https://postgres.fm/episodes/monitoring-checklist 
• pgvector https://github.com/pgvector/pgvector 

~~~

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 logical replication — some history, initialization, change data capture, how to scale it, some limitiations, and ways that it is getting better.
 
Here are some links to some things they mentioned:

• Logical replication https://www.postgresql.org/docs/current/logical-replication.html 
• GitLab upgraded multi-terabyte, heavily-loaded clusters with zero-downtime https://twitter.com/samokhvalov/status/1700574156222505276 
• pg_waldump https://www.postgresql.org/docs/current/pgwaldump.html 
• pg_dump and external snapshots (blog post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/ 
• Failover of logical replication slots in Patroni (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=SllJsbPVaow 
• Our episode on replication https://postgres.fm/episodes/replication 

~~~

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

Nikolay and Michael discuss their favourite feature each from the upcoming PostgreSQL 16 release.
 
Here are some links to some things they mentioned:

• v16 draft release notes https://www.postgresql.org/docs/16/release-16.html
• PGSQL Phriday #012 invitation from Ryan Booz https://www.pgsqlphriday.com/2023/08/pgsql-phriday-012/ 
• Subscribe options for the podcast https://postgres.fm/subscribe
• A recent closed source ClickHouse feature https://github.com/ClickHouse/ClickHouse/issues/44767#issuecomment-1683293218  
• Postgres TV hacking session with Andrey Borodin on \watch with limited number of loops (v16) https://www.youtube.com/watch?v=vTV8XhWf3mo 
• Allow \watch queries to stop on minimum rows returned (v17) https://github.com/postgres/postgres/commit/f347ec76e2a227e5c5b5065cce7adad16d58d209 
• pg_stat_io commit mentioning the op_bytes column (v16) https://github.com/postgres/postgres/commit/a9c70b46dbe152e094f137f7e6ba9cd3a638ee25 
• pg_size_pretty function https://www.postgresql.org/docs/current/functions-admin.html#id-1.5.8.33.9.3.2.2.7.1.1.1 
• Visualizing Postgres I/O Performance (talk by Melanie Plageman at PGCon) https://www.youtube.com/watch?v=CxyPZHG5beI 
• Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default 
• EXPLAIN (GENERIC_PLAN) blog post by Laurenz Albe https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/ 
• Running EXPLAIN on any query (video by Lukas Fittl) https://www.youtube.com/watch?v=CMftYJnqou0 
• PostgreSQL 16 Beta 1 New Features with Examples.(English Version) by Noriyoshi Shinoda https://twitter.com/nori_shinoda/status/1664481483355226114 
• Have auto_explain's log_verbose mode honor the value of compute_query_id (commitfest entry) https://commitfest.postgresql.org/42/4136/ 
• Make auto_explain print the query identifier in verbose mode (commit) https://github.com/postgres/postgres/commit/9d2d9728b8d546434aade4f9667a59666588edd6

~~~

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