Como projetar uma tabela de séries temporais somente de inserção no PostgreSQL

Objetivo

Uma tabela que aceite um fluxo constante de linhas com timestamp, responda rapidamente a "série X entre t1 e t2" e descarte dados antigos de forma barata, sem bloat.

Pré-requisitos

Um período de retenção definido, os formatos de consulta dominantes (uma série ao longo de uma janela; agregações por janela), uma estimativa de linhas por dia e o tipo do relógio: timestamptz, a abreviação documentada de timestamp with time zone, armazena um instante absoluto e evita a ambiguidade do horário de verão.

Passos

1. Defina a linha: series_id (chave estrangeira para uma tabela de metadados), ts timestamptz NOT NULL, colunas medidas com NOT NULL quando um valor ausente for impossível e nenhum id substituto, a menos que as linhas sejam referenciadas individualmente. Uma chave primária em (series_id, ts) também atende à consulta principal; a documentação exige que a chave primária ou a restrição unique de uma tabela particionada inclua todas as colunas da chave de partição.
2. Crie a tabela com PARTITION BY RANGE (ts) e uma partição por dia, semana ou mês, escolhida de modo que a janela de uma consulta típica abranja poucas partições e que o período de retenção seja um número inteiro delas. A documentação alerta que partições demais aumentam o tempo de planejamento e o uso de memória.
3. Crie as partições futuras com antecedência, a partir de um job agendado (ou de uma ferramenta como o pg_partman). Um insert em um intervalo inexistente falha; uma partição DEFAULT captura essas linhas silenciosamente, e a documentação observa que, nesse caso, anexar uma partição posterior faz a varredura dela sob um lock ACCESS EXCLUSIVE, a menos que uma restrição CHECK exclua o novo intervalo.
4. Crie um índice para cada formato de consulta: a chave primária (B-tree) para consultas por intervalo dentro de uma série; um índice BRIN em ts para varreduras de tempo na tabela inteira. A documentação dos tipos de índice descreve o BRIN como um índice que guarda resumos por intervalo de blocos físicos, eficaz quando os valores se correlacionam com a posição física, o que ocorre em dados somente de inserção.
5. Implemente a retenção com ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, arquive opcionalmente e depois execute DROP TABLE. A documentação de particionamento afirma que isso é muito mais rápido do que um DELETE em massa e evita a sobrecarga de VACUUM que ele causaria.
6. Adicione uma tabela de rollup (agregações por hora ou por dia), preenchida por um job, quando os dashboards consultarem intervalos longos.
7. Carregue em lotes (COPY ou INSERT com várias linhas) ordenados por tempo, para que os intervalos do BRIN permaneçam estreitos.

Resultado esperado

As consultas com predicado de tempo acessam apenas as partições do intervalo (partition pruning), os inserts caem na partição mais recente, os dados antigos desaparecem por uma operação de metadados e a exclusão não causa bloat.

Limites e base de verificação

O pruning exige um predicado sobre a chave de partição; uma consulta por série sem limite de tempo lê o índice de todas as partições. Atualizações e chegadas fora de ordem enfraquecem a correlação do BRIN. O texto segue a documentação citada; não se afirma nenhum número de throughput ou de tamanho.