Concevoir une table de séries temporelles en ajout seul dans PostgreSQL

Objectif

Une table qui accepte un flux régulier de lignes horodatées, répond rapidement à « série X entre t1 et t2 », et élimine les anciennes données à faible coût, sans ballonnement (bloat).

Prérequis

Une période de rétention connue, les schémas de requête dominants (une série sur une fenêtre ; des agrégats par fenêtre), une estimation du nombre de lignes par jour, et le type d'horloge : timestamptz, l'abréviation documentée de timestamp with time zone, stocke un instant absolu et évite l'ambiguïté de l'heure d'été.

Étapes

1. Définir la ligne : series_id (clé étrangère vers une table de métadonnées), ts timestamptz NOT NULL, des colonnes de mesure avec NOT NULL là où une valeur manquante est impossible, et pas d'identifiant de substitution à moins que les lignes ne soient référencées individuellement. Une clé primaire sur (series_id, ts) sert aussi la requête principale ; la documentation exige qu'une clé primaire ou une contrainte d'unicité sur une table partitionnée inclue toutes les colonnes de la clé de partition.
2. Créer la table avec PARTITION BY RANGE (ts) et une partition par jour, semaine ou mois, choisie de sorte qu'une fenêtre de requête typique couvre peu de partitions et que la période de rétention soit un nombre entier de partitions. La documentation avertit que trop de partitions allongent la planification et augmentent l'utilisation mémoire.
3. Créer les partitions futures à l'avance depuis un job planifié (ou un outil tel que pg_partman). Une insertion dans une plage manquante échoue ; une partition DEFAULT capture silencieusement de telles lignes, et la documentation note qu'attacher ensuite une partition ultérieure la scanne sous un verrou ACCESS EXCLUSIVE, sauf si une contrainte CHECK exclut la nouvelle plage.
4. Indexer selon le schéma de requête : la clé primaire (B-tree) pour les requêtes par plage et par série ; un index BRIN sur ts pour les scans temporels sur toute la table. La documentation sur les types d'index décrit BRIN comme stockant des résumés par plage de blocs physiques, efficace lorsque les valeurs sont corrélées avec la position physique, ce qui est le cas des données en ajout seul.
5. Mettre en œuvre la rétention par ALTER TABLE ... DETACH PARTITION ... CONCURRENTLY, éventuellement archiver, puis DROP TABLE. La documentation sur le partitionnement indique que c'est bien plus rapide qu'un DELETE en masse et évite la charge de VACUUM que celui-ci provoquerait.
6. Ajouter une table de synthèse (agrégats horaires ou journaliers) remplie par un job lorsque les tableaux de bord interrogent de longues plages.
7. Charger par lots (COPY ou insertion multi-lignes) triés par temps afin que les plages BRIN restent resserrées.

Résultat attendu

Les requêtes avec un prédicat temporel ne touchent que les partitions dans la plage (élagage de partitions), les insertions atterrissent dans la partition la plus récente, les anciennes données disparaissent par une opération de métadonnées, et la suppression ne provoque aucun ballonnement.

Limites et base de vérification

L'élagage nécessite un prédicat sur la clé de partition ; une requête par série sans borne temporelle lit l'index de chaque partition. Les mises à jour et les arrivées désordonnées affaiblissent la corrélation de BRIN. Suit la documentation citée ; aucun chiffre de débit ou de taille n'est revendiqué.