Tabela temporária e consulta pesada que estouram o disco: work_mem, spill e temp_file_limit

O volume da primária tinha 1 TB e guardava dados e pg_wal juntos, configuração padrão na maioria das instalações. O alerta de disco disparava em 90%, pensado para crescimento de tabelas, que leva semanas. Arquivo temporário não respeita esse ritmo: uma única consulta escreve centenas de megabytes por segundo, e o caminho entre 70% e 100% levou onze minutos.

14:02  Metabase dispara "faturamento por cliente e categoria" (outubro)
       novo filtro de promoção faz join por categoria_id, sem condição de data
       10,3 mi de itens x ~40 promoções por categoria = 412 mi de linhas

14:03  Hash Join com work_mem = 4MB: 32 batches em base/pgsql_tmp
14:09  GROUP BY vira Sort external merge: arquivos de 1 GB, um atrás do outro
14:15  Metabase reexecuta o card por timeout do navegador: segunda cópia da consulta
14:20  disco do volume de dados + pg_wal: 71% -> 88%    (sem alerta abaixo de 90%)
14:31  100%. PANIC: could not write to file "pg_wal/xlogtemp.48121": No space left on device
14:31  postmaster encerra todas as sessões, crash recovery
14:33  banco volta, arquivos temporários apagados no restart: disco em 62%
14:40  "não tem nada no log": log_temp_files = -1, nenhuma linha sobre os 380 GB

Três detalhes tornam esse incidente típico. Primeiro, o disco não encheu por causa de dados, encheu por causa de trabalho intermediário de uma consulta que nem gravava nada. Segundo, quem morreu não foi a consulta, foi o banco inteiro: quando o Postgres não consegue escrever WAL, ele entra em PANIC, porque não pode garantir durabilidade. Terceiro, desde o Postgres 14 o parâmetro remove_temp_files_after_crash vem ligado e o restart limpa o pgsql_tmp. É o comportamento correto para devolver o espaço, e também o motivo de o time ter encontrado um disco saudável e nenhum culpado.

A reexecução às 14h15 merece atenção. O Metabase, como a maioria das ferramentas de BI, reexecuta a consulta quando o usuário recarrega o painel, e a consulta anterior continua rodando no banco até terminar ou ser cancelada. Duas cópias da mesma consulta descontrolada dobram a velocidade com que o disco enche.

work_mem não é a memória de uma sessão nem de uma consulta. É o limite de cada operação de ordenação ou de hash dentro do plano. Uma consulta com três hash joins e um sort pode usar quatro vezes work_mem, e cada worker paralelo tem o seu próprio orçamento. Operações de hash ainda podem usar work_mem multiplicado por hash_mem_multiplier, que vale 2.0 a partir do Postgres 15. Quando a operação passa do limite, ela não falha: continua em disco, escrevendo arquivos em base/pgsql_tmp (ou no tablespace de temp_tablespaces), em segmentos de 1 GB chamados pgsql_tmp<pid>.<n>.

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, c.nome, i.categoria_id, sum(i.valor) AS faturamento
  FROM clientes c
  JOIN pedidos p        ON p.cliente_id = c.id
  JOIN pedidos_itens i  ON i.pedido_id = p.id
  JOIN promocoes pr     ON pr.categoria_id = i.categoria_id
 WHERE p.criado_em >= '2026-10-01' AND p.criado_em < '2026-11-01'
 GROUP BY c.id, c.nome, i.categoria_id;

-- Sort que nao coube em work_mem: external merge com 58,6 GB em disco.
GroupAggregate  (actual time=688120.552..701934.207 rows=1840221 loops=1)
  Group Key: c.id, i.categoria_id
  ->  Sort  (actual time=612034.118..688120.552 rows=412000000 loops=1)
        Sort Key: c.id, i.categoria_id
        Sort Method: external merge  Disk: 61473280kB
        -- temp written em blocos de 8 kB: 7.684.160 x 8 kB = 58,6 GB.
        Buffers: shared hit=1203 read=5821044, temp read=7684160 written=7684160
        ->  Hash Join  (actual time=4120.334..401227.918 rows=412000000 loops=1)
              Hash Cond: (i.categoria_id = pr.categoria_id)
              ->  Hash Join  (actual rows=10300000 loops=1)
                    ...
              ->  Hash  (actual rows=2400 loops=1)
                    -- Hash join que precisou particionar: 32 batches em vez de 1.
                    Buckets: 262144  Batches: 32 (originally 1)  Memory Usage: 8193kB

Leia o plano de baixo para cima. O join com promocoes multiplica 10,3 milhões de itens por cerca de 40 promoções por categoria, porque a condição de vigência da promoção ficou de fora. Daí em diante nenhum ajuste de memória salva a consulta: ordenar 412 milhões de linhas para depois o GROUP BY reduzi-las a 1,8 milhão de grupos, com o faturamento multiplicado por 40 no caminho, é trabalho inútil e ainda produz um número errado, e o disco é só onde esse trabalho fica visível. A linha temp read/written de BUFFERS é a métrica mais honesta, porque mostra o volume real que foi para o disco, inclusive de nós que não exibem Disk.

Cuidado com EXPLAIN ANALYZE em consulta suspeita: ele executa a consulta de verdade e escreve os mesmos 380 GB. Comece com EXPLAIN sem ANALYZE para ver as estimativas de linhas, depois rode ANALYZE com um período de um dia em vez de um mês, sempre com statement_timeout e temp_file_limit definidos na sessão. A correção aqui foi juntar promocoes pela vigência (pr.inicio <= p.criado_em AND pr.fim > p.criado_em) e agregar os itens antes do join. A consulta caiu para 1,9 milhão de linhas intermediárias, terminou em 41 segundos e não escreveu nenhum arquivo temporário com work_mem de 64 MB.

Arquivo temporário de consulta e tabela temporária são coisas diferentes, e a documentação deixa explícito que temp_file_limit não conta o espaço de tabelas temporárias criadas com CREATE TEMP TABLE. Elas são relações de verdade: têm arquivo em disco (base/<oid do banco>/t<id da sessão>_<relfilenode>, ou no tablespace de temp_tablespaces), passam por um cache local da sessão com tamanho definido por temp_buffers, que tem 8 MB por padrão, e não geram WAL. Uma tabela temporária de 200 GB cresce até encher o disco sem nenhum limite de configuração no caminho.

• Pool de conexões prolonga a vida da tabela. Sem ON COMMIT DROP, a tabela temporária só some quando a sessão termina. Atrás de um pool, a sessão pode viver dias, e o espaço fica ocupado depois que o job já acabou.
• Autovacuum não processa tabelas temporárias. Sem ANALYZE manual, o planejador usa estimativas padrão, escolhe nested loop onde deveria usar hash join e a consulta seguinte demora horas.
• Criar e apagar tabelas temporárias em alta frequência incha pg_class e pg_attribute, porque cada tabela insere e remove linhas no catálogo. Já vi pg_attribute com 40 GB em um sistema que criava uma tabela temporária por requisição HTTP.
• Um crash deixa schemas pg_temp_N órfãos. O autovacuum remove essas tabelas depois, mas o espaço só volta quando isso acontece.

-- ETL antigo: a tabela vive enquanto a conexao do pool viver.
CREATE TEMP TABLE staging_pedidos AS
SELECT * FROM pedidos WHERE criado_em >= now() - interval '90 days';

-- ETL corrigido: escopo da transacao, so as colunas usadas, estatistica antes do join.
BEGIN;
CREATE TEMP TABLE staging_pedidos ON COMMIT DROP AS
SELECT id, cliente_id, total
  FROM pedidos
 WHERE criado_em >= now() - interval '90 days';

ANALYZE staging_pedidos;  -- autovacuum nunca analisa tabela temporaria

UPDATE clientes c
   SET total_90d = s.total
  FROM (SELECT cliente_id, sum(total) AS total
          FROM staging_pedidos
         GROUP BY cliente_id) s
 WHERE s.cliente_id = c.id;
COMMIT;  -- a tabela e os arquivos dela somem aqui

A versão corrigida resolve os quatro pontos: ON COMMIT DROP amarra a tabela à transação e não à conexão, selecionar só três colunas reduz o tamanho em dez vezes nesse caso, ANALYZE dá ao planejador o número real de linhas e o job deixa de depender de quando o pool resolve fechar a conexão. Se o ETL usa PgBouncer em transaction pooling, tabela temporária fora de uma transação nem é segura: a próxima instrução pode cair em outra conexão do servidor.

Durante o incidente, a pergunta é qual sessão está escrevendo. O nome de cada arquivo em pgsql_tmp carrega o pid do processo, e pg_ls_tmpdir (Postgres 12+, liberada para o papel pg_monitor) lista os arquivos com tamanho. Basta extrair o pid do nome e cruzar com pg_stat_activity. Depois do incidente, a pergunta muda para qual consulta faz isso com frequência, e aí entram pg_stat_database e pg_stat_statements.

-- Quem esta escrevendo arquivo temporario agora, somado por sessao.
-- Workers paralelos sao agrupados no pid do lider.
WITH arquivos AS (
  SELECT substring(t.name FROM '^pgsql_tmp([0-9]+)')::int AS pid, t.size
    FROM pg_tablespace ts
   CROSS JOIN LATERAL pg_ls_tmpdir(ts.oid) t
   WHERE ts.spcname <> 'pg_global'
), por_sessao AS (
  SELECT COALESCE(a.leader_pid, a.pid) AS pid, sum(f.size)::bigint AS bytes
    FROM arquivos f
    JOIN pg_stat_activity a ON a.pid = f.pid
   GROUP BY 1
)
SELECT s.pid, pg_size_pretty(s.bytes) AS temp_em_uso,
       a.usename, a.application_name,
       now() - a.query_start AS duracao,
       left(a.query, 120) AS consulta
  FROM por_sessao s
  JOIN pg_stat_activity a ON a.pid = s.pid
 ORDER BY s.bytes DESC;

-- Acumulado desde o ultimo reset: quantos arquivos e quantos bytes.
SELECT datname, temp_files, pg_size_pretty(temp_bytes) AS temp_total, stats_reset
  FROM pg_stat_database
 WHERE datname = current_database();

-- As consultas que mais escreveram temp (requer pg_stat_statements).
SELECT queryid, calls,
       pg_size_pretty(temp_blks_written * current_setting('block_size')::bigint) AS temp_escrito,
       left(query, 120) AS consulta
  FROM pg_stat_statements
 ORDER BY temp_blks_written DESC
 LIMIT 10;

-- Tabelas temporarias vivas e o tamanho de cada uma.
SELECT n.nspname AS schema_temp, c.relname,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS tamanho
  FROM pg_class c
  JOIN pg_namespace n ON n.oid = c.relnamespace
 WHERE c.relpersistence = 't' AND c.relkind = 'r'
 ORDER BY pg_total_relation_size(c.oid) DESC
 LIMIT 20;

• Hash join paralelo grava os batches em subdiretórios com sufixo .fileset (.sharedfileset antes do Postgres 15). pg_ls_tmpdir só lista arquivos regulares e deixa esses diretórios de fora. Se a soma da consulta ficar muito abaixo do que o df mostra, rode du -sh no pgsql_tmp para confirmar.
• pg_stat_database.temp_bytes só é incrementado quando o arquivo é fechado. Uma consulta que ainda está escrevendo não aparece ali, por isso ele serve para tendência e não para o incidente em curso.
• log_temp_files registra o caminho e o tamanho de cada arquivo removido, seguido do STATEMENT que o criou. É a única evidência que sobrevive a um crash, porque o log fica fora do pgsql_tmp.
• Para parar a consulta, use pg_cancel_backend(pid). Os arquivos temporários são apagados quando a operação termina. Nunca apague arquivos do pgsql_tmp com rm enquanto o banco está de pé: o processo ainda tem o arquivo aberto, o espaço não volta e a consulta falha de forma imprevisível.

Não existe um parâmetro único que resolva. work_mem global alto parece a saída óbvia, mas é multiplicado por operação, por worker e por conexão: 100 conexões ativas com três operações de hash cada e work_mem de 256 MB podem pedir 150 GB de RAM em um servidor de 64 GB, e o OOM killer derruba o banco do mesmo jeito. O caminho é dar memória a quem precisa, colocar teto em quem pode escapar e tirar o pg_wal do caminho do arquivo temporário.

-- Instancia: registrar todo arquivo temporario de 100 MB ou mais.
ALTER SYSTEM SET log_temp_files = '100MB';
-- Teto por processo para arquivos temporarios de sort e hash.
ALTER SYSTEM SET temp_file_limit = '20GB';
SELECT pg_reload_conf();

-- Arquivos e tabelas temporarias em outro volume, longe do pg_wal.
-- mkdir -p /mnt/pgtemp/data && chown postgres:postgres /mnt/pgtemp/data
CREATE TABLESPACE temp_local LOCATION '/mnt/pgtemp/data';
ALTER SYSTEM SET temp_tablespaces = 'temp_local';
SELECT pg_reload_conf();

-- Papel do BI: mais memoria por operacao, teto de temp e de tempo.
ALTER ROLE metabase SET work_mem = '64MB';
ALTER ROLE metabase SET temp_file_limit = '50GB';
ALTER ROLE metabase SET statement_timeout = '10min';

-- Job de ETL: memoria grande so dentro da transacao.
BEGIN;
SET LOCAL work_mem = '512MB';
SET LOCAL hash_mem_multiplier = 2.0;
INSERT INTO resumo_vendas_mensal (mes, cliente_id, categoria_id, total)
SELECT date_trunc('month', p.criado_em), p.cliente_id, i.categoria_id, sum(i.valor)
  FROM pedidos p
  JOIN pedidos_itens i ON i.pedido_id = p.id
 WHERE p.criado_em >= '2026-10-01' AND p.criado_em < '2026-11-01'
 GROUP BY 1, 2, 3;
COMMIT;  -- Volta ao valor do papel no COMMIT.

temp_file_limit só pode ser alterado por superusuário (ou por quem recebeu GRANT SET no parâmetro, a partir do Postgres 15), o que é proposital: um usuário comum não consegue desligar o próprio teto. Quando a consulta passa do limite, ela recebe "ERROR: temporary file size exceeds temp_file_limit" e os arquivos são removidos. O teto global de 20 GB serve para as aplicações, e o papel do BI recebe 50 GB porque relatórios legítimos chegam perto disso. Com temp_tablespaces em um disco separado, mesmo o pior caso enche apenas esse disco, e o banco continua aceitando escrita. Em serviços gerenciados como RDS, Cloud SQL e Azure, o arquivo temporário costuma ficar no mesmo armazenamento dos dados e o tablespace separado nem sempre está disponível, então temp_file_limit e o monitor passam a ser a defesa principal.

temp_file_limit protege contra uma consulta, mas dez sessões com 20 GB cada somam 200 GB e nenhuma delas passou do limite. O alerta de disco olha o volume inteiro e chega tarde. O monitor abaixo olha especificamente os arquivos temporários de todas as sessões a cada 15 segundos, avisa quando a soma passa de 50 GB com o nome de quem está escrevendo e cancela qualquer consulta que passe de 150 GB. Ele precisa de um papel com pg_monitor, para ler pg_ls_tmpdir e o texto das consultas, e pg_signal_backend, para cancelar.

// temp-watchdog.js
import pg from 'pg';

const GB = 1024 ** 3;
const ALERTA_BYTES = Number(process.env.TEMP_ALERTA_GB ?? 50) * GB;
const CANCELAR_BYTES = Number(process.env.TEMP_CANCELAR_GB ?? 150) * GB;
const INTERVALO_MS = 15_000;
const SILENCIO_MS = 10 * 60_000;

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL, max: 1 });

// Soma dos arquivos temporarios de todas as sessoes, por tablespace.
// pg_ls_tmpdir so lista arquivos regulares: o fileset de hash paralelo fica de fora.
const SQL = `
  WITH arquivos AS (
    SELECT substring(t.name FROM '^pgsql_tmp([0-9]+)')::int AS pid, t.size
      FROM pg_tablespace ts
     CROSS JOIN LATERAL pg_ls_tmpdir(ts.oid) t
     WHERE ts.spcname <> 'pg_global'
  ), por_sessao AS (
    SELECT COALESCE(a.leader_pid, a.pid) AS pid, sum(f.size)::bigint AS bytes
      FROM arquivos f
      JOIN pg_stat_activity a ON a.pid = f.pid
     GROUP BY 1
  )
  SELECT s.pid, s.bytes, a.usename, a.application_name,
         extract(epoch FROM now() - a.query_start)::int AS segundos,
         left(a.query, 200) AS consulta
    FROM por_sessao s
    JOIN pg_stat_activity a ON a.pid = s.pid
   ORDER BY s.bytes DESC`;

const gb = (bytes) => (Number(bytes) / GB).toFixed(1);
let ultimoAlerta = 0;

async function alertar(mensagem) {
  console.warn(mensagem);
  if (!process.env.ALERT_WEBHOOK_URL) return;
  await fetch(process.env.ALERT_WEBHOOK_URL, {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({ content: mensagem.slice(0, 1900) }),
  });
}

async function verificar() {
  const { rows } = await pool.query(SQL);
  const total = rows.reduce((soma, r) => soma + Number(r.bytes), 0);

  // Evita alerta repetido a cada ciclo.
  if (total >= ALERTA_BYTES && Date.now() - ultimoAlerta > SILENCIO_MS) {
    ultimoAlerta = Date.now();
    const maior = rows[0];
    await alertar(
      `[temp] ${gb(total)} GB em arquivos temporarios. Maior: pid ${maior.pid} ` +
        `(${maior.usename}/${maior.application_name}) com ${gb(maior.bytes)} GB ` +
        `ha ${maior.segundos}s: ${maior.consulta}`,
    );
  }

  // Acima do limite duro, cancela a consulta (nao derruba a conexao).
  for (const r of rows.filter((linha) => Number(linha.bytes) >= CANCELAR_BYTES)) {
    const { rows: [res] } = await pool.query('SELECT pg_cancel_backend($1) AS ok', [r.pid]);
    await alertar(`[temp] pid ${r.pid} com ${gb(r.bytes)} GB: cancelado=${res.ok}`);
  }
}

// Nunca sobrepor duas verificacoes.
async function loop() {
  try {
    await verificar();
  } catch (error) {
    console.error('[temp] falha na verificacao', error.message);
  } finally {
    setTimeout(loop, INTERVALO_MS);
  }
}

loop();

1. Crie o papel: CREATE ROLE temp_watchdog LOGIN PASSWORD ... e GRANT pg_monitor, pg_signal_backend TO temp_watchdog. pg_signal_backend não cancela sessões de superusuário, o que é o comportamento desejado.
2. Calibre os limites pelo espaço livre real: o alerta em cerca de 25% do espaço livre e o cancelamento em cerca de 60%, deixando folga para o pg_wal.
3. Rode o monitor fora do servidor do banco, com reinício automático (systemd, container ou o mesmo orquestrador da aplicação), e alerte também se ele parar de reportar.
4. Mantenha o alerta de disco do sistema operacional baseado em velocidade de crescimento, não só em percentual: 5% em cinco minutos é incidente, 5% em uma semana é planejamento.