Postgres FM

Latest Episodes:

Denormalization Nov 08, 2024

Show notes

Nikolay and Michael discuss denormalization in Postgres — when and why to denormalize things, and a couple of specific cases Nikolay came across recently.
Here are some links to things they mentioned:

Denormalization https://en.wikipedia.org/wiki/Denormalization
Our episode on materialized views https://postgres.fm/episodes/materialized-views
Our episode on data model trade-offs https://postgres.fm/episodes/data-model-trade-offs
Our episode with Markus Winand https://postgres.fm/episodes/modern-sql
Universal Relation Data Modelling Considered Harmful (blog post by Michael Stonebraker and Álvaro Hernández) https://www.enterprisedb.com/blog/universal-relation-data-modelling-considered-harmful
Boyce–Codd normal form https://en.wikipedia.org/wiki/Boyce%E2%80%93Codd_normal_form
Our episode on slow count https://postgres.fm/episodes/slow-count
pg_ivm https://github.com/sraoss/pg_ivm
denorm https://github.com/rivethealth/denorm
Postgres Materialized Views, The Timescale Way (blog post by David Kohn) https://www.timescale.com/blog/materialized-views-the-timescale-way/
PgQ https://github.com/pgq/pgq
Databases, types, and the relational model (by C.J. Date and Hugh Darwen) https://www.dcs.warwick.ac.uk/~hugh/TTM/DTATRM.pdf

~~~

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

Postgres online communities Nov 01, 2024

Show notes

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

Advanced psql Oct 25, 2024

Show notes

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

Postgres Gotchas Oct 18, 2024

Show notes

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

Advanced EXPLAIN Oct 11, 2024

Show notes

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

Patroni Oct 04, 2024

Show notes

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

Postgres 17 Sep 27, 2024

Show notes

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

Planning time Sep 20, 2024

Show notes

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

Slow count Sep 13, 2024

Show notes

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

Skip scan Sep 06, 2024

Show notes

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: