Postgres FM

Latest Episodes:

Managed service support Apr 25, 2025

Show notes

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 Apr 11, 2025

Show notes

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 Apr 04, 2025

Show notes

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 Mar 28, 2025

Show notes

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 Mar 21, 2025

Show notes

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 Mar 14, 2025

Show notes

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 Mar 07, 2025

Show notes

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 Feb 28, 2025

Show notes

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 Feb 14, 2025

Show notes

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 Feb 07, 2025

Show notes

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: