Aller au contenu

The hidden costs of CREATE TEMP TABLE

13 novembre 2026 · 39 min

Notes de l’épisode

Tip: watch on YouTube. Since episode 193, the video shows charts and illustrations as we talk. https://www.youtube.com/watch?v=gAdhpYewLZ8

Nik and Kirk discuss the hidden costs of CREATE TEMP TABLE: dead tuples and catalog bloat, and the shared invalidation queue that every session has to read. They cover why thousands of idle sessions make it much worse, what it looks like in wait events, and what to use instead.

Here are some links to things they mentioned:
Times are podcast times of the first mention.


Main link


Michael


Episodes


Postgres docs


Postgres source and history


Tools


Other


Corrections (checked against the Postgres source after recording)

  • [00:13:58] Two temp tables and an index are not "three records in, three out". Each temp table adds at least 13 catalog rows (pg_class, pg_type, pg_attribute, pg_depend), about 31 if it has a text column, and the index adds more. So 10M queries a day means at least 600 million catalog row changes, not 60M.
  • [00:15:47] sinval means shared invalidation (of the catalog and relation caches), not "snapshot invalidation".
  • [00:18:49] LWLock:LockManager: up to Postgres 17 a session has 16 fast-path lock slots; Postgres 18 has 64 by default and scales them with max_locks_per_transaction. But temp-table DDL takes AccessExclusiveLock, which never uses the fast path, so raising the setting does not make this wait go away (the brief measured 6.6 to 12.7% LockManager with it set to 1024).
  • [00:25:09] The invalidation queue is a circular buffer of 4,096 messages, but writers never wait for readers. When it is half full (2,048 messages), the session that is furthest behind gets a catch-up signal, and it passes the signal on, one session at a time. When it is full, sessions that have not read are reset and must rebuild all their caches. Writers wait on SInvalWriteLock because there is one such lock for the whole cluster, and both a write (which flags every session) and a queue cleanup (which scans every session) hold it.
  • [00:27:26] ON COMMIT DELETE ROWS does not make dead tuples: at commit it truncates the table and its indexes. And ON COMMIT DROP is not slow mostly because of more messages: without idle sessions the cost is catalog changes and file create and unlink; sinval is under 1%.

Clarifications

  • [00:04:02] Temp table data is invisible to other sessions, but the definition and statistics are visible (pg_class, pg_stat_all_tables).
  • [00:05:32] The docs glossary defines bloat, and the definition includes dead row versions.
  • [00:14:56] Wraparound: load is not the condition. Autovacuum never freezes temp tables, so any long-lived temp table holds back datfrozenxid.
  • [00:17:05] "Catalog is global": the catalog is per database and ...

Tous les épisodes de Postgres FM

Paramètres

Affichage

Langue

Détermine les stations affichées en premier sur la page d’accueil.

Historique

Les stations et épisodes écoutés récemment et tes dernières recherches sont seulement enregistrés dans ce navigateur.

Notifications

Un court message quand un podcast que tu suis publie un nouvel épisode. Désactive un podcast précis avec la cloche sur sa page.

Compte

Enregistrez vos favoris – dans l’app et sur le site.

Raccourcis clavier

/
Ouvrir la recherche
Espace
Lecture ou pause
← →
Dans le lecteur : 15 s en arrière, 30 s en avant (épisodes) · dans une rangée : tuile suivante
↑ ↓
Dans une rangée : cible dans la tuile
Début / Fin
Dans une rangée : première ou dernière tuile
Esc
Fermer la fenêtre ou le menu
?
Cet aperçu
En cours de lecture

Voir la station

À suivre

    Ajoutez des épisodes avec « À suivre » – ils seront lus après l'épisode en cours.