powered byiMasterstutoriais · referência · código em portuguêsPatrocínio
Banco de dados

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.

Por Patrícia GuimarãesColunista8 min de leitura
Imagem gerada por IA

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 é:

sql
-- 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:

sql
-- 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 é:

bash
pgbench -c 32 -j 32 -T 60 \
 -D a=random(1, 99900) \
 -f temp-table.sql dbname

O 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ãoTPSMensagens de invalidação por transação
CTE, sem tabela869970
Tabela temp única, on commit delete rows178665
Tabela temp única, truncate a cada transação440514
create temp table ... on commit drop3783132
Tabela temp única, delete a cada transação15140

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_class para 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 coluna text adicionada e as colunas da tabela TOAST;
  • 1 linha em pg_index;
  • 6 linhas em pg_depend, que ligam tudo isso para o DROP conseguir 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_attribute e companhia raramente encolhem, porque VACUUM só 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 com VACUUM, como o time do Kirk Wolak precisou fazer manualmente;
  • enquanto essa sessão não limpa ou derruba a tabela, o freeze do 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 ociosasTPS (alvo: 500)LWLock:SInvalWrite no tempo ativo dos escritoresResets completos de cache por segundo
05021,3%0,1
100050254%49
300027486%194
5000127 (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 é:

  1. wait_event igual a SInvalWrite na active session history ou em pg_stat_activity, amostrado ao longo do tempo;
  2. número de conexões ociosas contra max_connections (select count(*) from pg_stat_activity where state = 'idle';);
  3. taxa de delete em catálogos como pg_attribute e pg_class, visível em pg_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 DELETE ou TRUNCATE explí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

PatrocínioContinue estudando Banco de dadosTrilhas completas, projetos e exercícios na DevMedia.Travou em algum passo?Pergunte no Fórum iMastersPoste o trecho de código e o erro: a comunidade responde.Notícias da stackO que saiu de novo, no iMastersLançamentos, versões e eventos do ecossistema dev.

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.

Patrícia Guimarães

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.

Mais de Patrícia Guimarães

Newsletter

Os melhores tutoriais da semana, no seu e-mail

Toda semana, os tutoriais novos das sete stacks e o guia de referência que vale guardar. Gratuita, sem spam.