Aguardando o Postgres 19: finalmente é hora de lidar com o “tempo”

• Para responder a perguntas sobre como os dados estavam em um determinado momento (na terça-feira passada), o Postgres 19 introduz suporte nativo a tabelas temporais (temporal tables), permitindo rastrear dados antes e depois de alterações sem um sistema separado de triggers de auditoria
• O Postgres incorpora tardiamente ao core as tabelas temporais definidas há mais de uma década pelo padrão SQL:2011, entrando nessa área depois de outros mecanismos de banco de dados
• Em vez de duas colunas valid_from/valid_to e de uma restrição de exclusão (exclusion constraint) baseada na extensão btree_gist, oferece uma representação mais intuitiva com uma única coluna de tipo de intervalo (range type) e a restrição WITHOUT OVERLAPS
• Com a sintaxe FOR PORTION OF, o mecanismo faz a divisão automática de linhas em UPDATE e DELETE, cuidando para evitar lacunas e sobreposições no eixo do tempo
• Esta introdução corresponde à metade de tempo válido (application time) de sistemas bitemporais; o tempo de sistema (system time) ainda não é suportado, mas a base para versões futuras fica preparada

---

A forma antiga - The Old-Fashioned Way

• A primeira tentativa de rastrear preços de produtos ao longo do tempo consistia em duas colunas de data, valid_from e valid_to, e uma restrição CHECK valid_from < valid_to
  • Porém, isso não impede a inserção de duas linhas com intervalos de datas sobrepostos para o mesmo produto (por exemplo, o produto 42 custando $9.99 e $14.99 na mesma terça-feira)
• A solução tradicional usa a extensão btree_gist e uma restrição de exclusão (exclusion constraint)
  • No formato EXCLUDE USING gist (product_id WITH =, daterange(valid_from, valid_to) WITH &&), a inserção de linhas sobrepostas gera um erro
• Problemas dessa abordagem
  • GiST é um índice próprio do Postgres que exige experiência, e o fato de ser uma extensão opcional cria uma barreira de entrada
  • A sintaxe de restrições de exclusão não é intuitiva, tornando difícil pensar nela como a abordagem padrão
  • A própria tabela não tem consciência temporal incorporada; ao alterar intervalos de tempo, é preciso dividir e combinar linhas manualmente, transferindo para a aplicação a responsabilidade pela consistência temporal

Uma breve história do tempo - A Brief History of Time

• O padrão SQL:2011 introduziu períodos de tempo válido (APPLICATION TIME), a restrição WITHOUT OVERLAPS e a sintaxe FOR PORTION OF para manipulação de dados temporais
• Henrietta Dombrovskaya (Hetti), junto com Chad Slaughter, desenvolveu a extensão pg_bitemporal, um framework em PL/pgSQL para gerenciar tabelas bitemporais dentro do Postgres
  • Desde 2015, apresentou o conceito em várias conferências e demonstrou como rastrear simultaneamente o tempo válido (valid time) (quando um fato é verdadeiro no mundo real) e o tempo de transação (transaction time) (quando o banco de dados registrou o fato)
• Distinção entre as duas dimensões de tempo
  • Tempo válido significa “este preço é válido de janeiro a junho”
  • Tempo de transação é a perspectiva do banco de dados: “esta linha foi inserida em 12 de março às 15h47 e substituída em 3 de abril às 9h01”
  • Combinando os dois, é possível construir uma tabela bitemporal que responde “com base no que sabíamos na época, qual preço achávamos que valia na terça-feira passada?”
• O pg_bitemporal usa EXCLUDE USING gist em dobro, aplicado uma vez ao intervalo effective (tempo válido) e outra ao intervalo asserted (tempo de transação)
  • Fornece funções para inserção, atualização, correção, desativação e exclusão bitemporais, além de implementar as relações de intervalo de Allen (Allen's interval relationships) para inferência temporal
• Limitações da extensão
  • Ela não pode alterar o planejador de consultas para reconhecer condições temporais, integrar-se ao sistema de restrições no nível do mecanismo nem oferecer uma sintaxe nativa de manipulação → era necessário entrar no core
  • O Postgres 19 incorpora a metade de tempo válido dos sistemas bitemporais; não é tudo, mas é um grande avanço

Intervalos ao resgate - Ranges to the Rescue

• A abordagem do Postgres 19 usa uma única coluna de tipo de intervalo, valid_at DATERANGE, em vez de valid_from/valid_to separados
  • Com PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS), a extensão btree_gist e a restrição de exclusão deixam de ser necessárias
  • WITHOUT OVERLAPS garante que product_id seja único em qualquer ponto no tempo, permitindo várias linhas do mesmo produto desde que os intervalos não se sobreponham
• Internamente, ainda usa um índice GiST e precisa de btree_gist para as colunas não temporais da chave, mas o Postgres trata automaticamente as dependências ao inicializar a restrição
• Notação de intervalos: [ inclui, ) exclui → [2025-01-01, 2025-07-01) inclui 1º de janeiro e não inclui 1º de julho
  • A última linha de Gadget, [2026-01-01,), é um intervalo com fim aberto (open-ended), sem data final definida para o preço atual
• Como funciona a proteção contra sobreposição
  • Ao inserir o intervalo inválido [2025-03-01, 2025-01-01), ocorre o erro “o limite inferior do intervalo deve ser menor ou igual ao limite superior”
  • Ao inserir o intervalo sobreposto [2025-03-01, 2025-09-01), ocorre erro de violação da restrição de exclusão products_pkey
  • O uso de intervalos oferece duas validações de uma só vez

Recortando e dividindo - Slicing and Dicing

• Se o preço de um produto for alterado para $10.99 apenas de março a setembro de 2025, a abordagem antiga exigia dividir e inserir linhas manualmente, com risco de lacunas ou sobreposições em caso de erro
• Em tabelas temporais, isso é expresso diretamente como pretendido
  • UPDATE products FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01' SET price = 10.99 WHERE product_id = 1
• Como resultado, as linhas do Widget aumentam de 3 para 5
  • A linha existente de $9.99 é reduzida para [2025-01-01, 2025-03-01)
  • Uma nova linha de $10.99 é adicionada ao intervalo restante
  • A linha existente de $12.99 é reduzida para [2025-09-01, 2026-01-01)
  • Uma nova linha de $10.99 é adicionada ao intervalo restante [2025-07-01, 2025-09-01)
• O motivo de $10.99 ficar dividido em duas linhas é que FOR PORTION OF processa cada linha correspondente de forma independente, sem mesclar (coalesce) intervalos adjacentes depois
  • O resultado final não tem lacunas nem sobreposições, uma vantagem que a lógica pura de exclusão não oferecia
• Casos de borda (edge cases)
  • Se o intervalo de FOR PORTION OF estiver totalmente dentro de uma única linha existente, são geradas no máximo 2 linhas remanescentes (antes e depois)
  • Se coincidir exatamente com as bordas existentes, não são necessárias linhas remanescentes
• As novas linhas temporais remanescentes não exigem privilégio de INSERT, mas triggers de INSERT existentes são disparadas → é preciso atenção em logs de auditoria ou funções de trigger SECURITY DEFINER

Apagando o histórico - Erasing History

• FOR PORTION OF também funciona em DELETE; por exemplo, remover temporariamente um produto do catálogo de junho a outubro de 2025
  • DELETE FROM products FOR PORTION OF valid_at FROM '2025-06-01' TO '2025-10-01' WHERE product_id = 2
• Resultado
  • O intervalo de junho a outubro é recortado, e a linha de $22.99 que cobria [2025-04-01, 2026-01-01) é dividida em duas linhas remanescentes: uma que termina em junho e outra que começa em outubro
  • Os dados de preço antes e depois da lacuna são preservados com seus valores originais, fazendo com que o DELETE aumente o número de linhas
• O mecanismo de gerenciamento de tabelas temporais cuida de tudo automaticamente, eliminando no nível da aplicação o risco de exclusões excessivas ou fragmentos órfãos (orphaned fragments)

Verdade na propaganda - Truth in Advertising

• Tabelas temporais não ficam completas sem chaves estrangeiras temporais (temporal foreign keys), e o Postgres 19 oferece suporte a isso com a palavra-chave PERIOD
  • No formato FOREIGN KEY (product_id, PERIOD valid_at) REFERENCES products (product_id, PERIOD valid_at)
• A palavra-chave PERIOD indica que a própria chave estrangeira é baseada em tempo
  • O product referenciado deve existir durante todo o período do intervalo valid_at do variant
  • A combinação de todas as linhas correspondentes da tabela referenciada deve cobrir completamente o período da linha que faz a referência
• Uma tentativa de criar um variant que ultrapasse o intervalo temporal do product ([2025-01-01, 2027-01-01)) é rejeitada
  • Como o preço do Widget só está definido até meados de 2026, um variant que afirma ser válido até 2027 é rejeitado por violar a restrição de chave estrangeira
• Uma limitação importante
  • Chaves estrangeiras temporais só suportam NO ACTION como ação referencial, excluindo CASCADE, SET NULL e SET DEFAULT
  • Ao excluir uma linha de product da qual um variant depende, sempre ocorre erro, devido à complexidade das operações temporais em cascata; a aplicação precisa lidar com isso explicitamente

Pequenos passos - Baby Steps

• Funcionalidades já disponíveis: tabelas temporais baseadas em tempo válido com prevenção de sobreposição, manipulação de dados temporais e chaves estrangeiras temporais
• A maior ausência é o tempo de sistema (system time), também chamado de tempo de transação
  • O tempo válido rastreia quando um fato é verdadeiro no mundo real; o tempo de sistema rastreia quando o banco de dados toma conhecimento desse fato; muitos sistemas usam ambos
  • Essa é a área que a extensão pg_bitemporal vem preenchendo desde 2015
  • É possível emular (emulation) tempo de sistema com triggers, mas isso não é o mesmo que o mecanismo gerenciar tudo de forma transparente, como faz com os novos recursos temporais
• A documentação de tabelas temporais também explicita que o tempo de sistema não tem suporte nativo e pode ser emulado; ainda não se sabe se ele chegará no Postgres 20 ou depois, mas a base já está preparada

Considerações finais - Final Thoughts

• A abordagem EXCLUDE USING gist funciona, mas é um contorno relativamente tosco; extensões como pg_bitemporal provaram o conceito e mantiveram a discussão viva
• Uma abordagem muito mais intuitiva que as restrições de exclusão GiST
  • WITHOUT OVERLAPS em chaves primárias se lê quase como inglês comum, e FOR PORTION OF descreve exatamente a ação
  • A divisão automática de linhas em atualizações e exclusões temporais elimina uma classe de bugs potenciais
• A jornada do SQL:2011 ao Postgres 19 foi longa, e Hetti e a comunidade demonstraram por anos a necessidade e a viabilidade desses padrões; agora eles chegaram ao core
• Vale acompanhar o suporte a tempo de sistema em versões futuras; quando o Postgres tiver as duas metades da bitemporalidade, as possibilidades se expandirão bastante