Chave estrangeira sem índice: a exclusão que trava a tabela inteira

A primeira investigação quase sempre vai para o lugar errado. O time abre o comando do expurgo, roda o EXPLAIN, vê uma busca por índice em pedidos filtrando por status e data, com custo baixo e estimativa de duas mil linhas, e conclui que o problema não está ali. O plano está certo. O que o plano do DELETE não mostra é o trabalho que acontece depois de cada linha removida, fora do comando que você escreveu, nos gatilhos internos que o banco usa para garantir a integridade referencial.

No PostgreSQL, cada chave estrangeira é implementada por gatilhos de sistema. Quando uma linha de pedidos é excluída, o gatilho da restrição executa, para aquela linha, uma consulta na tabela filha procurando registros que apontam para o pedido removido: um DELETE se a restrição for ON DELETE CASCADE, um UPDATE se for SET NULL, ou uma verificação de existência se for NO ACTION ou RESTRICT. Se a coluna da filha tem índice, essa consulta é uma busca de microssegundos. Se não tem, é uma varredura sequencial da filha inteira. Por linha excluída no pai.

É aí que está o detalhe que engana: o banco cria índice automaticamente para a chave primária e para restrições UNIQUE, que é o lado referenciado, mas não cria nada na coluna que referencia. Declarar a chave estrangeira garante a integridade, não o desempenho de mantê-la. No caso do incidente, a tabela historico_status tinha índice por data para relatórios e nenhum por pedido_id, porque nenhuma tela buscava histórico por pedido no caminho quente. A restrição existia há três anos, com ON DELETE CASCADE, e cada exclusão de pedido pagava uma leitura completa de uma tabela que crescia mais rápido que qualquer outra do sistema.

Custo do expurgo diario (2.000 pedidos cancelados por dia):

  ano 1: historico_status com 2 milhoes de linhas
    2.000 exclusoes x 1 varredura de 2 mi de linhas (~80 ms)  = ~3 min

  ano 3: historico_status com 40 milhoes de linhas
    2.000 exclusoes x 1 varredura de 40 mi de linhas (~1,6 s) = ~53 min

  com indice em historico_status (pedido_id), em qualquer ano:
    2.000 exclusoes x 1 busca no indice (~0,05 ms)            = ~0,1 s

Custo = (linhas excluidas no pai) x (tamanho da tabela filha)
Os dois fatores crescem com o negocio: o tempo cresce com o produto.

A multiplicação explica por que o problema nunca aparece cedo. Em homologação, a filha tem dez mil linhas, a varredura cabe em memória e custa menos de um milissegundo. Nos primeiros meses de produção, o expurgo leva segundos. A degradação é contínua e silenciosa, sem um degrau que dispare alerta, até o dia em que o tempo do job ultrapassa alguma coisa que importa: a janela de manutenção, o intervalo do agendador, o timeout de uma migração ou a paciência do pool de conexões.

Há uma consequência ainda menos intuitiva. Com NO ACTION, que é o padrão quando ninguém escreve nada, excluir um pedido que não tem nenhuma linha na filha custa exatamente a mesma varredura completa. O banco precisa provar a ausência, e sem índice a única forma de provar que nenhuma das quarenta milhões de linhas aponta para aquele pedido é ler todas elas.

A forma mais rápida de confirmar o diagnóstico é o EXPLAIN ANALYZE do próprio DELETE. Ele executa o comando de verdade, então precisa rodar dentro de uma transação que termina em ROLLBACK, e de preferência em uma cópia restaurada do banco, porque mesmo revertido ele segura os bloqueios pelo tempo inteiro da execução. O que interessa não é o plano, e sim as linhas de gatilho que aparecem no final da saída, com o tempo acumulado e o número de chamadas de cada restrição.

-- Rodar numa copia restaurada: EXPLAIN ANALYZE executa o DELETE de verdade
-- e segura os bloqueios ate o ROLLBACK.
BEGIN;

EXPLAIN (ANALYZE, BUFFERS)
DELETE FROM pedidos
WHERE status = 'cancelado'
  AND atualizado_em < now() - interval '90 days';

ROLLBACK;

-- Saida resumida:
--  Delete on pedidos (actual time=41.2..41.2 rows=0 loops=1)
--    ->  Index Scan using pedidos_status_atualizado_idx on pedidos
--          (actual time=0.03..8.9 rows=2000 loops=1)
--  Planning Time: 0.4 ms
--  Trigger for constraint historico_status_pedido_id_fkey: time=3171840.5 calls=2000
--  Trigger for constraint pagamentos_pedido_id_fkey: time=96.1 calls=2000
--  Execution Time: 3171990.3 ms
--
-- O DELETE em si levou 41 ms. Os 53 minutos estao inteiros em uma restricao:
-- 2.000 chamadas, cerca de 1,6 s cada, uma varredura de historico_status por
-- pedido. A restricao de pagamentos tem indice e custa 96 ms no total.

A comparação entre as duas linhas de gatilho é o que fecha o caso sem discussão. As duas restrições recebem o mesmo número de chamadas, uma por pedido removido, e a diferença de quatro ordens de grandeza no tempo vem só da existência do índice. Quando não dá para rodar o EXPLAIN ANALYZE, o sintoma aparece também nas estatísticas: o contador seq_scan da tabela filha em pg_stat_user_tables sobe em milhares durante a janela do job, e seq_tup_read acompanha na casa dos bilhões.

O comportamento varia entre motores, e a variação importa para quem opera mais de um banco ou migra entre eles. Quem vem do MySQL costuma não conhecer o problema porque o InnoDB exige um índice na coluna da chave estrangeira e cria um automaticamente se não houver. Quem vem do Oracle conhece a versão mais agressiva dele, em que a falta de índice faz a exclusão no pai bloquear a tabela filha inteira durante o comando.

O mesmo mecanismo dispara em mais situações do que a exclusão explícita. Um UPDATE que troca o valor da chave primária do pai executa a mesma busca na filha. ON DELETE SET NULL faz um UPDATE na filha por linha do pai, com a mesma varredura. E um expurgo por LGPD, que remove um cliente com cascata para pedidos, que por sua vez cascateia para histórico, pagamentos e eventos, multiplica o problema por cada nível da árvore em que falta índice.

No PostgreSQL, uma exclusão lenta por si só não bloqueia a tabela filha para todo mundo. Ela segura bloqueios de linha nos pedidos removidos, bloqueios de linha nas linhas de histórico que a cascata apaga e um bloqueio ROW EXCLUSIVE nas tabelas envolvidas, que convive com leituras e com outras escritas. Por cinquenta e três minutos isso é um desperdício de disco e de CPU, não uma queda. O que transforma a lentidão em indisponibilidade é o segundo participante: qualquer comando que precise de um bloqueio forte na mesma tabela.

Um ALTER TABLE que adiciona coluna, mesmo sendo uma operação instantânea de metadados, pede ACCESS EXCLUSIVE, o modo que conflita com todos os outros. Ele entra na fila atrás da transação do expurgo e espera. O problema é que a fila de bloqueios é ordenada: todo pedido de bloqueio que chega depois e conflita com o ACCESS EXCLUSIVE pendente fica atrás dele, inclusive um simples INSERT, que sozinho seria compatível com o expurgo. A partir desse instante, a tabela está travada para qualquer acesso, e ela fica assim até o expurgo terminar e a migração rodar.

09:30  expurgo      BEGIN; DELETE FROM pedidos ...
                     ROW EXCLUSIVE em historico_status, dura 53 min
10:05  migracao     ALTER TABLE historico_status ADD COLUMN origem text
                     pede ACCESS EXCLUSIVE -> espera o expurgo
10:05  checkout #1  INSERT INTO historico_status ... -> espera a migracao
10:05  checkout #2  INSERT INTO historico_status ... -> espera a migracao
  ...               toda mudanca de status do sistema entra na fila
10:06  pool de conexoes esgotado, checkout responde 503
10:23  expurgo faz COMMIT -> migracao roda em 40 ms -> fila escoa

Nenhum dos tres comandos e lento sozinho, exceto o expurgo.
A queda nasce da combinacao: transacao longa + bloqueio forte na fila.

Existe um efeito colateral mais lento e que continua depois do incidente. Uma transação aberta por quase uma hora impede o autovacuum de limpar versões mortas de linhas em todo o banco, não só nas tabelas envolvidas, porque o horizonte de visibilidade fica preso no início dela. Tabelas com alta taxa de atualização incham durante a janela e as consultas ficam mais lentas nas horas seguintes, o que costuma ser investigado como um segundo problema sem relação.

Quando o travamento está acontecendo, a pergunta útil não é qual consulta está lenta, e sim quem está esperando quem. A função pg_blocking_pids devolve, para cada sessão, as sessões que a bloqueiam, e cruzar isso com os bloqueios não concedidos mostra a cadeia inteira em uma consulta. O padrão do incidente é inconfundível: centenas de sessões esperando uma única sessão de migração, que por sua vez espera uma única transação antiga.

-- Quem espera quem, com a idade da transacao e o modo de bloqueio pedido.
SELECT
  a.pid,
  pg_blocking_pids(a.pid)   AS bloqueado_por,
  now() - a.xact_start      AS idade_transacao,
  l.mode                    AS modo_pedido,
  l.relation::regclass      AS tabela,
  left(a.query, 60)         AS consulta
FROM pg_stat_activity a
LEFT JOIN pg_locks l
  ON l.pid = a.pid AND NOT l.granted
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.xact_start;

-- Resultado tipico do incidente:
--   pid  | bloqueado_por | idade_transacao | modo_pedido         | consulta
--   8812 | {7710}        | 00:18:02        | AccessExclusiveLock | ALTER TABLE historico_status ...
--   9031 | {8812}        | 00:00:41        | RowExclusiveLock    | INSERT INTO historico_status ...
--   9044 | {8812}        | 00:00:39        | RowExclusiveLock    | INSERT INTO historico_status ...
--   (mais 212 linhas bloqueadas por 8812)
--
-- 7710 e o expurgo. Cancelar a migracao (pg_cancel_backend(8812)) libera
-- a fila em segundos; o expurgo pode continuar enquanto o indice nao existe.

A mitigação imediata é cancelar a migração, não o expurgo. Cancelar o expurgo depois de cinquenta minutos joga fora todo o trabalho e o rollback de uma exclusão grande também leva tempo. Cancelar a migração libera a fila em segundos, e ela pode ser repetida depois. A prevenção estrutural para esse lado do problema é independente do índice: toda migração que pede bloqueio forte deve rodar com lock_timeout curto, de três a cinco segundos, e com retentativa. Uma migração que desiste depois de cinco segundos esperando é um aviso no log do deploy; uma que espera indefinidamente é uma queda.

Esperar o próximo job lento para descobrir a próxima restrição sem índice é a estratégia que mantém o problema vivo. O catálogo do banco tem toda a informação necessária para listar, de uma vez, cada chave estrangeira cuja tabela filha não tem um índice capaz de atender a busca da restrição. O critério correto é mais estrito do que parece: não basta existir um índice que contenha a coluna.

• O índice precisa começar pelas colunas da chave estrangeira. Um índice em (criado_em, pedido_id) não serve para buscar por pedido_id, porque a coluna não é a primeira.
• Em chave composta, as colunas da restrição precisam ocupar as primeiras posições do índice, em qualquer ordem entre elas. Um índice só por tenant_id não atende uma restrição em (tenant_id, pedido_id) em uma tabela com milhões de linhas por inquilino.
• Índice parcial não conta. Um índice em pedido_id WHERE ativo não é usado pela consulta interna da restrição, que não tem esse filtro.
• Índice inválido não conta. Uma criação concorrente que falhou deixa o índice no catálogo, ocupando espaço e custando escrita, sem ser usado por nenhuma consulta.
• Colunas incluídas com INCLUDE não contam como chave de busca, só as colunas-chave do índice.

-- fk-sem-indice.sql
-- Chaves estrangeiras cuja tabela filha nao tem indice valido, nao parcial,
-- que comece exatamente pelas colunas da restricao. Ordenado pelo tamanho da
-- filha, que e o que define o custo de cada varredura.
SELECT
  c.conrelid::regclass  AS tabela_filha,
  c.conname             AS restricao,
  c.confrelid::regclass AS tabela_pai,
  (SELECT string_agg(a.attname, ', ' ORDER BY k.pos)
     FROM unnest(c.conkey) WITH ORDINALITY AS k(attnum, pos)
     JOIN pg_attribute a
       ON a.attrelid = c.conrelid AND a.attnum = k.attnum) AS colunas,
  CASE c.confdeltype
    WHEN 'c' THEN 'cascade'
    WHEN 'n' THEN 'set null'
    WHEN 'd' THEN 'set default'
    WHEN 'r' THEN 'restrict'
    ELSE 'no action'
  END AS ao_excluir,
  pg_size_pretty(pg_relation_size(c.conrelid)) AS tamanho_filha
FROM pg_constraint c
WHERE c.contype = 'f'
  AND NOT EXISTS (
    SELECT 1
    FROM pg_index i
    WHERE i.indrelid = c.conrelid
      AND i.indisvalid
      AND i.indpred IS NULL
      AND i.indnkeyatts >= cardinality(c.conkey)
      -- As N primeiras colunas do indice sao exatamente as N colunas da FK.
      AND (SELECT array_agg(x.attnum ORDER BY x.attnum)
             FROM unnest(i.indkey::int2[]) WITH ORDINALITY AS x(attnum, pos)
            WHERE x.pos <= cardinality(c.conkey))
        = (SELECT array_agg(y ORDER BY y) FROM unnest(c.conkey) AS y)
  )
ORDER BY pg_relation_size(c.conrelid) DESC;

A primeira execução dessa consulta em um banco com alguns anos costuma devolver entre dez e cinquenta restrições, e a reação natural é criar índice em todas. Não é necessário, e a ordenação existe justamente para evitar isso. O que importa é o cruzamento de três colunas: o tamanho da filha, que define o custo de cada varredura; a regra de exclusão, que diz se o pai sofre remoção com efeito na filha; e o conhecimento de domínio sobre a tabela pai, que diz se ela é um catálogo imutável ou uma tabela transacional que tem expurgo, cancelamento ou pedido de exclusão de titular.

Um detalhe de leitura: em tabelas particionadas, a consulta devolve a restrição na tabela particionada, com tamanho zero, e em cada partição, com o tamanho real. O índice deve ser criado na tabela particionada, que propaga para todas as partições atuais e futuras, e não partição por partição.

A correção é uma linha, mas a forma ingênua de aplicá-la repete o incidente. Um CREATE INDEX comum pede bloqueio SHARE na tabela, que impede qualquer escrita durante toda a construção. Em uma tabela de quarenta milhões de linhas isso são vários minutos sem gravar histórico, ou seja, vários minutos sem checkout. A versão CONCURRENTLY constrói o índice sem bloquear escrita, ao custo de ler a tabela duas vezes e de ter três restrições operacionais que causam a maioria das falhas.

1. Ela não pode rodar dentro de um bloco de transação. Ferramentas de migração que envolvem cada arquivo em BEGIN e COMMIT precisam de uma marcação explícita para desligar isso naquele arquivo, e sem ela o comando falha na hora.
2. Ela espera todas as transações que já tocam a tabela terminarem antes de concluir. Se o expurgo lento estiver rodando, a criação do índice fica parada atrás dele. Pare o job antes, ou rode fora da janela dele.
3. Uma falha no meio, por timeout, cancelamento ou conflito, deixa o índice no catálogo marcado como inválido. Ele recebe todas as escritas e não serve nenhuma leitura, o pior dos dois mundos.
4. IF NOT EXISTS não protege contra o item anterior: ele considera o índice inválido como existente e devolve sucesso sem fazer nada. Uma retentativa automática com IF NOT EXISTS depois de uma falha deixa o índice quebrado para sempre, em silêncio.

-- migracao: 20260922_historico_status_pedido_id_idx.sql
-- Precisa rodar FORA de transacao explicita (desligue o BEGIN automatico
-- da ferramenta para este arquivo) e com o job de expurgo parado.

-- A construcao pode levar minutos; um timeout no meio deixa o indice invalido.
SET statement_timeout = 0;

CREATE INDEX CONCURRENTLY historico_status_pedido_id_idx
  ON historico_status (pedido_id);

-- Conferencia obrigatoria depois de criar: indisvalid precisa ser true.
SELECT indexrelid::regclass AS indice, indisvalid, indisready
FROM pg_index
WHERE indexrelid = 'historico_status_pedido_id_idx'::regclass;

-- Se indisvalid vier false, remova e repita o CREATE acima.
-- Nao use IF NOT EXISTS como retentativa: ele aceita o indice invalido.
-- DROP INDEX CONCURRENTLY historico_status_pedido_id_idx;

Com o índice válido, o expurgo cai de cinquenta e três minutos para menos de um segundo, e o problema principal está resolvido. Vale ainda mudar a forma do job, porque o índice resolve o custo por linha, mas não o tamanho da transação. Excluir dois mil pedidos em um único comando ainda segura dois mil bloqueios de linha até o COMMIT, e no dia em que o volume de cancelamentos for dez vezes maior, por causa de uma campanha ou de uma falha de pagamento em massa, a transação volta a ficar longa. Processar em lotes com COMMIT entre eles mantém cada transação curta, independentemente do volume do dia.

-- Expurgo em lotes: cada lote e uma transacao curta. Exige PostgreSQL 11+
-- (COMMIT dentro de procedimento) e deve ser chamado fora de transacao.
CREATE OR REPLACE PROCEDURE expurgar_pedidos_cancelados(tamanho_lote int DEFAULT 500)
LANGUAGE plpgsql
AS $
DECLARE
  removidos int;
BEGIN
  LOOP
    DELETE FROM pedidos
    WHERE id IN (
      SELECT id
      FROM pedidos
      WHERE status = 'cancelado'
        AND atualizado_em < now() - interval '90 days'
      ORDER BY id
      LIMIT tamanho_lote
      -- Linhas travadas por outra sessao ficam para a proxima execucao.
      FOR UPDATE SKIP LOCKED
    );
    GET DIAGNOSTICS removidos = ROW_COUNT;
    EXIT WHEN removidos = 0;

    COMMIT;                -- libera bloqueios e o horizonte do vacuum
    PERFORM pg_sleep(0.1); -- deixa respirar a replicacao e o disco
  END LOOP;
END;
$;

CALL expurgar_pedidos_cancelados(500);

Um índice em cada chave estrangeira é uma regra fácil de seguir e razoável como padrão, mas tem custo real em tabelas de escrita intensa: mais uma árvore para atualizar em cada INSERT, mais páginas no cache, mais volume no WAL e na replicação. Existe um conjunto pequeno de casos em que dispensar o índice é a escolha certa, e o que separa uma dispensa consciente de um esquecimento é exatamente o registro da decisão.

A terceira linha é a única dispensa legítima, e ela tem uma condição que envelhece: o catálogo é imutável hoje. No dia em que alguém decidir remover um tipo de pagamento descontinuado, aquela exclusão de uma linha vai varrer uma tabela de centenas de milhões de registros dentro de uma transação. Por isso a exceção precisa estar escrita em um lugar que alguém lê quando a premissa muda, e não apenas na memória de quem decidiu.

O lugar certo para isso é o CI. A mesma consulta de catálogo, rodada contra o banco criado pelas migrações do repositório, transforma o problema de uma descoberta em produção em uma falha de build no pull request que adicionou a restrição. A lista de exceções vive no código, com uma justificativa por entrada, e o script avisa quando uma exceção deixou de ser necessária, para que a lista não acumule entradas mortas.

// verificar-fk-sem-indice.mjs
// Roda no CI contra o banco criado pelas migracoes. Falha o build quando
// aparece chave estrangeira sem indice fora da lista de excecoes.
import { readFile } from 'node:fs/promises';
import pg from 'pg';

// Toda excecao exige justificativa: quem ler daqui a um ano precisa saber
// por que a restricao ficou sem indice de proposito.
const EXCECOES = new Map([
  ['pagamentos_moeda_fkey', 'moedas e catalogo imutavel; nenhuma exclusao ou troca de chave'],
]);

const sql = await readFile(new URL('./fk-sem-indice.sql', import.meta.url), 'utf8');
const cliente = new pg.Client({ connectionString: process.env.DATABASE_URL });

await cliente.connect();
try {
  const { rows } = await cliente.query(sql);
  const encontradas = new Set(rows.map((linha) => linha.restricao));
  const violacoes = rows.filter((linha) => !EXCECOES.has(linha.restricao));

  for (const v of violacoes) {
    console.error(
      `FK sem indice: ${v.restricao} em ${v.tabela_filha} (${v.colunas}) -> ${v.tabela_pai}, ao excluir: ${v.ao_excluir}`,
    );
  }
  for (const nome of EXCECOES.keys()) {
    if (!encontradas.has(nome)) console.warn(`Excecao obsoleta, remova da lista: ${nome}`);
  }

  if (violacoes.length > 0) process.exitCode = 1;
} finally {
  await cliente.end();
}

Com essa verificação no pipeline, o custo de manter a regra cai para perto de zero: quem cria uma restrição nova recebe a falha no mesmo pull request, com o nome da tabela e da coluna, e decide ali entre criar o índice na mesma migração ou registrar a exceção com o motivo. A decisão continua sendo humana; o que deixa de existir é a possibilidade de ela não ser tomada.