Postgres FM

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.htmlauto_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-introRunning 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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith 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.plantunerpg_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.aiMichael Christofides, founder of pgMustardWith 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/PostgresMarathonDay 2: Postgres shutdown and restart attempts https://twitter.com/samokhvalov/status/1707147450044297673Day 3: How to troubleshoot long Postgres startup https://twitter.com/samokhvalov/status/1707466169245171773GitLab repo with #PostgresMarathon posts (markdown): https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtosEpisode 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.aiMichael Christofides, founder of pgMustardWith 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.htmlpgBackRest 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.aiMichael Christofides, founder of pgMustardWith 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.htmlNew 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.aiMichael Christofides, founder of pgMustardWith 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing 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.htmlPGSQL Phriday #012 invitation from Ryan Booz https://www.pgsqlphriday.com/2023/08/pgsql-phriday-012/ Subscribe options for the podcast https://postgres.fm/subscribeA 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.aiMichael Christofides, founder of pgMustardWith special thanks to:Jessie Draws for the amazing artwork 

Topics discussed include different connection options, security measures and performance tradeoffs, protecting the database with encryption, benchmarking Unix domain sockets vs TCP/IP connections for performance, the implications of object ownership on connections, and connecting to a Postgres database without a password.

Delving into the concept of decoupling storage and compute in cloud products like RDS Aurora, Google Cloud AlloyDB, and Neon. Exploring the benefits and challenges of decoupled storage and compute, compatibility in open-source systems like Neon, and the need for thorough research when using Postgres in different providers. Discussing serverless architecture, including scale to zero and decoupled storage and compute. Exploring control and configurability of storage and compute in decoupled systems, and the potential benefits of Aurora's plan management feature for auto scaling and serverless approaches.