Particionamento de séries temporais no PostgreSQL com Prisma: design e retenção

Quando e por que usar

Dados de séries temporais crescem rápido e têm características bem definidas: alta taxa de inserção, consultas por janela de tempo e necessidade de retenção/rotação. O particionamento resolve problemas de manutenção, performance e retenção removendo o custo de varrer grandes tabelas e permitindo operações de manutenção por partição.

Use particionamento quando:

• Você tem tabelas que crescem em milhões de linhas por dia.
• Consultas são concentradas em janelas temporais (últimas horas, dias, semanas).
• Retenção é um requisito — apagar por partição é muito mais barato que delete em massa.

Benefícios práticos

Particionamento por range de timestamp reduz tempo de varredura, melhora uso de cache e permite indexação por partição. Também facilita operações de manutenção: VACUUM e reindex por partição são mais rápidos e menos disruptivos.

Limitações e trade-offs

Há overhead operacional: criação de partições, roteamento de inserts, manutenção de índices e constraints em cada partição. Consultas que precisam acessar muitas partições podem sofrer. Além disso, alguns ORMs (incluindo Prisma) não expõem suporte nativo a partições, então é preciso combinar SQL puro com mapeamento do ORM.

Comparação com alternativas

Antes de implementar particionamento nativo do PostgreSQL, compare opções: tabla única com índices, sharding na aplicação, pg_partman, timescaledb e armazenamento externo (clickhouse, influxdb).

Tabela única com índices vs particionamento

Tabela única é simples e funciona até certo ponto. Índices covering ajudam, mas manutenção de dados antigos (DELETE) é custosa: afeta WAL, gera bloat e exige VACUUM intensivo. Particionamento substitui DELETE massivo por DROP PARTITION, que é meta-data-only e muito rápido.

TimescaleDB e ClickHouse

TimescaleDB oferece abstrações específicas para séries temporais com compressão e políticas de retenção. ClickHouse tem leitura muito rápida para OLAP, mas exige outro sistema. Se você já está em PostgreSQL e precisa de forte integração transacional, particionamento nativo costuma ser o balanço ideal.

Escolha tecnologia pelo domínio: se sua aplicação precisa de transações e integra com outras tabelas relacionais, prefira PostgreSQL particionado; para analytics em batch extremo, considere ClickHouse.

Implementação progressiva

Implementar particionamento deve ser incremental: partir de uma estratégia simples e operacionalizar retenção automática. Aqui apresento um caminho prático e seguro.

1. Modelo inicial da tabela pai

Crie a tabela pai como partição montante e use RANGE sobre a coluna de timestamp. Exemplo funcional abaixo cria tabela e partições mensais.

Contexto: esse SQL cria a estrutura básica e uma partição exemplo.

-- Cria tabela pai particionada por range de timestamp
CREATE TABLE metrics (
  id bigserial not null,
  device_id uuid not null,
  ts timestamptz not null,
  value double precision not null,
  PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

-- Cria particao para janeiro de 2026
CREATE TABLE metrics_2026_01 PARTITION OF metrics
  FOR VALUES FROM ('2026-01-01 00:00:00+00') TO ('2026-02-01 00:00:00+00');

-- Index local na particao
CREATE INDEX ON metrics_2026_01 (device_id, ts);

O que acontece: a tabela pai não armazena linhas; inserts são roteados para a partição adequada. Indexes devem existir por partição. Sem partição cover, inserts fora do range falham com erro.

2. Strategy de criação automática

Criar partições manualmente é viável no começo, mas você deve automatizar criação para evitar inserts em lacunas. Duas abordagens comuns: uma função em PL/pgSQL que cria partições em lote e um job externo que usa SQL via Prisma ou psql.

Exemplo de função para criar partições mensais por intervalo de datas:

-- Função: cria partições mensais para um range
CREATE OR REPLACE FUNCTION create_monthly_partitions(start_date date, end_date date)
RETURNS void LANGUAGE plpgsql AS $$
DECLARE
  cur date := date_trunc('month', start_date);
  next date;
  partname text;
BEGIN
  WHILE cur < end_date LOOP
    next := (cur + interval '1 month')::date;
    partname := format('metrics_%s_%02s', to_char(cur, 'YYYY'), to_char(cur, 'MM'));
    EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF metrics FOR VALUES FROM (%L) TO (%L)',
      partname, cur::text || ' 00:00:00+00', next::text || ' 00:00:00+00');
    cur := next;
  END LOOP;
END
$$;

-- Uso
SELECT create_monthly_partitions('2026-01-01', '2026-07-01');

O que acontece: a função gera partições mensais necessárias. Agende um job para executar periodicamente com intervalos futuros previsíveis.

3. Retenção via remoção de partições

Retenção eficiente: calcular partições antigas fora da janela de retenção e executar DROP TABLE para cada partição. DROP é rápido e não gera remoção row-by-row nem grande WAL se o partitioned table é local.

Exemplo de tarefa externa em Node que usa Prisma para descobrir e dropar partições antigas.

// Node.js: rotina de retenção que usa prisma para executar SQL
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function dropOldPartitions(retentionDays = 90) {
  const cutoff = new Date(Date.now() - retentionDays * 24 * 60 * 60 * 1000);
  const cutoffStr = cutoff.toISOString().slice(0, 10); // YYYY-MM-DD

  // Lista partições via catalogo
  const parts = await prisma.$queryRaw`
    SELECT relname
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE c.relkind = 'r'
      AND relname LIKE 'metrics_%'
      AND relname < 'metrics_' || ${cutoffStr.replace(/-/g, '_')}
  `;

  for (const p of parts) {
    await prisma.$executeRawUnsafe(`DROP TABLE IF EXISTS ${p.relname}`);
    console.log('dropped', p.relname);
  }
}

dropOldPartitions().finally(() => prisma.$disconnect());

O que acontece: o script consulta catalogo e dropa partições antigas. Use conexões limitadas e execute em janelas off-peak. Em clusters com replica, coordene para não impactar replicação.

Integracao com ecossistema real

Na prática, você precisa integrar particionamento com Prisma, migrations, monitoramento e backup. Prisma não gera partições automaticamente; você precisa manter SQL brut e mapear o modelo para a tabela pai.

Modelagem Prisma mapeando tabela particionada

Prisma schema deve mapear a tabela pai e usar @@map para conectar com nome existente. Evite representar partições no schema; elas são transparência para queries contra a tabela pai.

// schema.prisma: mapeia tabela metrics existente
model Metric {
  id       BigInt   @id @default(autoincrement())
  deviceId String   @map('device_id')
  ts       DateTime @map('ts')
  value    Float

  @@map('metrics')
  @@index([deviceId, ts], name: 'metrics_device_ts_idx')
}

O que acontece: Prisma opera sobre a tabela pai. Inserts via Prisma são redirecionados pelo Postgres para a partição correta. Migrations do Prisma não criam partições — você deve incluir SQL bruto em migrations ou executar migrações manuais.

Migrations e deploy

Workflow sugerido:

1. Gerar migration do Prisma para criar a tabela pai minimal.
2. Adicionar migration SQL manual que cria partições iniciais e funções auxiliares.
3. Deploy ordenado: aplicar migração SQL antes de testar carga de produção.

Inclua scripts idempotentes de criação de partições e verificação no processo de inicialização do serviço.

Monitoramento e backups

Monitore contagem de partições, tamanho de partições, autovacuum e tempo de criação. No backup, considere que ferramentas como pg_basebackup ou pg_dump funcionam: partições são tabelas normais. Para RTO, restaure e reprovisione partições se necessário.

Dica: versionar suas SQLs de particionamento junto com o código. Migrations binárias separadas inválidas geram dor em produção.

Checklist de qualidade para producao

Antes de mover para produção, passe por um checklist técnico que cobre modelagem, performance, manutenção e observabilidade.

Modelagem e índices

• Verificar que a coluna de particionamento é a mais usada nas cláusulas WHERE.
• Indexar por partição colunas de filtro e ordenação; criar índices globais apenas quando necessário.
• Evitar FKs entre grandes tabelas particionadas e outras sem planejar performance.

Retenção e automação

• Implementar job automatizado para criação de partições futuras.
• Implementar job de retenção que DROP PARTITION com logs e retries.
• Testar o job em staging com volume representativo.

Operacional

Configure monitoramento de métricas:

• Quantidade de partições por tabela
• Tempo médio de criação de partição
• Taxa de megs/partição e autovacuum backlog

Garanta backups consistentes e procedimentos de restauração documentados.

Erros comuns e armadilhas

A seguir os problemas que eu vejo em projetos que aplicaram particionamento sem disciplina.

1. Falta de partições futuras

Problema: inserts falham com error "no partition of relation" porque não há partição para a data. Solução: criar partições com antecipação e validar antes de aceitar dados de produtores externos.

2. Índices inconsistentes

Problema: esquecer de criar índice em cada partição causa scans lentos. Solução: criar scripts que criem índices em novas partições automaticamente ou usar templates via SQL na função de criação.

3. Migrations do Prisma sobrescrevendo estrutura

Problema: prisma migrate pode tentar alterar tabela pai de forma que afete partições. Solução: colocar alterações sensíveis em migrations SQL manuais e testar scripts de rollback.

4. Bloat e autovacuum

Mesmo com particionamento, partições recentes podem crescer e gerar bloat. Monitore autovacuum e configure thresholds por partição se necessário. Em casos extremos, configure autovacuum agressivo em partições mutáveis.

Conclusao e passos práticos

Particionamento é a ferramenta certa para séries temporais em PostgreSQL quando você precisa manter forte integração transacional e quer controle fino sobre retenção. Combinado ao Prisma, a estratégia prática é mapear a tabela pai no schema, usar SQL manual para criar partições e automatizar criação/remoção com jobs externos ou functions plpgsql.

Checklist prático final:

1. Defina coluna de particionamento e granularidade (dia/semana/mês) baseada em taxa de ingestão e consulta.
2. Crie partições iniciais e uma função/rota para criação automática.
3. Implemente retenção por DROP PARTITION com jobs monitorados.
4. Mapeie tabela pai no Prisma e execute migrations SQL manuais para objetos de particionamento.
5. Monitore partições, autovacuum e tempos de query; ajuste índices por partição.

Com esses passos você reduz custos operacionais, melhora performance das queries time-bounded e ganha um caminho claro para retenção sem downtime.

Exemplo final: script SQL para listar partições e tamanho, útil para monitoramento rápido:

-- Lista particoes e tamanho em MB
SELECT
  inhrelid::regclass AS partition_name,
  pg_size_pretty(pg_total_relation_size(inhrelid)) AS total_size
FROM pg_inherits
WHERE inhparent = 'metrics'::regclass;

O que acontece: retorna cada partição com tamanho total, permitindo decisões de retenção baseadas em espaço.

Implementar particionamento exige disciplina, mas é uma alavanca grande para sistemas de séries temporais. Automatize criação/remoção, version control para SQL e integração com Prisma para evitar surpresas.