PostgreSQL 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.

Catégories:
1Bases de données	/fr/categories/databases/	engineer.company	70
1PostgreSQL	/fr/categories/postgresql/	engineer.company	70
1SQL	/fr/categories/sql/	engineer.company	70
1Ingénierie des données	/fr/categories/data-engineering/	engineer.company	70
1Optimisation des performances	/fr/categories/performance/	engineer.company	70
1GIS et données géospatiales	/fr/categories/gis/	engineer.company	70

Prestations:
1Administration de bases de données (DBA)	/fr/services/database-administration/	engineer.company	70
1Optimisation des performances de bases de données	/fr/services/database-performance/	engineer.company	70

hCette page sur le Web	URL:https://engineer.company/fr/portfolio/accelerated-postgresql-performance-by-10x/	engineer.company	70
