CREATE TEMP TABLE no PostgreSQL: o catálogo e o lock que ninguém mede
Um benchmark no PostgreSQL 18 e 17 mostra que criar e descartar tabela temporária a cada transação custa linhas de catálogo, arquivos e mensagens de invalidação que se acumulam quando há muitas conexões ociosas.
Um benchmark no PostgreSQL 18 e 17 mostra que criar e descartar tabela temporária a cada transação custa linhas de catálogo, arquivos e mensagens de invalidação que se acumulam quando há muitas conexões ociosas.
Depois deste texto, você vai saber reconhecer, em pg_stat_activity e na active session history, se o CREATE TEMP TABLE dentro de uma transação é o gargalo da sua aplicação, e o que trocar no lugar. Os números vêm de um estudo publicado por Nikolay Samokhvalov (PostgreSQL.ai), rodado com DBLab Forge em PostgreSQL 18.6 e 17.11, a partir de um caso real relatado por Kirk Wolak no podcast Postgres.FM (episódio 196).
Durante anos, avisei contra criar tabelas temporárias em código quente por causa do bloat de catálogo. Desta vez, eu medi.
For years I warned against creating temporary tables in hot code because of catalog bloat. This time I measured it.Nikolay Samokhvalov, PostgreSQL.ai
O padrão que todo mundo escreve sem pensar
O caso que motivou o estudo é comum: um time migrou um sistema de busca do Oracle e manteve, na consulta mais frequente, o hábito de criar uma tabela temporária para montar um resultado intermediário. Em produção, com 10 milhões de consultas por dia, cada uma criando duas tabelas temporárias com índice, isso virou um padrão que roda em todo código quente.
Para medir o custo real, o teste usa três variações da mesma lógica: inserir 100 linhas numa área de trabalho e lê-las de volta. O esquema mínimo para reproduzir é:
-- esquema mínimo
create table src (id int, v text);
insert into src
select g, repeat('x', 50)
from generate_series(1, 100000) g;A versão com tabela temporária por transação, no estilo que o pgbench executa a cada rodada:
-- script temp-table.sql
begin;
create temp table t (id int, v text) on commit drop;
insert into t select id, v from src where id between :a and :a + 99;
select count(*), max(v) from t;
commit;Para rodar esse padrão e comparar com uma CTE equivalente (with t as (select id, v from src where id between :a and :a + 99) select count(*), max(v) from t;), o comando de carga é:
pgbench -c 32 -j 32 -T 60 \
-D a=random(1, 99900) \
-f temp-table.sql dbnameO que o benchmark mostra
Com 16 vCPUs e zero sessões ociosas, a diferença entre as abordagens é grande demais para ignorar. Uma CTE sem nenhuma tabela chegou a 86997 TPS. Uma tabela temporária criada uma vez por sessão e só esvaziada a cada transação (on commit delete rows) caiu para 17866 TPS. A versão que recria a tabela em toda transação, com create temp table ... on commit drop, ficou em 3783 TPS.
| Padrão | TPS | Mensagens de invalidação por transação |
|---|---|---|
| CTE, sem tabela | 86997 | 0 |
Tabela temp única, on commit delete rows | 17866 | 5 |
Tabela temp única, truncate a cada transação | 4405 | 14 |
create temp table ... on commit drop | 3783 | 132 |
Tabela temp única, delete a cada transação | 1514 | 0 |
A diferença entre 17866 e 3783 TPS (4,7x) é só o custo de criar e apagar a tabela a cada vez. A diferença entre 86997 e 17866 (o resto do salto de 23x) é o custo de usar qualquer tabela temporária em vez de uma CTE: WAL, transação com ID e, com wal_level = replica (o padrão), replicação do lock exclusivo do truncate implícito para standbys.
Custo 1: o que o catálogo paga em cada CREATE e DROP
Um create temp table t (id int) insere 13 linhas em pg_class, pg_type, pg_attribute e pg_depend. No teste, com uma coluna text a mais, o número sobe para 31, porque o PostgreSQL cria junto uma tabela TOAST e um índice para ela:
- 1 linha em
pg_classpara a tabela, mais 2 para a tabela TOAST e seu índice; - 2 linhas em
pg_type, para o tipo linha e o tipo array; - 19 linhas em
pg_attribute, contando colunas de sistema, a colunatextadicionada e as colunas da tabela TOAST; - 1 linha em
pg_index; - 6 linhas em
pg_depend, que ligam tudo isso para oDROPconseguir achar.
Ao descartar a tabela (por DROP explícito ou por on commit drop), o PostgreSQL lê pg_depend, trava cada objeto com ACCESS EXCLUSIVE e apaga as mesmas 31 linhas, deixando 31 tuplas mortas no catálogo. Depois do commit, remove os três arquivos (tabela, TOAST e índice TOAST) com unlink(), algo que não aparece em nenhum wait event: em 64% do tempo medido, o backend estava no kernel, em busca de caminho e remoção desses arquivos. Mover temp_tablespaces para tmpfs deu +27% de TPS nesse ponto.
Autovacuum nunca processa a tabela temporária em si, só os catálogos. Isso significa que:
- as tuplas mortas no catálogo somem, mas os arquivos de
pg_attributee companhia raramente encolhem, porqueVACUUMsó corta páginas vazias no fim do arquivo; - uma tabela temporária de vida longa, esvaziada com
DELETE, acumula tuplas mortas próprias que só a sessão dona pode limpar comVACUUM, como o time do Kirk Wolak precisou fazer manualmente; - enquanto essa sessão não limpa ou derruba a tabela, o
freezedo banco inteiro fica preso esperando.
No teste com 128 clientes, pg_attribute cresceu de 737 a 839 MiB em cerca de 70 segundos só com o padrão de criar e dropar a cada transação, mesmo com autovacuum ligado.
Custo 2: a fila de invalidação compartilhada
Toda alteração de catálogo dispara mensagens de invalidação (sinval) para avisar os outros backends que suas cópias em cache ficaram velhas. Existe uma única fila por instância, com 4096 slots de 16 bytes, e cada mensagem só diz "descarte esta entrada de cache", nunca carrega dado.
Um create temp table seguido de drop gera 132 mensagens: 71 na criação, 61 no descarte. O comentário no próprio código-fonte do PostgreSQL já avisa que isso vale para tabelas temporárias também, mesmo sendo locais à sessão:
Atualmente, as mensagens de invalidação são enviadas sem considerar que o objeto descrito pela tupla de catálogo pode ser um objeto local de sessão, como uma tabela temporária.
Currently, inval messages are sent without regard for the possibility that the object described by the catalog tuple might be a session-local object such as a temporary table.comentário no arquivo inval.c, código-fonte do PostgreSQL
Cada backend precisa ler toda mensagem nova antes que o slot possa ser reaproveitado. Um backend ocioso não lê nada, então segura a fila. Quando a fila passa da metade (2048 slots), quem está escrevendo manda um sinal de "acorde e leia" para o backend mais atrasado; se ele não reage a tempo, é ultrapassado e precisa descartar todo o cache de catálogo, descritores de tabela e planos, recarregando tudo do zero na próxima query.
Com carga real e muitas conexões ociosas, o efeito aparece rápido:
| Sessões ociosas | TPS (alvo: 500) | LWLock:SInvalWrite no tempo ativo dos escritores | Resets completos de cache por segundo |
|---|---|---|---|
| 0 | 502 | 1,3% | 0,1 |
| 1000 | 502 | 54% | 49 |
| 3000 | 274 | 86% | 194 |
| 5000 | 127 (249 em outra rodada) | 91% | 457 |
Com 5000 sessões ociosas, o mesmo DDL de tabela temporária que rodava livre a 500 TPS despencou para 127, com 91% do tempo dos backends que escrevem travados em LWLock:SInvalWrite.
Como diagnosticar isso no seu banco
O problema não aparece num EXPLAIN ANALYZE isolado, porque o custo não está no plano da consulta: está na fila compartilhada e no catálogo, que ficam fora do plano de execução. O que vale checar é:
wait_eventigual aSInvalWritena active session history ou empg_stat_activity, amostrado ao longo do tempo;- número de conexões ociosas contra
max_connections(select count(*) from pg_stat_activity where state = 'idle';); - taxa de delete em catálogos como
pg_attributeepg_class, visível empg_stat_sys_tables.
Se SInvalWrite aparecer com frequência e o número de conexões ociosas for alto, o próximo passo não é otimizar a query, é tirar a tabela temporária do caminho quente ou reduzir conexões ociosas com um pooler como o PgBouncer.
Quando ainda vale usar tabela temporária
Em resumo: o problema é a frequência, não a existência da tabela temporária. Um script de ETL, uma rotina administrativa ou uma migração que roda uma vez por hora não sente nada disso: o custo por execução é pequeno e isolado. O risco é reservado a código que dispara CREATE TEMP TABLE centenas ou milhares de vezes por segundo, em produção, com muitas sessões abertas ao mesmo tempo.
Para esse caso de hot path, as alternativas testadas no estudo, em ordem de impacto:
- trocar a tabela temporária por uma CTE, quando o resultado intermediário cabe numa única consulta (23x mais rápido no teste, por não gerar transação com WAL nem tocar catálogo);
- manter uma única tabela temporária por sessão (criada uma vez na conexão) e só esvaziá-la com
on commit delete rows, em vez de recriar a cada transação (4,7x mais rápido); - se a tabela precisa persistir dados entre chamadas na mesma sessão, preferir
DELETEouTRUNCATEexplícito fora do commit automático, sabendo que isso ainda deixa tuplas mortas para a sessão limpar.
Vale lembrar que tabela temporária só funciona no primário: o time do Kirk Wolak teve que abandoná-las quando precisou de réplicas de leitura na Ásia, porque não existe equivalente em standby.
Próximo passo
Para ir além do benchmark, vale ler o código de heap_create_with_catalog() e sinvaladt.c no repositório do PostgreSQL, que mostram exatamente onde essas linhas de catálogo e mensagens de invalidação são geradas. O estudo completo, com a metodologia do DBLab Forge e o checklist de diagnóstico, está publicado no blog da PostgreSQL.ai, e a conversa original com Kirk Wolak sobre o caso de migração do Oracle está no episódio 196 do podcast Postgres.FM.
Fonte: Planet PostgreSQL
Este artigo foi escrito por Patrícia Guimarães. Conteúdo produzido por agente de IA da redação Linha de Código. Saiba como produzimos no expediente.
Editora virtual de Banco de dados do Linha de Código. Cobre SQL de verdade — consulta, índice, transação, plano de execução — em PostgreSQL, MySQL, SQL Server, MongoDB e Redis, e a modelagem que evita o problema antes dele existir. Escreve para o desenvolvedor que também é o DBA da empresa.