Postgres FM

pg_flight_recorder

Nikolay Samokhvalov and Michael Christofides — Fri, 15 May 2026 14:00:00 +0100

Nik and Michael are joined by David Ventimiglia to discuss pg_flight_recorder, a new tool he created for monitoring a Postgres database from within.

Here are some links to things they mentioned:

David Ventimiglia https://postgres.fm/people/david-ventimiglia
pg_flight_recorder https://github.com/dventimisupabase/pg_flight_recorder
Supabase https://supabase.com
pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
pg_ash https://github.com/NikolayS/pg_ash
pg_cron https://github.com/citusdata/pg_cron
pg_tle https://github.com/aws/pg_tle

~~~

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 credit to:

Jessie Draws for the elephant artwork

PgQue

Nikolay Samokhvalov and Michael Christofides — Fri, 08 May 2026 14:00:00 +0100

Nik and Michael discuss Nik's new project PgQue, a descendent of Skype's PgQ, for running queue-like workloads in Postgres.

Here are some links to things they mentioned:

Our first episode on Queues in Postgres https://postgres.fm/episodes/queues-in-postgres
PgQue https://github.com/NikolayS/pgque
HN discussion https://news.ycombinator.com/item?id=47817349
PgQ https://github.com/pgq/pgq
pgmq https://github.com/pgmq/pgmq
River https://riverqueue.com
Keeping a Postgres queue healthy (blog post by Simeon Griggs / PlanetScale) https://planetscale.com/blog/keeping-a-postgres-queue-healthy
Postgres Job Queues & Failure By MVCC (blog post by Brandur) https://brandur.org/postgres-queues
My queries to monitor autovacuum (blog post by Laurenz Albe) https://www.cybertec-postgresql.com/en/monitor-autovacuum-my-queries/
SELECT FOR UPDATE considered harmful (blog post by Laurenz Albe) https://www.cybertec-postgresql.com/en/select-for-update-considered-harmful-postgresql/
Christophe Pettus blog post https://thebuild.com/blog/2026/05/03/pgque-two-snapshots-and-a-diff
Our episode on pg_ash https://postgres.fm/episodes/pg_ash
Rediscovering PgQ (Alexander Kukushkin slides) https://speakerdeck.com/cyberdemn/rediscovering-pgq
Tick frequency tuning https://github.com/NikolayS/PgQue/blob/main/docs/tick-frequency.md

~~~

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 credit to:

Jessie Draws for the elephant artwork

pg_wait_tracer

Nikolay Samokhvalov and Michael Christofides — Fri, 24 Apr 2026 14:00:00 +0100

Nik and Michael are joined by Dmitry Fomin to discuss his new tool pg_wait_tracer, as well as changes that could be made to core to allow wait event tracing with lower overhead, and on managed services.

Here are some links to things they mentioned:

Dmitry Fomin https://postgres.fm/people/dmitry-fomin
pg_wait_tracer https://github.com/DmitryNFomin/pg_wait_tracer
pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
pg_10046 https://github.com/DmitryNFomin/pg_10046
Jeremy Schneider reply on LinkedIn https://www.linkedin.com/feed/update/urn:li:activity:7414966981847748608
RDS for PostgreSQL wait event docs https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.concepts.summary.html
Custom wait events for extensions (added in PostgreSQL 17) https://www.postgresql.org/docs/current/xfunc-c.html#XFUNC-ADDIN-WAIT-EVENTS
Hacking Postgres with Dmitry, Kirk, and Nik (Part 1) https://www.youtube.com/watch?v=3Gtuc2lnnsE
Hacking Postgres with Dmitry, Kirk, and Nik (Part 2) https://www.youtube.com/watch?v=6kqpjnpl5Gc
Tanel Poder https://tanelpoder.com/about/
USDT static tracepoints for wait event tracing (proof of concept by Nik) https://github.com/NikolayS/postgres/pull/18
Add wait_event_timing: Oracle-style wait event instrumentation (patch by Dmitry) https://github.com/DmitryNFomin/postgres/pull/1
PgQue benchmarks https://github.com/NikolayS/pgque/blob/main/docs/benchmarks.md
The Art of Computer Systems Performance Analysis Techniques for Experimental Design, Measurement, Simulation and Modeling (by Raj K. Jain) https://www.researchgate.net/publication/259310412_The_Art_of_Computer_Systems_Performance_Analysis_Techniques_For_Experimental_Design_Measurement_Simulation_and_Modeling_NY_Wiley
Performance modeling and design of computer systems queueing theory in action (by Prof. Mor Harchol-Balter) https://www.cs.cmu.edu/~harchol/PerformanceModeling/book.html
Oracle Performance Firefighting (by Craig_Shallahamer) https://www.abebooks.co.uk/9780984102303/Oracle-Performance-Firefighting-Craig-Shallahamer-0984102302/plp
Process Mining: Data Science in Action (by Wil van der Aalst) https://link.springer.com/book/10.1007/978-3-662-49851-4

~~~

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 credit to:

Jessie Draws for the elephant artwork

Schema design checklist

Nikolay Samokhvalov and Michael Christofides — Fri, 17 Apr 2026 14:00:00 +0100

Nik and Michael discuss a list of things to check when designing new schema in Postgres.

Here are some links to things they mentioned:

Use BIGINT in Postgres (blog post by Ryan Lambert) https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default
Postgres 18 and UUIDv7 (blog post by Gwen Shapira) https://www.thenile.dev/blog/uuidv7
How to use UUID (how-to guide by Nik) https://postgres.ai/docs/postgres-howtos/schema-design/data-types/how-to-use-uuid
Our episode on constraints https://postgres.fm/episodes/constraints
Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
MultiXact member space exhaustion episode (with Metronome) https://postgres.fm/episodes/multixact-member-space-exhaustion
Our Column Tetris episode https://postgres.fm/episodes/column-tetris
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
Over-indexing episode https://postgres.fm/episodes/over-indexing
Under-indexing episode https://postgres.fm/episodes/under-indexing
Gadget's use of Postgres https://postgres.fm/episodes/gadgets-use-of-postgres
Partitioning episode https://postgres.fm/episodes/partitioning
RLS vs performance episode https://postgres.fm/episodes/rls-vs-performance

~~~

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 credit to:

Jessie Draws for the elephant artwork

What’s Missing in Postgres?

Nikolay Samokhvalov and Michael Christofides — Fri, 03 Apr 2026 13:07:50 +0100

Nik and Michael are joined by Bruce Momjian to discuss his new talk "What’s Missing in Postgres?"

Here are some links to things they mentioned:

Bruce Momjian https://postgres.fm/people/bruce-momjian
EDB https://www.enterprisedb.com
What’s Missing in Postgres? (Bruce’s slides) https://momjian.us/main/writings/pgsql/missing.pdf
The Wonderful World of WAL (Bruce’s slides) https://momjian.us/main/writings/pgsql/wal.pdf
Getting started with benchmarking (our episode with Melanie Plageman) https://postgres.fm/episodes/getting-started-with-benchmarking
Myths and Truths about Synchronous Replication in Postgres (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=PFn9qRGzTMc
The Future of Postgres Sharding (Bruce’s slides) https://momjian.us/main/writings/pgsql/sharding.pdf
MultiXact member space exhaustion https://postgres.fm/episodes/multixact-member-space-exhaustion

~~~

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 credit to:

Jessie Draws for the elephant artwork

Long-running transactions

Nikolay Samokhvalov and Michael Christofides — Fri, 27 Mar 2026 14:00:00 +0000

Nik and Michael discuss long-running transactions, including when they're harmless, when they cause issues, and how to mitigate those issues.

Here are some links to things they mentioned:

transaction_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-TRANSACTION-TIMEOUT
Our episode on transaction_timeout https://postgres.fm/episodes/transaction_timeout
Our episode on slow queries (which was also our first ever episode!) https://postgres.fm/episodes/slow-queries-and-slow-transactions
Our episode on locks https://postgres.fm/episodes/locks
lock_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT
Transaction Isolation levels https://www.postgresql.org/docs/current/transaction-iso.html
pg_current_xact_id_if_assigned() https://pgpedia.info/p/pg_current_xact_id_if_assigned.html
Monitor xmin horizon to prevent XID/MultiXID wraparound and high bloat (how-to guide by Nik) https://postgres.ai/docs/postgres-howtos/performance-optimization/monitoring/how-to-monitor-xmin-horizon
idle_replication_slot_timeout https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-IDLE-REPLICATION-SLOT-TIMEOUT
PREPARE TRANSACTION https://www.postgresql.org/docs/current/sql-prepare-transaction.html
log_autovacuum_min_duration https://www.postgresql.org/docs/devel/runtime-config-logging.html#GUC-LOG-AUTOVACUUM-MIN-DURATION
PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
statement_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT
idle_in_transaction_session_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
lock_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-LOCK-TIMEOUT

~~~

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 credit to:

Jessie Draws for the elephant artwork

PostGIS

Nikolay Samokhvalov and Michael Christofides — Fri, 13 Mar 2026 14:00:00 +0000

Nik and Michael are joined by Regina Obe and Paul Ramsey to discuss PostGIS.

Here are some links to things they mentioned:

Regina Obe https://postgres.fm/people/regina-obe
Paul Ramsey https://postgres.fm/people/paul-ramsey
PostGIS https://postgis.net
MobilityDB https://github.com/MobilityDB/MobilityDB
pgRouting https://github.com/pgRouting/pgrouting
Google BigQuery GIS public alpha blog post https://cloud.google.com/blog/products/data-analytics/whats-happening-bigquery-integrated-machine-learning-maps-and-more
PostGIS Day 2025 talk recordings https://www.youtube.com/watch?v=wuNO_cW2g-0&list=PLavJpcg8cl1EkQWoCbczsOjFTe-SHg_8m
pg_lake https://github.com/Snowflake-Labs/pg_lake
GeoParquet https://geoparquet.org
ST_DWithin https://postgis.net/docs/ST_DWithin.html
Postgres JSONB Columns and TOAST: A Performance Guide https://www.snowflake.com/en/engineering-blog/postgres-jsonb-columns-and-toast
FOSS4G https://foss4g.org
OpenStreetMap https://www.openstreetmap.org
PgDay Boston https://2026.pgdayboston.org
SKILL.md file https://github.com/postgis/postgis/blob/68dde711039986b47eb62feda45bb24b13b0ea37/doc/SKILL.md
Production query plans without production data (blog post by Radim Marek) https://boringsql.com/posts/portable-stats
PostgreSQL: Up and Running, 4th Edition (by Regina Obe, Leo Hsu) https://www.oreilly.com/library/view/postgresql-up-and/9798341660885

~~~

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 credit to:

Jessie Draws for the elephant artwork

Plan flips

Nikolay Samokhvalov and Michael Christofides — Fri, 06 Mar 2026 14:00:00 +0000

Nik and Michael discuss query plan flips in Postgres — what they are, some causes, mitigations, longer term solutions, and the recent outage at Clerk.

Here are some links to things they mentioned:

Recent postmortem from Clerk https://clerk.com/blog/2026-02-19-system-outage-postmortem
The real cost of random I/O (blog post by Tomas Vondra) https://vondra.me/posts/the-real-cost-of-random-io
autovacuum_analyze_scale_factor https://www.postgresql.org/docs/current/runtime-config-vacuum.html#GUC-AUTOVACUUM-ANALYZE-SCALE-FACTOR
default_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET
pg_hint_plan https://github.com/ossc-db/pg_hint_plan
Aurora PostgreSQL query plan management https://docs.aws.amazon.comAmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.Start.html
pg_stat_plans https://github.com/pganalyze/pg_stat_plans
pg_plan_alternatives https://jnidzwetzki.github.io/2026/03/04/pg-plan-alternatives.html
Waiting for Postgres 19: Better Planner Hints with Path Generation Strategies https://pganalyze.com/blog/5mins-postgres-19-better-planner-hints

~~~

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 credit to:

Jessie Draws for the elephant artwork

pg_ash

Nikolay Samokhvalov and Michael Christofides — Fri, 20 Feb 2026 14:19:00 +0000

Nik and Michael discuss pg_ash — a new tool (not extension!) from Nik that samples and stores wait events from pg_stat_activity.

Here are some links to things they mentioned:

pg_ash https://github.com/NikolayS/pg_ash
pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
Amazon RDS performance insights https://aws.amazon.com/rds/performance-insights
Our episode on wait events https://postgres.fm/episodes/wait-events
pg-flight-recorder https://github.com/dventimisupabase/pg-flight-recorder
pg_profile https://github.com/zubkov-andrei/pg_profile
pg_cron https://github.com/citusdata/pg_cron

~~~

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 credit to:

Jessie Draws for the elephant artwork

Comments and metadata

Nikolay Samokhvalov and Michael Christofides — Fri, 13 Feb 2026 14:00:00 +0000

Nik and Michael discuss query level comments, object level comments, and another way of adding object level metadata.

Here are some links to things they mentioned:

Object comments https://www.postgresql.org/docs/current/sql-comment.html
Query comment syntax (from an old version of the docs) https://www.postgresql.org/docs/7.0/syntax519.htm
SQL Comments, Please! (Post by Markus Winand) https://modern-sql.com/caniuse/comments
“While C-style block comments are passed to the server for processing and removal, SQL-standard comments are removed by psql.” https://www.postgresql.org/docs/current/app-psql.html
marginalia https://github.com/basecamp/marginalia
track_activity_query_size https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE
Custom Properties for Database Objects Using SECURITY LABELS (post by Andrei Lepikhov) https://www.pgedge.com/blog/custom-properties-for-postgresql-database-objects-without-core-patches

~~~

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 credit to:

Jessie Draws for the elephant artwork

PgDog update

Nikolay Samokhvalov and Michael Christofides — Fri, 23 Jan 2026 14:00:00 +0000

Nik and Michael are joined by Lev Kokotov for an update on all things PgDog.

Here are some links to things they mentioned:

Lev Kokotov https://postgres.fm/people/lev-kokotov
PgDog https://github.com/pgdogdev/pgdog
Our first PgDog episode (March 2025) https://postgres.fm/episodes/pgdog
Sharding pgvector (blog post by Lev) https://pgdog.dev/blog/sharding-pgvector
Prepared statements and partitioned table lock explosion (series by Nik) https://postgres.ai/blog/20251028-postgres-marathon-2-009

~~~

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 credit to:

Jessie Draws for the elephant artwork

RegreSQL

Nikolay Samokhvalov and Michael Christofides — Fri, 16 Jan 2026 14:00:00 +0000

Nik and Michael are joined by Radim Marek from boringSQL to talk about RegreSQL, a regression testing tool for SQL queries they forked and improved recently.

Here are some links to things they mentioned:

Radim Marek https://postgres.fm/people/radim-marek
boringSQL https://boringsql.com
RegreSQL: Regression Testing for PostgreSQL Queries (blog post by Radim) https://boringsql.com/posts/regresql-testing-queries
Discussion on Hacker News https://news.ycombinator.com/item?id=45924619
Radim’s fork of RegreSQL on GitHub https://github.com/boringSQL/regresql
Original RegreSQL on GitHub (by Dimitri Fontaine) https://github.com/dimitri/regresql
The Art of PostgreSQL (book) https://theartofpostgresql.com
How to make the non-production Postgres planner behave like in production (how-to post by Nik) https://postgres.ai/docs/postgres-howtos/performance-optimization/query-tuning/how-to-imitate-production-planner
Just because you’re getting an index scan, doesn't mean you can’t do better! (Blog post by Michael) https://www.pgmustard.com/blog/index-scan-doesnt-mean-its-fast
boringSQL Labs https://labs.boringsql.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 credit to:

Jessie Draws for the elephant artwork

Postgres year in review 2025

Nikolay Samokhvalov and Michael Christofides — Fri, 02 Jan 2026 14:00:00 +0000

Nik and Michael discuss the events and trends they thought were most important in the Postgres ecosystem in 2025.

Here are some links to things they mentioned:

Postgres 18 release notes https://www.postgresql.org/docs/18/release-18.html
Our episode on Postgres 18 https://postgres.fm/episodes/postgres-18
LWLock:LockManager benchmarks for Postgres 18 (blog post by Nik) https://postgres.ai/blog/20251009-postgres-marathon-2-005
PostgreSQL bug tied to zero-day attack on US Treasury https://www.theregister.com/2025/02/14/postgresql_bug_treasury
PgDog episode https://postgres.fm/episodes/pgdog
Multigres episode https://postgres.fm/episodes/multigres
Neki announcement https://planetscale.com/blog/announcing-neki
Our 100TB episode from 2024 https://postgres.fm/episodes/to-100tb-and-beyond
PlanetScale for Postgres https://planetscale.com/blog/planetscale-for-postgres
Oracle's MySQL job cuts https://www.theregister.com/2025/09/11/oracle_slammed_for_mysql_job
Amazon Aurora DSQL is now generally available https://aws.amazon.com/about-aws/whats-new/2025/05/amazon-aurora-dsql-generally-available
Announcing Azure HorizonDB https://techcommunity.microsoft.com/blog/adforpostgresql/announcing-azure-horizondb/4469710
Lessons from Replit and Tiger Data on Storage for Agentic Experimentation https://www.tigerdata.com/blog/lessons-replit-tiger-data-storage-agentic-experimentation
Instant database clones with PostgreSQL 18 https://boringsql.com/posts/instant-database-clones
turbopuffer episode https://postgres.fm/episodes/turbopuffer
Crunchy joins Snowflake https://www.crunchydata.com/blog/crunchy-data-joins-snowflake
Neon joins Databricks https://neon.com/blog/neon-and-databricks

~~~

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 credit to:

Jessie Draws for the elephant artwork

Archiving

Nikolay Samokhvalov and Michael Christofides — Fri, 19 Dec 2025 14:00:00 +0000

Nik and Michael discuss a listener question about archiving a database.

Here are some links to things they mentioned:

Listener request to talk about archiving https://www.youtube.com/watch?v=KFRK8PiIvTg&lc=UgyiFrO37gEgUaVhRgN4AaABAg
Our episode on “Is pg_dump a backup tool?” https://postgres.fm/episodes/is-pg_dump-a-backup-tool

~~~

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 credit to:

Jessie Draws for the elephant artwork

max_connections vs migrations

Nikolay Samokhvalov and Michael Christofides — Fri, 05 Dec 2025 14:00:00 +0000

Nik and Michael discuss max_connections, especially in the context of increasing it to solve problems like migrations intermittently failing(!)

Here are some links to things they mentioned:

max_connections https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS
Tweet about deployments vs connections issue https://x.com/brankopetric00/status/1991394329886077090
Nik tweet in response https://x.com/samokhvalov/status/1991465573684027443
Analyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) https://www.citusdata.com/blog/2020/10/08/analyzing-connection-scalability/
Exponential Backoff And Jitter (blog post by Marc Brooker) https://aws.amazon.com/blogs/architecture/exponential-backoff-and-jitter/

~~~

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 credit to:

Jessie Draws for the elephant artwork

What's new in EXPLAIN

Nikolay Samokhvalov and Michael Christofides — Fri, 21 Nov 2025 14:00:00 +0000

Nik and Michael discuss the various changes to EXPLAIN that arrived in Postgres 18.

Here are some links to things they mentioned:

EXPLAIN (official docs) https://www.postgresql.org/docs/current/sql-explain.html
Using EXPLAIN (official docs) https://www.postgresql.org/docs/current/using-explain.html
EXPLAIN glossary (pgMustard site) https://www.pgmustard.com/docs/explain
Postgres 18 release notes https://www.postgresql.org/docs/release/18.0/
Enable BUFFERS with EXPLAIN ANALYZE by default (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078eb
Our (first) BUFFERS by default episode https://postgres.fm/episodes/buffers-by-default
Show index search count in EXPLAIN ANALYZE (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=0fbceae841cb5a31b13d3f284ac8fdd19822eceb
Our episode on Skip scan with Peter Geoghegan https://postgres.fm/episodes/skip-scan
What do the new Index Searches lines in EXPLAIN mean? https://www.pgmustard.com/blog/what-do-index-searches-in-explain-mean
pg_stat_plans presentation by Lukas Fittl https://www.youtube.com/watch?v=26coQV3f-wk
Improve EXPLAIN's display of window functions (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=8b1b34254
Show Parallel Bitmap Heap Scan worker stats in EXPLAIN ANALYZE (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5a1e6df3b
Add information about WAL buffers being full (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=320545bfc

~~~

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 credit to:

Jessie Draws for the elephant artwork

Tens of TB per hour

Nikolay Samokhvalov and Michael Christofides — Fri, 14 Nov 2025 14:00:00 +0000

Nik talks Michael through a recent benchmark he worked with Maxim Boguk on, to see how quickly they could provision a replica.

Here are some links to things they mentioned:

Ultra-fast replica creation with pgBackRest (blog post by Maxim Boguk and Nik) https://postgres.ai/blog/20251105-postgres-marathon-2-012-ultra-fast-replica-creation-pgbackrest
Copying a database episode https://postgres.fm/episodes/copying-a-database
Add snapshot backup support for PostgreSQL in wal-g (draft PR by Andrey Borodin) https://github.com/wal-g/wal-g/pull/2101
Multi-threaded pg_basebackup discussion 1: https://www.postgresql.org/message-id/flat/CAEHH7R4%3D_GN%2BLSsj0YZOXZ13yc%3DGk9umJOLNopjS%3DimK0c1mWA%40mail.gmail.com
Multi-threaded pg_basebackup discussion 2: https://www.postgresql.org/message-id/flat/
io_method https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-IO-METHOD
pgBackRest https://github.com/pgbackrest/pgbackrest
Add sequence synchronization for logical replication (commit) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=5509055d6956745532e65ab218e15b99d87d66ce
Allow process priority to be set (pgBackRest feature added by David Steele) https://github.com/pgbackrest/pgbackrest/pull/2693
Hard limit on process-max (pgBackRest issue from 2019) https://github.com/pgbackrest/pgbackrest/issues/696

~~~

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 credit to:

Jessie Draws for the elephant artwork

Gapless sequences

Nikolay Samokhvalov and Michael Christofides — Fri, 31 Oct 2025 14:00:00 +0000

Nik and Michael discuss the concept of gapless sequences — when you might want one, why sequences in Postgres can have gaps, and an idea or two if you do want them.

And one quick clarification: changing the CACHE option in CREATE SEQUENCE can lead to even more gaps, the docs mention it explicitly.

Here are some links to things they mentioned:

CREATE SEQUENCE https://www.postgresql.org/docs/current/sql-createsequence.html
Sequence Manipulation Functions https://www.postgresql.org/docs/current/functions-sequence.html
One, Two, Skip a Few (post by Pete Hamilton from Incident io) https://incident.io/blog/one-two-skip-a-few
Postgres sequences can commit out-of-order (blog post by Anthony Accomazzo / Sequin) https://blog.sequinstream.com/postgres-sequences-can-commit-out-of-order
Logical Replication of sequences (hackers thread) https://www.postgresql.org/message-id/flat/CAA4eK1LC%2BKJiAkSrpE_NwvNdidw9F2os7GERUeSxSKv71gXysQ%40mail.gmail.com
Synchronization of sequences to subscriber (patch entry in commitfest) https://commitfest.postgresql.org/patch/5111/
Get or Create (episode with Haki Benita) https://postgres.fm/episodes/get-or-create
German tank problem https://en.wikipedia.org/wiki/German_tank_problem

~~~

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 credit to:

Jessie Draws for the elephant artwork

LWLocks

Nikolay Samokhvalov and Michael Christofides — Fri, 17 Oct 2025 14:00:00 +0100

Nik and Michael discuss lightweight locks in Postgres — how they differ to (heavier) locks, some occasions they can be troublesome, and some resources for working out what to do if you hit issues.

Here are some links to things they mentioned:

Wait Events of Type LWLock https://www.postgresql.org/docs/current/monitoring-stats.html#WAIT-EVENT-LWLOCK-TABLE
Our episode on (heavier) locks https://postgres.fm/episodes/locks
Nik’s new marathon posts https://postgres.ai/blog/tags/postgres-marathon
Postgres LISTEN/NOTIFY does not scale (blog post by Recall ai) https://www.recall.ai/blog/postgres-listen-notify-does-not-scale
Explicit Locking https://www.postgresql.org/docs/current/explicit-locking.html
pg_stat_activity https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ACTIVITY-VIEW
Tuning with wait events for RDS for PostgreSQL https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.html
MultiXact member exhaustion incidents (blog post by Cosmo Wolfe / Metronome) https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025
pg_index_pilot https://gitlab.com/postgres-ai/pg_index_pilot
Myths and Truths about Synchronous Replication in PostgreSQL (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=PFn9qRGzTMc
Postgres Indexes, Partitioning and LWLock:LockManager Scalability (blog post by Jeremy Schneider) https://ardentperf.com /2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability

~~~

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 credit to:

Jessie Draws for the elephant artwork

User management

Nikolay Samokhvalov and Michael Christofides — Fri, 03 Oct 2025 14:00:00 +0100

Nik and Michael discuss user management in Postgres — how roles work, making administration easier, setting passwords, and avoiding them being logged.

Here are some links to things they mentioned:

Roles https://www.postgresql.org/docs/current/user-manag.html
Privileges https://www.postgresql.org/docs/current/ddl-priv.html
ALTER DEFAULT PRIVILEGES https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html
GRANT https://www.postgresql.org/docs/current/sql-grant.html
REASSIGN OWNED https://www.postgresql.org/docs/current/sql-reassign-owned.html
ALTER ROLE (including SET) https://www.postgresql.org/docs/current/sql-alterrole.html
CREATE ROLE https://www.postgresql.org/docs/current/sql-createrole.html
Have I Been Pwned https://haveibeenpwned.com
Pwned Passwords API https://haveibeenpwned.com/API/v3#PwnedPasswords
Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/announcing-the-crunchy-data-postgresql-stig
Our episode on auditing https://postgres.fm/episodes/auditing

~~~

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 credit to:

Jessie Draws for the elephant artwork

Postgres 18

Nikolay Samokhvalov and Michael Christofides — Fri, 26 Sep 2025 14:00:00 +0100

Nik and Michael discuss the newly released Postgres 18 — the bigger things it includes, some of their personal highlights, and some thoughts towards the future.

Here are some links to things they mentioned:

Postgres 18 announcement https://www.postgresql.org/about/news/postgresql-18-released-3142
Postgres 18 release notes https://www.postgresql.org/docs/18/release-18.html
Skip scan episode with Peter Geoghegan https://postgres.fm/episodes/skip-scan
Easier Postgres fine-tuning with online_advisor https://neon.com/blog/easier-postgres-fine-tuning-with-online_advisor
pganalyze Index Advisor https://pganalyze.com/index-advisor
BUFFERS by default https://postgres.fm/episodes/buffers-by-default
Buffers II (the sequel) https://postgres.fm/episodes/buffers-ii-the-sequel
Return of the BUFFERS https://postgres.fm/episodes/return-of-the-buffers
UUID https://postgres.fm/episodes/uuid
Partitioning by ULID https://postgres.fm/episodes/partitioning-by-ulid
uuidv7 and uuid_extract_timestamp functions https://www.postgresql.org/docs/current/functions-uuid.html
Add --no-policies option to pg_dump, pg_dumpall, and pg_restore https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=cd3c45125
Add ONLY support for VACUUM and ANALYZE https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=62ddf7ee9
Make "vacuumdb --analyze-only" process partitioned tables (committed recently for Postgres 19) https://commitfest.postgresql.org/patch/5871/
NOT VALID constraints https://postgres.fm/episodes/not-valid-constraints
The year of the Lock Manager’s Revenge (post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability
Increase the number of fast-path lock slots https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=c4d5cb71d
"enable" parameters will work differently in Postgres 18 https://www.pgmustard.com/blog/enable-parameters-work-differently-in-postgres-18
logerrors https://github.com/munakoiso/logerrors

~~~

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 credit to:

Jessie Draws for the elephant artwork

Gadget's use of Postgres

Nikolay Samokhvalov and Michael Christofides — Fri, 19 Sep 2025 14:00:00 +0100

Nik and Michael are joined by Harry Brundage from Gadget to talk about their recent zero-downtime major version upgrade, how they use Postgres more generally, their dream database, and some challenges of providing Postgres as an abstracted service at scale.

Here are some links to things they mentioned:

Harry Brundage https://postgres.fm/people/harry-brundage
Gadget https://gadget.dev
Zero downtime Postgres upgrades using logical replication (blog post) https://gadget.dev/blog/zero-downtime-postgres-upgrades-using-logical-replication
HOT updates https://www.postgresql.org/docs/current/storage-hot.html
PgDog https://pgdog.dev
Multigres https://multigres.com
Neki https://www.neki.dev
Running 10 Million PostgreSQL Indexes In Production (Heap blog post) https://www.heap.io/blog/running-10-million-postgresql-indexes-in-production
pgwatch2 (Postgres.ai Edition) https://gitlab.com/postgres-ai/pgwatch2
Advanced query insights on AlloyDB https://cloud.google.com/alloydb/docs/advanced-query-insights-overview
OrioleDB https://www.orioledb.com
Not discussed but relevant: Gadget have also now published a blog post about their sharding! https://gadget.dev/blog/sharding-our-core-postgres-database-without-any-downtime

~~~

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 credit to:

Jessie Draws for the elephant artwork

turbopuffer

Nikolay Samokhvalov and Michael Christofides — Fri, 12 Sep 2025 14:00:00 +0100

Nik and Michael are joined by Simon Eskildsen from turbopuffer — among other things, they discuss ANN index types, tradeoffs that can make sense for search workloads, and when it can make sense to move search out of Postgres.

Here are some links to things they mentioned:

Simon Eskildsen https://postgres.fm/people/simon-eskildsen
turbopuffer https://turbopuffer.com
Use ULID Idempotency Keys (tip 6 in this blog post from Shopify) https://shopify.engineering/building-resilient-payment-systems
PostgreSQL 18 Release Candidate 1 https://www.postgresql.org/about/news/postgresql-18-rc-1-released-3130
Understanding DiskANN (blog post by Junaid Ahmed) https://www.tigerdata.com/blog/understanding-diskann
SPFresh: Incremental In-Place Update for Billion-Scale Vector Search (paper) https://arxiv.org/abs/2410.14452
Amazon S3 adds new functionality for conditional writes https://aws.amazon.com/about-aws/whats-new/2024/11/amazon-s3-functionality-conditional-writes
Amazon S3 Vectors https://aws.amazon.com/s3/features/vectors

~~~

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 credit to:

Jessie Draws for the elephant artwork

When not to use Postgres

Nikolay Samokhvalov and Michael Christofides — Fri, 05 Sep 2025 14:00:00 +0100

Nik and Michael discuss when not to use Postgres — specifically use cases where it still makes sense to store data in another system.

Here are some links to things they mentioned:

Just use Postgres (blog post by Ethan McCue) https://mccue.dev/pages/8-16-24-just-use-postgres
Just Use Postgres for Everything (blog post by Stephan Schmidt) https://www.amazingcto.com/postgres-for-everything
Real-time analytics episode https://postgres.fm/episodes/real-time-analytics
Crunchy Data Joins Snowflake https://www.crunchydata.com/blog/crunchy-data-joins-snowflake
Two sizes fit most: PostgreSQL and Clickhouse (blog post by Sid Sijbrandij) https://about.gitlab.com/blog/two-sizes-fit-most-postgresql-and-clickhouse
pg_duckdb episode https://postgres.fm/episodes/pg_duckdb
Cloudberry https://github.com/apache/cloudberry
Time-series considerations episode https://postgres.fm/episodes/time-series-considerations
Queues in Postgres episode https://postgres.fm/episodes/queues-in-postgres
Large Objects https://www.postgresql.org/docs/current/largeobjects.html
PGlite https://pglite.dev
ParadeDB https://www.paradedb.com
ZomboDB https://github.com/zombodb/zombodb
turbopuffer https://turbopuffer.com
HNSW vs. DiskANN (blog post by Haziqa Sajid) https://www.tigerdata.com/learn/hnsw-vs-diskann
SPANN: Highly-efficient Billion-scale Approximate Nearest Neighbor Search (paper) https://www.microsoft.com/en-us/research/wp-content/uploads/2021/11/SPANN_finalversion1.pdf
Amazon S3 Vectors https://aws.amazon.com/s3/features/vectors
Iterative Index Scans added to pgvector in 0.8.0 https://github.com/pgvector/pgvector/issues/678
S3 FDW from Supabase https://github.com/supabase/wrappers/tree/main/wrappers/src/fdw/s3_fdw

~~~

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 credit to:

Jessie Draws for the elephant artwork

Disks

Nikolay Samokhvalov and Michael Christofides — Fri, 29 Aug 2025 14:00:00 +0100

Nik and Michael discuss disks in relation to Postgres — why they matter, how saturation can happen, some modern nuances, and how to prepare to avoid issues.

Here are some links to things they mentioned:

Nik’s tweet demonstrating a NOTIFY hot spot https://x.com/samokhvalov/status/1959468091035009245
Postgres LISTEN/NOTIFY does not scale (blog post by Recall ai) https://www.recall.ai/blog/postgres-listen-notify-does-not-scale
track_io_timing https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-IO-TIMING
pg_test_timing https://www.postgresql.org/docs/current/pgtesttiming.html
PlanetScale for Postgres https://planetscale.com/blog/planetscale-for-postgres
Out of disk episode https://postgres.fm/episodes/out-of-disk
100TB episode https://postgres.fm/episodes/to-100tb-and-beyond
Latency Numbers Every Programmer Should Know https://gist.github.com/jboner/2841832
Fio https://github.com/axboe/fio

~~~

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 credit to:

Jessie Draws for the elephant artwork

Multi-column indexes

Nikolay Samokhvalov and Michael Christofides — Fri, 22 Aug 2025 14:00:00 +0100

Nik and Michael discuss multi-column indexes in Postgres — what they are, how to think about them, and some guidance around using them effectively.

Here are some links to things they mentioned:

Multicolumn Indexes (docs) https://www.postgresql.org/docs/current/indexes-multicolumn.html
Our episode on Index-only scans https://postgres.fm/episodes/index-only-scans
Combining Multiple Indexes (docs) https://www.postgresql.org/docs/current/indexes-bitmap-scans.html
Enable BUFFERS with EXPLAIN ANALYZE by default https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07
“PostgreSQL includes an implementation of the standard btree […] The only limitation is that an index entry cannot exceed approximately one-third of a page” https://www.postgresql.org/docs/current/btree.html
Our episode on HOT updates https://postgres.fm/episodes/hot-updates
Our episode on LIMIT vs Performance https://postgres.fm/episodes/limit-vs-performance

~~~

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 credit to:

Jessie Draws for the elephant artwork

Self-driving Postgres

Nikolay Samokhvalov and Michael Christofides — Fri, 15 Aug 2025 14:00:00 +0100

Nikolay and Michael discuss self-driving Postgres — what it could mean, using self-driving cars as a reference, and ideas for things to build and optimize for in this area.

Here are some links to things they mentioned:

Nikolay’s blog post on Self-driving Postgres https://postgres.ai/blog/20250725-self-driving-postgres
SAE J3016 levels of driving automation https://www.sae.org/news/2019/01/sae-updates-j3016-automated-driving-graphic
Oracle Autonomous Database https://www.oracle.com/uk/autonomous-database/
Self-Driving Database Management Systems (2017 paper) https://db.cs.cmu.edu/papers/2017/p42-pavlo-cidr17.pdf
PGTune https://pgtune.leopard.in.ua/
pg_index_pilot https://gitlab.com/postgres-ai/pg_index_pilot/
[Vibe] Hacking Postgres with Andrey, Kirk, Nik – index bloat, btree page merge https://www.youtube.com/watch?v=D1PEdDcvZTw

~~~

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 credit to:

Jessie Draws for the elephant artwork

caSe-inSENsiTive

Nikolay Samokhvalov and Michael Christofides — Fri, 08 Aug 2025 14:00:00 +0100

Nikolay and Michael discuss case-insensitive data — when we want to treat columns as case-insensitive, and the pros and cons of using citext, functions like lower(), or a custom collation.

Here are some links to things they mentioned:

citext https://www.postgresql.org/docs/current/citext.html
Our episode on over-indexing https://postgres.fm/episodes/over-indexing
Nondeterministic collations https://www.postgresql.org/docs/current/collation.html#COLLATION-NONDETERMINISTIC
How to migrate from Django’s PostgreSQL CI Fields to use a case-insensitive collation (blog post by Adam Johnson) https://adamj.eu/tech/2023/02/23/migrate-django-postgresql-ci-fields-case-insensitive-collation
The collation versioning problem with ICU 73 (blog post by Daniel Vérité) https://postgresql.verite.pro/blog/2023/10/20/icu-73-versioning.html
amcheck https://www.postgresql.org/docs/current/amcheck.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 credit to:

Jessie Draws for the elephant artwork

Monitoring from Postgres AI

Nikolay Samokhvalov and Michael Christofides — Fri, 25 Jul 2025 14:15:00 +0100

Nikolay talks to Michael about Postgres AI's new monitoring tool — what it is, how its different to other tools, and some of the thinking behind it.

Here are some links to things they mentioned:

postgres_ai monitoring https://gitlab.com/postgres-ai/postgres_ai
DB Lab 4.0 announcement https://github.com/postgres-ai/database-lab-engine/releases/tag/v4.0.0
pganalyze https://pganalyze.com
postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup
Percona Monitoring and Management (PMM) https://github.com/percona/pmm
pgwatch https://github.com/cybertec-postgresql/pgwatch
pgwatch Postgres AI Edition https://gitlab.com/postgres-ai/pgwatch2
libpg_query https://github.com/pganalyze/libpg_query
The Four Golden Signals https://sre.google/sre-book/monitoring-distributed-systems/#xref_monitoring_golden-signals
logerrors https://github.com/munakoiso/logerrors

~~~

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 credit to:

Jessie Draws for the elephant artwork

MultiXact member space exhaustion

Nikolay Samokhvalov and Michael Christofides — Fri, 18 Jul 2025 14:00:00 +0100

Nikolay and Michael are joined by Andrew Johnson and Nate Brennand from Metronome to discuss MultiXact member space exhaustion — what it is, how they managed to hit it, and some tips to prevent running into it at scale.

Here are some links to things they mentioned:

Nate Brennand https://postgres.fm/people/nate-brennand
Andrew Johnson https://postgres.fm/people/andrew-johnson
Metronome https://metronome.com
Root Cause Analysis: PostgreSQL MultiXact member exhaustion incidents (blog post by Metronome) https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025
Multixacts and Wraparound (docs) https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND
multixact.c source code https://github.com/postgres/postgres/blob/master/src/backend/access/transam/multixact.c
Add pg_stat_multixact view for multixact membership usage monitoring (patch proposal by Andrew, needing review!) https://commitfest.postgresql.org/patch/5869/
PostgreSQL subtransactions considered harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
vacuum_multixact_failsafe_age doesn't account for MultiXact member exhaustion (thread started by Peter Geoghegan) https://www.postgresql.org/message-id/flat/CAH2-WzmLPWJk3gbAxy8dHY%2BA-Juz_6uGwfe6DkE8B5-dTDvLcw%40mail.gmail.com
Amazon S3 Vectors https://aws.amazon.com/blogs/aws/introducing-amazon-s3-vectors-first-cloud-storage-with-native-vector-support-at-scale/
MultiXacts in PostgreSQL: usage, side effects, and monitoring (blog post by Shawn McCoy and Divya Sharma from AWS) https://aws.amazon.com/blogs/database/multixacts-in-postgresql-usage-side-effects-and-monitoring/
Postgres Aurora multixact monitoring queries https://gist.github.com/natebrennand/0924f723ff61fa897c4106379fc7f3dc
And finally an apology and a correction, the membership space is ~4B, not ~2B as said by Michael in the episode! Definition here:
https://github.com/postgres/postgres/blob/f6ffbeda00e08c4c8ac8cf72173f84157491bfde/src/include/access/multixact.h#L31

And here's the formula discussed for calculating how the member space can grow quadratically by the number of overlapping transactions:

Members can be calculated via: aₙ = 2 + [sum from k=3 to n+1 of k]
This simplifies to: aₙ = (((n+1)(n+2))/2) - 1

~~~

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

Multigres

Nikolay Samokhvalov and Michael Christofides — Fri, 11 Jul 2025 14:00:00 +0100

Nikolay and Michael are joined by Sugu Sougoumarane to discuss Multigres — a project he's joined Supabase to lead, building an adaptation of Vitess for Postgres!

Here are some links to things they mentioned:

Sugu Sougoumarane https://postgres.fm/people/sugu-sougoumarane
Supabase https://supabase.com
Announcing Multigres https://supabase.com/blog/multigres-vitess-for-postgres
Vitess https://github.com/vitessio/vitess
SPQR https://github.com/pg-sharding/spqr
Citus https://github.com/citusdata/citus
PgDog https://github.com/pgdogdev/pgdog
Myths and Truths about Synchronous Replication in PostgreSQL (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=PFn9qRGzTMc
Consensus algorithms at scale (8 part series by Sugu) https://planetscale.com/blog/consensus-algorithms-at-scale-part-1
A More Flexible Paxos (blog post by Sugu) https://www.sougou.io/a-more-flexible-paxos
libpg_query https://github.com/pganalyze/libpg_query
PL/Proxy https://github.com/plproxy/plproxy
PlanetScale Postgres Benchmarking https://planetscale.com/blog/benchmarking-postgres
MultiXact member exhaustion incidents (blog post by Cosmo Wolfe / Metronome) https://metronome.com/blog/root-cause-analysis-postgresql-multixact-member-exhaustion-incidents-may-2025

~~~

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

Multi-tenant options

Nikolay Samokhvalov and Michael Christofides — Fri, 20 Jun 2025 14:00:00 +0100

Nikolay and Michael are joined by Gwen Shapira to discuss multi-tenant architectures — the high level options, the pros and cons of each, and how they're trying to help with Nile.

Here are some links to things they mentioned:

Gwen Shapira https://postgres.fm/people/gwen-shapira
Nile https://www.thenile.dev
SaaS Tenant Isolation Strategies (AWS whitepaper) https://docs.aws.amazon.com/whitepapers/latest/saas-tenant-isolation-strategies/saas-tenant-isolation-strategies.html
Row Level Security https://www.postgresql.org/docs/current/ddl-rowsecurity.html
Citus https://github.com/citusdata/citus
Postgres.AI Bot https://postgres.ai/blog/20240127-postgres-ai-bot
RLS Performance and Best Practices https://supabase.com/docs/guides/troubleshooting/rls-performance-and-best-practices-Z5Jjwv
Case Gwen mentioned about the planner thinking an optimisation was unsafe
Re-engineering Postgres for Millions of Tenants (Gwen’s recent talk at PGConf.dev) https://www.youtube.com/watch?v=EfAStGb4s88
Multi-tenant database the good, the bad, the ugly (talk by Pierre Ducroquet at PgDay Paris) https://www.youtube.com/watch?v=4uxuPfSvTGU

~~~

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

Mean vs p99

Nikolay Samokhvalov and Michael Christofides — Fri, 13 Jun 2025 14:00:00 +0100

Nikolay and Michael discuss looking at queries by mean time — when it makes sense, why ordering by a percentile (like p99) might be better, and the merits of approximating percentiles in pg_stat_statements using the standard deviation column.

Here are some links to things they mentioned:

Approximate the p99 of a query with pg_stat_statements (blog post by Michael) https://www.pgmustard.com/blog/approximate-the-p99-of-a-query-with-pgstatstatements
pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
Our episode about track_planning https://postgres.fm/episodes/pg-stat-statements-track-planning
pg_stat_monitor https://github.com/percona/pg_stat_monitor
statement_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT

~~~

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 credit to:

Jessie Draws for the elephant artwork

What to log

Nikolay Samokhvalov and Michael Christofides — Fri, 06 Jun 2025 14:00:00 +0100

Nikolay and Michael discuss logging in Postgres — mostly what to log, and why changing quite a few settings can pay off big time in the long term.

Here are some links to things they mentioned:

What to log https://www.postgresql.org/docs/current/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
Our episode about Auditing https://postgres.fm/episodes/auditing
Our episode on auto_explain https://postgres.fm/episodes/auto_explain

Here are the parameters they mentioned changing:

log_checkpoints
log_autovacuum_min_duration
log_statement
log_connections and log_disconnections
log_lock_waits
log_temp_files
log_min_duration_statement
log_min_duration_sample and log_statement_sample_rate

And finally, some very useful tools they meant to mention but forgot to!

~~~

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 credit to:

Jessie Draws for the elephant artwork

How to move off RDS

Nikolay Samokhvalov and Michael Christofides — Fri, 30 May 2025 14:00:00 +0100

Nikolay and Michael discuss moving off managed services — when and why you might want to, and some tips on how for very large databases.

Here are some links to things they mentioned:

Patroni https://github.com/patroni/patroni
pgBackRest https://github.com/pgbackrest/pgbackrest
WAL-G https://github.com/wal-g/wal-g
Hetzner Cloud https://www.hetzner.com/cloud
Postgres Extensions Day https://pgext.day
pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
pg_stat_kcache https://github.com/powa-team/pg_stat_kcache
auto_explain https://www.postgresql.org/docs/current/auto-explain.html
Fivetran https://www.fivetran.com
pgcopydb https://github.com/dimitri/pgcopydb
Kafka https://kafka.apache.org
Debezium https://debezium.io
max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE
log_statement DDL https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-STATEMENT
PgBouncer pause/resume https://www.pgbouncer.org/usage.html#pause-db

~~~

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 credit to:

Jessie Draws for the elephant artwork

Locks

Nikolay Samokhvalov and Michael Christofides — Fri, 23 May 2025 14:00:00 +0100

Nikolay and Michael discuss heavyweight locks in Postgres — how to think about them, why you can't avoid them, and some tips for minimising issues.

Here are some links to things they mentioned:

Locking (docs) https://www.postgresql.org/docs/current/explicit-locking.html
Postgres rocks, except when it blocks (blog post by Marco Slot) https://www.citusdata.com/blog/2018/02/15/when-postgresql-blocks/
Lock Conflicts (tool by Hussein Nasser) https://pglocks.org/
log_lock_waits (docs) https://www.postgresql.org/docs/current/runtime-config-logging.html#GUC-LOG-LOCK-WAITS
How to analyze heavyweight lock trees (guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0042_how_to_analyze_heavyweight_locks_part_2.md
Lock management (docs) https://www.postgresql.org/docs/current/runtime-config-locks.html
Our episode on zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations

~~~

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 credit to:

Jessie Draws for the elephant artwork

Top ten dangerous issues

Nikolay Samokhvalov and Michael Christofides — Fri, 09 May 2025 14:00:00 +0100

Nikolay and Michael discuss ten dangerous Postgres related issues — ones that might be painful enough to get onto the CTO and even CEOs desk, and then what you can do proactively.

The ten issues discussed are:

Heavy lock contention
Bloat control and index maintenance
Lightweight lock contention
Transaction ID wraparound
4-byte integer PKs hitting the limit
Replication limits
Hard limits
Data loss
Poor HA choice (split brain)
Corruption of various kinds

Some previous episodes they mentioned that cover the issues in more detail:

PgDog https://postgres.fm/episodes/pgdog
Performance cliffs https://postgres.fm/episodes/performance-cliffs
Zero-downtime migrations https://postgres.fm/episodes/zero-downtime-migrations
Queues in Postgres https://postgres.fm/episodes/queues-in-postgres
Bloat https://postgres.fm/episodes/bloat
Index maintenance https://postgres.fm/episodes/index-maintenance
Subtransactions https://postgres.fm/episodes/subtransactions
Four million TPS https://postgres.fm/episodes/four-million-tps
Transaction ID wraparound https://postgres.fm/episodes/transaction-id-wraparound
pg_squeeze https://postgres.fm/episodes/pg_squeeze
synchronous_commit https://postgres.fm/episodes/synchronous_commit
Managed service support https://postgres.fm/episodes/managed-service-support

And finally, some other things they mentioned:

A great recent SQL Server-related podcast episode on tuning techniques https://kendralittle.com/2024/05/20/erik-darling-and-kendra-little-rate-sql-server-performance-tuning-techniques/
Postgres Indexes, Partitioning and LWLock:LockManager Scalability (blog post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability/
Do you vacuum everyday? (talk by Hannu Krosing) https://www.youtube.com/watch?v=JcRi8Z7rkPg
pg_stat_wal https://pgpedia.info/p/pg_stat_wal.html
The benefit of lz4 and zstd for Postgres WAL compression (Small Datum blog, Mark Callaghan) https://smalldatum.blogspot.com/2022/05/the-benefit-of-lz4-and-zstd-for.html
Split-brain in case of network partition (CloudNativePG issue/discussion) https://github.com/cloudnative-pg/cloudnative-pg/discussions/7462

~~~

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 credit to:

Jessie Draws for the elephant artwork

synchronous_commit

Nikolay Samokhvalov and Michael Christofides — Fri, 02 May 2025 14:00:00 +0100

Nikolay and Michael discuss synchronous_commit — what it means on single node setups, for synchronous replication setups, and the pros and cons of the different options for each.

Here are some links to things they mentioned:

synchronous_commit https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-SYNCHRONOUS-COMMIT
synchronous_commit history on pgPedia https://pgpedia.info/s/synchronous_commit.html
Patroni’s maximum_lag_on_failover setting https://patroni.readthedocs.io/en/master/replication_modes.html#asynchronous-mode-durability
wal_writer_delay https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-WRITER-DELAY
Selective asynchronous commits in PostgreSQL - balancing durability and performance (blog post by Shayon Mukherjee) https://www.shayon.dev/post/2025/75/selective-asynchronous-commits-in-postgresql-balancing-durability-and-performance/
Asynchronous Commit https://www.postgresql.org/docs/current/wal-async-commit.html
synchronous_standby_names https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-SYNCHRONOUS-STANDBY-NAMES
Jepson article about Amazon RDS multi-AZ clusters (by Kyle Kingsbury, aka "Aphyr”) https://jepsen.io/analyses/amazon-rds-for-postgresql-17.4

~~~

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 credit to:

Jessie Draws for the elephant artwork

Managed service support

Nikolay Samokhvalov and Michael Christofides — Fri, 25 Apr 2025 14:00:00 +0100

Nikolay and Michael discuss managed service support — some tips on how to handle cases that aren't going well, tips for requesting features, whether to factor in support when choosing service provider, and whether to use one at all.

Here are some links to things they mentioned:

YugabyteDB’s new upgrade framework https://www.yugabyte.com/blog/postgresql-upgrade-framework
Episode on Blue-green deployments https://postgres.fm/episodes/blue-green-deployments
pg_createsubscriber https://www.postgresql.org/docs/current/app-pgcreatesubscriber.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 credit to:

Jessie Draws for the elephant artwork

Time-series considerations

Nikolay Samokhvalov and Michael Christofides — Fri, 11 Apr 2025 14:00:00 +0100

Nikolay and Michael discuss time-series considerations for Postgres — including when it matters, some tips for avoiding issues, performance considerations, and more.

Here are some links to things they mentioned:

Time series data https://en.wikipedia.org/wiki/Time_series
TimescaleDB https://github.com/timescale/timescaledb
13 Tips to Improve PostgreSQL Insert Performance https://www.timescale.com/blog/13-tips-to-improve-postgresql-insert-performance
Why we're leaving the cloud (37 Signals / Basecamp / David Heinemeier Hansson) https://world.hey.com/dhh/why-we-re-leaving-the-cloud-654b47e0
UUID v7 and partitioning (“how to” by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.md
pg_cron https://github.com/citusdata/pg_cron
pg_partman https://github.com/pgpartman/pg_partman
Our episode on BRIN indexes https://postgres.fm/episodes/brin-indexes
Tutorial from Citus (Andres Freund and Marco Slot) including rollups https://www.youtube.com/watch?v=0ybz6zuXCPo
IoT with PostgreSQL (talk by Chris Ellis) https://youtube.com/watch?v=KnUoDBGv4aw&t=58
pg_timeseries https://github.com/tembo-io/pg_timeseries
DuckDB https://duckdb.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 credit to:

Jessie Draws for the elephant artwork

Performance cliffs

Nikolay Samokhvalov and Michael Christofides — Fri, 04 Apr 2025 14:00:00 +0100

Nikolay and Michael are joined by Tomas Vondra to discuss single query performance cliffs — what they are, why they happen, some things we can do to make them less likely or less severe, and some potential improvements to Postgres that could help.

Here are some links to things they mentioned:

Tomas Vondra https://postgres.fm/people/tomas-vondra
Where do performance cliffs come from? (Talk by Tomas) https://www.youtube.com/watch?v=UzdAelm-QSY
Where do performance cliffs come from? (Slides) https://vondra.me/pdf/performance-cliffs-posette-2024.pdf
Increase the number of fast-path lock slots (committed for Postgres 18) https://www.postgresql.org/message-id/flat/E1ss4gX-000IvX-63%40gemulon.postgresql.org
San Francisco Bay Area Postgres meet-up with Tomas on 8th April (online) https://www.meetup.com/postgresql-1/events/306484787
Our episode on Extended Statistics https://postgres.fm/episodes/extended-statistics
Logging plan of the currently running query (proposed patch by Rafael Thofehrn Castro and Atsushi Torikoshi) https://commitfest.postgresql.org/patch/5330
Our episode with Peter Geoghegan on Skip Scan https://postgres.fm/episodes/skip-scan
Index Prefetching patch that Tomas is collaborating with Peter Geoghegan on https://commitfest.postgresql.org/patch/4351
A generalized join algorithm, G-Join (paper by Goetz Graefe) https://dl.gi.de/server/api/core/bitstreams/ce8e3fab-0bac-45fc-a6d4-66edaa52d574/content
Smooth Scan: Robust Access Path Selection without Cardinality Estimation (paper by R. Borovica, S. Idreos, A. Ailamaki, M. Zukowski, C. Fraser) https://stratos.seas.harvard.edu/sites/g/files/omnuum4611/files/stratos/files/smoothscan.pdf
Just-in-Time Compilation (JIT) https://www.postgresql.org/docs/current/jit.html
Notes from a pgconf.dev unconference session in 2024 about JIT (discusses issues) https://wiki.postgresql.org/wiki/PGConf.dev_2024_Developer_Unconference#JIT_compilation
Implementing an alternative JIT provider for PostgreSQL (by Xing Guo) https://higuoxing.com/archives/implementing-jit-provider-for-pgsql
Tomas’ Office Hours https://vondra.me/posts/office-hours-experiment

~~~

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

PgDog

Nikolay Samokhvalov and Michael Christofides — Fri, 28 Mar 2025 14:00:00 +0000

Nikolay and Michael are joined by Lev Kokotov to discuss PgDog — including whether or when sharding is needed, the origin story (via PgCat), what's already supported, and what's coming next.

Here are some links to things they mentioned:

Lev Kokotov https://postgres.fm/people/lev-kokotov
PgDog https://github.com/pgdogdev/pgdog
PgCat https://github.com/postgresml/pgcat
Adopting PgCat (Instacart blog post) https://www.instacart.com/company/how-its-made/adopting-pgcat-a-nextgen-postgres-proxy
PgDog discussion on Hacker News https://news.ycombinator.com/item?id=43364668
Citus https://github.com/citusdata/citus
Sharding & IDs at Instagram (blog post) https://instagram-engineering.com/sharding-ids-at-instagram-1cf5a71e5a5c
Sharding pgvector (blog post by Lev) https://pgdog.dev/blog/sharding-pgvector

~~~

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

Snapshots

Nikolay Samokhvalov and Michael Christofides — Fri, 21 Mar 2025 14:00:00 +0000

Nikolay talks Michael through using cloud snapshots — how they can be used to reduce RTO for huge Postgres setups, also to improve provisioning time, and some major catches to be aware of.

Here are some links to things they mentioned:

Snapshots on RDS https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html
pgBackRest https://pgbackrest.org
WAL-G https://github.com/wal-g/wal-g
pg_backup_start and pg_backup_stop (docs) https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-BACKUP
How to troubleshoot long Postgres startup (by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0003_how_to_troubleshoot_long_startup.md
Restoring to a DB instance (RDS docs mentioning lazy loading) https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html
Amazon EBS fast snapshot restore https://docs.aws.amazon.com/ebs/latest/userguide/ebs-fast-snapshot-restore.html
Our 100th episode “To 100TB, and beyond!” https://postgres.fm/episodes/to-100tb-and-beyond

~~~

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 credit to:

Jessie Draws for the elephant artwork

GIN Indexes

Nikolay Samokhvalov and Michael Christofides — Fri, 14 Mar 2025 14:00:00 +0000

Nikolay and Michael discuss GIN indexes in Postgres — what they are, what they're used for, and some limitations to be aware of.

Here are some links to things they mentioned:

GIN Indexes https://www.postgresql.org/docs/current/gin.html
Generalized Search Trees for Database Systems (Hellerstein, Naughton, Pfeffer) https://dsf.berkeley.edu/papers/vldb95-gist.pdf
RUM extension https://pgxn.org/dist/rum/1.1.0/
Understanding Postgres GIN Indexes: The Good and the Bad (Lukas Fittl) https://pganalyze.com/blog/gin-index

~~~

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 credit to:

Jessie Draws for the elephant artwork

Best Practices

Nikolay Samokhvalov and Michael Christofides — Fri, 07 Mar 2025 14:00:00 +0000

Nikolay and Michael use a recent "best practices" article as a prompt — giving a few tips each on the topics mentioned, like schema design, performance, backups, and more.

Here are some links to things they mentioned:

7 Crucial PostgreSQL Best Practices (recent blog post) https://speakdatascience.com/postgresql-best-practices
“Don't do this” episode https://postgres.fm/episodes/dont-do-this
Article discussion on Hacker News https://news.ycombinator.com/item?id=42992913
Mozilla’s SQL Style Guide https://docs.telemetry.mozilla.org/concepts/sql_style
“SQL vs NoSQL” episode with Franck Pachot https://postgres.fm/episodes/sql-vs-nosql
HA episode https://postgres.fm/episodes/high-availability

~~~

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 credit to:

Jessie Draws for the elephant artwork

Extended Statistics

Nikolay Samokhvalov and Michael Christofides — Fri, 28 Feb 2025 14:00:00 +0000

Nikolay and Michael discuss the CREATE STATISTICS feature in Postgres — what it's for, how often it's used, and how to spot cases where it would help.

Here are some links to things they mentioned:

CREATE STATISTICS https://www.postgresql.org/docs/current/sql-createstatistics.html
citext https://www.postgresql.org/docs/current/citext.html
Statistics Used by the Planner https://www.postgresql.org/docs/current/planner-stats.html
default_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET
Tomáš Vondra on Postgres TV https://www.youtube.com/watch?v=8la-OWfD3VI
Recent commit to Postgres 18 for pg_upgrade https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=1fd1bd871012732e3c6c482667d2f2c56f1a9395
Multivariate Statistics Examples https://www.postgresql.org/docs/current/multivariate-statistics-examples.html
Extended statistics (README) https://github.com/postgres/postgres/blob/master/src/backend/statistics/README
How we used Postgres extended statistics to achieve a 3000x speedup (blog post by Jared Rulison) https://medium.com/affinity-engineering/how-we-used-postgres-extended-statistics-to-achieve-a-3000x-speedup-ea93d3dcdc61

~~~

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 credit to:

Jessie Draws for the elephant artwork

SQL vs NoSQL

Nikolay Samokhvalov and Michael Christofides — Fri, 14 Feb 2025 14:30:00 +0000

Nikolay and Michael are joined by Franck Pachot to discuss SQL vs NoSQL — did Franck change teams by joining MongoDB, normalisation vs denormalisation, developer experience, NULLs, and more!

Here are some links to things they mentioned:

Franck Pachot https://postgres.fm/people/franck-pachot
Franck's workshop at PGConf India https://pgconf.in/conferences/pgconfin2025/program/proposals/958
PostgreSQL Conference Germany https://2025.pgconf.de
"Schema Later" Considered Harmful by Michael Stonebraker and Álvaro Hernández https://www.enterprisedb.com/blog/schema-later-considered-harmful
Comparison of JOINS by Michael Stonebraker and Álvaro Hernández https://www.enterprisedb.com/blog/comparison-joins-mongodb-vs-postgresql
Franck’s post about why he joined MongoDB https://www.linkedin.com/pulse/2025-im-joining-mongodb-franck-pachot-e4shf
EdgeDB https://www.edgedb.com
Nikolay’s tweet about a recent issue with NULLs https://x.com/samokhvalov/status/1889078097124999272
PartiQL https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.html
FerretDB https://www.ferretdb.com
DocumentDB https://github.com/microsoft/documentdb

~~~

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

Return of the BUFFERS

Nikolay Samokhvalov and Michael Christofides — Fri, 07 Feb 2025 14:00:00 +0000

Nikolay and Michael return to the topic of BUFFERS for the third (and final?) time! They discuss the news that it'll be on by default with EXPLAIN ANALYZE in Postgres 18, and what effect that might have.

Here are some links to things they mentioned:

Our first BUFFERS episode https://postgres.fm/episodes/buffers-by-default
Our second BUFFERS episode https://postgres.fm/episodes/buffers-ii-the-sequel
BUFFERS enabled for EXPLAIN ANALYZE by default (commit for Postgres 18) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=c2a4078ebad71999dd451ae7d4358be3c9290b07
https://explain.depesz.com/history
Latency Numbers Every Programmer Should Know https://gist.github.com/jboner/2841832
Twitter conversation about flushing caches https://x.com/prochazkafilip/status/1881655337499205736
pg_buffercache_evict https://www.postgresql.org/docs/current/pgbuffercache.html#PGBUFFERCACHE-PG-BUFFERCACHE-EVICT
How to compare the quality of SQL query plans (blog post by Andrei Lepikhov) https://danolivo.substack.com/p/whose-optimisation-is-better
We were added as PostgreSQL Contributors! https://www.postgresql.org/about/news/new-postgresql-contributors-3006

~~~

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

Reads causing writes

Nikolay Samokhvalov and Michael Christofides — Fri, 24 Jan 2025 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 17 Jan 2025 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 10 Jan 2025 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 03 Jan 2025 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 20 Dec 2024 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 13 Dec 2024 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 06 Dec 2024 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 29 Nov 2024 14:00:00 +0000

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?

Nikolay Samokhvalov and Michael Christofides — Fri, 22 Nov 2024 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 15 Nov 2024 14:00:00 +0000

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

Denormalization

Nikolay Samokhvalov and Michael Christofides — Fri, 08 Nov 2024 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 01 Nov 2024 14:00:00 +0000

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

Nikolay Samokhvalov and Michael Christofides — Fri, 25 Oct 2024 14:00:00 +0100

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

Nikolay Samokhvalov and Michael Christofides — Fri, 18 Oct 2024 14:00:00 +0100

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

Nikolay Samokhvalov and Michael Christofides — Fri, 11 Oct 2024 14:00:00 +0100

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

Nikolay Samokhvalov and Michael Christofides — Fri, 04 Oct 2024 14:00:00 +0100

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

Nikolay Samokhvalov and Michael Christofides — Fri, 27 Sep 2024 14:00:00 +0100

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

Nikolay Samokhvalov and Michael Christofides — Fri, 20 Sep 2024 14:00:00 +0100

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

Nikolay Samokhvalov and Michael Christofides — Fri, 13 Sep 2024 14:00:00 +0100

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

Nikolay Samokhvalov and Michael Christofides — Fri, 06 Sep 2024 14:00:00 +0100

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

Postgres Emergency Room

Nikolay Samokhvalov and Michael Christofides — Fri, 30 Aug 2024 14:00:00 +0100

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

Get or Create

Nikolay Samokhvalov and Michael Christofides — Fri, 23 Aug 2024 14:00:00 +0100

Michael and Nikolay are joined by Haki Benita, a technical lead and database enthusiast who writes an excellent blog and gives popular talks and training sessions too, to discuss the surprisingly complex topic of trying to implement “get or create” in PostgreSQL — handling issues around idempotency, concurrency, and bloat.

Here are some links to things they mentioned:

Haki Benita https://hakibenita.com
How to Get or Create in PostgreSQL (blog post by Haki) https://hakibenita.com/postgresql-get-or-create
"Find-or-insert" using a single query (how-to guide by Nikolay) https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0036_find-or-insert_using_a_single_query.md?ref_type=heads
Is SELECT or INSERT in a function prone to race conditions? (Answer by Erwin Brandstetter) https://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions/15950324#15950324
get_or_create() in Django https://docs.djangoproject.com/en/5.1/ref/models/querysets/#get-or-create
Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
MERGE (Postgres documentation) https://www.postgresql.org/docs/current/sql-merge.html
Hidden dangers of duplicate key violations in PostgreSQL and how to avoid them (blog post by Divya Sharma and Shawn McCoy from the RDS team) https://aws.amazon.com/blogs/database/hidden-dangers-of-duplicate-key-violations-in-postgresql-and-how-to-avoid-them/
One, Two, Skip a Few... (blog post by Pete Hamilton from Incident) https://incident.io/blog/one-two-skip-a-few

~~~

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

Getting started with benchmarking

Nikolay Samokhvalov and Michael Christofides — Fri, 16 Aug 2024 14:00:00 +0100

Michael and Nikolay are joined by Melanie Plageman, database internals engineer at Microsoft and major contributor and committer to PostgreSQL, to discuss getting started with benchmarking — how it differs for users and developers of Postgres, how and when it comes up during development, some tools and lessons, as well as what she's working on at the moment.

Here are some links to things they mentioned:

Melanie Plageman https://postgres.fm/people/melanie-plageman
Meanie’s Introduction to Benchmarking With pgbench talk slides https://postgresql.us/events/pgconfnyc2023/schedule/session/1410-introduction-to-benchmarking-with-pgbench/#slides
Melanie’s Visualizing Postgres I/O Performance for Development talk recording https://www.youtube.com/watch?v=CxyPZHG5beI
Melanie’s Visualizing Postgres I/O Performance for Development talk slides https://speakerdeck.com/melanieplageman/o-performance-for-development
pgbench https://www.postgresql.org/docs/current/pgbench.html
Mark Callaghan’s blog https://smalldatum.blogspot.com
Some of Tomas Vondra’s blog posts https://www.enterprisedb.com/blog/author/tomas-vondra
Some of Andres Freund’s blog posts https://www.citusdata.com/blog/authors/andres-freund/
An example of Alexander Lakhin’s work https://www.postgresql.org/message-id/b32bed1b-0746-9b20-1472-4bdc9ca66d52%40gmail.com
Simplifying the TPC Benchmark C, an OLTP Workload (talk by Mark Wong) https://www.youtube.com/watch?v=qi0I74urLoY
Matplotlib https://matplotlib.org
pandas https://pandas.pydata.org
pg_stat_io https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW
pg_stat_io commit https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a9c70b46dbe152e094f137f7e6ba9cd3a638ee25
pg_buffercache https://www.postgresql.org/docs/current/pgbuffercache.html
tmpfs https://docs.kernel.org/filesystems/tmpfs.html
Eager page freeze criteria mailing list thread https://www.postgresql.org/message-id/CAAKRu_b3tpbdRPUPh1Q5h35gXhY%3DspH2ssNsEsJ9sDfw6%3DPEAg%40mail.gmail.com
The path to using AIO in postgres (talk by Andres Freund) https://www.youtube.com/watch?v=qX50xrHwQa4
Improve dead tuple storage for lazy vacuum (Masahiko Sawada) https://www.postgresql.org/message-id/flat/CAD21AoAfOZvmfR0j8VmZorZjL7RhTiQdVttNuC4W-Shdc2a-AA@mail.gmail.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

Index-Only Scans

Nikolay Samokhvalov and Michael Christofides — Fri, 09 Aug 2024 14:00:00 +0100

Nikolay and Michael discuss Index-Only Scans in Postgres — what they are, how they help, some things to look out for, and some advice.

Here are some links to things they mentioned:

Index-Only Scans and Covering Indexes (docs) https://www.postgresql.org/docs/current/indexes-index-only-scans.html
Discussion on Twitter about JIT and Parallel Query defaults https://x.com/jer_s/status/1819749688184373742
Postgres Wiki on Index-Only Scans https://wiki.postgresql.org/wiki/Index-only_scans
Heap Fetches https://www.pgmustard.com/docs/explain/heap-fetches
Rows Removed By Filter https://www.pgmustard.com/docs/explain/rows-removed-by-filter
random_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/
pg_dump docs change https://x.com/samokhvalov/status/1820539826363588755
Crunchy Bridge changed random_page_cost to 1.1 for new servers https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1
Autovacuum Tuning Basics (updated blog post by Tomas Vondra) https://www.enterprisedb.com/blog/autovacuum-tuning-basics
Our episode on over-indexing https://postgres.fm/episodes/over-indexing
Our episode on HOT updates https://postgres.fm/episodes/hot-updates
Our episode on partitioning https://postgres.fm/episodes/partitioning

~~~

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

Why Postgres?

Nikolay Samokhvalov and Michael Christofides — Fri, 02 Aug 2024 14:00:00 +0100

Nikolay and Michael discuss why they chose Postgres — as users, for their businesses, for their careers, as well as some doubts.

Here are some links to things they mentioned:

Our episode on why Postgres become popular https://postgres.fm/episodes/why-is-postgres-popular
Database Systems: The Complete Book (by Hector Garcia-Molina, Jeff Ullman, and Jennifer Widom) http://infolab.stanford.edu/~ullman/dscb.html
Our episode on the Postgres startup ecosystem https://postgres.fm/episodes/postgres-startup-ecosystem
Will Postgres Live Forever? (talk by Bruce Momjian) https://www.youtube.com/watch?v=iYVxWpyaGpA
"Anarcho-Syndicalist Commune" from Monty Python and the Holy Grail https://www.youtube.com/watch?v=R7qT-C-0ajI
The Cathedral and the Bazaar: Musings on Linux and Open Source by an Accidental Revolutionary (book by Eric S. Raymond) https://en.wikipedia.org/wiki/The_Cathedral_and_the_Bazaar

~~~

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

Compression

Nikolay Samokhvalov and Michael Christofides — Fri, 26 Jul 2024 14:00:00 +0100

Nikolay and Michael discuss compression in Postgres — what's available natively, newer algorithms in recent versions, and several extensions with compression features.

Here are some links to things they mentioned:

wal_compression https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-COMPRESSION
Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Synthetic wal_compression and streaming replication wal size test https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/11
default_toast_compression https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-DEFAULT-TOAST-COMPRESSION
ZFS https://en.wikipedia.org/wiki/ZFS
Our episode on TOAST https://postgres.fm/episodes/toast
On compression of everything in Postgres (talk by Andrey Borodin) https://learn.microsoft.com/en-us/shows/cituscon-an-event-for-postgres-2023/on-compression-of-everything-in-postgres-citus-con-2023
cstore_fdw https://citusdata.github.io/cstore_fdw/
Using Hydra columnar https://columnar.docs.hydra.so/concepts/using-hydra-columnar
About compression in Timescale https://docs.timescale.com/use-timescale/latest/compression/about-compression/
pg_tier https://github.com/tembo-io/pg_tier
pgBackRest https://pgbackrest.org/
WAL-G https://github.com/wal-g/wal-g
pg_dump https://www.postgresql.org/docs/current/app-pgdump.html
pg_dump compression specifications in PostgreSQL 16 (article by Pablo Glob from Cybertec) https://www.cybertec-postgresql.com/en/pg_dump-compression-specifications-postgresql-16/
Our episode on pgvector (with Jonathan Katz) https://postgres.fm/episodes/pgvector

~~~

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

Out of disk

Nikolay Samokhvalov and Michael Christofides — Fri, 19 Jul 2024 14:00:00 +0100

Nikolay and Michael discuss Postgres running out of disk space — including what happens, what can cause it, how to recover, and most importantly, how to prevent it from happening in the first place.

Here are some links to things they mentioned:

Disk Full (docs) https://www.postgresql.org/docs/current/disk-full.html
pgcompacttable https://github.com/dataegret/pgcompacttable
Our episode on massive deletes https://postgres.fm/episodes/massive-deletes
Getting Rid of Data (slides from VLDB 2019 keynote by Tova Milo)
pg_tier https://github.com/tembo-io/pg_tier
Data tiering in Timescale Cloud https://docs.timescale.com/use-timescale/latest/data-tiering/
Postgres is Out of Disk and How to Recover (blog post by Elizabeth Christensen) https://www.crunchydata.com/blog/postgres-is-out-of-disk-and-how-to-recover-the-dos-and-donts
max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE
Our episode on checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Aiven docs on full disk issues https://aiven.io/docs/products/postgresql/howto/prevent-full-disk

~~~

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

Nikolay Samokhvalov and Michael Christofides — Fri, 12 Jul 2024 14:00:00 +0100

Nikolay and Michael discuss the Postgres startup ecosystem — some recent closures, some recent fundraising announcements, and their thoughts on where things are going and what they'd like to see.

Here are some links to things they mentioned:

Prediction from Dax Raad https://x.com/thdxr/status/1808972166752580039
OtterTune shut down https://x.com/andy_pavlo/status/1801687420330770841
Snaplet shutting down https://www.snaplet.dev/post/snaplet-is-shutting-down
bit.io shut down https://blog.bit.io/whats-next-for-bit-io-joining-databricks-ace9a40bce0d?gi=8ef885454eef
Timescale acquired PopSQL https://www.timescale.com/blog/best-postgresql-gui-popsql-joins-timescale
Aiven https://aiven.io
Hasura https://hasura.io
Supabase https://supabase.com
Neon https://neon.tech
Tembo https://tembo.io
FerretDB https://www.ferretdb.com
Hydra https://www.hydra.so
pgEdge https://www.pgedge.com
Tembo raised $14m https://techcrunch.com/2024/07/08/database-startup-tembo-lands-new-cash-to-expand
Ry Walker’s Cybertruck with STARTUP license plate https://x.com/rywalker/status/1810061804380557516
Supabase acquired OrioleDB https://supabase.com/blog/supabase-acquires-oriole
Michael Stonebraker Turing Award Lecture https://www.youtube.com/watch?v=BbGeKi6T6QI
Microsoft acquired Citus https://blogs.microsoft.com/blog/2019/01/24/microsoft-acquires-citus-data-re-affirming-its-commitment-to-open-source-and-accelerating-azure-postgresql-performance-and-scale
Crunchy Bridge https://www.crunchydata.com/products/crunchy-bridge
PeerDB https://www.peerdb.io
ParadeDB https://www.paradedb.com
pganalyze https://pganalyze.com
DBeaver https://dbeaver.io
Postico / Egger Apps https://eggerapps.at/postico2
Postgres Compare https://www.postgrescompare.com
Coroot https://coroot.com
okmeter https://okmeter.io
Slides from Nikolay’s talk on monitoring https://bit.ly/pg-monitoring
Nile https://www.thenile.dev
Ubicloud https://www.ubicloud.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

Four million TPS

Nikolay Samokhvalov and Michael Christofides — Fri, 05 Jul 2024 14:00:00 +0100

Nikolay talks Michael through a recent experiment to find the current maximum transactions per second single-node Postgres can achieve — why he was looking into it, what bottlenecks occurred along the way, and ideas for follow up experiments.

Here are some links to things they mentioned:

How many TPS can we get from a single Postgres node? (Article by Nikolay) https://www.linkedin.com/pulse/how-many-tps-can-we-get-from-single-postgres-node-nikolay-samokhvalov-yu0rc
Chat history with Postgres AI bot https://postgres.ai/chats/01905a83-4573-7dca-b47a-bb60ce30fe6c
Our episode on the overhead of pg_stat_statements and pg_stat_kcache https://postgres.fm/episodes/overhead-of-pg_stat_statements-and-pg_stat_kcache
PostgreSQL 17 beta 2 is out https://www.postgresql.org/about/news/postgresql-17-beta-2-released-2885/
PostgreSQL and MySQL: Millions of Queries per Second (about the work by Sveta and Alexander) https://www.percona.com/blog/millions-queries-per-second-postgresql-and-mysql-peaceful-battle-at-modern-demanding-workloads
postgresql_cluster https://github.com/vitabaks/postgresql_cluster
Track on CPU events for pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling/pull/74
The year of the Lock Manager’s Revenge (post by Jeremy Schneider) https://ardentperf.com/2024/03/03/postgres-indexes-partitioning-and-lwlocklockmanager-scalability
Pluggable cumulative statistics (Postgres hackers thread started by Michael Paquier) https://www.postgresql.org/message-id/flat/Zmqm9j5EO0I4W8dx%40paquier.xyz

~~~

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

Soft delete

Nikolay Samokhvalov and Michael Christofides — Fri, 28 Jun 2024 14:00:00 +0100

Nikolay and Michael discuss soft deletion in Postgres — what it means, several use cases, some implementation options, and which implementations suit which use cases.

Here are some links to things they mentioned:

Soft deletion probably isn't worth it (blog post by Brandur) https://brandur.org/soft-deletion
Easy alternative soft deletion (blog post by Brandur) https://brandur.org/fragments/deleted-record-insert
Our episode on auditing https://postgres.fm/episodes/auditing
CREATE FUNCTION … SECURITY DEFINER (docs) https://www.postgresql.org/docs/current/sql-createfunction.html
Principle of least privilege https://en.wikipedia.org/wiki/Principle_of_least_privilege

~~~

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

Should we use foreign keys?

Nikolay Samokhvalov and Michael Christofides — Fri, 21 Jun 2024 14:00:00 +0100

Nikolay and Michael discuss foreign keys in Postgres — what they are, their benefits, their overhead, some edge cases to be aware of, some improvements coming, and whether or not they generally recommend using them.

Here are some links to things they mentioned:

Foreign keys (docs) https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK
Our episode about constraints: https://postgres.fm/episodes/constraints
GitLab migration helper add_concurrent_foreign_key https://github.com/gitlabhq/gitlabhq/blob/master/rubocop/cop/migration/add_concurrent_foreign_key.rb
Adding a foreign key without downtime (tweet by Nikolay) https://x.com/samokhvalov/status/1732056107483636188
Bloat, pg_repack, and deferred constraints (blog post by Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec
Postgres 17 draft release notes, server configuration section https://www.postgresql.org/docs/17/release-17.html#RELEASE-17-SERVER-CONFIG
Our 100th episode https://postgres.fm/episodes/to-100tb-and-beyond
Stop! Trigger Time (blog post by Michael) https://www.pgmustard.com/blog/trigger-time
Should I Create an Index on Foreign Keys? (Blog post by Percona) https://www.percona.com/blog/should-i-create-an-index-on-foreign-keys-in-postgresql
Avoid Postgres performance cliffs with MultiXact IDs and foreign keys (5 min video by Lukas Fittl) https://pganalyze.com/blog/5mins-postgres-multiXact-ids-foreign-keys-performance
Experiment to see basic overhead of foreign keys https://v2.postgres.ai/chats/01902ee6-8ed1-70ec-9345-5606305012f4
Experiment showing an extreme contention case https://v2.postgres.ai/chats/018fb28d-865f-788d-adb7-efa7ed3a48c4
Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
Notes on some PostgreSQL implementation details (blog post by Nelson Elhage that mentions “subtransactions are cursed”) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details

~~~

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

pgvectorscale

Nikolay Samokhvalov and Michael Christofides — Fri, 14 Jun 2024 22:01:18 +0100

Nikolay is joined by Mat Arye and John Pruitt, from Timescale, to discuss their new extension pgvectorscale and high-performance vector search in Postgres more generally.

Main links:

~~~

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:

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

With special thanks to:

Jessie Draws for the elephant artwork

To 100TB, and beyond!

Nikolay Samokhvalov and Michael Christofides — Fri, 07 Jun 2024 14:00:00 +0100

Michael and Nikolay are joined by three special guests for episode 100 who have all scaled Postgres to significant scale — Arka Ganguli from Notion, Sammy Steele from Figma, and Derk van Veen from Adyen. They cover how their setup has evolved, what their plans are for the future, and get into the weeds of some fun and interesting challenges along the way!

Links to some of the things discussed:

Arka Ganguli from Notion https://postgres.fm/people/arka-ganguli
Sammy Steele from Figma https://postgres.fm/people/sammy-steele
Derk van Veen from Adyen https://postgres.fm/people/derk-van-veen
Thank you to yerrysherry on Reddit for the idea! https://www.reddit.com/r/PostgreSQL/comments/1cn8ajh/what_should_we_do_for_episode_100_of_postgres_fm
Lessons learned from sharding Postgres at Notion (October 2021) https://www.notion.so/blog/sharding-postgres-at-notion
Adding Postgres capacity (again) with zero downtime (July 2023) https://www.notion.so/blog/the-great-re-shard
How Figma’s databases team lived to tell the scale (March 2024) https://www.figma.com/blog/how-figmas-databases-team-lived-to-tell-the-scale
Updating a 50 terabyte PostgreSQL database (March 2018) https://www.adyen.com/knowledge-hub/updating-a-50-terabyte-postgresql-database
Part 1: Introduction to Table Partitioning (July 2023) https://www.adyen.com/knowledge-hub/introduction-to-table-partioning
Part 2: Partitioning at Adyen (October 2023) https://www.adyen.com/knowledge-hub/partitioning-at-adyen
Part 3: Maintenance Under Pressure (January 2024) https://www.adyen.com/knowledge-hub/maintenance-under-pressure
Fighting PostgreSQL write amplification with HOT updates (May 2022) https://www.adyen.com/knowledge-hub/postgresql-hot-updates
Tracking HOT updates and tuning FillFactor (October 2022) https://www.adyen.com/knowledge-hub/postgresql-hot-updates-part2
Partitioning your Postgres tables for 20X better performance (upcoming talk by Derk at Posette) https://www.citusdata.com/posette/speakers/derk-van-veen
Figma, including Sammy’s team, is hiring https://www.figma.com/careers
Notion’s engineering team is hiring https://www.notion.so/careers
Adyen’s engineering team is hiring https://careers.adyen.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

Sponsoring the community

Nikolay Samokhvalov and Michael Christofides — Fri, 31 May 2024 14:00:00 +0100

Michael is joined by Claire Giordano, Head of Postgres Open Source Community Initiatives at Microsoft, to discuss several ways to contribute to the Postgres community — from core contributions, to extensions, to events, and (of course) podcasts.

Here are some links to things they mentioned:

What’s new with Postgres at Microsoft (blog post by Claire) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/what-s-new-with-postgres-at-microsoft-2024-edition/ba-p/4140085
Citus https://github.com/citusdata/citus
Fibonacci Spirals and 21 Ways to Contribute to Postgres Beyond Code (talk by Claire) https://archive.fosdem.org/2020/schedule/event/postgresql_fibonacci_spirals_and_21_ways_to_contribute_to_postgres_beyond_code/
How to contribute to PostgreSQL or, 50 Ways To Love Your Project (talk slides by Josh Berkus) https://berkus.org/pdf_presos/50_ways.pdf
pgconf dot dev https://2024.pgconf.dev/
POSETTE: An Event for Postgres https://www.citusdata.com/posette
About Talk Selection for POSETTE https://www.citusdata.com/blog/2024/04/22/about-talk-selection-for-posette-an-event-for-postgres-2024/
Claire’s video about how to say Postgres, PostgreSQL, Citus, and a few questionably named Microsoft things https://x.com/clairegiordano/status/1503784151614320640
Citus goes fully open source https://www.citusdata.com/blog/2024/04/22/about-talk-selection-for-posette-an-event-for-postgres-2024/
Contributor Profiles https://www.postgresql.org/community/contributors/
Mastodon post by Álvaro Herrera (of EDB) https://lile.cl/@alvherre/112444579030481334
Talking Postgres podcast (formerly called Path To Citus Con): https://talkingpostgres.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

Full text search

Nikolay Samokhvalov and Michael Christofides — Fri, 24 May 2024 14:00:00 +0100

Nikolay and Michael discuss full text search in Postgres — some of the history, some of the features, and whether it now makes sense to try to replace or combine it with semantic search.

Here are some links to things they mentioned:

Full Text Search https://www.postgresql.org/docs/current/textsearch.html
tsearch2 https://www.postgresql.org/docs/9.6/tsearch2.html
Dictionaries https://www.postgresql.org/docs/current/textsearch-dictionaries.html
RUM index https://github.com/postgrespro/rum
Okapi BM25 https://en.wikipedia.org/wiki/Okapi_BM25
tf–idf https://en.wikipedia.org/wiki/Tf%E2%80%93idf
unaccent https://www.postgresql.org/docs/current/unaccent.html
tsvector and tsquery https://www.postgresql.org/docs/current/datatype-textsearch.html
GiST indexes https://www.postgresql.org/docs/current/gist.html
GIN indexes https://www.postgresql.org/docs/current/gin.html
Controlling Text Search (including setweight function) https://www.postgresql.org/docs/current/textsearch-controls.html
pg_trgrm https://www.postgresql.org/docs/current/pgtrgm.html
btree_gist https://www.postgresql.org/docs/current/btree-gist.html
btree_gin https://www.postgresql.org/docs/current/btree-gin.html
websearch_to_tsquery https://www.postgresql.org/docs/current/textsearch-controls.html#TEXTSEARCH-PARSING-QUERIES
pgvector https://github.com/pgvector/pgvector
Our previous episode on search https://postgres.fm/episodes/search

~~~

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

Minor releases

Nikolay Samokhvalov and Michael Christofides — Fri, 17 May 2024 14:00:00 +0100

Nikolay and Michael discuss Postgres minor releases — how the schedule works, options for upgrading to them, and the importance of reading the release notes.

Here are some links to things they mentioned:

PostgreSQL 16.3, 15.7, 14.12, 13.15, and 12.19 released (announcement) https://www.postgresql.org/about/news/postgresql-163-157-1412-1315-and-1219-released-2858/
PostgreSQL versioning policy https://www.postgresql.org/support/versioning/
PostgreSQL 14.4 release notes (most recent minor release not on the usual schedule) https://www.postgresql.org/docs/release/14.4/
Minor release roadmap https://www.postgresql.org/developer/roadmap/
Our last episode on upgrades (major and minor) https://postgres.fm/episodes/upgrades
All versions of Postgres https://bucardo.org/postgres_all_versions.html
Why upgrade? (Useful tool by depesz) https://why-upgrade.depesz.com/
Stop and start Postgres faster https://postgres.fm/episodes/stop-and-start-postgres-faster
WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Postgres CVE-2024-4317 and how to fix the system views (5 mins of Postgres by Lukas Fittl) https://youtube.com/watch?v=fLwVvJ3fKdA
Our episode on NULL https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
What should we do for episode 100? https://www.reddit.com/r/PostgreSQL/comments/1cn8ajh/what_should_we_do_for_episode_100_of_postgres_fm/

~~~

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

Custom vs generic plan

Nikolay Samokhvalov and Michael Christofides — Fri, 10 May 2024 14:00:00 +0100

Nikolay and Michael discuss custom and generic planning in prepared statements — how it works, how issues can present themselves, some ways to view the generic plan, and some benefits of avoiding planning (not just time).

Here are some links to things they mentioned:

PREPARE https://www.postgresql.org/docs/current/sql-prepare.html
track_activity_query_size https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-ACTIVITY-QUERY-SIZE
plan_cache_mode https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE
EXPLAIN (GENERIC_PLAN) https://www.postgresql.org/docs/current/sql-explain.html#id-1.9.3.148.8
EXPLAIN (GENERIC_PLAN) in PostgreSQL 16 (blog post by Laurenz from Cybertec) https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/
Running EXPLAIN on any query, even with $1 parameters (blog post and video by Lukas Fittl of pganalyze) https://pganalyze.com/blog/5mins-postgres-explain-generic-plan
EXPLAIN from pg_stat_statements, how to get the generic plan (blog post by Franck Pashto of Yugabyte) https://dev.to/yugabyte/explain-from-pgstatstatements-normalized-queries-how-to-always-get-the-generic-plan-in--5cfi
Rework query relation permission checking (commit by Amit Langote) https://git.postgresql.org/gitweb/postgres.git?p=postgresql.git;a=commit;h=a61b1f74823c9c4f79c95226a461f1e7a367764b
Partition pruning, prepared statements and generic vs custom query plans (a follow up blog post and video by Lukas) https://pganalyze.com/blog/5mins-postgres-partition-pruning-prepared-statements-generic-vs-custom-query-plans
Our episode on over-indexing (inc LWLock discussion) https://postgres.fm/episodes/over-indexing
“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/

~~~

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

LIMIT vs Performance

Nikolay Samokhvalov and Michael Christofides — Fri, 03 May 2024 14:00:00 +0100

Nikolay and Michael discuss LIMIT in Postgres — what it does, how it can help with performance, and an interesting example where adding it can actually hurt performance(!)

Here are some links to things they mentioned:

LIMIT considered harmful in PostgreSQL (Twitter thread by Christophe Pettus) https://twitter.com/Xof/status/1413542818673577987
LIMIT and OFFSET (docs) https://www.postgresql.org/docs/current/queries-limit.html
No OFFSET (by Markus Winand) https://use-the-index-luke.com/no-offset
LIMIT clause (docs) https://www.postgresql.org/docs/current/sql-select.html#SQL-LIMIT

~~~

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

Buffers II (the sequel)

Nikolay Samokhvalov and Michael Christofides — Fri, 26 Apr 2024 14:00:00 +0100

Nikolay and Michael return to the topic of using the buffers explain parameter — with a new analogy, some (conspiracy) theories of why it's still not on by default, and some related chat about the serialize parameter coming in 17.

Here are some links to things they mentioned:

BUFFERS by default (episode 4) https://postgres.fm/episodes/buffers-by-default
Lightning talk by Michael at pgDay Paris (5 mins) https://www.youtube.com/watch?v=WfY-mSpUzaQ&t=1470s
Waiting for SERIALIZE https://www.depesz.com/2024/04/11/waiting-for-postgresql-17-invent-serialize-option-for-explain/

~~~

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 brought to you by:

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

With special thanks to:

Jessie Draws for the elephant artwork

Massive DELETEs

Nikolay Samokhvalov and Michael Christofides — Fri, 19 Apr 2024 14:00:00 +0100

Nikolay and Michael discuss doing massive DELETE operations in Postgres — what can go wrong, how to prevent major issues, and some ideas to minimise their impact.

Here are some links to things they mentioned:

Article based on Nikolay’s talk, including batching implementation (translated to English) https://habr-com.translate.goog/en/articles/523536/?_x_tr_sl=ru&_x_tr_tl=en&_x_tr_hist=true
Our episode on WAL and checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Egor Rogov’s book on Postgres Internals (chapter 10 on WAL) https://edu.postgrespro.com/postgresql_internals-14_en.pdf
full_page_writes https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-FULL-PAGE-WRITES
TRUNCATE https://www.postgresql.org/docs/current/sql-truncate.html
Our episode on partitioning https://postgres.fm/episodes/partitioning
Our episode on bloat https://postgres.fm/episodes/bloat
Our episode on index maintenance https://postgres.fm/episodes/index-maintenance

~~~

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

Logical replication common issues

Nikolay Samokhvalov and Michael Christofides — Fri, 12 Apr 2024 14:00:00 +0100

Nikolay and Michael are joined by Sai Srirampur, CEO and Co-founder of PeerDB, to discuss how to overcome several logical replication issues. They discuss the protocol versions, minimising slot growth, minimising lag, and some tips and tricks for scaling things well.

Here are some links to things they mentioned:

PeerDB https://www.peerdb.io/
Our episode on logical replication https://postgres.fm/episodes/logical-replication
Exploring versions of the Postgres logical replication protocol (blog post by Sai) https://blog.peerdb.io/exploring-versions-of-the-postgres-logical-replication-protocol
Logical Streaming Replication Protocol https://www.postgresql.org/docs/current/protocol-logical-replication.html
REPLICA IDENTITY FULL https://www.postgresql.org/docs/current/sql-altertable.html#SQL-ALTERTABLE-REPLICA-IDENTITY
On the performance impact of REPLICA IDENTITY FULL in Postgres (blog post by Xata) https://xata.io/blog/replica-identity-full-performance
max_slot_wal_keep_size https://www.postgresql.org/docs/current/runtime-config-replication.html#GUC-MAX-SLOT-WAL-KEEP-SIZE
Active Active in Postgres 16 (blog post by Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16
pgEdge https://www.pgedge.com/
DistSQL (term used by Mark Callaghan) https://smalldatum.blogspot.com/2023/07/keeping-up-with-sql-dbms-market.html
Five tips on Postgres logical decoding (blog post by Sai) https://blog.peerdb.io/five-tips-on-postgres-logical-decoding
PG Slot Notify: Monitor Postgres Slot Growth in Slack (blog post by PeerDB) https://blog.peerdb.io/pg-slot-notify-monitor-postgres-slot-growth-in-slack

~~~

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 brought to you by:

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

With special thanks to:

Jessie Draws for the elephant artwork

Don't do this

Nikolay Samokhvalov and Michael Christofides — Fri, 05 Apr 2024 14:00:00 +0100

Nikolay and Michael discuss several "Don't do this" lists about Postgres — picking out their favourite items, as well as some contentious ones that could be clearer, or not included.

Here are some links to things they mentioned:

Don’t do this (PostgreSQL wiki page) https://wiki.postgresql.org/wiki/Don't_Do_This
How to get into trouble using some Postgres features (how to by Nikolay) https://github.com/postgres-ai/postgres-howtos/blob/main/0016_how_to_get_into_trouble_using_some_postgres_features.md
Don’t do this (jOOQ list) http://www.jooq.org/doc/3.19/manual/reference/dont-do-this
Don’t use NOT IN (jOOQ) https://www.jooq.org/doc/3.19/manual/reference/dont-do-this/dont-do-this-sql-not-in/
Our episode about NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
Our episode on timestamps https://postgres.fm/episodes/timestamps

~~~

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

Search

Nikolay Samokhvalov and Michael Christofides — Fri, 29 Mar 2024 14:00:00 +0000

Nikolay and Michael have a high-level discussion on all things search — touching on full-text search, semantic search, and faceted search. They discuss what comes in Postgres core, what is possible via extensions, and some thoughts on performance vs implementation complexity vs user experience.

Here are some links to things they mentioned:

Simon Riggs https://www.linkedin.com/feed/update/urn:li:activity:7178702287740022784/
Companion databases episode https://postgres.fm/episodes/companion-databases
pgvector episode https://postgres.fm/episodes/pgvector
Full Text Search https://www.postgresql.org/docs/current/textsearch.html
Semantic search https://en.wikipedia.org/wiki/Semantic_search
Faceted search https://en.wikipedia.org/wiki/Faceted_search
Faceting large result sets in PostgreSQL https://www.cybertec-postgresql.com/en/faceting-large-result-sets/
RUM index https://github.com/postgrespro/rum
Hybrid search (Supabase guide) https://supabase.com/docs/guides/ai/hybrid-search
Elastic https://www.elastic.co/
GiST indexes https://www.postgresql.org/docs/current/gist.html
GIN indexes https://www.postgresql.org/docs/current/gin.html
btree_gist https://www.postgresql.org/docs/current/btree-gist.html
btree_gin https://www.postgresql.org/docs/current/btree-gin.html
pg_trgrm https://www.postgresql.org/docs/current/pgtrgm.html
Text Search Types (tsvector and tsquery) https://www.postgresql.org/docs/current/datatype-textsearch.html
Postgres full text search with the “websearch” syntax (blog post by Adam Johnson) https://adamj.eu/tech/2024/01/03/postgresql-full-text-search-websearch/
Understanding Postgres GIN Indexes: The Good and the Bad (blog post by Lukas Fittl) https://pganalyze.com/blog/gin-index
ParadeDB https://www.paradedb.com/
ZomboDB https://www.zombodb.com/
Introduction to Information Retrieval (book by Manning, Raghavan, and Schütze) https://www.amazon.co.uk/Introduction-Information-Retrieval-Christopher-Manning/dp/0521865719
How to build a search engine with Ruby on Rails (blog post by Justin Searls) https://blog.testdouble.com/posts/2021-09-09-how-to-build-a-search-engine-with-ruby-on-rails/

~~~

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

Health check

Nikolay Samokhvalov and Michael Christofides — Fri, 22 Mar 2024 14:00:00 +0000

Nikolay and Michael discuss Postgres health checks — what they are, things to include, how often makes sense, and whether improvements to Postgres would increase or decrease the need for them.

Here are some links to things they mentioned:

MOT (car test in the UK) https://en.wikipedia.org/wiki/MOT_test
Let's make PostgreSQL multi-threaded (discussion started by Heikki) https://www.postgresql.org/message-id/flat/31cc6df9-53fe-3cd9-af5b-ac0d801163f4%40iki.fi
postgres-checkup https://gitlab.com/postgres-ai/postgres-checkup
Why upgrade https://why-upgrade.depesz.com/
Related episodes:
- Default configuration https://postgres.fm/episodes/default-configuration
- Index maintenance https://postgres.fm/episodes/index-maintenance
- Bloat https://postgres.fm/episodes/bloat
- Monitoring checklist https://postgres.fm/episodes/monitoring-checklist
- pg_stat_statements https://postgres.fm/episodes/pg_stat_statements
- 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 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

superuser

Nikolay Samokhvalov and Michael Christofides — Fri, 15 Mar 2024 14:00:00 +0000

Nikolay and Michael discuss the superuser role in PostgreSQL — what it is, how and when it shouldn’t be used, and whether most cloud providers are right to not give us it (no prizes for guessing).

Here are some links to things they mentioned:

superuser (docs) https://www.postgresql.org/docs/current/role-attributes.html#id-1.6.9.6.2.1.2.1.1
Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG) https://www.crunchydata.com/blog/crunchy-data-postgresql-security-technical-implementation-guide-now-available
Supabase docs (unsupported operations) https://supabase.com/docs/guides/database/postgres/roles-superuser
Crunchy Data docs https://docs.crunchybridge.com/concepts/users
RDS docs https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.PostgreSQL.CommonDBATasks.html
Cloud SQL docs https://cloud.google.com/sql/docs/postgres/users
Azure docs https://learn.microsoft.com/en-us/azure/postgresql/single-server/concepts-servers
Roles, Privileges, and Security (talk by Ryan Booz) https://www.youtube.com/watch?v=mtPM3iZFE04

~~~

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

transaction_timeout

Nikolay Samokhvalov and Michael Christofides — Fri, 08 Mar 2024 14:00:00 +0000

Nikolay and Michael discuss transaction_timeout (a recently committed addition for Postgres 17) — what it's for, how to get around not having it already, and whether it will replace the need to set statement_timeout globally in future.

Here are some links to things they mentioned:

transaction_timeout (devel docs) https://www.postgresql.org/docs/devel/runtime-config-client.html#GUC-TRANSACTION-TIMEOUT
Commit: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5
Mailing list thread: https://www.postgresql.org/message-id/flat/CAAhFRxiQsRs2Eq5kCo9nXE3HTugsAAJdSQSmxncivebAxdmBjQ%40mail.gmail.com
Hacking Postgres session on Postgres TV where work started https://www.youtube.com/live/WLoMpg8A4WU?t=50
Our first ever episode, on slow queries and slow transactions https://postgres.fm/episodes/slow-queries-and-slow-transactions
statement_timeout (v7.3) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-STATEMENT-TIMEOUT
idle_in_transaction_session_timeout (v9.6) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
idle_session_timeout (v14) https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT

~~~

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

Rails + Postgres

Nikolay Samokhvalov and Michael Christofides — Fri, 01 Mar 2024 14:00:00 +0000

Michael and Nikolay are joined by Andrew Atkinson, author of High Performance PostgreSQL for Rails, to discuss how Rails and Postgres work together — where the limits are, how people use the ORM, things that are improving, and some things we can do as a Postgres community to make it even better.

Here are some links to things they mentioned:

Planet Argon survey https://rails-hosting.com/2022/#databases
Active Record https://guides.rubyonrails.org/active_record_basics.html
PostgreSQL specific usage of Active Record https://guides.rubyonrails.org/active_record_postgresql.html
Multiple Databases with Active Record https://guides.rubyonrails.org/active_record_multiple_databases.html
schema.rb vs structure.sql https://blog.appsignal.com/2020/01/15/the-pros-and-cons-of-using-structure-sql-in-your-ruby-on-rails-application.html
activerecord-clean-db-structure (Ruby gem by Lukas Fittl) https://github.com/lfittl/activerecord-clean-db-structure
GitLab’s migration_helpers.rb https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/migration_helpers.rb
SQLite https://www.sqlite.org
PlanetScale’s foreign key support announcement video https://twitter.com/PlanetScale/status/1732070818958500083
DoorDash Engineering Blog https://doordash.engineering/blog
rails-pg-extras https://github.com/pawurb/rails-pg-extras
Benoit Tigeot testing Peter Geoghegan improvement for large IN lists https://gist.github.com/benoittgt/ab72dc4cfedea2a0c6a5ee809d16e04d
High Performance PostgreSQL for Rails (Andy’s book, 35% discount code “postgres.fm”) https://pragprog.com/titles/aapsql/high-performance-postgresql-for-rails
Andy’s blog and website https://andyatkinson.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

Why isn't Postgres using my index?

Nikolay Samokhvalov and Michael Christofides — Fri, 23 Feb 2024 14:00:00 +0000

Nikolay and Michael discuss a common question — why Postgres isn't using an index, and what you can do about it!

Here are some links to things they mentioned:

Why isn’t Postgres using my index? (blog post by Michael) https://www.pgmustard.com/blog/why-isnt-postgres-using-my-index
Why isn’t Postgres using my functional index? (Stack Exchange question from Brent Ozar) https://dba.stackexchange.com/questions/336019/why-isnt-postgres-using-my-functional-index
enable_seqscan (and similar parameters) https://www.postgresql.org/docs/current/runtime-config-query.html
Crunchy Bridge changed random_page_cost to 1.1 https://docs.crunchybridge.com/changelog#postgres_random_page_cost_1_1
Make indexes invisible (trick from Haki Benita) https://hakibenita.com/sql-tricks-application-dba#make-indexes-invisible
ANALYZE https://www.postgresql.org/docs/current/sql-analyze.html
Statistics used by the planner https://www.postgresql.org/docs/current/planner-stats.html
Our episode on query hints https://postgres.fm/episodes/query-hints
transaction_timeout (commit for Postgres 17) https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=51efe38cb92f4b15b68811bcce9ab878fbc71ea5
What’s new in the Postgres 16 query planner / optimizer (blog post by David Rowley) https://www.citusdata.com/blog/2024/02/08/whats-new-in-postgres-16-query-planner-optimizer/

~~~

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

Overhead of pg_stat_statements and pg_stat_kcache

Nikolay Samokhvalov and Michael Christofides — Fri, 16 Feb 2024 14:00:00 +0000

Nikolay and Michael discuss the overhead of pg_stat_statements and pg_stat_kcache — mostly focusing on some interesting things Nikolay found while stress-testing some large spot instances up to and beyond 2m TPS(!)

Here are some links to things they mentioned:

pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
pg_stat_kcache https://github.com/powa-team/pg_stat_kcache
pg_stat_statements overhead estimate in pganalyze FAQs https://pganalyze.com/faq
What is the performance impact of pg_stat_statements? (DBA Stack Exchange answer with several links) https://dba.stackexchange.com/questions/303503/what-is-the-performance-impact-of-pg-stat-statements
Towards Millions TPS (blog post by Alexander Korotkov) https://akorotkov.github.io/blog/2016/05/09/scalability-towards-millions-tps
Postgres.ai blog post with links to benchmarks https://postgres.ai/blog/20240127-postges-ai-bot
postgresql_cluster https://github.com/vitabaks/postgresql_cluster
pg_wait_sampling https://github.com/postgrespro/pg_wait_sampling
pg_stat_kcache issue, discussion, and fast resolution https://github.com/powa-team/pg_stat_kcache/issues/41
log_statement_sample_rate https://postgresqlco.nf/doc/en/param/log_statement_sample_rate/
auto_explain.sample_rate https://www.postgresql.org/docs/current/auto-explain.html#id-1.11.7.14.5.3.13.1.3
pg_stat_statements performance penalty on Intel much higher than on AMD https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues/52

~~~

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

Modern SQL

Nikolay Samokhvalov and Michael Christofides — Fri, 02 Feb 2024 14:00:00 +0000

Michael is joined by Markus Winand, creator of use-the-index-luke.com and modern-sql.com, and author of SQL Performance Explained, to discuss Modern SQL — what Markus means by it, why it's important, some benefits, some examples, and at least one phrase that should be on a t-shirt.

Here are some links to things they mentioned:

Modern SQL (site) https://modern-sql.com
Use the index, Luke! (site) https://use-the-index-luke.com
SQL Performance Explained (book) https://sql-performance-explained.com
Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
IS DISTINCT FROM (null-safe not equals comparison) https://modern-sql.com/caniuse/is-distinct-from
UNIQUE NULLS DISTINCT https://modern-sql.com/caniuse/unique-nulls-distinct
Modernes SQL ist mehr als SELECT * FROM (a German language podcast Markus was on recently) https://engineeringkiosk.dev/podcast/episode/99-modernes-sql-ist-mehr-als-select-from-mit-markus-winand
Markus’ website https://winand.at

~~~

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

Bloat

Nikolay Samokhvalov and Michael Christofides — Fri, 26 Jan 2024 14:00:00 +0000

Michael is joined by Chelsea Dole, Staff Software Engineer and Tech Lead of the Data Storage Team at Brex, to discuss bloat. They cover what it is, why it's a problem, strategies for minimising it, and the options when it's really bad.

Here are some links to things they mentioned:

Managing your tuple graveyard (talk by Chelsea) https://www.youtube.com/watch?v=gAgbzvGT6ck
pgstattuple https://www.postgresql.org/docs/current/pgstattuple.html
pg_class https://www.postgresql.org/docs/current/catalog-pg-class.html
Bloat in PostgreSQL: a taxonomy (talk by Peter Geoghegan) https://youtube.com/watch?v=JDG4bMHxCH8
It's Not You, It's Me: Breaking Up with Massive Tables via Partitioning (talk by Chelsea) https://youtube.com/watch?v=TafwSuLNxe8
pg_repack https://github.com/reorg/pg_repack
pg_squeeze https://github.com/cybertec-postgresql/pg_squeeze
VACUUM https://www.postgresql.org/docs/current/sql-vacuum.html
autovacuum https://www.postgresql.org/docs/current/runtime-config-autovacuum.html
CLUSTER https://www.postgresql.org/docs/current/sql-cluster.html
HOT updates https://www.postgresql.org/docs/current/storage-hot.html
Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default
Our episode on TOAST https://postgres.fm/episodes/toast
Our episode on index maintenance https://postgres.fm/episodes/index-maintenance
Chelsea's website: https://chelseadole.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 amazing artwork

pgvector

Nikolay Samokhvalov and Michael Christofides — Fri, 19 Jan 2024 14:00:00 +0000

Michael is joined by Jonathan Katz, PostgreSQL Core Team member, Principle Product Manager at AWS, and also pgvector contributor, to discuss pgvector. They cover what it is, why it's so popular all of a sudden, some tuning and tradeoff guidance, and a look to the future.

Here are some links to things they mentioned:

pgvector https://github.com/pgvector/pgvector
Our episode on companion databases https://postgres.fm/episodes/companion-databases
Vectors are the new JSON (talk by Jonathan) https://www.youtube.com/watch?v=D_1zunKblAU
pgvector 0.5.0 feature highlights and howtos (blog post by Jonathan) https://jkatz05.com/post/postgres/pgvector-overview-0.5.0
HNSW indexing and searching (blog post by Jonathan) https://aws.amazon.com/blogs/database/accelerate-hnsw-indexing-and-searching-with-pgvector-on-amazon-aurora-postgresql-compatible-edition-and-amazon-rds-for-postgresql
Our episode on TOAST https://postgres.fm/episodes/toast
pgvector changelog https://github.com/pgvector/pgvector/blob/master/CHANGELOG.md
HQANN paper https://arxiv.org/abs/2207.07940
HNSW fast build branch https://github.com/pgvector/pgvector/tree/hnsw-fast-build
pgconf.dev https://2024.pgconf.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 amazing artwork

pgBadger

Nikolay Samokhvalov and Michael Christofides — Fri, 12 Jan 2024 14:00:00 +0000

Michael is joined by Alicja Kucharczyk, Program Manager for Azure Database for PostgreSQL at Microsoft and organiser of the Warsaw PostgreSQL User Group, to discuss Alicja's favorite tool: pgBadger. They cover what it is, how and when it's useful, and some guidance for getting the most out of it (without causing an outage).

Here are some links to things they mentioned:

pgBadger website https://pgbadger.darold.net
pgBadger repo https://github.com/darold/pgbadger
An example pgBadger report https://pgbadger.darold.net/examples/sample.html
pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
Azure Guide https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/how-to-generate-pgbadger-report-from-azure-database-for/ba-p/3756328
Google Cloud SQL guide https://cloud.google.com/blog/products/databases/guide-to-the-database-observability-with-cloud-sql
RDS guide https://aws.amazon.com/blogs/database/optimizing-and-tuning-queries-in-amazon-rds-postgresql-based-on-native-and-external-tools/
Community Insights on pgBadger (PGSQL Phriday Recap) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/community-insights-on-pgbadger-a-pgsql-phriday-010-recap/ba-p/3880911
PGSQL Phriday #010: Log analysis (blog post by Lætitia Avrot) https://mydbanotebook.org/post/log-analysis/
Nothing Compares To VACUUM/The Ballad of Bloat https://www.youtube.com/watch?v=2NxIngqq1y0
Explain Analyze (Feliz Navidad cover) https://www.youtube.com/watch?v=qznnzYZPdkM

~~~

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

EXPLAIN

Nikolay Samokhvalov and Michael Christofides — Fri, 05 Jan 2024 14:00:00 +0000

With Nikolay taking a well-earned break, Michael covers the basics of EXPLAIN — what it is, the parameters available, and a process he recommends for using it.

Here are some links to the things mentioned:

EXPLAIN (docs) https://www.postgresql.org/docs/current/sql-explain.html
Using EXPLAIN (docs) https://www.postgresql.org/docs/current/using-explain.html
EXPLAIN Explained (talk by Josh Berkus https://www.youtube.com/watch?app=desktop&v=mCwwFAl1pBU
Beyond joins and indexes (slides from talk by Bruce Momjian) https://momjian.us/main/writings/pgsql/beyond.pdf
Depesz https://explain.depesz.com
Dalibo https://explain.dalibo.com
pgMustard https://www.pgmustard.com/
A beginners guide to explain (talk by Michael) https://www.youtube.com/watch?v=31EmOKBP1PY&t=24s
Explain beyond the basics (talk by Michael) https://www.youtube.com/watch?v=sGkSOvuaPs4
auto_explain episode https://postgres.fm/episodes/auto_explain
Row estimates episode https://postgres.fm/episodes/row-estimates
Slow queries and slow transactions episode https://postgres.fm/episodes/slow-queries-and-slow-transactions
Benchmarking episode https://postgres.fm/episodes/benchmarking
EXPLAIN glossary by Michael (forgot to mention, oops!) https://www.pgmustard.com/docs/explain

~~~

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

work_mem

Nikolay Samokhvalov and Michael Christofides — Sun, 31 Dec 2023 22:43:21 +0000

Happy holidays! Today's topic is work_mem — how it's used and how to tune it.

Related post: https://x.com/samokhvalov/status/1740813478150189172?s=46&t=HSxrW86NZqzPQP9vXVJEWg.

Partitioning by ULID

Nikolay Samokhvalov and Michael Christofides — Fri, 22 Dec 2023 14:00:00 +0000

Nikolay and Michael discuss partitioning by ULID — revisiting some of the old UUID vs bigint key debate in light of some new UUID specs, and how these can be used for partitioning (by time).

Here are some links to things they mentioned:

Nikolay’s episode on UUID (for primary keys) https://postgres.fm/episodes/uuid
Postgres TV hacking session with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8
UUIDv7 patch https://commitfest.postgresql.org/43/4388/
Use ULID Idempotency Keys (tip 6 in this blog post from Shopify) https://shopify.engineering/building-resilient-payment-systems
Nikolay’s howto post on UUID v7 and partitioning with TimescaleDB https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos/-/blob/main/0065_uuid_v7_and_partitioning_timescaledb.md
Hussein Nasser’s YouTube video about the above https://www.youtube.com/watch?v=f53-Iw_5ucA
UUID proposal that includes UUIDv7 and UUIDv8 (work in progress): https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/history/
James Blackwood-Sewell advice on more elegant solution using TimescaleDB https://twitter.com/jamessewell/status/1730125437903450129
ULIDs and Stripe IDs (section of Identity Crisis blog post by Brandur) https://brandur.org/nanoglyphs/026-ids#ulids
Crunchy Bridge changed their default random_page_cost to 1.1 🙌 https://twitter.com/brandur/status/1720477470116422028
UUIDs vs serials for keys (newer post from Christophe Pettus) https://thebuild.com/blog/2023/02/16/uuids-vs-serials-for-keys/

~~~

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

Hash indexes

Nikolay Samokhvalov and Michael Christofides — Fri, 15 Dec 2023 14:00:00 +0000

Nikolay and Michael discuss hash indexes in Postgres — what they are, some brief history, their pros and cons vs btrees, and whether or when they recommend using them.

Update: the idea Nikolay mentioned at the end of this episode turns out to be a little fraught (and as such, inadvisable).

Here are some links to things they mentioned:

Index types (docs) https://www.postgresql.org/docs/current/indexes-types.html
Re-introducing hash indexes in PostgreSQL (blog post by Haki Benita and Michael) https://hakibenita.com/postgresql-hash-index
Hash indexes intro (docs) https://www.postgresql.org/docs/current/hash-intro.html
Hash indexes implementation (docs) https://www.postgresql.org/docs/current/hash-implementation.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 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

Constraints

Nikolay Samokhvalov and Michael Christofides — Fri, 08 Dec 2023 14:00:00 +0000

Nikolay and Michael discuss PostgreSQL constraints — the six different types we have, how to add and modify them, including on large tables with high TPS.

Here are some links to things they mentioned:

Constraints (docs) https://www.postgresql.org/docs/current/ddl-constraints.html
Unique index vs unique constraint issue (2017) https://www.postgresql.org/message-id/flat/CANNMO%2B%2B7%2BuVMj%2BZPGKB_zuRpKE0sNthy2Z-_oKSS%3DoE3_rN7QA%40mail.gmail.com
Our episode on NULLs https://postgres.fm/episodes/nulls-the-good-the-bad-the-ugly-and-the-unknown
Advanced Int to Bigint Conversions (talk by Robert Treat) https://www.youtube.com/watch?v=_IieyXzdmcM
Use BIGINT in Postgres (blog post by Ryan Lambert) https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default
How to add a foreign key (Tweet by Nikolay) https://twitter.com/samokhvalov/status/1732056107483636188
Bloat, pg_repack, and deferred constraints (blog post by Nikolai Averin from Miro) https://medium.com/miro-engineering/postgresql-bloat-pg-repack-and-deferred-constraints-d0ecf33337ec

~~~

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

Events

Nikolay Samokhvalov and Michael Christofides — Fri, 01 Dec 2023 14:00:00 +0000

Nikolay and Michael discuss PostgreSQL events — whether in-person or online, large conferences or small meet-ups, as well as some strong opinions based on their experiences attending, speaking, and organising them.

Here are some links to things they mentioned:

PGSQL Phriday #014: PostgreSQL Events https://www.cybertec-postgresql.com/en/pgsql-phriday-014-postgresql-events/
PGCon https://www.pgcon.org/
Highload https://highload.rs/
The San Francisco Bay Area PostgreSQL Meetup Group https://www.meetup.com/postgresql-1/
Our episode on “Why is Postgres popular?” https://postgres.fm/episodes/why-is-postgres-popular
PGConf EU https://pgconf.eu/
Open talks series on Postgres TV https://www.youtube.com/playlist?list=PLH8y1BNPAKjJCuZiDRl0qUEDaKLBpFvZ9
Rails World (including videos!) https://rubyonrails.org/world
Upcoming events https://www.postgresql.org/about/events/

~~~

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

Subtransactions

Nikolay Samokhvalov and Michael Christofides — Fri, 24 Nov 2023 14:00:00 +0000

Nikolay and Michael discuss PostgreSQL subtransactions — what they are, and four issues they can cause at scale.

Here are some links to things they mentioned:

Subtransactions (docs) https://www.postgresql.org/docs/current/subxacts.html
SAVEPOINT (docs) https://www.postgresql.org/docs/current/sql-savepoint.html
PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay) https://postgres.ai/blog/20210831-postgresql-subtransactions-considered-harmful
Subtransactions and performance in PostgreSQL (blog post by Laurenz at Cybertec) https://www.cybertec-postgresql.com/en/subtransactions-and-performance-in-postgresql/
Notes on some PostgreSQL implementation details (blog post by Nelson Elhage) https://buttondown.email/nelhage/archive/notes-on-some-postgresql-implementation-details/
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/

~~~

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

Companion databases

Nikolay Samokhvalov and Michael Christofides — Fri, 17 Nov 2023 14:00:00 +0000

Nikolay and Michael discuss companion databases — when and why you might want to add another database management system to your stack (or not), and some specifics for analytics, timeseries, search, and vectors.

Here are some links to things they mentioned:

Heap were using Postgres + Citus for analytics as of 2022 https://www.heap.io/blog/juggling-state-machines-incident-response-and-data-soup-a-glimpse-into-heaps-engineering-culture
Heap recently moved their core analytics to SingleStore (we only spotted this after recording 🤦‍♂️) https://www.heap.io/blog/heaps-next-generation-data-platform
Posthog moved their analytics from Postgres to Clickhouse https://posthog.com/blog/clickhouse-vs-postgres
Timescale https://www.timescale.com/
Citus https://www.citusdata.com/
Hydra https://www.hydra.so/
Our episode on real-time analytics https://postgres.fm/episodes/real-time-analytics
Our episode on queues https://postgres.fm/episodes/queues-in-postgres
Our episode on logical replication https://postgres.fm/episodes/logical-replication
Full Text Search in PostgreSQL (docs) https://www.postgresql.org/docs/current/textsearch.html
ZomboDB https://www.zombodb.com/
ParadeDB https://www.paradedb.com/
PeerDB https://www.peerdb.io/
RUM indexes https://github.com/postgrespro/rum
Debezium https://debezium.io/
pgmq https://github.com/tembo-io/pgmq
Tembo https://tembo.io/
pgvector vs Pinecone (Supabase blog post) https://supabase.com/blog/pgvector-vs-pinecone
Jepsen analyses https://jepsen.io/analyses

~~~

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

Blue-green deployments

Nikolay Samokhvalov and Michael Christofides — Fri, 10 Nov 2023 14:00:00 +0000

Nikolay and Michael discuss blue-green deployments — specifically an RDS blog post, how similar this is (or not) to what they understand to be blue-green deployments, and how applicable the methodology might be in the database world more generally.

Here are some links to things they mentioned:

Fully managed Blue/Green Deployment in Amazon Aurora PostgreSQL and Amazon RDS for PostgreSQL https://aws.amazon.com/blogs/database/new-fully-managed-blue-green-deployment-in-amazon-aurora-postgresql-and-amazon-rds-for-postgresql/
Blue-green deployment (blog post by Martin Fowler) https://martinfowler.com/bliki/BlueGreenDeployment.html
Our episode on logical replication https://postgres.fm/episodes/logical-replication
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 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

Data model trade-offs

Nikolay Samokhvalov and Michael Christofides — Fri, 03 Nov 2023 14:00:00 +0000

Nikolay and Michael discuss data model trade-offs — some cases where you might want to (gasp) de-normalize, and which PostgreSQL internals are at play.

Here are some links to things they mentioned:

Our episode on JSON https://postgres.fm/episodes/json
PostgreSQL limits https://www.postgresql.org/docs/current/limits.html
Boyce–Codd normal form https://en.wikipedia.org/wiki/Boyce–Codd_normal_form
Our episode on over-indexing https://postgres.fm/episodes/over-indexing
Heap-Only Tuples (HOT) optimisation https://www.postgresql.org/docs/current/storage-hot.html
The Surprising Impact of Medium-Size Texts on PostgreSQL Performance https://hakibenita.com/sql-medium-text-performance
Query planner settings (collapse limits and Genetic Query Optimizer) https://www.postgresql.org/docs/current/runtime-config-query.html
WITH Queries (Common Table Expressions) https://www.postgresql.org/docs/current/queries-with.html
Our episode on benchmarking https://postgres.fm/episodes/benchmarking
The tests and benchmarks Nikolay mentioned https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/issues

~~~

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

Under-indexing

Nikolay Samokhvalov and Michael Christofides — Fri, 27 Oct 2023 14:00:00 +0100

Nikolay and Michael discuss under-indexing — how to tell if this applies to you, some general and specific cases, as well as some tricky ones.

Here are some links to things they mentioned:

Indexes (docs) https://www.postgresql.org/docs/current/indexes.html
random_page_cost https://postgresqlco.nf/doc/en/param/random_page_cost/
Sequential scans https://www.pgmustard.com/docs/explain/sequential-scan
pg_stat_user_tables https://pgpedia.info/p/pg_stat_user_tables.html
pg_stat_statements https://www.postgresql.org/docs/current/pgstatstatements.html
auto_explain https://www.postgresql.org/docs/current/auto-explain.html
Our episode on slow queries https://postgres.fm/episodes/slow-queries-and-slow-transactions
Our episode on query macro analysis https://postgres.fm/episodes/macro-query-analysis-intro
Running 10 Million PostgreSQL Indexes In Production (And Counting) https://www.heap.io/blog/running-10-million-postgresql-indexes-in-production
Faceting large result sets in PostgreSQL https://www.cybertec-postgresql.com/en/faceting-large-result-sets/
Our episode on over-indexing https://postgres.fm/episodes/over-indexing

~~~

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

Over-indexing

Nikolay Samokhvalov and Michael Christofides — Fri, 20 Oct 2023 14:00:00 +0100

Nikolay and Michael discuss over-indexing — what we mean by it, the regular issues people discuss about it, as well as a novel one Nikolay has come across and benchmarked recently.

Here are some links to things they mentioned:

Nikolay’s tweet on over-indexing https://twitter.com/samokhvalov/status/1713101666629927112
Heap-Only Tuples (HOT) optimization https://www.postgresql.org/docs/current/storage-hot.html
Our episode on index maintenance https://postgres.fm/episodes/index-maintenance
PgBouncer now supports prepared statements https://github.com/pgbouncer/pgbouncer/releases/tag/pgbouncer_1_21_0
Our episode on connection poolers https://postgres.fm/episodes/connection-poolers
Configurable FP_LOCK_SLOTS_PER_BACKEND (Hackers mailing list discussion) https://www.postgresql.org/message-id/flat/CAM527d-uDn5osa6QPKxHAC6srOfBH3M8iXUM%3DewqHV6n%3Dw1u8Q%40mail.gmail.com
LWLock:lock_manager (Amazon RDS docs) https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/wait-event.lw-lock-manager.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 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

Query hints

Nikolay Samokhvalov and Michael Christofides — Fri, 13 Oct 2023 14:00:00 +0100

Nikolay and Michael discuss query hints — what they are, what we do and don't have in PostgreSQL, and some other things in and around the ecosystem to be aware of.

Here are some links to some extra things they mentioned:

Query planning (docs) https://www.postgresql.org/docs/current/runtime-config-query.html
Statistics used by the planner (docs) https://www.postgresql.org/docs/current/planner-stats.html
default_statistics_target https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-DEFAULT-STATISTICS-TARGET
Optimiser hints discussion (wiki) https://wiki.postgresql.org/wiki/OptimizerHintsDiscussion
An example mailing list thread from 2006 https://www.postgresql.org/message-id/flat/20061012151439.GT28647%40nasby.net
Peter Geoghegan tweet regarding invalid index https://twitter.com/petervgeoghegan/status/1599191964045672449
plantuner http://sigaev.ru/git/gitweb.cgi?p=plantuner.git;a=blob;hb=HEAD;f=README.plantuner
pg_hint_plan https://github.com/ossc-db/pg_hint_plan
Aurora PostgreSQL query plan management https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.overview.html
Building automatic adviser & performance tuning tools - Julien Rouhaud & Tatsuro Yamada: PGCon 2020 (the completely misremembered presentation Michael mentioned 🙈) https://www.youtube.com/watch?v=LQZK6p3SwwA
hypopg https://github.com/HypoPG/hypopg

~~~

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

Stop and start Postgres faster

Nikolay Samokhvalov and Michael Christofides — Sat, 07 Oct 2023 15:10:04 +0100

In this episode (recorded live on YouTube), Nikolay discusses Postgres shutdown and startup times – how to troubleshoot them and, when needed, optimize.

Some extra things mentioned in this episode:

#PostgresMarathon series – every day, Nikolay posts a new howto-style article https://twitter.com/hashtag/PostgresMarathon
Day 2: Postgres shutdown and restart attempts https://twitter.com/samokhvalov/status/1707147450044297673
Day 3: How to troubleshoot long Postgres startup https://twitter.com/samokhvalov/status/1707466169245171773
GitLab repo with #PostgresMarathon posts (markdown): https://gitlab.com/postgres-ai/postgresql-consulting/postgres-howtos
Episode 010 "WAL and checkpoint tuning": https://postgres.fm/episodes/wal-and-checkpoint-tuning

~~~

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

Backups

Nikolay Samokhvalov and Michael Christofides — Fri, 29 Sep 2023 14:00:00 +0100

Nikolay and Michael discuss Postgres backups — why we need them, what the options are, whether a dump is a backup or not, and some considerations for lowering RPO and RTO at scale.

Here are some links to some extra things they mentioned:

pg_dump https://www.postgresql.org/docs/current/app-pgdump.html
pg_basebackup https://www.postgresql.org/docs/current/app-pgbasebackup.html
pgBackRest https://github.com/pgbackrest/pgbackrest
WAL-G https://github.com/wal-g/wal-g
Barman https://github.com/EnterpriseDB/barman
Data loss at GitLab (2017) https://about.gitlab.com/blog/2017/02/01/gitlab-dot-com-database-incident/
Dev Deletes Entire Production Database, Chaos Ensues (YouTube video) https://www.youtube.com/watch?v=tLdRBsuvVKc
Our episode on corruption https://postgres.fm/episodes/corruption
DBLab Engine https://github.com/postgres-ai/database-lab-engine

~~~

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

Postgres 16

Nikolay Samokhvalov and Michael Christofides — Fri, 22 Sep 2023 14:00:00 +0100

Nikolay and Michael discuss the release of PostgreSQL 16 — the most important new features, what they mean for us as users, whether and when to upgrade, and more.

Here are some links to some extra things they mentioned:

Release notes https://www.postgresql.org/docs/current/release-16.html
New Features With Examples (PDF from Noriyoshi Shinoda of Hewlett Packard Enterprise Japan) https://h50146.www5.hpe.com/products/software/oe/linux/mainstream/support/lcc/pdf/PostgreSQL16Beta1_New_Features_en_20230528_1.pdf
Why Upgrade? (site by depesz) https://why-upgrade.depesz.com/
Waiting for PostgreSQL 16 (blog post series from Depesz) https://www.depesz.com/tag/pg16/
Our episode on favourite features https://postgres.fm/episodes/our-favourite-v16-feature
Our episode on logical replication https://postgres.fm/episodes/logical-replication
Active Active in Postgres 16 (blog post from Crunchy Data) https://www.crunchydata.com/blog/active-active-postgres-16
AlloyDB adaptive autovacuum https://cloud.google.com/blog/products/databases/alloydb-for-postgresql-under-the-hood-adaptive-autovacuum
Visualizing Postgres I/O Performance (talk by Melanie Plageman at PGCon) https://www.youtube.com/watch?v=CxyPZHG5beI
Our monitoring checklist episode https://postgres.fm/episodes/monitoring-checklist
pgvector https://github.com/pgvector/pgvector

~~~

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

Logical replication

Nikolay Samokhvalov and Michael Christofides — Fri, 15 Sep 2023 13:00:00 +0100

Nikolay and Michael discuss logical replication — some history, initialization, change data capture, how to scale it, some limitiations, and ways that it is getting better.

Here are some links to some things they mentioned:

Logical replication https://www.postgresql.org/docs/current/logical-replication.html
GitLab upgraded multi-terabyte, heavily-loaded clusters with zero-downtime https://twitter.com/samokhvalov/status/1700574156222505276
pg_waldump https://www.postgresql.org/docs/current/pgwaldump.html
pg_dump and external snapshots (blog post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-9-5-feature-highlight-pg-dump-snapshots/
Failover of logical replication slots in Patroni (talk by Alexander Kukushkin) https://www.youtube.com/watch?v=SllJsbPVaow
Our episode on replication https://postgres.fm/episodes/replication

~~~

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 brought to you by:

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

With special thanks to:

Jessie Draws for the elephant artwork

Our favourite v16 feature

Nikolay Samokhvalov and Michael Christofides — Fri, 08 Sep 2023 13:30:00 +0100

Nikolay and Michael discuss their favourite feature each from the upcoming PostgreSQL 16 release.

Here are some links to some things they mentioned:

v16 draft release notes https://www.postgresql.org/docs/16/release-16.html
PGSQL Phriday #012 invitation from Ryan Booz https://www.pgsqlphriday.com/2023/08/pgsql-phriday-012/
Subscribe options for the podcast https://postgres.fm/subscribe
A recent closed source ClickHouse feature https://github.com/ClickHouse/ClickHouse/issues/44767#issuecomment-1683293218
Postgres TV hacking session with Andrey Borodin on \watch with limited number of loops (v16) https://www.youtube.com/watch?v=vTV8XhWf3mo
Allow \watch queries to stop on minimum rows returned (v17) https://github.com/postgres/postgres/commit/f347ec76e2a227e5c5b5065cce7adad16d58d209
pg_stat_io commit mentioning the op_bytes column (v16) https://github.com/postgres/postgres/commit/a9c70b46dbe152e094f137f7e6ba9cd3a638ee25
pg_size_pretty function https://www.postgresql.org/docs/current/functions-admin.html#id-1.5.8.33.9.3.2.2.7.1.1.1
Visualizing Postgres I/O Performance (talk by Melanie Plageman at PGCon) https://www.youtube.com/watch?v=CxyPZHG5beI
Our episode on BUFFERS https://postgres.fm/episodes/buffers-by-default
EXPLAIN (GENERIC_PLAN) blog post by Laurenz Albe https://www.cybertec-postgresql.com/en/explain-generic-plan-postgresql-16/
Running EXPLAIN on any query (video by Lukas Fittl) https://www.youtube.com/watch?v=CMftYJnqou0
PostgreSQL 16 Beta 1 New Features with Examples.(English Version) by Noriyoshi Shinoda https://twitter.com/nori_shinoda/status/1664481483355226114
Have auto_explain's log_verbose mode honor the value of compute_query_id (commitfest entry) https://commitfest.postgresql.org/42/4136/
Make auto_explain print the query identifier in verbose mode (commit) https://github.com/postgres/postgres/commit/9d2d9728b8d546434aade4f9667a59666588edd6

~~~

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

Connections

Nikolay Samokhvalov and Michael Christofides — Fri, 01 Sep 2023 13:30:00 +0100

Nikolay and Michael discuss connections — the options, security and performance tradeoffs, and a few other things to be aware of.

Here are some links to some things they mentioned:

Episode on connection poolers https://postgres.fm/episodes/connection-poolers
listen_addresses https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-LISTEN-ADDRESSES
SSLMODE Explained (blog post by Andrew Kane) https://ankane.org/postgres-sslmode-explained
pg_hba.conf https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
Timing a query (blog post by Bruce Momjian) https://momjian.us/main/blogs/pgblog/2012.html#June_6_2012
How to connect (blog post by Lætitia Avrot) https://mydbanotebook.org/post/cant-connect/
Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462
idle_session_timeout https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-IDLE-SESSION-TIMEOUT
Do you vacuum everyday? (talk by Hannu Krosing) https://www.youtube.com/watch?v=JcRi8Z7rkPg
Don’t use now() with pg_stat_activity (tweet by Nikolay) https://twitter.com/samokhvalov/status/1664981076014690304

~~~

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

Decoupled storage and compute

Nikolay Samokhvalov and Michael Christofides — Fri, 25 Aug 2023 13:30:00 +0100

Nikolay and Michael discuss a listener question — about products that take Postgres and transform it to something that decouples compute from storage (RDS Aurora, GC AlloyDB, Neon etc.) and whether they see something like this landing upstream in the medium term.

Here are some links to some things they mentioned:

Amazon Aurora https://aws.amazon.com/rds/aurora/
Google Cloud AlloyDB for PostgreSQL https://cloud.google.com/alloydb
Neon https://neon.tech/
Google Cloud Spanner https://cloud.google.com/spanner
Is Aurora PostgreSQL really faster and cheaper than RDS PostgreSQL? (blog post by Avinash Vallarapu from MigOps) https://www.migops.com/blog/is-aurora-postgresql-really-faster-and-cheaper-than-rds-postgresql-benchmarking/
Deep dive on Amazon Aurora with PostgreSQL compatibility (presentation by Grant McAllister) https://www.youtube.com/watch?v=HQg8wqlxefo
Intro to Aurora PostgreSQL Query Plan Management https://aws.amazon.com/blogs/database/introduction-to-aurora-postgresql-query-plan-management/
Michael Stonebraker Turing Award Lecture https://www.youtube.com/watch?v=BbGeKi6T6QI
Interview with Stas Kelvich from Neon on Postgres TV https://www.youtube.com/watch?v=4PUKNznq_eM
Interview with Ben Vandiver from Google Cloud Spanner on Postgres TV https://www.youtube.com/watch?v=BW-Uexhv-bk
Timescale Cloud bottomless storage feature (data tiering to Amazon S3) https://www.timescale.com/blog/expanding-the-boundaries-of-postgresql-announcing-a-bottomless-consumption-based-object-storage-layer-built-on-amazon-s3/
Testing Database Changes the Right Way (Heap Analytics article) https://www.heap.io/blog/testing-database-changes-right-way

~~~

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

Self-managing

Nikolay Samokhvalov and Michael Christofides — Fri, 18 Aug 2023 13:30:00 +0100

Nikolay and Michael discuss self-managing Postgres — both the practicalities of doing so, as well as some managed-service style tooling.

Here are some links to some things they mentioned:

Our episode on Managed services vs. DIY https://postgres.fm/episodes/managed-services-vs-diy
WAL-G https://github.com/wal-g/wal-g
pgBackRest https://pgbackrest.org/
Barman https://github.com/EnterpriseDB/barman
Dead Man’s Snitch https://deadmanssnitch.com/
Netdata https://www.netdata.cloud/
Upgrades https://postgres.fm/episodes/upgrades
High availability https://postgres.fm/episodes/high-availability
Configuration https://postgres.fm/episodes/default-configuration
Corruption https://postgres.fm/episodes/corruption
Connection poolers https://postgres.fm/episodes/connection-poolers
Index maintenance https://postgres.fm/episodes/index-maintenance
StackGres supported extensions (Michael was wrong, it also has a timescale_tls extension!) https://stackgres.io/extensions/
postgresql_cluster https://github.com/vitabaks/postgresql_cluster
Supabase self-hosting https://supabase.com/docs/guides/self-hosting
Tembo https://github.com/tembo-io/tembo
Open source licenses, clouds, Postgres (Postgres TV discussion) https://www.youtube.com/watch?v=1rcbyIjA4gI&t=149s

~~~

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

Sharding

Nikolay Samokhvalov and Michael Christofides — Fri, 11 Aug 2023 13:30:00 +0100

Nikolay and Michael discuss sharding Postgres — what it means, why and when it's needed, and the available options right now.

Here are some links to some things they mentioned:

PGSQL Friday monthly blogging event https://www.pgsqlphriday.com/
Did “sharding” come from Ultima Online? https://news.ycombinator.com/item?id=23438399
Our episode on partitioning: https://postgres.fm/episodes/partitioning
Vitess https://vitess.io/
Citus https://www.citusdata.com/
Lessons learned from sharding Postgres (Notion 2021) https://www.notion.so/blog/sharding-postgres-at-notion
The Great Re-shard (Notion 2023) https://www.notion.so/blog/the-great-re-shard
The growing pains of database architecture (Figma 2023) https://www.figma.com/blog/how-figma-scaled-to-multiple-databases/
Timescale multi-node https://docs.timescale.com/self-hosted/latest/multinode-timescaledb/about-multinode/
PgCat https://github.com/postgresml/pgcat
SPQR https://github.com/pg-sharding/spqr
PL/Proxy https://plproxy.github.io/
Sharding GitLab by top-level namespace https://about.gitlab.com/handbook/engineering/development/enablement/data_stores/database/doc/root-namespace-sharding.html
Loose foreign keys (GitLab) https://docs.gitlab.com/ee/development/database/loose_foreign_keys.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 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

Data types

Nikolay Samokhvalov and Michael Christofides — Fri, 04 Aug 2023 13:30:00 +0100

Nikolay and Michael discuss data types in PostgreSQL — including system types, choosing between types, types provided by extensions, and more.

Here are some links to some things they mentioned:

Data Types (docs) https://www.postgresql.org/docs/current/datatype.html
10 tips for beginners https://postgres.ai/blog/20230722-10-postgres-tips-for-beginners
Tid Scan (explain glossary) https://www.pgmustard.com/docs/explain/tid-scan
Don’t do this (wiki) https://wiki.postgresql.org/wiki/Don't_Do_This
Boundless `text` and back again https://brandur.org/text
UUID episode https://postgres.fm/episodes/uuid
I use ENUM (30min talk by Boriss Mejías) https://archive.fosdem.org/2021/schedule/event/postgresql_i_use_enum_vindicating_the_underdog_of_data_types/
Peter Geoghegan tweet https://twitter.com/petervgeoghegan/status/1680275871905775616
JSON episode https://postgres.fm/episodes/json
pg_repack reorder columns discussion https://github.com/reorg/pg_repack/issues/101
Use bigint https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default

~~~

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

High availability

Nikolay Samokhvalov and Michael Christofides — Fri, 28 Jul 2023 13:30:00 +0100

Nikolay and Michael discuss HA (high availability) — what it means, tools and techniques for maximising it, while going through some of the more common causes of downtime.

Here are some links to some things they mentioned:

https://en.wikipedia.org/wiki/High_availability
https://postgres.fm/episodes/upgrades
https://github.com/shayonj/pg_easy_replicate/
pg_easy_replicate discussion on Hacker News https://news.ycombinator.com/item?id=36405761
https://postgres.fm/episodes/connection-poolers
https://www.postgresql.org/docs/current/libpq.html
Support load balancing in libpq (new feature in Postgres 16)
https://commitfest.postgresql.org/42/3679/
target_session_attrs options for high availability and scaling (2021; a post by Laurenz Albe) https://www.cybertec-postgresql.com/en/new-target_session_attrs-settings-for-high-availability-and-scaling-in-postgresql-v14/
Postgres 10 highlight - read-write and read-only mode of libpq (2016, a post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-10-libpq-read-write/
Postgres 10 highlight - Quorum set of synchronous standbys (2017, a post by Michael Paquier) https://paquier.xyz/postgresql-2/postgres-10-quorum-sync/
https://github.com/zalando/patroni
https://postgres.fm/episodes/replication
https://blog.rustprooflabs.com/2021/06/postgres-bigint-by-default
Zero-downtime Postgres schema migrations need this: lock_timeout and retries (2021) https://postgres.ai/blog/20210923-zero-downtime-postgres-schema-migrations-lock-timeout-and-retries
A fix in Patroni to mitigate a very long shutdown attempt when archive_command has a lot of WALs to archive https://github.com/zalando/patroni/pull/2067

~~~

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 brought to you by:

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

With special thanks to:

Jessie Draws for the elephant artwork

Beginner tips

Nikolay Samokhvalov and Michael Christofides — Fri, 21 Jul 2023 13:30:00 +0100

Nikolay and Michael discuss 10 beginner tips Nikolay recently shared — they go into a bit more detail on each, and even disagree a little on one or two!

Here are some links to some things they mentioned:

Nikolay’s tweet with all 10 tips: https://twitter.com/samokhvalov/status/1679953049899642880
Tip 1: tuples are physical versions of rows
Related episodes: https://postgres.fm/episodes/how-to-become-a-dba and https://postgres.fm/episodes/hot-updates
Tip 2: always use EXPLAIN (ANALYZE, BUFFERS)
Related episode: https://postgres.fm/episodes/buffers-by-default
Tip 3: throw away pgAdmin
Related episode: https://postgres.fm/episodes/psql-vs-guis
Tip 4: enable as much logging as you can afford
Related episode: https://postgres.fm/episodes/default-configuration
Tip 5: install pg_stat_statements
Related episodes: https://postgres.fm/episodes/pg_stat_statements and https://postgres.fm/episodes/auto_explain, and https://postgres.fm/episodes/macro-query-analysis-intro
Tip 6: run experiments on realistic data sets (use thin cloning and branching)
Related episode: https://postgres.fm/episodes/database-branching
Tip 7: make sure data checksums are enabled
Related episode: https://postgres.fm/episodes/corruption
Tip 8: tune autovacuum to run frequently and move faster
Related episode: https://postgres.fm/episodes/vacuum
Tip 9: query optimization will eventually be more important than configuration tuning
Related episodes: https://postgres.fm/episodes/intro-to-query-optimization and https://postgres.fm/episodes/102-query-optimization
Tip 10: indexes need to be rebuilt, unfortunately, since their health decline over time
Related episode: https://postgres.fm/episodes/index-maintenance
Bonus tip: the official documentation, release notes, and source code comments/readmes are your friends!

~~~

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

Connection poolers

Nikolay Samokhvalov and Michael Christofides — Fri, 14 Jul 2023 13:30:00 +0100

Nikolay and Michael discuss Postgres connection poolers — when and why we need them, the king that is PgBouncer, and the many new pretenders to the throne.

Here are links to a few things they mentioned:

max_connections https://www.postgresql.org/docs/current/runtime-config-connection.html#GUC-MAX-CONNECTIONS
Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/improving-postgres-connection-scalability-snapshots/ba-p/1806462
PgBouncer https://github.com/pgbouncer/pgbouncer
Odyssey https://github.com/yandex/odyssey
PgCat https://github.com/postgresml/pgcat
Adopting PgCat: A Nextgen Postgres Proxy https://www.instacart.com/company/how-its-made/adopting-pgcat-a-nextgen-postgres-proxy/
Supavisor https://github.com/supabase/supavisor
pgagroal https://github.com/agroal/pgagroal
PgBouncer is useful, important, and fraught with peril (blog post from JP Camara) https://jpcamara.com/2023/04/12/pgbouncer-is-useful.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 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

Anniversary mailbag

Nikolay Samokhvalov and Michael Christofides — Fri, 07 Jul 2023 13:30:00 +0100

Nikolay and Michael celebrate the podcast's 1 year anniversary by going through several questions and suggestions received over the year.

Here are the questions and some links to things we mentioned:

Question 1: Effect of wal_log_hints=on after bulk Deletes: Why next select runs slow and generated tons of WAL? https://twitter.com/dmx551/status/1598253188926570496
wal_log_hints https://www.postgresql.org/docs/current/runtime-config-wal.html#GUC-WAL-LOG-HINTS
Exploring how SELECT Queries can produce disk writes https://blog.okmeter.io/postgresql-exploring-how-select-queries-can-produce-disk-writes-f36c8bee6b6f

Question 2: How to get started reading PostgreSQL source code. Maybe a PostgreSQL style C reference guide to consult with for non C programmers https://twitter.com/andatki/status/1578088843940593678
So, you want to be a developer? https://wiki.postgresql.org/wiki/So,_you_want_to_be_a_developer%3F
GitHub search https://github.com/search?q=repo%3Apostgres%2Fpostgres+wal_log_hints&type=code
The Internals of PostgreSQL (by Hironobu SUZUKI) https://www.interdb.jp/pg/
PostgreSQL 14 Internals (by Egor Rogov) https://postgrespro.com/community/books/internals

Question 3: Isolation Levels Primer/Strategies — their uses in different scenarios, battle tested strategies and insights, performance tradeoffs, edge cases to consider at scale (with replication and sharding, etc.) I remember reading some interesting stuff on the jepsen analysis https://jepsen.io/analyses/postgresql-12.3 about Postgres's Serializable Isolation Level behaving more like Snapshot Isolation. Has this type of behavior or another one similar to this affected you or your clients in any significant way?
Transaction Isolation https://www.postgresql.org/docs/current/transaction-iso.html
What developers find surprising about Postgres transactions https://blog.lawrencejones.dev/isolation-levels/

Question 4: Data encryption in Postgres
Cybertec Transparent Data Encryption https://www.cybertec-postgresql.com/en/products/postgresql-transparent-data-encryption/
EDB Transparent Data Encryption https://www.enterprisedb.com/docs/tde/latest/

Question 5: Migration from other DBMSs
PostgreSQL transition/migration guide https://github.com/postgresql-transition-guide/guide

Question 6: Latest failover best practices
Patroni https://github.com/zalando/patroni

~~~

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

pg_upgrade: the tricky and dangerous parts

Nikolay Samokhvalov — Fri, 30 Jun 2023 21:00:00 +0100

Nikolay (alone, again) reveals some issues that might hit those who perform major PostgreSQL upgrades with minimal downtime.

Links:
- "Upgrades" – PostgresFM episode 037: https://postgres.fm/episodes/upgrades
- recovery_target_lsn: https://postgresqlco.nf/doc/en/param/recovery_target_lsn/
- recovery_target_action: https://postgresqlco.nf/doc/en/param/recovery_target_action/
- pg_easy_replicate https://github.com/shayonj/pg_easy_replicate/
- HN discussion of this tool: https://news.ycombinator.com/item?id=36405761
- Waiting for Postgres 16: Logical decoding on standbys: https://pganalyze.com/blog/5mins-postgres-16-logical-decoding
- pg_upgrade and logical replication (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/20230217075433.u5mjly4d5cr4hcfe%40jrouhaud
- allow upgrading publisher node (proposal to natively support running pg_upgrade on publisher, WIP): https://commitfest.postgresql.org/43/4273/
- ❓🤔 pg_upgrade instructions involving "rsync --size-only" might lead to standby corruption? (discussion in pgsql-hackers): https://www.postgresql.org/message-id/flat/CAM527d8heqkjG5VrvjU3Xjsqxg41ufUyabD9QZccdAxnpbRH-Q%40mail.gmail.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!

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

UUID

Nikolay Samokhvalov — Fri, 23 Jun 2023 20:00:00 +0100

Lonely Nikolay discusses the performance aspects of using UUID for primary keys.

Here are links to a few things I mentioned:

"postgresql" posts on HN, most popular last week (Algolia search): https://hn.algolia.com/?dateRange=pastWeek&page=0&prefix=true&query=postgresql&sort=byPopularity&type=story
Unexpected downsides of UUID keys in PostgreSQL (a post by Ants Aasma, Cybertec): https://www.cybertec-postgresql.com/en/unexpected-downsides-of-uuid-keys-in-postgresql/
HN discussion of that post: https://news.ycombinator.com/item?id=36429986
Additional math by me (converting to bytes): https://twitter.com/samokhvalov/status/1671962111092850689
Updated RFC4122 (proposal): https://github.com/ietf-wg-uuidrev/rfc4122bis
Status of that proposal: https://datatracker.ietf.org/doc/draft-ietf-uuidrev-rfc4122bis/history/
Patch UUID v7 (commitfest record): https://commitfest.postgresql.org/43/4388/
Postgres hacking with Andrey and Kirk: https://www.youtube.com/watch?v=YPq_hiOE-N8 (where that patch was developed)

~~~

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!

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

Memory

Nikolay Samokhvalov and Michael Christofides — Fri, 16 Jun 2023 13:30:00 +0100

Nikolay and Michael discuss memory in PostgreSQL — how it works, important settings, and how to go about tuning them.

Here are links to a few things we mentioned:

Resource Consumption (PostgreSQL docs) https://www.postgresql.org/docs/current/runtime-config-resource.html
Andres Freud tweet about shared_buffers https://twitter.com/AndresFreundTec/status/1438912583554113537
Henrietta (Hettie) Dombrovskaya https://hdombrovskaya.wordpress.com/about-the-author/
annotated.conf (by ash Berkus) https://github.com/jberkus/annotated.conf
Our episode about checkpoint tuning https://postgres.fm/episodes/wal-and-checkpoint-tuning
Our episode about BUFFERS https://postgres.fm/episodes/buffers-by-default
Analyzing the Limits of Connection Scalability in Postgres (blog post by Andres Freund) https://techcommunity.microsoft.com/t5/azure-database-for-postgresql/analyzing-the-limits-of-connection-scalability-in-postgres/ba-p/1757266#memory-usage
Tuning memory parameters for Aurora PostgreSQL https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.BestPractices.Tuning-memory-parameters.html
RDS for PostgreSQL memory https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Tuning.concepts.html#PostgreSQL.Tuning.concepts.memory
EDB guide by Via Fearing https://www.enterprisedb.com/postgres-tutorials/introduction-postgresql-performance-tuning-and-optimization#resourceusage
pg_stat_kcache https://github.com/powa-team/pg_stat_kcache
pg_buffercache https://www.postgresql.org/docs/current/pgbuffercache.html
Process and Memory Architecture chapter (from Hironobu SUZUKI) https://www.interdb.jp/pg/pgsql02.html
PostgreSQL 14 internals PDF book from Egor Rogov (pages 37, 184) https://edu.postgrespro.com/postgresql_internals-14_en.pdf
src/backend/storage/buffer/README https://github.com/postgres/postgres/blob/master/src/backend/storage/buffer/README
pg_backend_memory_contexts (PostgreSQL 14+) https://www.postgresql.org/docs/current/view-pg-backend-memory-contexts.html
pg_stat_io (coming in PostgreSQL 16) https://www.postgresql.org/docs/devel/monitoring-stats.html#MONITORING-PG-STAT-IO-VIEW
pg_prewarm https://www.postgresql.org/docs/current/pgprewarm.html
Configuring work_mem blog post https://www.pgmustard.com/blog/work-mem

~~~

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!

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

Extensions

Nikolay Samokhvalov and Michael Christofides — Fri, 09 Jun 2023 13:30:00 +0100

Nikolay and Michael discuss Postgres extensions — what they are, how they affect your decisions around Postgres, and some things to keep in mind when using them.

Here are links to a few things we mentioned:

Extensions (docs) https://www.postgresql.org/docs/current/external-extensions.html
Extension (pgPedia) https://pgpedia.info/e/extension.html
pgvector https://github.com/pgvector/pgvector
PL/Rust https://github.com/tcdi/plrust
ZomboDB https://github.com/zombodb/zombodb
Why is Postgres popular episode https://postgres.fm/episodes/why-is-postgres-popular
Citus https://github.com/citusdata/citus
TimescaleDB https://github.com/timescale/timescaledb
OrioleDB https://github.com/orioledb/orioledb
PostGIS https://trac.osgeo.org/postgis/
“There’s an extension for that” (tweet from Robert Treat) https://twitter.com/robtreat2/status/1665735485883314178
RDS supported extensions https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html
RUM https://github.com/postgrespro/rum
pg_repack https://github.com/reorg/pg_repack
PGXN https://pgxn.org/
pgTrunk by CoreDB https://pgtrunk.io/
Dbdev by Supabase https://supabase.com/blog/dbdev
StackGres https://github.com/ongres/stackgres
pg_tle by AWS https://github.com/aws/pg_tle
Modern Postgres monitoring (slides from Nikolay’s tutorial) https://twitter.com/samokhvalov/status/1664686535562625034
Awesome Postgres https://github.com/dhamaniasad/awesome-postgres

~~~

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!

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

Zero-downtime migrations

Nikolay Samokhvalov and Michael Christofides — Fri, 02 Jun 2023 13:30:00 +0100

Nikolay and Michael discuss zero-downtime schema migrations — why they're a challenge, a variety of different cases, and some things you can do to achieve them.

Here are links to a few things we mentioned:

Comparison of JOINS: MongoDB vs. PostgreSQL (blog post by Michael Stonebraker and Álvaro Hernández)
Common DB schema change mistakes (blog post by Nikolay)
lock_timeout and retries (blog post by Nikolay)
lock_timeout
Fast Column Creation with Defaults (blog post by Brandur)
Database Schema Changes Without Downtime (new version of blog post by Braintree)
Zero-downtime Postgres migrations - the hard parts (blog post from GoCardless)
GitLab migration_helpers.rb
GitLab migration style guide
dblab

~~~

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!

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

Parallelism

Nikolay Samokhvalov and Michael Christofides — Fri, 26 May 2023 13:30:00 +0100

Nikolay and Michael discuss parallelism — both parallel query execution and other parallel operations.

Here are links to a few things we mentioned:

Parallel query (docs)
Parallelism in PostgreSQL 11 (talk by Thomas Munro)
Parallelism in PostgreSQL 15 (talk by Thomas Munro)
Towards Millions TPS (blog post by Alexander Korotkov)
Memory resource consumption (docs)
Our episode about index maintenance
Our episode about partitioning
Patch to make postgres_fdw parallel-safe (by Swarm64)
PostgreSQL Parallelism Do’s and Don’ts (talk by Sebastian Dressler)
Increasing max_parallel_workers_per_gather (blog post by Michael)

~~~

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!

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

Corruption

Nikolay Samokhvalov and Michael Christofides — Fri, 19 May 2023 13:30:00 +0100

Nikolay and Michael discuss database corruption — various types, how they can come about, and what to do (and not do) if you come across it.

Here are links to a few things we mentioned:

The dangers of streaming across versions of glibc (TripAdvisor mailing list thread)
The floor is Java meme
Data Corruption talk by Sebastian Webber (on Postgres TV)
Data corruption monitoring & troubleshooting talk by Andrey Borodin (on Postgres TV)
Postgres data corruption (document from Nikolay)
Data Corruption and Bugs Runbook (document from Nikolay)
Corruption (Postgres wiki)
Checksums
pg_checksums
Original pg_checksums (by Credativ)
amcheck
Our episode on index maintenance
14.4 release notes about create index / reindex concurrently issue and fix
amcheck to check unique constraints in btree indexes (Commitfest entry)
amcheck verification of GiST and GIN (Commitfest entry)
How to corrupt your Postgres database (blog post from Cybertec)
Christophe Pettus talk
Christophe Pettus slides
pg_hexedit
pageinspect
pg_catcheck

~~~

What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, 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

ChatGPT x  PostgreSQL

Nikolay Samokhvalov and Michael Christofides — Fri, 12 May 2023 13:30:00 +0100

Nikolay and Michael discuss using ChatGPT for Postgres tasks — should you, if so what for, and some things to be mindful of!

Here are links to a few things we mentioned:

ChatGPT
Nikolay’s polls on Twitter and on LinkedIn
The Art of PostgreSQL (book by Dimitri Fontaine)
SQL Performance Explained (book by Markus Winand)
Nikolay’s YouTube correction about deletes and index amplification
Don’t use ChatGPT to solve problems (blog post by Christophe Pettus)
Query optimization session with ChatGPT, Michael, and Nikolay (on YouTube)
DBeaver SmartAssistance feature
Depesz anonymization feature

~~~

What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, 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

pg_stat_statements

Nikolay Samokhvalov and Michael Christofides — Fri, 05 May 2023 14:15:00 +0100

Nikolay and Michael discuss pg_stat_statements — why everyone should use it, but also some downsides!

Here are links to a few things we mentioned:

pg_stat_statements (docs)
pg_stat_statements (PgPedia)
PGSQL Phriday
Observer effect in pg_stat_statements and pg_stat_kcache (Postgres Hacking session on Postgres TV)
track_io_timing (docs)
Overhead comment (by Tom Kate, via Jeremy Schneider)
pg_stat_monitor
PGCon
Our episode on query analysis
Marginalia

~~~

What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, 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

auto_explain

Nikolay Samokhvalov and Michael Christofides — Fri, 28 Apr 2023 13:30:00 +0100

Nikolay and Michael discuss auto_explain — what it is, how it can help, and how to check it's overhead.

Here are links to a few things we mentioned:

auto_explain (docs)
ScaleGrid guide to auto_explain
Can auto_explain, with timing, have low overhead? (Blog post by Michael)
pgBadger
pg_stat_monitor
EXPLAIN ANALYZE may be lying to you (blog post by Álvaro from Ongres)
pg_test_timing
Our episode on benchmarking
Database Lab Engine

~~~

What did you like or not like? What should we discuss next time? Let us know on YouTube, on social media, 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

Queues in Postgres

Nikolay Samokhvalov and Michael Christofides — Fri, 21 Apr 2023 13:30:00 +0100

Nikolay and Michael discuss queues in Postgres — the pros and cons vs dedicated queuing tools, and some tips for scaling.

A couple of apologies-in-advance:

Near the end, we incorrectly say "idempotent" when we meant "stateless", and also 50 TPS instead of 500 TPS
We also had a couple of audio issues, sorry!

Here are links to a few things we mentioned:

Recent discussion on Hacker News
PgQ
What is SKIP LOCKED (blog post by Craig Ringer)
autovacuum
Postgres queues (blog post by Brandur)
pg_repack
Our episode on partitioning
Nikolay’s Twitter poll
Subtransactions Considered Harmful (blog post by Nikolay)

~~~

What did you like or not like? What should we discuss next time? Let us know on social media, 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

Read-only considerations

Nikolay Samokhvalov and Michael Christofides — Fri, 14 Apr 2023 13:30:00 +0100

Nikolay and Michael discuss a listener request — special considerations for databases that are used in a read-only mode all day, and get an update at night with additional data.

Here are links to a few things we mentioned:

Index-only scans
Vacuum
UK Covid-19 dashboard
pg_repack
Partitioning
Our episode on BRIN indexes
Always load sorted data (blog post by Haki Benita)
GIN indexes: the good and the bad (blog post by Lukas Fittl)
Our episode on materialised views
pg_buffercache
Towards Millions TPS (blog post by Alexander Korotkov)
Postgres WASM (by Snaplet and Supabase)
Yugabyte
AWS Aurora
Continuous Archiving and Point-in-Time Recovery (docs)
Our episode on checkpoint tuning
Our episode on partitioning
PgQ
Neon branching
Database Lab Engine
Cluster

~~~

What did you like or not like? What should we discuss next time? Let us know on social media, 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

Partitioning

Nikolay Samokhvalov and Michael Christofides — Fri, 07 Apr 2023 13:30:00 +0100

Nikolay and Michael discuss table partitioning — what it is, why and when it's helpful, and some considerations for your partition key.

Here are links to a few things we mentioned:

Partitioning docs
pg_partman
Index maintenance episode
Timescale partitioning
pg_cron
Xtreme PostgreSQL (talk by Christophe Pettus)
Database Antipatterns (also by Christophe, slides 46-49)
Understanding an outage (blog post by Duffel)

~~~

What did you like or not like? What should we discuss next time? Let us know on social media, 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

Peter Zaitsev

Nikolay Samokhvalov and Michael Christofides — Fri, 31 Mar 2023 16:12:52 +0100

This week we're sharing an edited version of Nikolay's recent interview with Peter Zaitsev from Percona — they discuss MySQL vs Postgres, Percona’s success, open source licenses, FerretDB, and databases on Kubernetes… phew!

And here are some links to a few things mentioned:

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

What did you like or not like? What should we discuss next time? Let us know on social media, 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

psql vs GUIs

Nikolay Samokhvalov and Michael Christofides — Fri, 24 Mar 2023 13:30:00 +0000

Nikolay and Michael discuss command line and graphical user interfaces for Postgres — what they are, some tips and tricks for learning, and what we each use and prefer.

Here are links to a few things we mentioned:

psql (docs)
psql is awesome! (talk by Lætitia Avrot)
psql tips (site by Lætitia Avrot)
pgAdmin
Postico
DBeaver
DataGrip
PgManage (new Command Prompt fork of OmniDB)
PopSQL
postgres_dba
pspg
Materialized views episode
pgcli

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

What did you like or not like? What should we discuss next time? Let us know on social media, 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

Upgrades

Nikolay Samokhvalov and Michael Christofides — Fri, 17 Mar 2023 13:30:00 +0000

Nikolay and Michael discuss major and minor version Postgres upgrades — what they are, how often they come out, and how regularly we should be upgrading.

Here are links to a few things we mentioned:

Postgres versioning policy
why-upgrade (by depesz)
postgresqlco.nf (by Ongres)
postgresql.conf comparison (by Rustproof Labs)
pg_upgrade
Logical replication
CHECKPOINT
amcheck
Locale data changes (e.g. glibc upgrades)
ANALYZE
Upgrades are hard (summary of panel discussion by Andreas 'ads' Scherbaum)
spilo
Recent pgsql hackers discussion about using logical and pg_upgrade together

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

What did you like or not like? What should we discuss next time? Let us know on social media, 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

Wait events

Nikolay Samokhvalov and Michael Christofides — Fri, 10 Mar 2023 13:30:00 +0000

Nikolay and Michael discuss wait events — what they are, why we have them, and how to use them to help diagnose performance issues.

Here are links to a few things we mentioned:

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

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

TOAST

Nikolay Samokhvalov and Michael Christofides — Fri, 03 Mar 2023 13:30:00 +0000

Nikolay and Michael discuss TOAST (The Oversized-Attribute Storage Technique) — what it is, how it works, and some general things to be aware of.

Here are links to a few things we mentioned:

TOAST docs
TOAST wiki
Hussein Nasser on rows per page (Twitter)
Toasting in action (dbi services blog)
Interview with Peter Zaitsev (Postgres TV)
Building columnar compression in a row-oriented database (Timescale blog post)
The Surprising Impact of Medium-Size Texts on PostgreSQL Performance (blog post by Haki Benita)
PostgreSQL at Scale: Saving Space Basically for Free (blog post by Braintree on column Tetris)
postgres_dba alignment padding query

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

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

JSON

Nikolay Samokhvalov and Michael Christofides — Fri, 24 Feb 2023 13:30:00 +0000

Nikolay and Michael discuss JSON — our options for storing it in Postgres, whether or when we should, as well as a brief intro and some tips for JSON functions available.

Here are links to a few things we mentioned:

hstore
XML type
XML functions
JSON types
JSON functions
JSONB indexing
NULLS episode
Why Postgres is popular episode
PostgreSQL 12 release notes
What’s New in SQL:2016 (blog post by Markus Winand)
SQL/JSON is postponed (blog post by depesz)
JSON[b] Roadmap (talk by Oleg Bartunov)
Slides, with benchmarks
RUM access method
JSON in PostgreSQL: how to use it right (blog post by Laurenz Albe from Cybertec)
pg_jsonschema
TOAST_TUPLE_TARGET and TOAST_TUPLE_THRESHOLD
The Surprising Impact of Medium-Size Texts on PostgreSQL Performance (blog post by Haki Benita)
Aggregate functions
How to store and index json data (blog post by ScaleGrid)
When to avoid JSONB (blog post by Heap)
FerretDB

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

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

Real-time analytics

Nikolay Samokhvalov and Michael Christofides — Fri, 17 Feb 2023 13:30:00 +0000

Nikolay and Michael discuss real-time analytics — what it means, what the options are, and some tips if you're trying to implement it within Postgres.

Here are links to a few things we mentioned:

Loose index scan / skip scan with recursive CTE (wiki)
Zheap (wiki)
cstore_fdw (now part of Citus)
Timescale compression docs
Hydra founders interview (on Postgres TV)
Materialised views episode
pg_ivm
Timescale continuous aggregates docs
Clickhouse
Snowflake
Replication episode
Timescale bottomless storage on S3 (blog post)
pg_partman
Querying Postgres from DuckDB (blog post)
Heap blog (filter by “Engineering”)
Incremental View Maintenance (wiki)
PostgreSQL HyperLogLog
Faster counting (by Joe Nelson on the Citus 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

Benchmarking

Nikolay Samokhvalov and Michael Christofides — Fri, 10 Feb 2023 13:30:00 +0000

Nikolay and Michael discuss benchmarking — reasons to do it, and some approaches, tools, and resources that can help.

Here are links to a few things we mentioned:

Towards Millions TPS (blog post by Alexander Korotkov)
Episode on testing
Episode on buffers
pgbench
sysbench
Improving Postgres Connection Scalability (blog post by Andres Freund)
pgreplay
pgreplay-go
JMeter
pg_qualstats
pg_query
Database experimenting/benchmarking (talk by Nikolay, 2018)
Database testing (talk by Nikolay at PGCon, 2022)
Systems Performance (Brendan Gregg’s book, chapter 12)
fio
Netdata
Subtransactions Considered Harmful (blog post by Nikolay including Netdata exports)
WAL compression benchmarks (by Vitaly from Postgres.ai)
Dumping/restoring a 1 TiB database benchmarks (by Vitaly from Postgres.ai)
PostgreSQL on EXT3/4, XFS, BTRFS and ZFS (talk slides from Tomas Vondra)
Insert benchmark on ARM and x86 cloud servers (blog post by Mark Callaghan)

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

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

Default configuration

Nikolay Samokhvalov and Michael Christofides — Fri, 03 Feb 2023 13:30:00 +0000

Nikolay and Michael discuss the default config — some tools and principles you can use to customise it, as well as several parameters you probably always want to change.

Here are links to a few things we mentioned:

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

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

Infra cost optimization

Nikolay Samokhvalov and Michael Christofides — Fri, 27 Jan 2023 13:30:00 +0000

Here are links to a few things we mentioned:

The Cost of Cloud, a Trillion Dollar Paradox (blog post from Andreessen Horowitz)
OVH
Hetzner
postgresql_cluster
Why we're leaving the cloud (blog post by DHH from Basecamp)
Managed services vs. DIY episode
ec2instances.info
Vantage
Postgres TV episode with Everett Berry from Vantage
Migrating to Aurora: easy except the bill (blog post by Kimberly Nicholls from Gridium)
Database Lab Engine
Postgres.ai consulting
Netdata
sysbench
fio
Query macro analysis episode
Top queries by buffers (Gist from Michael)

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

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

Auditing

Nikolay Samokhvalov and Michael Christofides — Fri, 20 Jan 2023 13:30:00 +0000

Here are links to a few things we mentioned:

Crunchy Data PostgreSQL Security Technical Implementation Guide (STIG)
pgAudit (site)
pgAudit (repo)
noset (extension)
Splunk
Kibana
Timescale
CREATE TRIGGER docs
Transition table triggers (blog post by David Fetter)
Table Audit (blog post by Lorenzo Alberton)
Row change auditing options (blog post by CYBERTEC)
Hydra founders interview (on Postgres TV)
max_slot_wal_keep_size
eBPF
Building a perf-like tool for PostgreSQL (talk by Ronan Dunklau)
Party tricks for PostgreSQL: perf, ftrace and bpftrace (talk by Dmitry Dolgov)

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

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

Copying a database

Nikolay Samokhvalov and Michael Christofides — Fri, 13 Jan 2023 13:30:00 +0000

Here are links to a few things we mentioned:

pg_dump
pg_restore
pgcopydb (tool by Dimitri Fontaine)
pg_dump and external snapshots (blog post by Michael Paquier)
Systems Performance (book by Brendan Gregg)
Performance troubleshooting methodologies (two part talk by Brendan Gregg)
pg_basebackup
wal-g
pgBackRest
Database Lab Engine
Database branching episode

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

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

Transaction ID wraparound

Nikolay Samokhvalov and Michael Christofides — Fri, 06 Jan 2023 13:30:00 +0000

Here are links to a few things we mentioned:

The Internals of PostgreSQL chapter 5 (book by Hironobu SUZUKI)
PostgreSQL 14 internals chapter 7 (book by Egor Rogov)
Transaction ID Wraparound (blog post from Sentry)
What We Learned from the Recent Mandrill Outage (blog post from Mailchimp)
How to simulate transaction ID wraparound (blog post by Prashant Dixit)
Add 64-bit XIDs into PostgreSQL 16 (commitfest entry)
Partitioning (docs)
Consider using VACUUM’s INDEX_CLEANUP option (tweet by Peter Geoghan)
Add wraparound failsafe to VACUUM (commit)
Do you vacuum everyday? (talk by Hannu Krosing)
Multixacts wraparound monitoring (mailing list thread)
Subtransactions Considered Harmful (blog post by Nikolay)
Buffer management in PostgreSQL (talk by Alexander Korotkov)
OrioleDB
pageinspect
pg_hexedit
pg_visibility
Visibility Map (docs)

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

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

Postgres year in review 2022

Nikolay Samokhvalov and Michael Christofides — Fri, 30 Dec 2022 13:30:00 +0000

Here are links to a few things we mentioned:

1. Startups building momentum

2. Educational resources

Postgres FM started 👋
Postgres TV became more active (including topic playlists)
Tobias Petry tips on Twitter and SQL for Devs
Hussein Nasser YouTube channel (backend engineering)
Postgres Weekly (newsletter)

3. Sharding progress

4. Database branching is coming

5. Postgres is everywhere

All cloud providers
AlloyDB announced
Kubernetes operators (comparison blog post)

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

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

Row estimates

Nikolay Samokhvalov and Michael Christofides — Fri, 23 Dec 2022 13:30:00 +0000

Here are links to a few things we mentioned:

ANALYZE (docs)
Autovacuum config (docs)
Statistics used by the planner (docs)
CREATE STATISTICS (docs)
Row count estimates (pgMustard blog post)
pg_hint_plan
Optimizer methodology (talk by Robert Haas)
Tomáš Vondra on statistics and hints (an excellent interview we forgot to mention, sorry!)

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

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

Replication

Nikolay Samokhvalov and Michael Christofides — Fri, 16 Dec 2022 14:53:46 +0000

Here are links to a few things we mentioned:

Replication (docs)
9.0 release notes (including built-in replication)
Slony
Londiste
pg_is_in_recovery (pgPedia page)
Patroni
Multiple Databases with Active Record
hot_standby_feedback (docs)
max_standby_archive_delay or max_standby_streaming_delay (for WAL-shipping and WAL-streaming respectively)
synchronous_commit
Synchronous replicas
Database Lab Engine
Neon Branching
Past, Present, and Future of Logical Replication (Postgres TV with Amit Kapila)
Failover of logical replication slots in Patroni (Postgres TV with Alexander Kukushkin)
PostgreSQL Subtransactions Considered Harmful (blog post by Nikolay)
Why we spent the last month eliminating PostgreSQL subtransactions (blog post by GitLab)
Fivetran

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

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

Timestamps

Nikolay Samokhvalov and Michael Christofides — Fri, 09 Dec 2022 13:30:00 +0000

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

PostgREST

Nikolay Samokhvalov and Michael Christofides — Fri, 02 Dec 2022 15:59:16 +0000

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

Materialized views

Nikolay Samokhvalov and Michael Christofides — Fri, 25 Nov 2022 13:30:00 +0000

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

HOT updates

Nikolay Samokhvalov and Michael Christofides — Fri, 18 Nov 2022 13:30:00 +0000

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

Database branching

Nikolay Samokhvalov and Michael Christofides — Fri, 11 Nov 2022 13:43:02 +0000

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

Version control for databases

Nikolay Samokhvalov and Michael Christofides — Fri, 04 Nov 2022 15:40:03 +0000

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

Contributing to Postgres

Nikolay Samokhvalov and Michael Christofides — Fri, 28 Oct 2022 21:32:22 +0100

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

Stored procedures

Nikolay Samokhvalov and Michael Christofides — Fri, 21 Oct 2022 13:30:00 +0100

Here are links to a few things we mentioned:

Extending SQL
PostgREST
PL/pgSQL
EdgeDB
pgTAP
Sqitch
Flyway
Liquibase
client_min_messages
log_min_messages
RAISE DEBUG
Simplify: move code into database functions (blog post by Derek Sivers)
PL/sh
Supabase
auto_explain.log_nested_statements
Hasura

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

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

PostgreSQL 15

Nikolay Samokhvalov and Michael Christofides — Fri, 14 Oct 2022 13:30:00 +0100

Here are links to a few things we mentioned:

PostgreSQL 15 release notes
Speeding up sort performance in Postgres 15 (blog post by David Rowley)
Past, Present, and Future of Logical Replication (talk by Amit Kapila)
Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert)
Do you vacuum everyday? (talk by Hannu Krosing)
Why upgrade PostgreSQL? (by depesz)

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas 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

102 Query optimization

Nikolay Samokhvalov and Michael Christofides — Fri, 07 Oct 2022 13:30:00 +0100

Here are links to a few things we mentioned:

pg_plan_advsr
How partial, covering, and multicolumn indexes may slow down UPDATEs (blog post by Nikolay)
Why Uber Switched from Postgres to MySQL (blog post)
pganalyze index advisor
Nancy bot (project is not active)
pgreplay
pgreplay go
Real Application Testing on YugabyteDB with pgreplay (blog post by Franck Pachot)
pg_query
Database Lab thin clones
Migrating to Aurora: easy except the bill (blog post by Kimberley Nicholls)

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas 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

Why is Postgres popular?

Nikolay Samokhvalov and Michael Christofides — Fri, 30 Sep 2022 13:30:00 +0100

This episode was badly affected by internet issues. Hopefully the edit came out ok, but the quality should be back to a better level from next week.

Here are links to a few things we mentioned:

Monthly blog event, PGSQL Phriday (blog post from Ryan Booz)
Who or what made Postgres cool? (tweet from Kenneth Cassel)
PostGIS
Acquisition of Sun by Oracle
DB-Engines trend
Hacker News hiring trends
Supabase on GitHub (nearly 40k stars)
How I Built This (podcast)

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @PostgresFM or by commenting on our topic ideas 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

Index maintenance

Nikolay Samokhvalov and Michael Christofides — Fri, 23 Sep 2022 14:15:00 +0100

Important correction from this episode: amcheck promises no false positives, not no false negatives, sorry!

Here are links to a few things we mentioned:

pgstattuple
pg_repack
postgres-checkup
Workload Analysis for GitLab.com
Bloat Analysis (also from GitLab)
Bloat, pg_repack, and deferred constraints (blog post by Miro)
amcheck
Peter Geoghegan interview (on Postgres TV)

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas 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 elephant artwork

Query macro analysis intro

Nikolay Samokhvalov and Michael Christofides — Fri, 16 Sep 2022 16:18:22 +0100

Here are links to a few things we mentioned:

pg_stat_statements
pgFouine
pgBadger
pg_query
pg_stat_activity
auto_explain
Can auto_explain (with timing) have low overhead? (blog post by Michael)
track_io_timing
pgbench
PgHero
pgCenter
pgwatch2 (Postgres AI edition)
pg_stat_kcache
PASH Viewer

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides or by commenting on our topic ideas 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

WAL and checkpoint tuning

Nikolay Samokhvalov and Michael Christofides — Fri, 09 Sep 2022 13:30:00 +0100

Here are links to a few things we mentioned:

LSN (log sequence number)
On the impact of full-page writes (blog post by Tomas Vondra)
Deep dive on Aurora Postgres (talk by Grant McAlister)
Netdata monitoring
PostgresqlCO.NF
pgPedia

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides 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 elephant artwork

Intro to query optimization

Nikolay Samokhvalov and Michael Christofides — Fri, 02 Sep 2022 10:30:00 +0100

Here are links to a few things we mentioned:

Using EXPLAIN (PostgreSQL documentation)
explain.depesz.com
explain.dalibo.com
pgMustard
EverSQL
pganalyze
pg_stat_monitor (Extension by Percona)
Recent thread on hackers mailing list about plan_id in pg_stat_activity
auto_explain
EXPLAIN observer effect (Ongres blog post by Álvaro Hernández)
auto_explain overhead (blog post by Michael)
pg_test_timing
Database Lab Engine (for thin clones)
Our previous episode on BUFFERS
EXPLAIN Explained (talk by Josh Berkus)
A beginner's guide to EXPLAIN (talk by Michael)
A deeper dive into EXPLAIN (talk by Michael)
EXPLAIN glossary (pgMustard docs)
Topic suggestions document

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides 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 elephant artwork

How to become a DBA

Nikolay Samokhvalov and Michael Christofides — Fri, 26 Aug 2022 13:30:48 +0100

And few things we mentioned:

Topic request on Reddit — thanks HerbyHoover!
Haki Benita's blog
PostgreSQL documentation (table of contents)
Planet PostgreSQL (blog aggregator)
MVCC Unmasked (by Bruce Momjian)
The Internals of PostgreSQL (by Hironobu SUZUKI)
PostgreSQL 14 Internals — parts I and II (by Egor Rogov)
Cybertec blog
modern-sql.com (by Markus Winand)
use-the-index-luke.com (by Markus Winand)
The Art of PostgreSQL (by Dimitri Fontaine)
explain.depesz.com
explain.dalibo.com

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

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

Monitoring checklist

Nikolay Samokhvalov and Michael Christofides — Fri, 19 Aug 2022 13:30:00 +0100

Monitoring checklist (dashboard 1):

TPS and (optional but also desired) QPS
Latency (query duration) — at least average. Better: histogram, percentiles
Connections (sessions) — stacked graph of session counts by state (first of all: active and idle-in-transaction; also interesting: idle, others) and how far the sum is from max_connection (+pool size for PgBouncer).
Longest transactions (max transaction age or top-n transactions by age), excluding autovacuum activity
Commits vs rollbacks — how many transactions are rolled back
Transactions left till transaction ID wraparound
Replication lags / bytes in replication slot / unused replication slots
Count of WALs waiting to be archived (archiving lag)
WAL generation rates
Locks and deadlocks
Basic query analysis graph (top-n by total_time or by mean_time?)
Basic wait event analysis (a.k.a. “active session analysis” or “performance insights”)

And links to a few things we mentioned:

Postgres monitoring review checklist (community document)
pgstats.dev
Improving Postgres Connection Scalability: Snapshots (blog post by Andres Freund)
Transaction ID Wraparound in Postgres (blog post by David Cramer)
Subtransactions Considered Harmful (blog post by Nikolay)
datadoghq.com
pgwatch2 (Postgres.ai Edition)

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

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

Vacuum

Nikolay Samokhvalov and Michael Christofides — Fri, 12 Aug 2022 13:50:00 +0100

Here are links to a few things we mentioned:

Do you vacuum everyday? (talk by Hannu Krosing)
Autovacuum tuning (EDB guide)
When autovacuum does not vacuum (2ndQuadrant blog post by Tomas Vondra)
Autovacuum tuning basics (old 2ndQuadrant blog post)
Discussion with Anastasia Lubennikova (on RuPostgres, in Russian)
B-tree indexes (talk by Anastasia Lubennikova, in English)
Discussion with Peter Geoghegan (on Postgres TV)
pg_repack
pg_squeeze

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

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

NULLs: the good, the bad, the ugly, and the unknown

Nikolay Samokhvalov and Michael Christofides — Fri, 05 Aug 2022 14:00:00 +0100

Here are links to a few things we mentioned:

Three-valued_logic (Wikipedia)
Postgres 15 improves UNIQUE and NULL (blog post by Ryan Lambert)
Practical SQL for Data Analysis — Interpolation (blog post by Haki Benita)
What is the deal with NULLs? (blog post by Jeff Davis)
NULL in SQL: Indicating the Absence of Data (Markus Winand on Modern SQL)
The Art of PostgreSQL (book by Dimitri Fontaine)

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

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

BUFFERS by default

Nikolay Samokhvalov and Michael Christofides — Fri, 29 Jul 2022 13:40:14 +0100

Here are links to a few things we mentioned:

EXPLAIN parameters (PostgreSQL documentation)
EXPLAIN (ANALYZE) needs BUFFERS (blog post by Nikolay)
Using BUFFERS for query optimization (blog post by Michael)
H3 indexes on PostGIS data (blog post by Ryan Lambert)
Turning BUFFERS on by default (latest patch)
pgMustard
explain.depesz.com
explain.dalibo.com
Database Lab Engine

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

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

BRIN indexes

Nikolay Samokhvalov and Michael Christofides — Thu, 21 Jul 2022 18:02:52 +0100

Here are links to the two main resources we mentioned:

A few other things we mentioned:

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

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

Managed services vs. DIY

Nikolay Samokhvalov and Michael Christofides — Thu, 14 Jul 2022 14:01:01 +0100

A well as discussing pros and cons, we mentioned a LOT of different providers and tools, and a few good articles/videos too. 😅

Here are links to most of them, roughly in the order they came up:

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

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

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

Slow queries and slow transactions

Nikolay Samokhvalov and Michael Christofides — Tue, 05 Jul 2022 20:29:49 +0100

What did you like or not like? What should we discuss next time? Let us know by tweeting us on @samokhvalov and @michristofides

If you would like to share this episode, here's a good link (and thank you!)

We also have an uncut video version on YouTube.

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