Postgres FM

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

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 

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 

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

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

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

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

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 

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 

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