Postgres FM | Performance cliffs

April 4, 2025 • 38 Minutes

Performance cliffs

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: