Tabela que cresceu demais: particionar por data no Postgres sem reescrever a aplicação

Tabelas de eventos, logs e histórico crescem em um só sentido, e o Postgres trata todas as linhas como iguais. Os problemas aparecem na manutenção, não na consulta do dia a dia. Apagar o mês vencido com DELETE marca cerca de 190 milhões de linhas como mortas, grava tudo no WAL, gera trabalho para o autovacuum e não devolve espaço em disco ao sistema operacional. O índice de 310 GB só cresce, porque as páginas liberadas por exclusões só são reaproveitadas de forma irregular. E cada tarefa de administração, como backup lógico, reindexação ou troca de tipo de coluna, passa a ser proporcional ao tamanho total, e não ao tamanho do que importa.

A última linha da tabela é a que o resto do artigo precisa respeitar: particionar não acelera nada que não use a chave. Ele troca um problema de manutenção por uma regra de acesso, e vale a pena quando a tabela tem uma dimensão de tempo clara, um ciclo de vida de dados com prazo e a maioria das consultas filtrando por essa dimensão.

• Resolve a retenção: expirar dados vira DROP TABLE de uma partição inteira, sem DELETE, sem VACUUM e sem WAL de exclusão.
• Resolve a manutenção: autovacuum, ANALYZE e REINDEX passam a atuar em pedaços de dezenas de gigabytes, e as partições antigas, que não mudam mais, quase não dão trabalho.
• Resolve parte da leitura: consultas com filtro na chave leem só as partições do intervalo, e o índice quente fica pequeno.
• Não resolve consulta sem filtro na chave: SELECT ... WHERE id = $1 passa a consultar o índice de todas as partições, 14 buscas em vez de uma. Ainda são rápidas, mas é custo novo.
• Não resolve unicidade global: a chave primária e qualquer índice único precisam conter a coluna de partição. O id continua único na prática pela sequência, mas o banco só garante (id, criado_em).
• Não resolve chave estrangeira apontando para a tabela: uma FK que referencia a coluna id precisaria referenciar (id, criado_em), o que obriga a mudar as tabelas filhas. Em tabelas de eventos e logs, que normalmente só apontam para fora, isso não aparece.

A granularidade segue o ciclo de vida, não a vontade de ter muitas partições. Com 86 GB de dados novos por mês e retenção de 12 meses, uma partição mensal é a escolha natural: 13 partições ativas, cada uma pequena o bastante para ser manipulada e grande o bastante para não gerar milhares de tabelas. Partição diária só compensa em volumes muito maiores; o planejador gasta tempo por partição, e centenas delas já deixam o planejamento visivelmente mais lento.

O caminho óbvio, criar a tabela particionada e copiar tudo com INSERT ... SELECT, exige gravar 1,2 TB de novo, manter as duas tabelas sincronizadas durante a cópia e fazer um corte. Existe um atalho: uma tabela comum pode ser anexada como partição de uma tabela particionada com ATTACH PARTITION, desde que seu conteúdo caiba no intervalo declarado. A tabela antiga vira a partição que cobre todo o passado até o fim do mês corrente, e as linhas novas depois dessa data caem nas partições novas. Nada é copiado, e a aplicação continua usando o mesmo nome de tabela.

Antes e depois da troca (as setas mostram para onde cada INSERT e cada consulta vai).

  ANTES                                   DEPOIS

  aplicacao                               aplicacao  (mesmo SQL, mesmo nome de tabela)
     |                                       |
  eventos_pedido (1,2 TB)                 eventos_pedido (pai, sem dados proprios)
  PK (id)                                 PK (id, criado_em)
  DELETE diario de 12 meses atras             |
                                              +-- eventos_pedido_legado   ate 2026-12-01 (1,2 TB)
                                              +-- eventos_pedido_2026_12  mes corrente de dados novos
                                              +-- eventos_pedido_2027_01  a frente
                                              +-- eventos_pedido_padrao   deve estar sempre vazia

  Daqui a 12 meses: DROP TABLE eventos_pedido_legado (milissegundos, sem DELETE, sem VACUUM).

O preparo é feito com a tabela em uso. Há duas armadilhas. A primeira é que o ATTACH precisa de índices equivalentes aos da tabela pai, e se não os encontrar, constrói novos com a tabela bloqueada, o que para 1,2 TB significa horas de indisponibilidade. A solução é criá-los antes, com CREATE INDEX CONCURRENTLY, na tabela antiga. A segunda é que o ATTACH também confere se todas as linhas cabem no intervalo, lendo a tabela inteira, a menos que exista um CHECK validado que já prove isso. O CHECK entra como NOT VALID, que é instantâneo, e o VALIDATE lê a tabela sem bloquear leitura nem escrita.

-- Estado de partida: eventos_pedido, 1,2 TB, PK (id), coluna criado_em timestamptz NOT NULL.
-- Nada abaixo bloqueia leitura ou escrita da tabela em producao.

-- 1. Tabela nova, particionada por mes, com a mesma forma. A PK inclui a chave de particao:
--    o Postgres so garante unicidade dentro de cada particao, entao criado_em entra na PK.
CREATE TABLE eventos_pedido_novo (
  id         bigint      NOT NULL,
  pedido_id  bigint      NOT NULL,
  tipo       text        NOT NULL,
  payload    jsonb       NOT NULL,
  criado_em  timestamptz NOT NULL,
  PRIMARY KEY (id, criado_em)
) PARTITION BY RANGE (criado_em);

-- 2. Particoes futuras e uma particao padrao como rede de seguranca (deve ficar sempre vazia).
CREATE TABLE eventos_pedido_2026_12 PARTITION OF eventos_pedido_novo
  FOR VALUES FROM ('2026-12-01 00:00:00+00') TO ('2027-01-01 00:00:00+00');
CREATE TABLE eventos_pedido_2027_01 PARTITION OF eventos_pedido_novo
  FOR VALUES FROM ('2027-01-01 00:00:00+00') TO ('2027-02-01 00:00:00+00');
CREATE TABLE eventos_pedido_padrao PARTITION OF eventos_pedido_novo DEFAULT;
CREATE INDEX ON eventos_pedido_novo (pedido_id, criado_em);

-- 3. Na tabela antiga: os indices que a particao precisara ter, criados sem bloquear.
--    No ATTACH, o Postgres reaproveita indice equivalente em vez de construir outro.
SET lock_timeout = '2s';
CREATE UNIQUE INDEX CONCURRENTLY eventos_pedido_id_criado_em_uq
  ON eventos_pedido (id, criado_em);
CREATE INDEX CONCURRENTLY eventos_pedido_pedido_criado_idx
  ON eventos_pedido (pedido_id, criado_em);

-- 4. O CHECK que prova ao ATTACH que todas as linhas cabem no intervalo. Com ele validado,
--    o Postgres nao varre 1,2 TB para conferir. NOT VALID e instantaneo; VALIDATE le a tabela
--    sem impedir leitura nem escrita.
ALTER TABLE eventos_pedido
  ADD CONSTRAINT eventos_pedido_limite
  CHECK (criado_em < '2026-12-01 00:00:00+00') NOT VALID;
ALTER TABLE eventos_pedido VALIDATE CONSTRAINT eventos_pedido_limite;

Dois detalhes do preparo costumam passar despercebidos. O limite do CHECK precisa ser uma data futura, no fim do mês corrente, porque a tabela antiga continuará recebendo escrita até a troca e o CHECK não pode ser violado nesse intervalo. E os literais de data levam o deslocamento explícito (+00): um timestamptz escrito sem deslocamento é interpretado no fuso da sessão, e uma divergência entre o fuso do script e o do servidor desloca todas as fronteiras de partição em algumas horas.

Com tudo pronto, a troca é uma transação que só mexe em catálogo. A chave primária da tabela antiga passa a ser (id, criado_em) reaproveitando o índice já construído, os nomes trocam de lugar, a tabela antiga é anexada como partição e a sequência do id passa a ser alimentada pela tabela pai. A transação pede ACCESS EXCLUSIVE na tabela e, por isso, usa lock_timeout: se um relatório longo estiver segurando a tabela, ela falha em 2 segundos em vez de formar uma fila atrás de si, e a tentativa se repete.

-- A troca. Tudo aqui e catalogo: nenhum comando le dados.
-- Se o lock_timeout estourar, a transacao inteira volta e tentamos de novo.
BEGIN;
SET LOCAL lock_timeout = '2s';

LOCK TABLE eventos_pedido IN ACCESS EXCLUSIVE MODE;

-- A PK da tabela antiga passa a ser (id, criado_em), igual a da tabela particionada.
ALTER TABLE eventos_pedido DROP CONSTRAINT eventos_pedido_pkey;
ALTER TABLE eventos_pedido
  ADD CONSTRAINT eventos_pedido_pkey PRIMARY KEY USING INDEX eventos_pedido_id_criado_em_uq;

-- Os nomes trocam de lugar: a aplicacao continua falando com eventos_pedido.
ALTER TABLE eventos_pedido RENAME TO eventos_pedido_legado;
ALTER TABLE eventos_pedido_novo RENAME TO eventos_pedido;

-- A tabela antiga vira a particao de tudo que existe ate o fim do mes corrente.
ALTER TABLE eventos_pedido
  ATTACH PARTITION eventos_pedido_legado
  FOR VALUES FROM (MINVALUE) TO ('2026-12-01 00:00:00+00');

-- A sequencia segue alimentando o id, agora pela tabela pai.
ALTER SEQUENCE eventos_pedido_id_seq OWNED BY eventos_pedido.id;
ALTER TABLE eventos_pedido ALTER COLUMN id SET DEFAULT nextval('eventos_pedido_id_seq');
ALTER TABLE eventos_pedido_legado ALTER COLUMN id DROP DEFAULT;

COMMIT;

-- Conferir: tudo que existia esta na particao legada, o resto vai para as novas.
SELECT tableoid::regclass AS particao, count(*) FROM eventos_pedido
WHERE criado_em >= now() - interval '1 hour' GROUP BY 1;

No ensaio com tráfego reproduzido, a transação levou 410 ms. Na produção, a primeira tentativa caiu no lock_timeout por causa de uma consulta de relatório aberta havia 50 segundos, a segunda passou, e o p99 da API não mudou. O log do servidor com nível DEBUG1, no ensaio, mostrou a mensagem de que a restrição da partição estava implícita nas restrições existentes, que é a confirmação de que o ATTACH não varreu a tabela. Se ele varrer, a transação demora e você descobre no ensaio, não na produção.

O que não vem junto precisa ser verificado: permissões (GRANT) da tabela, gatilhos, políticas de segurança por linha, comentários, publicações de replicação lógica e qualquer ferramenta de CDC que observe a tabela pelo nome. Publicações com publish_via_partition_root = true entregam as mudanças pelo nome da tabela pai, o que preserva o contrato para os consumidores.

Particionamento sem rotina de manutenção só adia a falha. Se faltar a partição do mês, a linha vai para a partição padrão, ou, se não houver uma, o INSERT falha com "no partition of relation found for row" e a aplicação deixa de gravar eventos. A rotina precisa criar partições com antecedência, expirar as vencidas e avisar quando algo foge do esperado. Ela é idempotente (CREATE TABLE IF NOT EXISTS, DROP TABLE IF EXISTS) e roda todo dia, para que um dia perdido não vire um problema no mês seguinte.

// manter-particoes.js - roda diariamente (cron). Idempotente: pode rodar duas vezes sem efeito.
import pg from 'pg';

const TABELA = 'eventos_pedido';
const MESES_A_FRENTE = 3;
const RETENCAO_MESES = 12;
// Enquanto a particao legada existir, ela cobre tudo ate esta data. Remover depois que ela cair.
const LEGADO_ATE = new Date('2026-12-01T00:00:00Z');

const inicioDoMes = (data, deslocamento) =>
  new Date(Date.UTC(data.getUTCFullYear(), data.getUTCMonth() + deslocamento, 1));
const dia = (data) => data.toISOString().slice(0, 10);
const sufixo = (data) =>
  data.getUTCFullYear() + '_' + String(data.getUTCMonth() + 1).padStart(2, '0');

const esperar = (ms) => new Promise((resolve) => setTimeout(resolve, ms));

// 55P03 = lock_not_available: um relatorio longo segurava a tabela. Tenta de novo em vez de
// deixar o ALTER na fila, porque tudo que chega depois dele esperaria atras.
async function comRetentativa(operacao, tentativas = 5) {
  for (let i = 1; ; i += 1) {
    try {
      return await operacao();
    } catch (erro) {
      if (erro.code !== '55P03' || i >= tentativas) throw erro;
      await esperar(1000 * i);
    }
  }
}

async function criarParticoesFuturas(client, hoje) {
  for (let i = 0; i <= MESES_A_FRENTE; i += 1) {
    const de = inicioDoMes(hoje, i);
    if (de < LEGADO_ATE) continue;
    const ate = inicioDoMes(hoje, i + 1);
    await comRetentativa(() =>
      client.query(
        'CREATE TABLE IF NOT EXISTS ' + TABELA + '_' + sufixo(de) +
          ' PARTITION OF ' + TABELA +
          " FOR VALUES FROM ('" + dia(de) + " 00:00:00+00') TO ('" + dia(ate) + " 00:00:00+00')",
      ),
    );
  }
}

async function removerExpiradas(client, hoje) {
  const limite = inicioDoMes(hoje, -RETENCAO_MESES);
  const { rows } = await client.query(
    'SELECT c.relname FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid ' +
      "WHERE i.inhparent = $1::regclass AND c.relname ~ '^eventos_pedido_[0-9]{4}_[0-9]{2}
#39;",
    [TABELA],
  );
  for (const { relname } of rows) {
    const [, ano, mes] = relname.match(/_(\d{4})_(\d{2})$/);
    if (new Date(Date.UTC(Number(ano), Number(mes) - 1, 1)) >= limite) continue;
    await comRetentativa(() => client.query('DROP TABLE IF EXISTS ' + relname));
  }
}

async function vigiarParticaoPadrao(client) {
  const { rows } = await client.query(
    'SELECT EXISTS (SELECT 1 FROM ' + TABELA + '_padrao) AS tem_linha',
  );
  if (rows[0].tem_linha) {
    throw new Error('Ha linhas na particao padrao: falta criar uma particao a frente');
  }
}

const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const client = await pool.connect();
try {
  await client.query("SET lock_timeout = '2s'");
  const hoje = new Date();
  await criarParticoesFuturas(client, hoje);
  await removerExpiradas(client, hoje);
  await vigiarParticaoPadrao(client);
} finally {
  client.release();
  await pool.end();
}

• Criar com 3 meses de antecedência dá 90 dias para alguém perceber que a rotina parou, e a verificação da partição padrão transforma uma falha silenciosa em erro explícito no agendador.
• A partição padrão é rede de segurança, não destino: ela precisa estar vazia, e uma linha nela pede ação. Criar uma partição nova exige conferir se a padrão não contém linhas do intervalo, e quanto maior a padrão, mais demorada e bloqueante é essa conferência.
• DETACH PARTITION ... CONCURRENTLY não funciona em tabela com partição padrão. Como DROP TABLE de uma partição leva apenas milissegundos de bloqueio na tabela pai, a rotina usa lock_timeout com retentativa em vez disso.
• A partição legada sai da rotina de propósito. Em dezembro de 2027, quando seu último dado completar 12 meses, ela é removida com um DROP TABLE, e esse é o ganho mais visível: 1,2 TB devolvidos em milissegundos. Até lá, a retenção continua sendo feita por DELETE em lotes nessa partição, só que agora sem pressão, porque o dado novo não vive mais nela.

A poda é o que faz o particionamento acelerar leitura, e ela depende de o filtro na chave ser comparável a uma constante ou a um parâmetro. Desde o Postgres 11, ela acontece também na execução, o que cobre now() e parâmetros de consultas preparadas. Funções aplicadas sobre a coluna da chave, como date(criado_em) ou to_char, desligam a poda e leem tudo.

-- Com a chave de particao no filtro, o planejador descarta as particoes que nao interessam.
EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, tipo, criado_em
FROM eventos_pedido
WHERE pedido_id = 918273 AND criado_em >= now() - interval '7 days';

--  Append (actual time=0.031..0.044 rows=12 loops=1)
--    Subplans Removed: 13
--    ->  Index Scan using eventos_pedido_2026_12_pedido_id_criado_em_idx
--          on eventos_pedido_2026_12 (actual time=0.029..0.040 rows=12 loops=1)
--          Index Cond: ((pedido_id = 918273) AND (criado_em >= (now() - '7 days'::interval)))
--  Planning Time: 0.9 ms   Execution Time: 0.07 ms

-- O que quebra a poda: aplicar funcao na coluna da chave.
-- Ruim: WHERE date(criado_em) = '2026-12-03'     (le todas as particoes)
-- Bom:  WHERE criado_em >= '2026-12-03' AND criado_em < '2026-12-04'

-- O pai particionado nao e analisado pelo autovacuum. Estatisticas do pai (usadas em
-- joins e em agregacoes) so se atualizam com ANALYZE manual, por exemplo uma vez por dia.
ANALYZE eventos_pedido;

1. Procure no código as consultas sem criado_em no filtro: buscas por id, joins pela chave e telas que listam "os últimos N". Elas continuam funcionando, mas agora passam por todas as partições. Em muitos casos basta acrescentar um limite de data razoável, como os últimos 90 dias.
2. Conferir o plano com EXPLAIN depois da troca, procurando por "Subplans Removed" ou por um Append que lista só as partições esperadas. Um Append com 14 filhas em uma consulta que deveria tocar uma é o sinal de que a poda não aconteceu.
3. Revise ORMs que geram consultas com funções sobre a coluna ou que carregam a linha pela chave primária sozinha: a chave primária agora tem duas colunas, e uma operação de atualização que filtre só por id passa por todas as partições.
4. Fique de olho em estatísticas e autovacuum: o pai não é analisado automaticamente, e cada partição nova começa sem estatísticas até receber dados. Um ANALYZE diário do pai e um olhar sobre o last_autovacuum das partições recentes evitam planos ruins.
5. Avise quem consome a tabela diretamente (BI, exportações): se eles leem por intervalo, ganham poda de graça; se leem tudo, nada muda.