En attendant Postgres 19 : enfin le temps de s’occuper du « temps »

• Pour répondre aux questions du type « à quoi ressemblaient les données à une date donnée (mardi dernier) ? », Postgres 19 introduit la prise en charge native des tables temporelles (temporal tables), permettant de suivre les données avant et après modification sans système de triggers d’audit séparé
• Postgres intègre enfin dans son cœur les tables temporelles définies il y a plus de dix ans par le standard SQL:2011, rejoignant plus tardivement d’autres moteurs de bases de données
• Au lieu de deux colonnes valid_from/valid_to et d’une contrainte d’exclusion (exclusion constraint) basée sur l’extension btree_gist, l’approche propose une expression plus intuitive avec une seule colonne de type intervalle (range type) et une contrainte WITHOUT OVERLAPS
• Avec la syntaxe FOR PORTION OF, le moteur découpe automatiquement les lignes lors des UPDATE et DELETE, et se charge d’éviter les trous et les chevauchements sur l’axe du temps
• Cette introduction correspond à la moitié temps de validité (application time) des systèmes bitemporels ; le temps système (system time) n’est pas pris en charge, mais les bases sont posées pour de futures versions

---

L’ancienne méthode - The Old-Fashioned Way

• Une première tentative de suivi chronologique des prix de produits reposait sur deux colonnes de date valid_from, valid_to et une contrainte CHECK valid_from < valid_to
  • Mais cela n’empêchait pas l’insertion de deux lignes dont les plages de dates se chevauchent pour un même produit (par exemple, le produit 42 à la fois à 9,99 $ et à 14,99 $ le même mardi)
• La solution traditionnelle consiste à utiliser l’extension btree_gist et une contrainte d’exclusion (exclusion constraint)
  • Sous la forme EXCLUDE USING gist (product_id WITH =, daterange(valid_from, valid_to) WITH &&), ce qui provoque une erreur lors de l’insertion de lignes qui se chevauchent
• Les problèmes de cette approche
  • GiST est un index propre à Postgres qui demande de l’expérience, et le fait qu’il s’agisse d’une extension optionnelle crée une barrière à l’entrée
  • La syntaxe des contraintes d’exclusion n’est pas intuitive, ce qui rend difficile d’y penser comme approche standard
  • La table elle-même n’a pas de conscience temporelle intégrée : lors d’un changement de plage temporelle, il faut découper et fusionner les lignes manuellement, et la responsabilité de la cohérence temporelle est reportée sur l’application

Brève histoire du temps - A Brief History of Time

• Le standard SQL:2011 a introduit les périodes de temps de validité (APPLICATION TIME), la contrainte WITHOUT OVERLAPS et la syntaxe FOR PORTION OF pour manipuler les données temporelles
• Henrietta Dombrovskaya (Hetti), avec Chad Slaughter, a développé l’extension pg_bitemporal, un framework en PL/pgSQL pour gérer des tables bitemporelles dans Postgres
  • Depuis 2015, elle a présenté le concept dans plusieurs conférences et démontré comment suivre simultanément le temps de validité (valid time) (le moment où un fait est vrai dans le monde réel) et le temps de transaction (transaction time) (le moment où la base de données a enregistré ce fait)
• Distinction entre les deux dimensions temporelles
  • Le temps de validité signifie « ce prix est valable de janvier à juin »
  • Le temps de transaction correspond au point de vue de la base de données : « cette ligne a été insérée le 12 mars à 15 h 47 et remplacée le 3 avril à 9 h 01 »
  • En les combinant, on construit une table bitemporelle capable de répondre à la question : « selon les informations connues à l’époque, quel prix pensions-nous être celui de mardi dernier ? »
• pg_bitemporal utilise deux fois EXCLUDE USING gist, une fois pour la plage effective (temps de validité) et une fois pour la plage asserted (temps de transaction)
  • Elle fournit des fonctions d’insertion, de mise à jour, de correction, de désactivation et de suppression bitemporelles, ainsi qu’une implémentation des relations d’intervalles d’Allen (Allen's interval relationships) pour le raisonnement temporel
• Limites de l’extension
  • Elle ne peut pas modifier le planificateur de requêtes pour qu’il reconnaisse les conditions temporelles, ni s’intégrer au système de contraintes au niveau du moteur, ni fournir une syntaxe de manipulation native → une intégration au cœur est nécessaire
  • Postgres 19 adopte la moitié temps de validité des systèmes bitemporels ; ce n’est pas encore l’ensemble, mais c’est une avancée majeure

Les intervalles à la rescousse - Ranges to the Rescue

• L’approche de Postgres 19 utilise une seule colonne de type intervalle valid_at DATERANGE au lieu de colonnes séparées valid_from/valid_to
  • Avec PRIMARY KEY (product_id, valid_at WITHOUT OVERLAPS), l’extension btree_gist et les contraintes d’exclusion ne sont plus nécessaires côté utilisateur
  • WITHOUT OVERLAPS garantit que product_id est unique à n’importe quel instant, tout en autorisant plusieurs lignes pour un même produit tant que leurs plages ne se chevauchent pas
• En interne, cela utilise toujours un index GiST et nécessite btree_gist pour les colonnes non temporelles de la clé, mais Postgres gère automatiquement les dépendances lors de l’initialisation de la contrainte
• Notation des intervalles : [ signifie inclus, ) signifie exclu → [2025-01-01, 2025-07-01) inclut le 1er janvier mais exclut le 1er juillet
  • La dernière ligne Gadget [2026-01-01,) est une plage ouverte (open-ended) : aucun jour de fin n’est défini pour le prix actuel
• Fonctionnement de la protection contre les chevauchements
  • L’insertion d’une plage incorrecte [2025-03-01, 2025-01-01) provoque une erreur indiquant que « la borne inférieure de la plage doit être inférieure ou égale à la borne supérieure »
  • L’insertion d’une plage qui se chevauche [2025-03-01, 2025-09-01) provoque une violation de la contrainte d’exclusion products_pkey
  • L’utilisation des plages permet d’obtenir deux validations en une seule fois

Découper et répartir - Slicing and Dicing

• Si l’on veut modifier le prix d’un produit à 10,99 $ uniquement de mars à septembre 2025, l’ancienne méthode obligeait à découper et insérer les lignes manuellement, avec un risque de trous ou de chevauchements en cas d’erreur
• Avec les tables temporelles, l’intention s’exprime directement
  • UPDATE products FOR PORTION OF valid_at FROM '2025-03-01' TO '2025-09-01' SET price = 10.99 WHERE product_id = 1
• Au final, les lignes de Widget passent de 3 à 5
  • La ligne existante à 9,99 $ est réduite à [2025-01-01, 2025-03-01)
  • Une nouvelle ligne à 10,99 $ est ajoutée pour la plage restante
  • La ligne existante à 12,99 $ est réduite à [2025-09-01, 2026-01-01)
  • Une nouvelle ligne à 10,99 $ est ajoutée pour la plage restante [2025-07-01, 2025-09-01)
• La raison pour laquelle le prix à 10,99 $ est réparti sur deux lignes est que FOR PORTION OF traite chaque ligne correspondante indépendamment, sans fusionner ensuite les plages adjacentes (coalesce)
  • Le résultat final ne comporte ni trous ni chevauchements, un avantage que la pure logique d’exclusion n’offrait pas
• Cas limites (edge cases)
  • Si la plage FOR PORTION OF se trouve entièrement à l’intérieur d’une seule ligne existante, jusqu’à deux lignes résiduelles (avant et après) sont créées
  • Si elle coïncide exactement avec les frontières existantes, aucune ligne résiduelle n’est nécessaire
• Les nouvelles lignes résiduelles temporelles ne nécessitent pas le privilège INSERT, mais les triggers INSERT existants se déclenchent tout de même → à prendre en compte pour les logs d’audit ou les fonctions de trigger SECURITY DEFINER

Effacer l’historique - Erasing History

• FOR PORTION OF fonctionne aussi avec DELETE ; prenons l’exemple d’un retrait temporaire d’un produit du catalogue de juin à octobre 2025
  • DELETE FROM products FOR PORTION OF valid_at FROM '2025-06-01' TO '2025-10-01' WHERE product_id = 2
• Résultat
  • La période de juin à octobre est supprimée, et la ligne à 22,99 $ qui couvrait [2025-04-01, 2026-01-01) est scindée en deux lignes résiduelles : l’une qui se termine en juin, l’autre qui commence en octobre
  • Les données de prix avant et après le trou sont conservées avec leurs valeurs d’origine, ce qui fait que DELETE augmente le nombre de lignes
• Le mécanisme de gestion des tables temporelles prend tout cela en charge automatiquement, supprimant au niveau applicatif les risques de suppression excessive ou de fragments orphelins (orphaned fragments)

La vérité dans la publicité - Truth in Advertising

• Les tables temporelles ne sont pas complètes sans clés étrangères temporelles (temporal foreign keys), que Postgres 19 prend en charge avec le mot-clé PERIOD
  • Sous la forme FOREIGN KEY (product_id, PERIOD valid_at) REFERENCES products (product_id, PERIOD valid_at)
• Le mot-clé PERIOD indique que la clé étrangère elle-même est temporelle
  • Le product référencé doit exister pendant toute la durée de la plage valid_at du variant
  • La combinaison de toutes les lignes correspondantes de la table référencée doit couvrir entièrement la période de la ligne qui référence
• Une tentative de créer un variant dépassant la plage temporelle du product ([2025-01-01, 2027-01-01)) est rejetée
  • Comme le prix de Widget n’est défini que jusqu’au milieu de 2026, un variant prétendant être valide jusqu’en 2027 est rejeté pour violation de contrainte de clé étrangère
• Une limite importante
  • Les clés étrangères temporelles ne prennent en charge que NO ACTION comme action référentielle ; CASCADE, SET NULL et SET DEFAULT sont exclus
  • La suppression d’une ligne product dont dépend un variant provoque toujours une erreur, en raison de la complexité des opérations temporelles en cascade ; l’application doit la gérer explicitement

Petits pas - Baby Steps

• Fonctionnalités actuellement disponibles : tables temporelles basées sur le temps de validité avec prévention des chevauchements, manipulation de données temporelles et clés étrangères temporelles
• Le grand élément manquant est le temps système (system time), aussi appelé temps de transaction
  • Le temps de validité suit le moment où un fait est vrai dans le monde réel ; le temps système suit le moment où la base de données prend connaissance de ce fait, et de nombreux systèmes utilisent les deux
  • C’est la partie que l’extension pg_bitemporal couvre depuis 2015
  • Il est possible d’émuler le temps système avec des triggers, mais ce n’est pas la même chose qu’une gestion transparente par le moteur comme pour les nouvelles fonctionnalités temporelles
• La documentation des tables temporelles indique également que le temps système n’est pas pris en charge nativement et peut être émulé ; son arrivée dans Postgres 20 ou au-delà reste incertaine, mais les fondations sont déjà en place

Conclusion - Final Thoughts

• L’approche EXCLUDE USING gist fonctionne, mais reste un contournement relativement grossier ; des extensions comme pg_bitemporal ont prouvé le concept et entretenu la discussion
• Une approche bien plus intuitive que les contraintes d’exclusion GiST
  • WITHOUT OVERLAPS dans une clé primaire se lit presque comme de l’anglais courant, et FOR PORTION OF décrit directement l’opération
  • Le découpage automatique des lignes lors des mises à jour et suppressions temporelles élimine toute une catégorie de bugs potentiels
• Le chemin de SQL:2011 à Postgres 19 a été long ; Hetti et la communauté ont démontré pendant des années la nécessité et la faisabilité de ce modèle, désormais intégré au cœur
• Il faudra surveiller la prise en charge du temps système dans les prochaines versions : lorsque Postgres disposera des deux moitiés du bitemporel, les possibilités s’élargiront considérablement