Maîtrisez les performances de PostgreSQL : les pratiques de WeWard pour des bases de données efficaces

Chez WeWard, où chaque milliseconde compte pour améliorer l'expérience utilisateur, nous nous sommes lancés dans une aventure ambitieuse visant à transformer PostgreSQL en un moteur aux performances exceptionnelles. Cet article explore les défis rencontrés, les solutions mises en œuvre et les meilleures pratiques qui nous ont permis d'optimiser PostgreSQL de manière significative.

🎯 Pourquoi l'optimisation des bases de données est-elle cruciale ?

Dans un monde où les utilisateurs s'attendent à des temps de réponse instantanés, la lenteur des bases de données peut être source de frustration et d'opportunités manquées. Chez WeWard, ce défi se traduit par des millions de transactions quotidiennes et des requêtes complexes nécessitant des performances sans faille. Nos défis comprenaient la lenteur des requêtes, une charge excessive et une architecture nécessitant une évolutivité accrue.

Voici comment nous avons surmonté ces obstacles.

⚙ Étape 1 : Optimisation des requêtes

La base de toute optimisation PostgreSQL commence par l'analyse des requêtes. Nous avons identifié des problèmes d'efficacité en étudiant les plans d'exécution des requêtes.

Analyse du plan d'exécution

En utilisant EXPLIQUER et ANALYSER, nous avons obtenu une vue radiographique de nos requêtes.

Exemple de requête problématique :

SÉLECTIONNEZ level_customer. *
DE level_customer
REJOIGNEZ LE NIVEAU SUR level.id = level_customer.level_id
OÙ level_customer.customer_id = XXXXXXX
ET level_customer.start_date <= '2024-05-07' : :date
ET level.version = 2
COMMANDEZ PAR level_customer.start_date DESC, level.value DESC
LIMITE 1 ;


Problèmes détectés :

• Absence d'index pour gérer efficacement le tri.
• Stratégie de jointure sous-optimale choisie par le planificateur.

Solution : Création d'index :

CRÉER UN INDEX idx_level_customer_customer_date_value
ON level_customer (customer_id, start_date DESC, level_id DESC) ;


Ajustement du planificateur :Nous avons forcé PostgreSQL à utiliser Jointure en boucle imbriquée au lieu de la valeur par défaut Hash Join pour cette requête spécifique.

Vues matérialisées :Pour les requêtes complexes, les vues matérialisées ont réduit la charge de calcul à chaque exécution, offrant des performances remarquablement stables.

⚖ Étape 2 : Distribution de la charge avec Replica

Avec des millions de requêtes quotidiennes, nous avons adopté une architecture basée sur des répliques pour répartir la charge.

Configuration de l'architecture :

• Nœud principal : Gère à la fois les opérations de lecture et d'écriture, garantissant ainsi la cohérence des données.
• Nœud de réplique : Dédié aux opérations en lecture seule, réduisant la charge sur le nœud principal et améliorant les temps de réponse pour les requêtes nécessitant une lecture intensive.

Avantages :

• Performances améliorées : Le transfert des requêtes de lecture vers des répliques réduit la latence et augmente le débit.
• Haute disponibilité : En cas de défaillance du nœud principal, les répliques peuvent être promues, garantissant ainsi la continuité du service.
• Évolutivité : Plusieurs nœuds de réplication peuvent être ajoutés pour être redimensionnés horizontalement à mesure que le trafic augmente.

Considérations techniques :

• Latence de réplication : Un léger décalage peut survenir entre le nœud principal et le nœud de réplication.
• Cohérence finale : Les requêtes de lecture sur les répliques peuvent renvoyer des données légèrement obsolètes.
• Surveillance : Une surveillance proactive est essentielle pour garantir une réplication fiable.

🗂 Étape 3 : Gestion des tables volumineuses à l'aide du partitionnement

Pourquoi partitionner ?

Les tableaux contenant des milliards de lignes peuvent considérablement ralentir les opérations de lecture et d'écriture. Le partitionnement divise une table en sous-ensembles plus petits, ce qui améliore l'efficacité des requêtes.

Outil : pg_partman

Avantages :

• Gestion automatique des partitions : Gère la création et la suppression de partitions en fonction de critères prédéfinis.
• Performances de requêtes améliorées : Les requêtes ciblent uniquement les partitions pertinentes.
• Maintenance simplifiée des données historiques : Simplifie l'archivage et la purge sans perturber les données actuelles.

🚦 Étape 4 : Stabilisation de la connexion

Chaque connexion PostgreSQL consomme des ressources système. Pour éviter la surcharge du serveur :

• Limites de connexion : connexions_max a été ajusté en fonction des besoins en matériel et en applications disponibles.
• Regroupement de connexions : La réutilisation des connexions existantes a permis de réduire la charge du serveur et d'améliorer la réactivité des applications.

Paramètres clés de la mémoire PostgreSQL :

• buffers_partagés: Contrôle l'allocation de mémoire pour la mise en cache des données fréquemment consultées.
• work_mem: Définit la mémoire pour le tri et les tables de hachage lors de l'exécution des requêtes.

📈 Étape 5 : Une architecture évolutive avec Data Lake

Les données critiques (« chaudes ») restent dans PostgreSQL, tandis que les données les moins fréquemment consultées (« froides ») sont transférées vers Amazon S3 ou Stockage dans le cloud de Google. L'analyse des données relatives au froid est réalisée à l'aide de Google BigQuery.

Avantages :

• Évolutivité : Capacité de stockage illimitée sur les plateformes cloud.
• Rentabilité : Coûts réduits pour le stockage à froid des données.
• Flexibilité : Des outils adaptés à chaque type de données.

👁 Étape 6 : Surveillance et observabilité

WeWard utilise des outils de surveillance avancés tels que :

• Informations sur les performances d'AWS : Visualisation des performances des bases de données en temps réel
• Surveillance des performances des applications (APM) : Suivi des transactions de bout en bout.
• Surveillance des performances des bases de données (DBM) : Informations sur les performances des requêtes.

Avantages :

• Détection rapide des problèmes.
• Optimisation des performances.

🏁 Conclusion : une base solide pour l'avenir

En combinant une optimisation rigoureuse des requêtes, une gestion intelligente des ressources et des architectures modernes, PostgreSQL est devenu un allié clé pour WeWard. Ces solutions sont applicables à toute organisation visant à maximiser le potentiel des bases de données.

💬 Quelles sont vos stratégies pour optimiser PostgreSQL ? Partagez vos expériences et participez à la conversation !

👉 Découvrez notre application mobile : Nous, Ward.

‍