Postgres FM

Nikolay and Michael discuss online Postgres communities — the ones they prefer, the types of conversations in each, and some other places to ask questions or follow news. 
 
Here are some links to things they mentioned:

• https://www.postgresql.org/community
• Mailing lists https://www.postgresql.org/list
• IRC https://www.postgresql.org/community/irc
• Slack https://pgtreats.info/slack-invite
• Stack Overflow https://stackoverflow.com/questions/tagged/postgres
• DBA Stack Exchange https://dba.stackexchange.com/questions/tagged/postgres
• Reddit https://www.reddit.com/r/PostgreSQL
• Discord https://discord.gg/bW2hsax8We
• How to run ANALYZE (merge request discussion) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/merge_requests/35
• This episode on YouTube 
• https://postgresqlco.nf
• Planet PostgreSQL https://planet.postgresql.org
• Postgres Weekly https://postgresweekly.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!

~~~

Postgres FM is produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Nikolay and Michael discuss some cool things you can do with psql, the official CLI that ships with Postgres.
 
Here are some links to things they mentioned:

• psql docs https://www.postgresql.org/docs/current/app-psql.html 
• Our episode on psql vs GUIs https://postgres.fm/episodes/psql-vs-guis
• postgres_dba https://github.com/NikolayS/postgres_dba
• Our episode on massive deletes https://postgres.fm/episodes/massive-deletes
• Postgres hacking session on \watch with limited number of loops https://www.youtube.com/watch?v=vTV8XhWf3mo 
• pspg https://github.com/okbob/pspg 
• Our episode on Postgres gotchas https://postgres.fm/episodes/postgres-gotchas
• current_setting() and set_config() docs https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SET
• psql tips (site by Lætitia Avrot) https://psql-tips.org

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Nikolay and Michael discuss some Postgres Gotchas, things you might expect to work one way in fact working another way.
 
Here are some links to things they mentioned:

• Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
• Postgres Gotchas (list by Ian Barwick) https://sql-info.de/postgresql/postgres-gotchas.html
• Our episode on slow count https://postgres.fm/episodes/slow-count
• Discussion on X about major version upgrades and statistics https://x.com/samokhvalov/status/1844593601638260850
• Our episode on upgrades https://postgres.fm/episodes/upgrades
• Statistics Import and Export (commitfest entry which link to email thread) https://commitfest.postgresql.org/50/4538/
• vacuumdb https://www.postgresql.org/docs/current/app-vacuumdb.html
• vacuum_cost_delay https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-VACUUM-COST-DELAY
• Zero-downtime Postgres schema migrations need lock_timeout and retries (blog post by Nikolay) https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries
• Postgres Hacking 101: adding params, prevent_unqualified_deletes and prevent_unqualified_updates https://www.youtube.com/watch?v=samLkrC5xQA 
• PostgREST suggestion to use pg-safeupdate https://docs.postgrest.org/en/v12/integrations/pg-safeupdate.html#pg-safeupdate
• DBOS (new company co-founded by Michael Stonebraker) https://www.dbos.dev

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Nikolay and Michael discuss some more advanced topics around EXPLAIN, including some tips for complex query plans, some recent improvements, and an idea or two that might make it even better.
 
Here are some links to things they mentioned:

• Michael’s solo episode on EXPLAIN basics https://postgres.fm/episodes/explain
• Our episode on auto_explain https://postgres.fm/episodes/auto_explain
• Which cloud providers support auto_explain? (Blog post by Michael) https://www.pgmustard.com/blog/which-cloud-providers-support-auto-explain
• psychopg https://www.psycopg.org
• Our first episode on BUFFERS https://postgres.fm/episodes/buffers-by-default
• Our second episode on BUFFERS https://postgres.fm/episodes/buffers-ii-the-sequel
• track_io_timing https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING
• compute_query_id https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID 
• EXPLAIN: beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Michael and Nikolay are joined by Alexander Kukushkin, PostgreSQL contributor and maintainer of Patroni, to discuss all things Patroni — what it is, how it works, recent improvements, and more.

Here are some links to things they mentioned:

• Alexander Kukushkin https://postgres.fm/people/alexander-kukushkin
• Patroni https://github.com/patroni/patroni
• Spilo https://github.com/zalando/spilo 
• Zalando Postgres Operator https://github.com/zalando/postgres-operator
• Crunchy Data Postgres Operator https://github.com/CrunchyData/postgres-operator
• Split-brain https://en.wikipedia.org/wiki/Split-brain_(computing)
• repmgr https://github.com/EnterpriseDB/repmgr
• CloudNativePG https://github.com/cloudnative-pg/cloudnative-pg
• Patroni release notes https://patroni.readthedocs.io/en/latest/releases.html
• Citus & Patroni talk and demo by Alexander (at Citus Con 2023) https://www.youtube.com/watch?v=Mw8O9d0ez7E

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Nikolay and Michael discuss the fresh new Postgres 17 release! They cover several performance improvements, favourite new features, and some considerations for upgrading.
 
Here are some links to things they mentioned:

• Postgres 17 release notes https://www.postgresql.org/docs/17/release-17.html
• transaction_timeout episode https://postgres.fm/episodes/transaction_timeout
• VACUUM improvements discussed towards end of episode with Melanie Plageman https://postgres.fm/episodes/getting-started-with-benchmarking
• B-tree improvements discussed in episdode with Peter Geoghegan https://postgres.fm/episodes/skip-scan
• As Rails developers, why we are excited about PostgreSQL 17 (blog post by Benoit Tigeot) https://benoittgt.github.io/blog/postgres_17_rails/ 
• Real World Performance Gains With Postgres 17 B-tree Bulk Scans (blog post by Brandur Leach) https://www.crunchydata.com/blog/real-world-performance-gains-with-postgres-17-btree-bulk-scans
• MERGE RETURNING came up towards end of episode with Haki Benita https://postgres.fm/episodes/get-or-create
• uuid_extract_timestamp and uuid_extract_version functions https://www.postgresql.org/docs/current/functions-uuid.html
• Episode on UUID https://postgres.fm/episodes/uuid
• Partitioning by ULID https://postgres.fm/episodes/partitioning-by-ulid
• Why Upgrade? (site by depesz) https://why-upgrade.depesz.com
• Why we spent the last month eliminating PostgreSQL subtransactions (GitLab blog post) https://about.gitlab.com/blog/2021/09/29/why-we-spent-the-last-month-eliminating-postgresql-subtransactions
• Synchronization of sequences to subscriber (patch that needs review!)  https://commitfest.postgresql.org/49/5111

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Nikolay and Michael discuss planning time in Postgres — what it is, how to spot issues, its relationship to things like partitioning, and some tips for avoiding issues.
 
Here are some links to things they mentioned:

• Query Planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.html
• Are there limits to partition counts? (Blog post by depesz) https://www.depesz.com/2021/01/17/are-there-limits-to-partition-counts
• Nikolays recent experiment https://postgres.ai/chats/01920004-a982-7896-b8cb-dfd2406359b0
• PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0
• “The year of the lock manager’s revenge” (from blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability
• pg_stat_statements.track_planning https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.42.9.2.4.1.3
• pg_hint_plan https://github.com/ossc-db/pg_hint_plan

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Nikolay and Michael discuss why counting can be slow in Postgres, and what the options are for counting things quickly at scale.
 
Here are some links to things they mentioned:

• Aggregate functions (docs) https://www.postgresql.org/docs/current/functions-aggregate.html
• PostgREST https://github.com/PostgREST/postgrest 
• Get rid of count by default in PostgREST https://github.com/PostgREST/postgrest/issues/273 
• Faster PostgreSQL Counting (by Joe Nelson on the Citus blog) https://www.citusdata.com/blog/2016/10/12/count-performance 
• Our episode on Index-Only Scans https://postgres.fm/episodes/index-only-scans
• Postgres HyperLogLog https://github.com/citusdata/postgresql-hll
• Our episode on Row estimates https://postgres.fm/episodes/row-estimates 
• Our episode about dangers of NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown 
• Aggregate expressions, including FILTER https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES
• Spread writes for counter cache (tip from Tobias Petry) https://x.com/tobias_petry/status/1475870220422107137
• pg_ivm extension (Incremental View Maintenance) https://github.com/sraoss/pg_ivm 
• pg_duckdb announcement https://motherduck.com/blog/pg_duckdb-postgresql-extension-for-duckdb-motherduck
• Our episode on Queues in Postgres https://postgres.fm/episodes/queues-in-postgres
• Our episode on Real-time analytics https://postgres.fm/episodes/real-time-analytics
• ClickHouse acquired PeerDB https://clickhouse.com/blog/clickhouse-acquires-peerdb-to-boost-real-time-analytics-with-postgres-cdc-integration
• Timescale Continuous Aggregates https://www.timescale.com/blog/materialized-views-the-timescale-way
• Timescale editions https://docs.timescale.com/about/latest/timescaledb-editions
• Loose indexscan https://wiki.postgresql.org/wiki/Loose_indexscan

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Michael and Nikolay are joined by Peter Geoghegan, major contributor and committer to Postgres, to discuss adding skip scan support to PostgreSQL over versions 17 and 18.
 
Here are some links to things they mentioned:

• Peter Geoghegan https://postgres.fm/people/peter-geoghegan
• Peter’s previous (excellent) interview on Postgres TV https://www.youtube.com/watch?v=iAPawr1DxhM
• Efficient Search of Multidimensional B-Trees (1995 paper by Harry Leslie, Rohit Jain, Dave Birdsall, and Hedieh Yaghmai) https://vldb.org/conf/1995/P710.PDF
• Index Skip Scanning in Oracle https://oracle-base.com/articles/9i/index-skip-scanning
• Peter’s introductory email to the hackers mailing list about adding skip scan https://www.postgresql.org/message-id/CAH2-Wzmn1YsLzOGgjAQZdn1STSG_y8qP__vggTaPAYXJP+G4bw@mail.gmail.com
• Loose Indexscan versus Index Skip Scan (PostgreSQL wiki) https://wiki.postgresql.org/wiki/Loose_indexscan
• Tom Lane will be on the Talking Postgres podcast on October 9th https://aka.ms/TalkingPostgres-Ep20-cal
• Benoit Tigeot feedback and repro (originally reported via Slack) https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d?permalink_comment_id=4597410#gistcomment-4597410
• Summary video and blog post about the v17 work by Lukas from pganalyze (not mentioned but great) https://pganalyze.com/blog/5mins-postgres-17-faster-btree-index-scans
• Understanding HNSW + filtering (pgvector repo discussion) https://github.com/pgvector/pgvector/issues/259
• btree_gin https://www.postgresql.org/docs/current/btree-gin.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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork 

Nikolay and Michael discuss PostgreSQL emergencies — both the psychological side of incident management, and some technical aspects too.
 
Here are some links to things they mentioned:

• Site Reliability Engineering resources from Google https://sre.google
• GitLab Handbook SRE https://handbook.gitlab.com/job-families/engineering/infrastructure/site-reliability-engineer
• Keeping Customers Streaming — The Centralized Site Reliability Practice at Netflix https://netflixtechblog.com/keeping-customers-streaming-the-centralized-site-reliability-practice-at-netflix-205cc37aa9fb
• Our monitoring checklist episode https://postgres.fm/episodes/monitoring-checklist
• Hannu Krosing talk on Postgres TV — Do you vacuum everyday? https://www.youtube.com/watch?v=JcRi8Z7rkPg
• Our episode on corruption https://postgres.fm/episodes/corruption
• Nikolay’s episode on stopping and starting Postgres faster https://postgres.fm/episodes/stop-and-start-postgres-faster
• Our episode on out of disk https://postgres.fm/episodes/out-of-disk
• The USE method (Brendan Gregg) https://www.brendangregg.com/usemethod.html 
• Thundering herd problem https://en.wikipedia.org/wiki/Thundering_herd_problem
• pgwatch2 Postgres AI edition https://gitlab.com/postgres-ai/pgwatch2

~~~

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 produced by:

• Michael Christofides, founder of pgMustard
• Nikolay Samokhvalov, founder of Postgres.ai

With special thanks to:

• Jessie Draws for the elephant artwork