[infra] TimescaleDB, hypertables et compression

lenaic commented

2026-09-01 10:35:24 +00:00

Owner

Exigence couverte

ENF-04, ENF-07

Épreuve servie

EC05 · Data, ETL et BI

Charge estimée

1 j.h

Ce qu'on veut obtenir

Préparer la base à recevoir la zone or matérialisée, avec des séries temporelles interrogeables sous 400 ms sur 24 heures.

Critères d'acceptation

L'extension timescaledb est active sur l'instance du ticket d'installation PostgreSQL.
Les tables de mesures sont converties en hypertables, partitionnées sur l'horodatage.
Une politique de compression est posée au delà de sept jours.
Un jeu de test d'au moins un mois de données permet de mesurer une requête de 24 heures.

Comment on le vérifie

Commande \dx dans psql, puis EXPLAIN ANALYZE sur une fenêtre de 24 heures
Attendu extension présente, requête sous 400 ms
Preuve la sortie du EXPLAIN ANALYZE

Manuel d'exploitation à mettre à jour

docs/runbooks/postgres.md, section hypertables et compression

Risque et retour arrière

Une conversion en hypertable sur une table déjà remplie est longue. La faire avant le chargement. Retour arrière : la table reste une table ordinaire, la cible de performance saute.

### Exigence couverte ENF-04, ENF-07 ### Épreuve servie EC05 · Data, ETL et BI ### Charge estimée 1 j.h ### Ce qu'on veut obtenir Préparer la base à recevoir la zone or matérialisée, avec des séries temporelles interrogeables sous 400 ms sur 24 heures. ### Critères d'acceptation - [ ] L'extension timescaledb est active sur l'instance du ticket d'installation PostgreSQL. - [ ] Les tables de mesures sont converties en hypertables, partitionnées sur l'horodatage. - [ ] Une politique de compression est posée au delà de sept jours. - [ ] Un jeu de test d'au moins un mois de données permet de mesurer une requête de 24 heures. ### Comment on le vérifie Commande \dx dans psql, puis EXPLAIN ANALYZE sur une fenêtre de 24 heures Attendu extension présente, requête sous 400 ms Preuve la sortie du EXPLAIN ANALYZE ### Manuel d'exploitation à mettre à jour docs/runbooks/postgres.md, section hypertables et compression ### Risque et retour arrière Une conversion en hypertable sur une table déjà remplie est longue. La faire avant le chargement. Retour arrière : la table reste une table ordinaire, la cible de performance saute.

lenaic added the

labels

2026-09-01 10:35:24 +00:00

justine was assigned by lenaic

2026-09-01 10:35:24 +00:00

lenaic added a new dependency

2026-09-01 10:36:09 +00:00

#26 [Infra] Installation PostgreSQL 17

lenaic added a new dependency

2026-09-01 10:36:09 +00:00

#35 [EF-12] Agrégation et zone or matérialisée

florian added this to the EnerVision project

2026-09-01 12:06:41 +00:00

olivier was assigned by lenaic

2026-09-02 09:59:21 +00:00

lenaic referenced this issue

2026-09-03 08:32:51 +00:00

[EF-12] Migrations de la zone or : les sept tables et le référentiel des sites #105

lenaic added a new dependency

2026-09-03 08:32:51 +00:00

#105 [EF-12] Migrations de la zone or : les sept tables et le référentiel des sites

olivier referenced this issue from a commit

2026-09-03 08:51:21 +00:00

db : amorce la zone or, hypertable « mesure » et compression au-delà de 7 jours

olivier referenced this issue

2026-09-03 08:52:13 +00:00

db : amorce la zone or, hypertable « mesure » et compression au-delà de 7 jours #106

olivier commented

2026-09-03 08:54:11 +00:00

Member

Vérifié et fait — relevé du 3 septembre 2026

Serveur ml-stagiaire-02, conteneur ev-postgres (enervision/postgres:17-ts2.29.2, PostgreSQL 17.11).

Un préalable qu'il fallait lever

Les quatre critères supposaient des tables de mesures à convertir. Il n'y en avait aucune : enervision_prod et enervision_preprod comptaient zéro table dans public, seuls les catalogues internes de TimescaleDB étaient là — ce que voit tout client SQL et qu'on prend facilement pour une installation configurée. C'est le constat du #105.

La migration 0007_zone_or_mesure.sql pose donc le strict nécessaire : site (les sept sites du GLOSSAIRE) et mesure en hypertable. Les cinq autres tables de la zone or restent au #105. Demande de fusion #106.

Le modèle suit le dossier d'architecture collectif, pas la forme esquissée dans docs/POSTGRESQL.md : clé capteur_id étrangère aux zones bronze et argent, valeur unique là où l'ADR 0006 exige la brute à côté de la retenue, et data_quality par défaut à 'ok' qui n'est dans aucune des deux énumérations retenues. Le document est corrigé, motifs inclus.

Les critères

L'extension timescaledb est active. 2.29.2 sur enervision_prod et enervision_preprod, avec l'ordonnanceur de fond actif sur chacune plus le lanceur. timescaledb.max_background_workers = 8 pour 4 bases (max_worker_processes = 16) : le correctif du 2 septembre tient.
Les tables de mesures sont converties en hypertables, partitionnées sur l'horodatage. mesure est une hypertable partitionnée sur horodatage, fragments d'un jour. Sur préproduction : 36 fragments pour 35 jours.
Une politique de compression est posée au-delà de sept jours.

 job_id |     proc_name      |                      config                      | schedule_interval
--------+--------------------+--------------------------------------------------+-------------------
   1000 | policy_compression | {"hypertable_id": 1, "compress_after": "7 days"} | 12:00:00

Elle agit, ce n'est pas qu'une déclaration : 28 fragments comprimés sur 36, les 8 restants étant exactement ceux de moins de sept jours. 35 Mo → 5 488 kio, facteur 6,5.

Un jeu de test d'au moins un mois permet de mesurer une requête de 24 heures. 352 807 lignes, 35 jours au pas de la minute sur les 7 sites, du 30/07 au 03/09. Profil conforme au GLOSSAIRE (creux nocturne, repli de week-end sur bureaux et commerces) et aux trois régimes de l'ADR 0006 : 97,52 % good, 0,97 % imputed (brute nulle, valeur reconstituée à côté), 1,00 % suspect, 0,51 % critical (tout nul).

La preuve — `EXPLAIN ANALYZE` sur 24 heures

Agrégat horaire, les 7 sites, fenêtre de 24 h :

 Sort (actual time=5.776..5.785 rows=175 loops=1)
   ->  Finalize HashAggregate (actual time=5.484..5.564 rows=175 loops=1)
         ->  Custom Scan (ChunkAppend) on mesure (actual time=3.573..5.342 rows=175 loops=1)
               Chunks excluded during startup: 0
               ->  Partial HashAggregate (actual time=3.572..3.614 rows=112 loops=1)
                     ->  Index Scan using _hyper_1_35_chunk_mesure_horodatage_idx on _hyper_1_35_chunk
                           Index Cond: (horodatage >= (now() - '24:00:00'::interval))
               ->  Partial HashAggregate (actual time=1.691..1.714 rows=63 loops=1)
                     ->  Seq Scan on _hyper_1_36_chunk
 Planning Time: 0.473 ms
 Execution Time: 5.043 ms

ChunkAppend ne touche que 2 fragments sur 36 : c'est le partitionnement journalier qui fait son travail.

Sur 20 exécutions : médiane 3,67 ms, p95 4,09 ms, max 10,31 ms. Cible 400 ms, marge d'un facteur 100.

Et la même fenêtre de 24 h prise dans les fragments comprimés (J-20), le cas le plus défavorable :

 ->  Custom Scan (ChunkAppend) on mesure (actual time=2.149..3.721 rows=175 loops=1)
       Chunks excluded during startup: 19
       ->  Custom Scan (ColumnarScan) on _hyper_1_16_chunk (actual time=0.140..0.839 rows=6384)
             Vectorized Filter: ((horodatage >= ...) AND (horodatage < ...))
             ->  Seq Scan on _hyper_1_16_chunk_compressed (actual rows=7 loops=1)
                   Filter: ((_ts_meta_v2_first_horodatage >= ...) AND (_ts_meta_v2_last_horodatage < ...))
 Execution Time: 4.939 ms

Lecture en ColumnarScan vectorisé, 19 fragments écartés au démarrage : 4,94 ms. Comprimer ne coûte rien en lecture ici.

Deux choses à savoir

La production est vide, et c'est voulu. mesure y est une hypertable comprimable avec sa politique, mais 0 ligne : la note de risque de ce ticket demande de convertir avant le chargement, une conversion de table remplie étant longue. La table attend la collecte du #33. Le jeu de test vit en préproduction seule, c'est là que la latence se mesure.

Un écart d'installation corrigé au passage. pg_default_acl était vide sur enervision_preprod alors que enervision_prod portait grafana=r sur public. Les tables créées ensuite auraient été lisibles par Grafana en production et muettes en préproduction — une asymétrie qui ne se voit qu'au moment de la démonstration. La clause manquante est posée, et la migration porte en plus un grant select explicite pour ne plus en dépendre.

Manuel d'exploitation

docs/runbooks/postgresql.md §9 reçoit une sous-section « Hypertables et compression » : état des fragments, gain réel, forcer un passage de la politique, et ce qu'une table comprimée refuse — un UPDATE ligne à ligne sur un fragment comprimé échoue ou coûte très cher, une reprise d'historique demande de décomprimer d'abord. docs/POSTGRESQL.md et db/migrations/README.md sont à jour.

La migration se rejoue sans effet de bord (vérifié en transaction annulée sur une hypertable déjà comprimée : uniquement des NOTICE).

Relevé et migration produits avec Claude Code.

## Vérifié et fait — relevé du 3 septembre 2026 Serveur `ml-stagiaire-02`, conteneur `ev-postgres` (`enervision/postgres:17-ts2.29.2`, PostgreSQL 17.11). ### Un préalable qu'il fallait lever Les quatre critères supposaient des tables de mesures à convertir. **Il n'y en avait aucune** : `enervision_prod` et `enervision_preprod` comptaient zéro table dans `public`, seuls les catalogues internes de TimescaleDB étaient là — ce que voit tout client SQL et qu'on prend facilement pour une installation configurée. C'est le constat du #105. La migration `0007_zone_or_mesure.sql` pose donc le strict nécessaire : `site` (les sept sites du `GLOSSAIRE`) et `mesure` en hypertable. **Les cinq autres tables de la zone or restent au #105.** Demande de fusion #106. Le modèle suit le dossier d'architecture collectif, **pas** la forme esquissée dans `docs/POSTGRESQL.md` : clé `capteur_id` étrangère aux zones bronze et argent, valeur unique là où l'ADR 0006 exige la brute à côté de la retenue, et `data_quality` par défaut à `'ok'` qui n'est dans aucune des deux énumérations retenues. Le document est corrigé, motifs inclus. ### Les critères - [x] **L'extension `timescaledb` est active.** `2.29.2` sur `enervision_prod` **et** `enervision_preprod`, avec l'ordonnanceur de fond actif sur chacune plus le lanceur. `timescaledb.max_background_workers = 8` pour 4 bases (`max_worker_processes = 16`) : le correctif du 2 septembre tient. - [x] **Les tables de mesures sont converties en hypertables, partitionnées sur l'horodatage.** `mesure` est une hypertable partitionnée sur `horodatage`, **fragments d'un jour**. Sur préproduction : 36 fragments pour 35 jours. - [x] **Une politique de compression est posée au-delà de sept jours.** ``` job_id | proc_name | config | schedule_interval --------+--------------------+--------------------------------------------------+------------------- 1000 | policy_compression | {"hypertable_id": 1, "compress_after": "7 days"} | 12:00:00 ``` Elle agit, ce n'est pas qu'une déclaration : `28 fragments comprimés sur 36`, les 8 restants étant exactement ceux de moins de sept jours. **35 Mo → 5 488 kio, facteur 6,5.** - [x] **Un jeu de test d'au moins un mois permet de mesurer une requête de 24 heures.** 352 807 lignes, **35 jours au pas de la minute** sur les 7 sites, du 30/07 au 03/09. Profil conforme au `GLOSSAIRE` (creux nocturne, repli de week-end sur bureaux et commerces) et aux trois régimes de l'ADR 0006 : 97,52 % `good`, 0,97 % `imputed` (brute nulle, valeur reconstituée à côté), 1,00 % `suspect`, 0,51 % `critical` (tout nul). ### La preuve — `EXPLAIN ANALYZE` sur 24 heures Agrégat horaire, les 7 sites, fenêtre de 24 h : ``` Sort (actual time=5.776..5.785 rows=175 loops=1) -> Finalize HashAggregate (actual time=5.484..5.564 rows=175 loops=1) -> Custom Scan (ChunkAppend) on mesure (actual time=3.573..5.342 rows=175 loops=1) Chunks excluded during startup: 0 -> Partial HashAggregate (actual time=3.572..3.614 rows=112 loops=1) -> Index Scan using _hyper_1_35_chunk_mesure_horodatage_idx on _hyper_1_35_chunk Index Cond: (horodatage >= (now() - '24:00:00'::interval)) -> Partial HashAggregate (actual time=1.691..1.714 rows=63 loops=1) -> Seq Scan on _hyper_1_36_chunk Planning Time: 0.473 ms Execution Time: 5.043 ms ``` `ChunkAppend` ne touche que **2 fragments sur 36** : c'est le partitionnement journalier qui fait son travail. Sur **20 exécutions** : médiane **3,67 ms**, p95 **4,09 ms**, max 10,31 ms. **Cible 400 ms, marge d'un facteur 100.** Et la même fenêtre de 24 h prise *dans* les fragments comprimés (J-20), le cas le plus défavorable : ``` -> Custom Scan (ChunkAppend) on mesure (actual time=2.149..3.721 rows=175 loops=1) Chunks excluded during startup: 19 -> Custom Scan (ColumnarScan) on _hyper_1_16_chunk (actual time=0.140..0.839 rows=6384) Vectorized Filter: ((horodatage >= ...) AND (horodatage < ...)) -> Seq Scan on _hyper_1_16_chunk_compressed (actual rows=7 loops=1) Filter: ((_ts_meta_v2_first_horodatage >= ...) AND (_ts_meta_v2_last_horodatage < ...)) Execution Time: 4.939 ms ``` Lecture en `ColumnarScan` vectorisé, 19 fragments écartés au démarrage : **4,94 ms**. Comprimer ne coûte rien en lecture ici. ### Deux choses à savoir **La production est vide, et c'est voulu.** `mesure` y est une hypertable comprimable avec sa politique, mais **0 ligne** : la note de risque de ce ticket demande de convertir *avant* le chargement, une conversion de table remplie étant longue. La table attend la collecte du #33. Le jeu de test vit en préproduction seule, c'est là que la latence se mesure. **Un écart d'installation corrigé au passage.** `pg_default_acl` était **vide** sur `enervision_preprod` alors que `enervision_prod` portait `grafana=r` sur `public`. Les tables créées ensuite auraient été lisibles par Grafana en production et muettes en préproduction — une asymétrie qui ne se voit qu'au moment de la démonstration. La clause manquante est posée, et la migration porte en plus un `grant select` explicite pour ne plus en dépendre. ### Manuel d'exploitation `docs/runbooks/postgresql.md` §9 reçoit une sous-section « Hypertables et compression » : état des fragments, gain réel, forcer un passage de la politique, et **ce qu'une table comprimée refuse** — un `UPDATE` ligne à ligne sur un fragment comprimé échoue ou coûte très cher, une reprise d'historique demande de décomprimer d'abord. `docs/POSTGRESQL.md` et `db/migrations/README.md` sont à jour. La migration se rejoue sans effet de bord (vérifié en transaction annulée sur une hypertable déjà comprimée : uniquement des `NOTICE`). *Relevé et migration produits avec Claude Code.*

olivier referenced this issue from a commit

2026-09-03 09:28:01 +00:00

db: renumérote le lot au-dessus du 0007 du #29 et le rend « crée ou complète »

olivier referenced this issue from a commit

2026-09-03 09:28:01 +00:00

docs: renumérote la fiche en 0008 et raccorde le manuel aux deux tickets

olivier referenced this issue

2026-09-03 09:29:21 +00:00

db : les sept tables de la zone or et le référentiel des sites (#105) #110

olivier referenced this issue

2026-09-03 09:30:18 +00:00

[EF-12] Migrations de la zone or : les sept tables et le référentiel des sites #105