Optimisation des performances de PostgreSQL : Comment optimiser votre serveur de base de données

L'optimisation des requêtes SQL n'est qu'une partie de l'histoire des performances.

Pour tirer le meilleur parti de PostgreSQL, vous devez également régler le serveur de base de données et le système d'exploitation.

Dans cet article, nous allons explorer les aspects clés de l'optimisation des performances, des considérations matérielles aux paramètres de configuration de PostgreSQL.

L'importance de l'optimisation des performances

Même les requêtes bien rédigées peuvent souffrir d'un manque de clarté :

Le serveur est mal configuré.
Les ressources matérielles ne sont pas optimisées.
Trop de connexions surchargent le système.
Les points de contrôle et les paramètres WAL sont mal réglés.

👉 L'optimisation des performances permet à PostgreSQL d'utiliser efficacement votre matériel et de monter en charge en douceur.

Étape 1 : Commencer par le système d'exploitation

Avant de modifier PostgreSQL, vérifiez que le système d'exploitation n'est pas le goulot d'étranglement :

Mémoire: Veillez à ce qu'il y ait suffisamment de mémoire vive pour éviter les échanges.
UNITÉ CENTRALE: Contrôler les temps de chargement et d'attente.
Entrée/sortie de disque: Vérifier les pics de latence.

Meilleures pratiques :

Placer les journaux des transactions (pg_wal) sur un disque dédié.
Utilisez RAID 10 (0+1) pour la vitesse et la redondance.
Éviter les systèmes de fichiers distants pour les répertoires de données.
Désactiver les surcharges inutiles du système de fichiers (par exemple, montage avec sans heure).

Étape 2 : Réglage des paramètres de PostgreSQL

Les paramètres par défaut de PostgreSQL sont conservateurs.

Ajustez-les en postgresql.conf:

Paramètres de connexion

max_connections: La valeur par défaut est souvent trop élevée. Utilisez un pooler de connexion (PgBouncer/pgpool-II) au lieu d'augmenter cette valeur aveuglément.

Réglages de la mémoire

tampons_partagés: Généralement fixé à 25-40% de RAM disponible.
travail_mémoire: Mémoire par opération de requête (tris, hachages). A régler avec soin pour éviter une utilisation excessive de la mémoire.
maintenance_work_mem: Utilisé pour VACUUM, CREATE INDEX. Augmenter pour une maintenance plus rapide.
taille_du_cache_effectif: Aide le planificateur à estimer le cache disponible (généralement 50-75% de RAM).

WAL et points de contrôle

niveau_de_monnaie: Utilisation réplique pour la réplication, logique pour le décodage logique.
tampons muraux (wal_buffers): Auto-tuned, mais peut être augmenté pour les systèmes à forte capacité d'écriture.
max_wal_size: Augmenter pour réduire la fréquence des points de contrôle.
checkpoint_timeout: Valeur par défaut : 5 minutes. L'augmenter réduit la pression du point de contrôle.

Étape 3 : Utiliser des requêtes parallèles

PostgreSQL supporte l'exécution parallèle pour :

Balayages séquentiels
Balayages de l'index uniquement
Jointures (hachage, fusion, boucle imbriquée)
Agrégations

Paramètres clés :

max_parallel_workers
max_parallel_workers_per_gather
parallel_setup_cost
parallel_tuple_cost

👉 Le parallélisme peut améliorer considérablement les performances des charges de travail analytiques.

Étape 4 : Chargement efficace des données

Pour les chargements de données en masse :

Utilisation COPIE au lieu de plusieurs INSERT.
Envelopper les insertions dans une transaction (COMMENCER ... ENGAGER).
Désactiver temporairement la journalisation WAL avec NONLOGGÉ tables ou set wal_level = minimal pendant la charge.
Suppression et recréation des index après le chargement de tables volumineuses.

Étape 5 : Envisager des paramètres non durables (avec prudence)

Pour les environnements de test ou les cas où la durabilité est moins critique :

fsync = off - Écriture plus rapide, mais risque de corruption.
synchronous_commit = off - Sauter la chasse d'eau WAL à chaque validation.
full_page_writes = off - Réduit la taille du WAL mais augmente le risque en cas d'accident.

⚠️ A n'utiliser que si la perte de données est acceptable.

Étape 6 : Utiliser des outils de suivi

L'effet de levier Postgres Enterprise Manager (PEM) ou des outils open-source tels que pgBadger et pg_stat_statements:

L'assistant de réglage PEM suggère des changements de configuration.
Le gestionnaire de capacité prévoit la croissance du stockage.
Les tableaux de bord des performances indiquent en temps réel l'utilisation de la mémoire, de l'unité centrale et des entrées/sorties.

Liste de contrôle rapide pour l'optimisation des performances

Optimiser le matériel (disques, RAM, CPU).
✅ Mettre WAL sur des disques séparés.
✅ Ajuster les paramètres de mémoire (shared_buffers, work_mem, maintenance_work_mem).
✅ Ajuster les paramètres WAL et les points de contrôle.
✅ Activer les requêtes parallèles.
Chargement des données avec les stratégies COPY et bulk.
✅ Contrôler en continu avec PEM ou pgBadger.

Réflexions finales

PostgreSQL offre aux administrateurs de bases de données la flexibilité nécessaire pour ajuster les performances à une grande variété de charges de travail.

Commencez par le système d'exploitation, passez aux paramètres de PostgreSQL et utilisez des outils de surveillance pour valider les améliorations.