Postgres FM – Détails, épisodes et analyse

Nikolay and Michael discuss append-only tables in Postgres — what they are, some unique challenges they bring, and some options for compressing / removing / offloading the data eventually.
 
Here are some links to things they mentioned:

• Append-only https://en.wikipedia.org/wiki/Append-only
• Our episode on BRIN indexes https://postgres.fm/episodes/brin-indexes
• Tips to Improve Insert Performance https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance/ 
• Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
• autovacuum_vacuum_insert_scale_factor https://www.postgresql.org/docs/current/runtime-config-autovacuum.html#GUC-AUTOVACUUM-VACUUM-INSERT-SCALE-FACTOR 
• Our episode about compression https://postgres.fm/episodes/compression
• New project announced recently called BemiDB https://x.com/samokhvalov/status/1854726389603484145
• pg_tier https://github.com/tembo-io/pg_tier

~~~

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

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 

Here are links to a few things we mentioned: 

• Date/Time Types (docs) 
• Don’t use timestamp without time zone (wiki)  
• Date/Time functions and operators (docs)  
• Postgres AT TIME ZONE explained (blog post by Bruce Momjian) 
• Our episode on BRIN indexes  
• RUM indexes 
• allballs (mailing list thread) 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: 

• PostgREST documentation
• Supabase
• No offset (blog post by Markus Winand)
• JSON functions and operators (PostgreSQL docs)
• Stored procedures (a previous Postgres FM episode)
• PostGraphile 
• Hasura 
• Parse, Inc. 
• Firebase
• PostgresML
• PgQ 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Apologies, Michael's audio is not great in this, we'll hopefully be back to normal next week!

Here are links to a few things we mentioned: 

• Materialized views (docs)
• Refresh materialized view (docs)
• Timescale blog post
• PlanetScale Boost (content warning: MySQL) 
• Incremental Materialized Views with pg_ivm (video by Lukas Fittl) 
• Articles on how to do your own incremental updates(?)
• Materialize (company) 
• Materialize talk
• Incremental View Maintenance (Postgres wiki)  
• Implementing Incremental View Maintenance (mailing list thread) 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: 

• Heap-only tuple updates (docs)
• README file (source code)
• Heap Only Tuple chapter (Internals of PostgreSQL by Hironobu SUZUKI) 
• How partial, covering, and multicolumn indexes may slow down UPDATEs (blog post by Nikolay) 
• Why Uber switched from Postgres to MySQL (blog post)  
• Fighting write amplification with HOT updates (Adyen blog post) 
• HOT Updates vs Bottom-Up Index Deletion (video by Lukas Fittl from pganalyze) 
• Reduced Bloat with Bottom-Up Deletion (blog post by Hamid Akthar from Percona) 
• What is fillfactor and how does it affect performance? (blog post by Kaarel Moppel from Cybertec)

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: 

• Database Lab Engine
• Overview of Aurora cloning 
• PlanetScale branching
• Common DB schema change mistakes (blog post by Nikolay)
• Supabase vision
• Neon branching
• Shift-left testing

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: 

• A brief history of Version Control Systems (blog post by Mehran Hajirajabi)
• Redgate Source Control for Oracle (and SQL Server)
• Flyway 
• Sqitch 
• Liquibase 
• pgAdmin Schema Diff 
• Migra 
• PostgresCompare 
• Schema based versioning and deployment (blog post by Valentine Gogichashvili)
• Change management tools and techniques (PostgreSQL Wiki)
• GitLab migration_helpers.rb 
• Database schema changes without downtime (blog post by James Coleman from Braintree/PayPal) 
• Zero-downtime Postgres schema migrations need lock_timeout and retries (blog post by Nikolay) 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork 

Here are links to a few things we mentioned: 

• PostgreSQL 14 coin (upside down!)
• Intro to Planner Hacking (talk by Melanie Plageman)
• Demystifying contributing to PostgreSQL (talk by Lætitia Avrot)
• How to become a PostgreSQL contributor (blog post by Aleksander Alekseev)
• Compile and install from source code (PostgreSQL Wiki)
• PostgreSQL mailing lists
• GitHub PostgreSQL mirror
• GitLab PostgreSQL mirror
• Commitfests
• So, you want to be a developer? (PostgreSQL Wiki)
• Resolving the search engine issue (mailing list thread)
• Planet PostgreSQL
• pg_stat_monitor (extension by Percona)
• ZomboDB
• pgx (framework for developing extensions)
• Awesome Postgres
• Depesz blog 

------------------------

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov / @michristofides / @PostgresFM, 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.ai
• Michael Christofides, founder of pgMustard

With special thanks to:

• Jessie Draws for the amazing artwork