Rapport d’optimisation du chargement de données – Préparation à l’insertion via COPY

Rapport d’optimisation du chargement de données – Préparation à l’insertion via COPY

Contexte

L’application actuelle utilise spring-boot-starter-jdbc et Spring WebMvc pour traiter des fichiers en streaming, avec insertion par batch via JPA/Hibernate. Le traitement souffre de performances insuffisantes (15 minutes pour 1M de lignes), principalement à cause :

• Des insertions par lots avec RETURNING id,
• Des vérifications de contraintes et index GIN recalculés à chaque lot,
• Des accès répétés à la base pour les correspondances de références (via checker).

---

Optimisations recommandées (immédiates, compatibles avec les dépendances actuelles)

1. Remplacer les batchs par COPY FROM STDIN via CopyManager

• Solution : Utiliser CopyManager de postgresql avec un flux TSV généré depuis le JSON.
• Mise en œuvre :
  • Transformer Flux<DataValue> ou Stream en lignes TSV.
  • Alimenter CopyManager.copyIn(sql, inputStream) avec un PipedInputStream alimenté par un thread.
• Compatibilité : 100 % compatible avec spring-boot-starter-jdbc – aucune migration vers WebFlux ou R2DBC requise.
• Gain estimé : de 15 min à 15 s (~60x) pour 1M de lignes.
  • Temps de COPY : 10–12 s.
  • Index GIN reconstruits post-insertion : 2–3 s.
  • Vérification CHECK unique : 1–2 s.

Impact : Réduction de 98 % du temps de traitement.

2. Vérification des références : chargement complet en mémoire des maps

• Solution : Charger tous les référentiels (sites, variables, échantillons, itinéraires) en Map<String, RefData> au démarrage.
• Empreinte mémoire : ~200 Mo pour 1M de lignes (UUID + 2 Ltree + clé).
• Avantage : Élimine tous les accès SQL pendant le traitement → accès O(1) en mémoire.
• Argumentaire : 200 Mo est négligeable sur un heap Java 21 typique (2–8 Go).

Gain : Suppression de 1M d’allers-retours → gain de plusieurs secondes, surtout si les index sont fragmentés.

3. Gestion des index et contraintes : désactivation temporaire

• Stratégie 😀

ALTER TABLE referencevalue DROP CONSTRAINT check_valid_refs, DISABLE TRIGGER ALL; -- Exécuter COPY ici ALTER TABLE referencevalue ADD CONSTRAINT check_valid_refs CHECK (...), ENABLE TRIGGER ALL; CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_refvalues_gin ON referencevalue USING GIN (refValues);

Impact : Accélération de la reconstruction des index GIN (bulk vs incrémental).

• Gain : 2x à 5x sur la phase d’indexation.

---

Optimisations moins utiles (dans ce contexte)

Optimisation	Pourquoi pas prioritaire
Migration vers WebFlux + R2DBC	Inutile pour COPY – CopyManager avec JDBC est aussi rapide. Complexité élevée, refonte complète.
Utilisation de CompletableFuture séparés	Déjà couvert par le chargement en mémoire. Apporte peu de gain marginal.
Parallélisation CPU du parsing	Déjà très rapide (microsecondes/ligne). Non bloquant comparé à I/O.

---

Solution la plus simple et optimale (avec dépendances actuelles)

1. Transformer le flux JSON → TSV en streaming :
   • Générer une ligne TSV par DataValue.
   • Gérer null → \\N, échapper `` → \\\\, sérialiser JSON.
2. Utiliser CopyManager via JDBC : ---java try (InputStream tsvStream = createTsvStream(dataStream); Connection conn = dataSource.getConnection()) { PGConnection pgConn = conn.unwrap(PGConnection.class); pgConn.getCopyAPI().copyIn( "COPY referencevalue (id, application, patternColumnName, ReferenceType, hierarchicalKey, naturalKey, refValues, refsLinkedTo, binaryFile, authorization) FROM STDIN WITH (FORMAT TSV)", tsvStream ); }

---

3. Précharger toutes les maps de référence :
   • Map<String, RefData> par type.
   • Taille max estimée : 200 Mo → acceptable.
4. Désactiver temporairement contraintes et index.

✅ Pas de changement d’architecture, gain de 60x, maintenabilité préservée.

---

Évolutions futures (complexes, gains marginaux)

Évolution	Mise en œuvre	Gain attendu
Migration vers R2DBC + COPY réactif	Remplacer JDBC par r2dbc-postgresql, utiliser CopyIn avec Sinks.Many.	Équivalent à CopyManager – gain négligeable. Utile seulement si tout le système devient réactif.
Cache LRU adaptatif	Utiliser LoadingCache avec bascule si > 1M d’entrées.	Évite OOM, mais +200 Mo ≠ risque. Gain : 0–5 %.
Partitionnement de la table	PARTITION BY HASH(naturalKey) pour insertion parallèle.	Possible gain sur très gros volumes (>100M), mais complexité opérationnelle élevée.

---

Conclusion

• Solution immédiate : COPY + CopyManager + chargement complet des maps.
• Gain : 15 min → 15 s (~60x), avec 0 modification d’architecture.
• Évolutions complexes : non nécessaires, gains marginaux, coûts élevés.
• Recommandation : implémenter la solution simple maintenant, et réévaluer si le volume dépasse 100M de lignes.

✔️ Objectif atteint : performance, simplicité, maintenabilité.