Postgres FM

Latest Episodes:

Reads causing writes Jan 24, 2025

Show notes

Nikolay and Michael discuss a couple of surprising ways read queries (selects) can cause writes (shared buffers dirtied) in Postgres.
Here are some links to things they mentioned:

Reads causing writes in Postgres (post by Alex Jesipow) https://jesipow.com/blog/postgres-reads-cause-writes/
Exploring how SELECT queries can produce disk writes (post by Nikolay Sivko) https://blog.okmeter.io/postgresql-exploring-how-select-queries-can-produce-disk-writes-f36c8bee6b6f
Hint Bits (wiki) https://wiki.postgresql.org/wiki/Hint_Bits
pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
pg_stat_kcache https://github.com/powa-team/pg_stat_kcache
pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
BUFFERS by default (episode 4!) https://postgres.fm/episodes/buffers-by-default
Page layout (docs) https://www.postgresql.org/docs/current/storage-page-layout.html
From Reddit (user merlinm), PD_ALL_VISIBLE may be a third case https://www.reddit.com/r/PostgreSQL/comments/1hjuyfn/comment/m3e6e7v/
Coroot https://coroot.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

NOT VALID constraints Jan 17, 2025

Show notes

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

pg_squeeze Jan 10, 2025

Show notes

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

pg_duckdb Jan 03, 2025

Show notes

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

RLS vs performance Dec 20, 2024

Show notes

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

jOOQ Dec 13, 2024

Show notes

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

Column Tetris Dec 06, 2024

Show notes

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

pg_stat_statements track_planning Nov 29, 2024

Show notes

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

Is pg_dump a backup tool? Nov 22, 2024

Show notes

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

Append-only tables Nov 15, 2024

Show notes

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

Our TOPPODCAST Picks

Follow Us

Stay Connected

Related Podcasts

Links

Stay Connected