Postgre accéléré de 10 fois
Accéléré PostgreSQL de 10 fois grâce à l'indexation, au partitionnement et à l'optimisation des requêtes sur les données des utilisateurs, des organisations clientes, les données géospatiales et les séries temporelles électriques.
2022 – 2024
Situation. La base PostgreSQL servait une application de taille moyenne gérant des comptes utilisateurs, des informations sur les organisations clientes, des données géographiques, par exemple des lieux et des régions, ainsi que des indicateurs quotidiens de consommation électrique. Bien que le système fonctionne avec un trafic faible, des problèmes de performance sont apparus avec l’augmentation des volumes de données. Les requêtes portant sur les données géospatiales et les journaux chronologiques de consommation électrique ralentissaient, entraînant des temps de réponse irréguliers. L’absence d’indexation optimisée, les requêtes fragmentées et les tables non partitionnées aggravaient le problème, créant des goulets d’étranglement pour des opérations critiques comme l’authentification des utilisateurs, la gestion des organisations clientes et la production de rapports.
Tâche. L’objectif était de multiplier les performances de la base de données par 10 sans refondre l’architecture existante. Le travail portait sur l’optimisation de l’exécution des requêtes, la réduction de la latence et la capacité à absorber la croissance future des données. Les priorités étaient d’améliorer les temps de réponse des requêtes géospatiales et temporelles, de réduire la concurrence pour les ressources et de préserver l’intégrité des données pendant les modifications.
Action. 1. Indexation : analyse des colonnes fréquemment interrogées, par exemple les identifiants d’utilisateurs et d’organisations clientes et les coordonnées géospatiales, puis création d’index ciblés. Des index GiST ont été ajoutés aux données géospatiales pour accélérer les requêtes spatiales. Des index composites ont été introduits pour les filtres multicolonnes, comme l’identifiant d’organisation cliente associé à une plage de dates pour les données électriques.
2. Partitionnement : mise en œuvre d’un partitionnement par plages temporelles de la table des données électriques, divisée par jour/mois. Cela réduisait le volume de données à examiner par requête et améliorait l’efficacité des parcours. Pour les données géographiques, un partitionnement par hachage a été utilisé pour répartir uniformément la charge entre les nœuds.
3. Optimisation des requêtes : réécriture des requêtes complexes pour éviter les parcours complets des tables, en utilisant des CTE (Common Table Expressions) et des vues matérialisées pour les jeux de données fréquemment consultés. L’outil EXPLAIN ANALYZE a identifié des jointures et sous‑requêtes inefficaces, restructurées pour obtenir de meilleurs plans d’exécution. En complément, la mise en cache des requêtes et la mutualisation des connexions ont été configurées pour réduire les surcoûts de traitement.
Résultat. Après optimisation, les temps de réponse des requêtes se sont améliorés d’un facteur 10, les opérations critiques, par exemple l’authentification et les recherches géospatiales, s’exécutant en quelques millisecondes. L’utilisation des ressources de la base a diminué de 40 %, permettant au système de traiter des volumes de données plus importants sans dégradation des performances. Les utilisateurs ont signalé des interactions plus fluides et le système pouvait mieux accompagner la croissance future. Les modifications ont également réduit le besoin de mises à niveau matérielles, limitant les coûts tout en maintenant la fiabilité à long terme. Ce projet a montré comment une indexation, un partitionnement et une révision des requêtes bien ciblés peuvent transformer même des systèmes peu chargés en bases de données efficaces et prêtes à évoluer.