Pular para o conteúdo

The hidden costs of CREATE TEMP TABLE

13 de novembro de 2026 · 39 min

Notas do episódio

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

Todos os episódios de Postgres FM

Configurações

Exibição

Idioma

Define quais estações você vê primeiro na página inicial.

Histórico

As rádios e episódios ouvidos recentemente e suas últimas buscas ficam só neste navegador.

Avisos

Um aviso rápido quando um podcast que você segue tem um episódio novo. Desative podcasts individuais pelo sino na página deles.

Conta

Salve favoritos – no app e no site.

Atalhos de teclado

/
Abrir busca
Espaço
Reproduzir ou pausar
← →
No player: voltar 15 s, avançar 30 s (episódios) · na fileira: próximo bloco
↑ ↓
Na fileira: alvo dentro do bloco
Home / End
Na fileira: primeiro ou último bloco
Esc
Fechar janela ou menu
?
Esta visão geral
Tocando agora

Ir para a estação

A seguir

    Adicione episódios com “A seguir” – eles tocam depois do episódio atual.