Maîtriser 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 des utilisateurs, nous nous sommes embarqués dans un voyage ambitieux pour transformer PostgreSQL en un moteur de performance exceptionnel. 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 attendent des temps de réponse instantanés, des bases de données lentes peuvent entraîner des frustrations et des opportunités manquées. Chez WeWard, ce défi se traduit par des millions de transactions quotidiennes et des requêtes complexes nécessitant une performance sans faille. Nos défis comprenaient des requêtes lentes, une charge excessive et une architecture nécessitant une évolutivité accrue.

Voici comment nous avons surmonté ces obstacles.

Étape 1 : Optimisation de la requête

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

Analyse du plan d'exécution

Utilisation EXPLIQUER et ANALYSERNous avons obtenu une vue radiographique de nos requêtes.

Exemple de requête problématique :

SELECT level_customer.*
FROM level_customer
JOIN level ON level.id = level_customer.level_id
WHERE level_customer.customer_id = XXXXXXX
 AND level_customer.start_date <= '2024-05-07'::date
 AND level.version = 2
ORDER BY level_customer.start_date DESC, level.value DESC
LIMIT 1;


Problèmes détectés :

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

Solution : Création d'un index :

CREATE 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 Boucle imbriquée au lieu de l'option par défaut Jointure de hachage pour cette requête spécifique.

Vues matérialisées :pour les requêtes complexes, les vues matérialisées réduisent la charge de calcul à chaque exécution, ce qui permet d'obtenir des performances remarquablement stables.

Étape 2 : Répartition de la charge avec réplique

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

Configuration de l'architecture :

• Nœud primaire : Il gère les opérations de lecture et d'écriture et assure la cohérence des données.
• Nœud de réplique : Dédié aux opérations en lecture seule, il réduit la charge du nœud principal et améliore les temps de réponse pour les requêtes en lecture intensive.

Avantages :

• Amélioration des performances : Le fait de décharger les requêtes de lecture sur les répliques permet de réduire la latence et d'augmenter le débit.
• Haute disponibilité : En cas de défaillance du nœud principal, les répliques peuvent être promues, ce qui garantit la continuité du service.
• Évolutivité : Plusieurs nœuds de réplique peuvent être ajoutés pour s'adapter horizontalement à l'augmentation du trafic.

Considérations techniques :

• Latence de réplication : Un léger décalage peut se produire entre le nœud primaire et le nœud de réplication.
• Cohérence éventuelle : 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 : Gérer les grandes tables avec le partitionnement

Pourquoi le partitionnement ?

Les tables contenant des milliards de lignes peuvent ralentir considérablement 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 automatisée des partitions : Gère la création et la suppression de partitions en fonction de critères prédéfinis.
• Amélioration des performances des requêtes : Les requêtes ne ciblent que les partitions pertinentes.
• Maintenance plus facile 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 une surcharge du serveur :

• Limites de connexion : max_connections a été ajustée en fonction du matériel disponible et des besoins de l'application.
• Mise en commun des 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 de PostgreSQL :

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

📈 Étape 5 : Une architecture évolutive avec un lac de données

Les données critiques ("chaudes") restent dans PostgreSQL, tandis que les données moins fréquemment consultées ("froides") sont transférées vers Amazon S3 ou Google Cloud Storage. L'analyse des données froides est réalisée à l'aide de Google BigQuery.

Avantages :

• Évolutivité : Capacité de stockage illimitée sur les plateformes en nuage.
• Rentabilité : Réduction des coûts de stockage des données froides.
• Flexibilité : Outils adaptés à chaque type de données.

👁 Étape 6 : Suivi et observabilité

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

• AWS Performance Insights : Visualisation en temps réel des performances des bases de données.
• Surveillance des performances des applications (APM) : Traçage des transactions de bout en bout.
• Surveillance des performances des bases de données (DBM) : Aperçu des 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 souhaitant maximiser le potentiel de ses 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 : WeWard.

‍