Postgres FM

Nikolay and Michael discuss adding constraints in an online fashion, using NOT VALID and then VALIDATE CONSTRAINT.
 
Here are some links to things they mentioned:

• ADD table_constraint NOT VALID  https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-ADD-TABLE-CONSTRAINT
• Our episode on zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations
• VALIDATE CONSTRAINT https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-DESC-VALIDATE-CONSTRAINT
• ALTER TABLE notes https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-NOTES
• 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 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 Antonín Houska to discuss pg_squeeze — what it is, how it started, some of its features, and hopes of getting the functionality into core.
 
Here are some links to things they mentioned:

• Antonín Houska https://postgres.fm/people/antonin-houska
• pg_squeeze https://github.com/cybertec-postgresql/pg_squeeze
• Cybertec https://www.cybertec-postgresql.com
• pg_repack https://github.com/reorg/pg_repack
• Introducing pg_squeeze (by Kaarel Moppel) https://www.cybertec-postgresql.com/en/introducing-pg_squeeze-a-postgresql-extension-to-auto-rebuild-bloated-tables
• pg_squeeze is included in the PostgreSQL APT packages https://wiki.postgresql.org/wiki/Apt
• pg_squeeze is included in the PostgreSQL Yum packages https://yum.postgresql.org
• pg_rewrite https://github.com/cybertec-postgresql/pg_rewrite
• VACUUM FULL / CLUSTER CONCURRENTLY patch https://commitfest.postgresql.org/51/5117

~~~

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 Joe Sciarrino and Jelte Fennema-Nio to discuss pg_duckdb — what it is, how it started, what early users are using it for, and what they're working on next.
 
Here are some links to things they mentioned:

• Joe Sciarrino https://postgres.fm/people/joe-sciarrino
• Jelte Fennema-Nio https://postgres.fm/people/jelte-fennema-nio
• pg_duckdb https://github.com/duckdb/pg_duckdb
• Hydra https://www.hydra.so
• MotherDuck https://motherduck.com
• The problems and benefits of an elephant with a beak (lightning talk by Jelte) https://www.youtube.com/watch?v=ogvbKE4fw9A&list=PLF36ND7b_WU4QL6bA28NrzBOevqUYiPYq&t=1073s
• pg_duckdb announcement post (by Jordan and Brett from MotherDuck) https://motherduck.com/blog/pg_duckdb-postgresql-extension-for-duckdb-motherduck
• pg_duckdb 0.2 release https://github.com/duckdb/pg_duckdb/releases/tag/v0.2.0

~~~

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 Row Level Security in Postgres, focussing on the performance side effects and some tips to avoid (or minimize) them.
 
Here are some links to things they mentioned:

• Row Security Policies (docs) https://www.postgresql.org/docs/current/ddl-rowsecurity.html
• 7+ million Postgres tables (recent talk by Kailash Nadh) https://www.youtube.com/watch?v=xhi5Q_wL9i0
• Row Level Security guide (Supabase docs) https://supabase.com/docs/guides/database/postgres/row-level-security
• current_setting function https://www.postgresql.org/docs/current/functions-admin.html#id-1.5.8.34.3.6.2.2.1.1.1.1
• Our slow count episode https://postgres.fm/episodes/slow-count
• RLS Performance and Best Practices (gist from Gary Austin) https://github.com/orgs/supabase/discussions/14576
• Everything you need to know about Postgres Row Level Security (talk by Paul Copplestone) https://www.youtube.com/watch?v=vZT1Qx2xUCo
• BUFFERS enabled for EXPLAIN ANALYZE by default (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07
• Add UUID version 7 generation function (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=78c5e141e9c139fc2ff36a220334e4aa25e1b0eb
• Postgres hacking session with Andrey and Kirk (for UUIDv7): https://www.youtube.com/watch?v=YPq_hiOE-N8

~~~

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 Lukas Eder, the creator of jOOQ, to discuss what it is, some nice developer experience features it has, and some fun things he's come across from a Postgres perspective.
 
Here are some links to things they mentioned:

• Lukas Eder https://postgres.fm/people/lukas-eder
• jOOQ https://www.jooq.org/ 
• DSL https://en.wikipedia.org/wiki/Domain-specific_language 
• SQL Dialects https://www.jooq.org/javadoc/latest/org.jooq/org/jooq/SQLDialect.html
• MERGE https://www.postgresql.org/docs/current/sql-merge.html 
• match_recognize https://modern-sql.com/feature/match_recognize 
• JOOQ, joy of SQL (talk by Kevin Davin) https://www.youtube.com/watch?v=8Ej47GZX9D8  
• BUFFERS enabled for EXPLAIN ANALYZE by default (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07 
• PostGIS https://postgis.net/ 
• 10 SQL Tricks That You Didn’t Think Were Possible (blog post by Lukas) https://blog.jooq.org/10-sql-tricks-that-you-didnt-think-were-possible/ 
• jOOQ questions on Stack Overflow https://stackoverflow.com/questions/tagged/jooq 
• Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown 

~~~

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 "Column Tetris" — what it is, why it matters, how to order columns for new tables, and how to re-organise existing ones.
 
Here are some links to things they mentioned:

• “Column Tetris” by Erwin Brandstetter on Stack Overflow  https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql/7431468#7431468
• Data Types https://www.postgresql.org/docs/current/datatype.html
• OrioleDB beta7 benchmarks https://www.orioledb.com/blog/orioledb-beta7-benchmarks
• pg_hexedit https://github.com/petergeoghegan/pg_hexedit
• Saving Space Basically for Free (blog post by James Coleman from Braintree) https://medium.com/paypal-tech/postgresql-at-scale-saving-space-basically-for-free-d94483d9ed9a
• Ordering Table Columns (GitLab https://docs.gitlab.com/ee/development/database/ordering_table_columns.html
• postgres_dba alignment padding query https://github.com/NikolayS/postgres_dba/blob/master/sql/p1_alignment_padding.sql
• Good explanation from Marco Slot of how alignment was used to fix a recent issue https://x.com/marcoslot/status/1858132850383421570
• pg_repack feature request discussion https://github.com/reorg/pg_repack/issues/101
• Our episode on bloat (with Chelsea Dole) https://postgres.fm/episodes/bloat
• Optimizing table layout for maximum efficiency (blog post by Renato Massaro) https://r.ena.to/blog/optimizing-postgres-table-layout-for-maximum-efficiency

~~~

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 track_planning parameter of pg_stat_statements — what it is, how it affects performance, and when or whether you should switch it on.
 
Here are some links to things they mentioned:

• pg_stat_statements.track_planning https://www.postgresql.org/docs/current/pgstatstatements.html#id-1.11.7.40.9.2.4.1.3
• Our episode about pg_stat_statements https://postgres.fm/episodes/pg_stat_statements
• PostgreSQL 13.0 release notes https://www.postgresql.org/docs/release/13.0/
• track_planning causing performance regression (thread on hackers during v13 beta) https://www.postgresql.org/message-id/flat/2895b53b033c47ccb22972b589050dd9%40EX13D05UWC001.ant.amazon.com
• Our episode on 4 million TPS https://postgres.fm/episodes/four-million-tps
• Observer effect in pg_stat_statements and pg_stat_kcache (Postgres TV Hacking session with Andrey and Kirk) https://www.youtube.com/live/wHMNX-fHb2A?si=DPgmrPaSpPF6DxuS

~~~

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 Gülçin Yıldırım Jelínek and Robert Haas to discuss both the technical question of whether or not pg_dump is a backup tool, as well as the tone and intent behind the statement "pg_dump is not a backup tool".
 
Here are some links to things they mentioned:

• Gülçin Yıldırım Jelínek https://postgres.fm/people/gulcin-yildirim-jelinek
• Robert Haas https://postgres.fm/people/robert-haas
• Why you should upgrade PostgreSQL today (blog post by Gülçin) https://xata.io/blog/cve-2024-7348-postgres-upgrade
• If pg_dump is not a backup tool, what is? (blog post by Gülçin) https://xata.io/blog/pgdump-is-not-a-backup-tool
• Is pg_dump a backup tool? (blog post by Robert) https://rhaas.blogspot.com/2024/10/is-pgdump-backup-tool.html?m=1
• Why pg_dump is amazing (blog post by Robert) https://rhaas.blogspot.com/2024/11/why-pgdump-is-amazing.html
• Avoid too prominent use of "backup" on pg_dump man page (commit by Peter Eisentraut) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=4f29394ea941f688fd4faf7260d2c198931ca797
• Is pg_dump a backup tool? (poll by Nikolay with options Yes / No / Define backup) https://x.com/samokhvalov/status/1847015453056786771
• What's the best way to make a backup (a recent example discussion on Reddit) https://www.reddit.com/r/PostgreSQL/comments/1gu4r05/whats_the_best_way_to_make_a_backup/
• Hackers mailing list https://www.postgresql.org/list/pgsql-hackers/ 
• Praise, Criticism, and Dialogue (blog post by Robert) https://rhaas.blogspot.com/2023/12/praise-criticism-and-dialogue.html 
• Out-of-cycle release scheduled for November 21, 2024 https://www.postgresql.org/about/news/out-of-cycle-release-scheduled-for-november-21-2024-2958/ 
• pgBackRest https://github.com/pgbackrest/pgbackrest 
• Barman https://github.com/EnterpriseDB/barman 
• Our previous episode on backups https://postgres.fm/episodes/backups 

~~~

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