🔝 Retour au Sommaire
PostgreSQL est un système de gestion de bases de données extrêmement flexible et puissant, capable de gérer des charges de travail allant d'une simple application de bureau à des systèmes d'entreprise massifs traitant des millions de transactions par seconde. Cependant, cette flexibilité a un prix : la configuration par défaut de PostgreSQL est délibérément conservatrice et conçue pour fonctionner sur pratiquement n'importe quel matériel, des Raspberry Pi aux serveurs haut de gamme.
Pour tirer pleinement parti de votre matériel et obtenir des performances optimales, vous devez configurer PostgreSQL selon vos besoins spécifiques. C'est l'objet de cette section : comprendre comment optimiser votre instance PostgreSQL pour qu'elle exploite au mieux les ressources disponibles tout en maintenant la stabilité et la fiabilité.
💡 Philosophie de PostgreSQL : "Mieux vaut des performances sous-optimales stables qu'un système performant mais instable". Les valeurs par défaut privilégient la sécurité et la compatibilité, pas la performance maximale.
Considérez cette situation typique :
Serveur moderne (2024) :
- RAM : 64 GB
- CPU : 16 cœurs
- Stockage : SSD NVMe
- Coût : ~5,000€
Configuration PostgreSQL par défaut :
shared_buffers = 128MB # 0.2% de la RAM !
work_mem = 4MB # Minuscule
max_connections = 100 # Conservateur
effective_cache_size = 4GB # 6% de la RAM
Résultat : Votre serveur haut de gamme fonctionne comme s'il avait 2 GB de RAM !
💡 Analogie : C'est comme acheter une Ferrari et la conduire en première vitesse avec le frein à main serré. Elle roule, mais vous n'exploitez que 10% de ses capacités.
1. Performances dégradées
Scénario : E-commerce avec 1000 requêtes/sec
Configuration par défaut : 150 ms de latence moyenne
Configuration optimisée : 15 ms de latence moyenne
→ Amélioration : 10× plus rapide !
2. Coûts matériels inutiles
Option A : Serveur 64 GB mal configuré → 100 TPS (transactions/sec)
Option B : Serveur 16 GB bien configuré → 150 TPS
→ Économie : ~3,000€ avec de meilleures performances !
3. Expérience utilisateur dégradée
Site web mal configuré :
- Temps de chargement : 3-5 secondes
- Taux de rebond : 40%
- Conversions : -25%
Site web optimisé :
- Temps de chargement : 0.5-1 seconde
- Taux de rebond : 15%
- Conversions : +40%
4. Problèmes de stabilité
Paramètres mal ajustés peuvent causer :
- Out of Memory (OOM) → Crash du serveur
- Deadlocks fréquents → Transactions échouées
- Bloat incontrôlé → Performances en chute libre
- Saturation des connexions → Refus de clients
PostgreSQL expose plus de 300 paramètres de configuration dans le fichier postgresql.conf :
# Compter les paramètres
grep -E "^#?[a-z_]+ =" /etc/postgresql/18/main/postgresql.conf | wc -l
# Résultat : ~320 paramètresCatégories principales :
- 🧠 Mémoire : 50+ paramètres (shared_buffers, work_mem, etc.)
- 💾 I/O et Stockage : 40+ paramètres (WAL, checkpoints, etc.)
- 🔄 Concurrence : 30+ paramètres (connexions, verrous, etc.)
- 📊 Planificateur : 40+ paramètres (coûts, statistiques, etc.)
- 🧹 Maintenance : 25+ paramètres (autovacuum, etc.)
- 📝 Logging : 30+ paramètres
- 🔐 Sécurité : 20+ paramètres
- 🔧 Divers : 85+ paramètres
🎯 Bonne nouvelle : Seuls 20-30 paramètres ont un impact significatif sur les performances dans 90% des cas. Nous allons nous concentrer sur ceux-là !
Les paramètres ne fonctionnent pas en isolation. Modifier un paramètre peut affecter le comportement d'autres :
Exemple d'interdépendances :
shared_buffers ←→ effective_cache_size
↓ ↓
work_mem × max_connections ≤ RAM disponible
↓
checkpoint_completion_target ←→ max_wal_size
↓
autovacuum_work_mem → maintenance_work_mem
Cas concret :
# Configuration apparemment raisonnable mais dangereuse
shared_buffers = 16GB # OK
work_mem = 100MB # Semble OK
max_connections = 500 # Semble OK
# Mais : 500 connexions × 100MB × 3 opérations par connexion
# = 150 GB de RAM potentiellement nécessaire ! 💥
La configuration optimale dépend de multiples facteurs :
| Matériel | Configuration Type |
|---|---|
| HDD (disque mécanique) | random_page_cost = 4.0, checkpoint espacés |
| SSD SATA | random_page_cost = 1.5, autovacuum agressif |
| SSD NVMe | random_page_cost = 1.1, I/O parallèle élevé |
| Cloud (AWS EBS, etc.) | Compromis, attention aux I/O crédits |
| Workload | Priorités Configuration |
|---|---|
| OLTP (transactionnel) | Connexions élevées, work_mem faible, autovacuum fréquent |
| OLAP (analytique) | work_mem élevé, connexions faibles, parallélisation |
| Mixed (hybride) | Équilibre des deux |
| Write-Heavy (écritures intensives) | WAL optimisé, checkpoints espacés |
| Read-Heavy (lectures intensives) | Cache élevé, index optimisés |
Startup (coût prioritaire) :
→ Matériel modeste, configuration conservatrice
E-commerce (performance critique) :
→ Matériel haut de gamme, configuration agressive
Finance (fiabilité absolue) :
→ Redondance, sauvegardes fréquentes, configuration conservatrice
Analytics (requêtes lourdes) :
→ Beaucoup de RAM et CPU, work_mem élevé
Scénarios catastrophiques réels :
Cas 1 : Startup qui a crashé en production (histoire vraie)
# Configuration appliquée sans tests
shared_buffers = 32GB # Sur serveur avec 32GB RAM
work_mem = 500MB # Énorme !
max_connections = 200
# Résultat lors du pic Black Friday :
# 200 connexions × 500MB × 3 = 300GB nécessaires
# → Out of Memory → Crash → 2h d'indisponibilité
# → Perte estimée : 500,000€
Cas 2 : Data warehouse avec performances catastrophiques
# Configuration OLTP sur workload OLAP
work_mem = 4MB # Beaucoup trop faible
max_parallel_workers = 0 # Pas de parallélisation
random_page_cost = 4.0 # Pensait avoir des HDD, avait des SSD
# Résultat :
# Requêtes analytiques : 30 minutes au lieu de 2 minutes
# Utilisation fichiers temporaires : 50GB+ par requête
# CEO furieux : "J'ai acheté ce serveur pour rien ?"
Cas 3 : Autovacuum désactivé (erreur classique)
# "Autovacuum ralentit ma base, je le désactive"
autovacuum = off
# Résultat après 3 semaines :
# - Table principale : gonflée de 10GB à 80GB (bloat)
# - Requêtes : 10× plus lentes
# - Transaction ID wraparound imminent
# - VACUUM FULL requis : 12h d'indisponibilité totale
Phase 1: BASELINE (État Initial)
↓
Phase 2: CONFIGURATION INITIALE (Ajustements de base)
↓
Phase 3: MONITORING (Observation)
↓
Phase 4: AJUSTEMENTS FINS (Itérations)
↓
Phase 5: MAINTENANCE (Optimisation continue)
Objectif : Mesurer les performances actuelles avant tout changement.
Métriques à capturer :
-- 1. Cache Hit Ratio
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 AS cache_hit_ratio
FROM pg_statio_user_tables;
-- 2. Requêtes lentes (avec pg_stat_statements)
SELECT query, calls, mean_exec_time, max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- 3. Tables les plus consultées
SELECT schemaname, tablename, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan + idx_scan DESC
LIMIT 10;
-- 4. État des connexions
SELECT count(*), state FROM pg_stat_activity GROUP BY state;Benchmark applicatif :
# Mesurer performances réelles de l'application
# - Temps de réponse API
# - Throughput (requêtes/sec)
# - Latence P50, P95, P99Objectif : Appliquer des configurations de base solides adaptées au matériel.
Approche recommandée :
- Utiliser PGTune pour générer une configuration de départ
- Appliquer en environnement de test
- Valider la stabilité
- Déployer en production
# Exemple de configuration générée pour serveur 32GB RAM
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
work_mem = 16MB
max_connections = 200
Objectif : Observer le comportement réel sous charge.
Outils de monitoring :
- pg_stat_* views (intégré PostgreSQL)
- pg_stat_statements (extension)
- pgBadger (analyse de logs)
- Prometheus + Grafana (dashboards)
- CloudWatch / Azure Monitor (cloud)
Métriques clés à surveiller :
Quotidien :
- Cache hit ratio (doit être > 95%)
- Connexions actives vs max_connections
- Tables avec bloat
- Checkpoints trop fréquents
Hebdomadaire :
- Slow queries (nouvelles entrées)
- Croissance des bases/tables
- Index non utilisés
- Autovacuum effectiveness
Objectif : Affiner la configuration selon les observations.
Approche itérative :
1. Identifier UN goulot d'étranglement
↓
2. Ajuster LES paramètres liés
↓
3. Tester l'impact
↓
4. Si amélioration → Conserver
Si dégradation → Revenir en arrière
↓
5. Répéter pour le prochain goulot
Exemple de cycle d'amélioration :
Observation : Cache hit ratio = 88% (trop bas)
↓
Hypothèse : shared_buffers trop petit
↓
Action : Augmenter shared_buffers de 8GB → 12GB
↓
Test : Mesurer cache hit ratio pendant 3 jours
↓
Résultat : Cache hit ratio = 96% ✅
↓
Conserver la modification
Objectif : Maintenir des performances optimales dans le temps.
Actions mensuelles :
- Réviser les métriques de monitoring
- Identifier nouveaux slow queries
- Vérifier la croissance des données
- Ajuster autovacuum si nécessaire
Actions trimestrielles :
- Benchmark complet
- Comparer avec benchmarks précédents
- Réévaluer si matériel ou charge ont changé
- Mettre à jour la documentation
Actions annuelles :
- Audit complet de configuration
- Révision de l'architecture
- Planification d'upgrade PostgreSQL si pertinent
Impact : ⭐⭐⭐⭐⭐ CRITIQUE
Ces paramètres définissent comment PostgreSQL utilise la RAM, la ressource la plus précieuse :
# Cache des données
shared_buffers = 8GB # Mémoire partagée pour cache
effective_cache_size = 24GB # Estimation cache total (PG + OS)
# Mémoire de travail
work_mem = 16MB # Par opération (tri, hash, etc.)
maintenance_work_mem = 2GB # Pour maintenance (VACUUM, INDEX, etc.)
autovacuum_work_mem = 512MB # Spécifique autovacuum
# Buffers WAL
wal_buffers = 16MB # Buffer pour Write-Ahead Log
À couvrir dans 16.13.1
Impact : ⭐⭐⭐⭐ TRÈS IMPORTANT
Contrôlent les écritures sur disque et la durabilité des données :
# WAL (Write-Ahead Log)
wal_level = replica # Niveau de logging
max_wal_size = 8GB # Taille max avant checkpoint
min_wal_size = 2GB # Taille min à conserver
checkpoint_timeout = 15min # Intervalle entre checkpoints
checkpoint_completion_target = 0.9 # Étalement des I/O
# I/O Asynchrone (PostgreSQL 18)
io_method = 'worker' # Défaut PG 18 : I/O asynchrone via workers
io_combine_limit = 256kB # Combinaison opérations I/O
À couvrir dans 16.13.2 et 16.13.3
Impact : ⭐⭐⭐⭐ TRÈS IMPORTANT
Gèrent le nettoyage automatique des tuples morts et préviennent le bloat :
# Activation et workers
autovacuum = on # JAMAIS désactiver !
autovacuum_max_workers = 5 # Nombre de workers simultanés
autovacuum_naptime = 30s # Fréquence de réveil
# Seuils de déclenchement
autovacuum_vacuum_threshold = 50
autovacuum_vacuum_scale_factor = 0.1 # 10% de tuples morts
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.05
# Performance
autovacuum_vacuum_cost_delay = 2ms # Throttling
autovacuum_vacuum_cost_limit = 400
À couvrir dans 16.13.4
Impact : ⭐⭐⭐ IMPORTANT
Gèrent le nombre et le comportement des connexions :
# Limites
max_connections = 200 # Connexions simultanées max
superuser_reserved_connections = 3 # Réservées pour admin
# Timeouts
statement_timeout = 0 # Timeout requête (0 = désactivé)
idle_in_transaction_session_timeout = 0
tcp_keepalives_idle = 600
Note : Pour gérer des milliers de connexions, utilisez PgBouncer (connection pooling)
À couvrir dans 16.13.6
Impact : ⭐⭐⭐ IMPORTANT
Influencent les décisions du planificateur de requêtes :
# Coûts relatifs
random_page_cost = 1.1 # SSD : 1.1, HDD : 4.0
seq_page_cost = 1.0 # Référence
cpu_tuple_cost = 0.01
cpu_index_tuple_cost = 0.005
cpu_operator_cost = 0.0025
# Parallélisation
max_parallel_workers_per_gather = 4 # Workers par requête
max_parallel_workers = 8 # Workers totaux
max_parallel_maintenance_workers = 4 # Pour CREATE INDEX, etc.
# Statistiques
default_statistics_target = 100 # Précision statistiques
Impact : ⭐⭐ MODÉRÉ (performance) / ⭐⭐⭐⭐ CRITIQUE (troubleshooting)
Contrôlent ce qui est logué et comment :
# Quoi logger
log_min_duration_statement = 1000 # Requêtes > 1s
log_checkpoints = on
log_connections = on
log_disconnections = on
log_autovacuum_min_duration = 0 # Toutes les opérations autovacuum
# Où logger
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
# Format
log_line_prefix = '%m [%p] %q%u@%d '
Impact : ⭐⭐⭐⭐⭐ CRITIQUE (sécurité)
# Authentification
password_encryption = scram-sha-256 # Algorithme de hash
ssl = on # Chiffrement SSL/TLS
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
# Limites
max_locks_per_transaction = 64
max_pred_locks_per_transaction = 64
Trouver postgresql.conf :
# Méthode 1 : Via psql
sudo -u postgres psql -c "SHOW config_file;"
# Méthode 2 : Chemins standards
# Ubuntu/Debian
/etc/postgresql/18/main/postgresql.conf
# RHEL/CentOS
/var/lib/pgsql/18/data/postgresql.conf
# macOS (Homebrew)
/usr/local/var/postgres/postgresql.conf
# Windows
C:\Program Files\PostgreSQL\18\data\postgresql.conf#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
data_directory = '/var/lib/postgresql/18/main'
hba_file = '/etc/postgresql/18/main/pg_hba.conf'
ident_file = '/etc/postgresql/18/main/pg_ident.conf'
#------------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#------------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = 'localhost'
port = 5432
max_connections = 100
# - Security and Authentication -
#authentication_timeout = 1min
#password_encryption = scram-sha-256
#------------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#------------------------------------------------------------------------------
# - Memory -
shared_buffers = 128MB
#huge_pages = try
#temp_buffers = 8MB
work_mem = 4MB
maintenance_work_mem = 64MB
...
# [250+ autres paramètres]
Conventions :
- Lignes commençant par
#: Commentaires ou valeurs par défaut - Lignes sans
#: Valeurs activées/modifiées - Format :
parametre = valeur
# 1. Backup du fichier
sudo cp /etc/postgresql/18/main/postgresql.conf \
/etc/postgresql/18/main/postgresql.conf.backup
# 2. Éditer
sudo nano /etc/postgresql/18/main/postgresql.conf
# 3. Vérifier syntaxe
sudo -u postgres /usr/lib/postgresql/18/bin/postgres \
-D /var/lib/postgresql/18/main \
-C config_file
# 4. Recharger (si paramètre ne nécessite pas redémarrage)
sudo systemctl reload postgresql
# 5. Ou redémarrer (si nécessaire)
sudo systemctl restart postgresql-- Modifier via SQL (écrit dans postgresql.auto.conf)
ALTER SYSTEM SET work_mem = '32MB';
ALTER SYSTEM SET maintenance_work_mem = '1GB';
-- Recharger la configuration
SELECT pg_reload_conf();
-- Vérifier
SHOW work_mem;Avantage : Changements tracés, fichier postgresql.auto.conf séparé
Fichier postgresql.auto.conf :
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '32MB'
maintenance_work_mem = '1GB'
Priorité : postgresql.auto.conf > postgresql.conf
# Démarrer avec paramètres spécifiques
postgres -D /data -c shared_buffers=16GB -c max_connections=500Priorité : Ligne de commande > postgresql.auto.conf > postgresql.conf
# Mémoire partagée
shared_buffers
huge_pages
max_connections
# WAL
wal_buffers (si > 2048 blocs)
wal_level
# Divers
max_worker_processes
max_prepared_transactions
track_activity_query_size
Tous les autres paramètres peuvent être modifiés avec un simple RELOAD :
sudo systemctl reload postgresql
# ou
SELECT pg_reload_conf();-- Voir toutes les valeurs
SELECT name, setting, unit, context, source
FROM pg_settings
ORDER BY name;
-- Voir un paramètre spécifique
SHOW shared_buffers;
SHOW work_mem;
-- Voir les paramètres modifiés (différents du défaut)
SELECT name, setting, source
FROM pg_settings
WHERE source != 'default'
ORDER BY name;
-- Voir uniquement les paramètres du fichier de config
SELECT name, setting
FROM pg_settings
WHERE source = 'configuration file'; -- Retour à la valeur par défaut
ALTER SYSTEM RESET work_mem;
SELECT pg_reload_conf();
-- Ou dans postgresql.conf : commenter la ligne
# work_mem = 32MB ← Commenté, retour au défautPour faciliter le tuning, plusieurs outils sont disponibles :
URL : https://pgtune.leopard.in.ua/
Fonction : Génère automatiquement une configuration optimisée basée sur :
- RAM du serveur
- Type de CPU
- Type de stockage (HDD/SSD)
- Type de workload (Web, OLTP, Data Warehouse, etc.)
Exemple :
Entrées :
- PostgreSQL 18
- 32 GB RAM
- 8 CPUs
- SSD
- Web Application
Génère :
shared_buffers = 8GB
effective_cache_size = 24GB
maintenance_work_mem = 2GB
work_mem = 10MB
...
À couvrir en détail dans 16.13.5
Problème : Chaque connexion PostgreSQL consomme ~10MB RAM
Solution : PgBouncer mutualise les connexions
1000 clients → PgBouncer → 50 connexions PostgreSQL
→ Économie : 9.5 GB RAM
Modes :
- Transaction : Une connexion par transaction (recommandé)
- Session : Une connexion par session client (compatible)
À couvrir en détail dans 16.13.6
- timescaledb-tune : Pour séries temporelles
- postgresqltuner : Audit configuration existante
- pg_stat_statements : Extension pour analyser requêtes
- pgBadger : Analyse de logs
- Prometheus + Grafana : Monitoring temps réel
Cette section 16.13 est organisée en 6 sous-sections complémentaires :
Objectif : Maîtriser les 4 paramètres les plus importants
shared_buffers: Le cache centralwork_mem: Mémoire par opérationmaintenance_work_mem: Mémoire pour maintenanceeffective_cache_size: Estimation du cache total
Impact : ⭐⭐⭐⭐⭐ Ces 4 paramètres ont le plus d'impact sur les performances
Objectif : Exploiter le nouveau sous-système I/O asynchrone
- Configuration
io_method(workerpar défaut,io_uringsur Linux) - Gains de performance jusqu'à 3×
- Optimisations pour SSD NVMe
Impact : ⭐⭐⭐⭐⭐ Nouveauté majeure qui change la donne
Objectif : Optimiser le Write-Ahead Log
wal_level: Niveau de loggingmax_wal_size: Taille avant checkpointcheckpoint_timeout: Intervalle checkpoints
Impact : ⭐⭐⭐⭐ Critique pour performances d'écriture
Objectif : Empêcher le bloat et maintenir la santé de la base
- Paramètres de déclenchement
- Configuration par table
- Nouveautés PostgreSQL 18
Impact : ⭐⭐⭐⭐⭐ Essentiel pour stabilité long terme
Objectif : Utiliser les outils pour faciliter la configuration
- Guide complet PGTune
- Méthodologie de déploiement
- Validation des changements
Impact : ⭐⭐⭐⭐ Gain de temps considérable
Objectif : Gérer des milliers de connexions avec peu de ressources
- Mode transaction vs session
- Configuration et monitoring
- Architecture avancée
Impact : ⭐⭐⭐⭐⭐ Indispensable pour applications à fort trafic
"You can't improve what you don't measure" - Peter Drucker
❌ MAUVAIS :
"Je vais augmenter shared_buffers, ça devrait aider"
✅ BON :
1. Mesurer cache hit ratio actuel : 88%
2. Augmenter shared_buffers de 8GB → 12GB
3. Re-mesurer cache hit ratio : 96%
4. Conserver si amélioration confirmée
❌ MAUVAIS :
Modifier 10 paramètres simultanément
→ Impossible de savoir lequel a eu quel impact
✅ BON :
1. Modifier shared_buffers
2. Observer pendant 3 jours
3. Si OK, passer au paramètre suivant
❌ MAUVAIS :
Modifier directement en production
→ Risque de crash, downtime
✅ BON :
Dev → Staging → Validation → Production
# TOUJOURS faire un backup de postgresql.conf
sudo cp /etc/postgresql/18/main/postgresql.conf \
/etc/postgresql/18/main/postgresql.conf.$(date +%Y%m%d)# Ajouter des commentaires dans postgresql.conf
# 2024-11-22 : Augmenté shared_buffers pour améliorer cache hit ratio
# Avant : 8GB (cache hit = 88%)
# Après : 12GB (cache hit = 96%)
shared_buffers = 12GB# Première itération : Configuration PGTune (conservatrice)
shared_buffers = 8GB
# Après 1 semaine d'observation : Ajustement
shared_buffers = 10GB
# Après 1 mois : Affinage final
shared_buffers = 12GB
Il n'existe pas de configuration parfaite. Tout est compromis :
Plus de shared_buffers :
+ Meilleur cache hit ratio
- Moins de mémoire pour l'OS
- Plus long à démarrer PostgreSQL
Plus de max_connections :
+ Peut accepter plus de clients
- Plus de mémoire consommée
- Plus de contention possible
Checkpoints plus fréquents :
+ Recovery plus rapide après crash
- Plus d'I/O → Performances dégradées
Cache Hit Ratio :
SELECT
sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100 AS cache_hit_pct
FROM pg_statio_user_tables;
-- Objectif : > 95%Latence Requêtes :
SELECT
query,
calls,
mean_exec_time,
max_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- Objectif : Réduction de 20-50% du mean_exec_timeThroughput :
# Transactions par seconde
pgbench -c 20 -j 4 -T 60 mydb
-- Objectif : Augmentation de 30-100%Temps de réponse API :
Avant : 200ms moyenne
Après : 50ms moyenne
→ Amélioration : 4×
Pages par seconde :
Avant : 100 pages/sec
Après : 300 pages/sec
→ Amélioration : 3×
Taux d'erreur :
Avant : 2% (connexions refusées)
Après : 0.1%
→ Amélioration : 20×
Temps de chargement site web :
< 1 seconde : +40% conversions
< 3 secondes : référence
> 3 secondes : -40% conversions
Satisfaction utilisateur :
Latence < 100ms : 98% satisfaction
Latence 100-500ms : 85% satisfaction
Latence > 500ms : 60% satisfaction
Coûts infrastructure :
Configuration optimale : -30% serveurs nécessaires
→ Économie : 50,000€/an
1. Sur-optimisation Prématurée
Ne pas passer 2 semaines à optimiser une base de 10 utilisateurs
→ ROI négatif (temps > gains)
2. Copier Aveuglément des Configurations Trouvées sur Internet
❌ "J'ai trouvé cette config sur Stack Overflow pour un serveur 128GB"
[Applique sur serveur 8GB] → Crash
✅ Adapter la configuration à VOTRE contexte
3. Ignorer les Logs et Warnings
PostgreSQL log :
"WARNING: could not write block 123 of relation base/16384/12345"
→ NE PAS IGNORER !
4. Oublier l'Impact sur le Recovery
Configuration ultra-agressive :
- max_wal_size = 32GB
- checkpoint_timeout = 1h
→ Performances excellentes
→ Mais recovery après crash = 1h+ au lieu de 5 minutes
5. Ne Pas Monitorer Après Changements
❌ "J'ai appliqué la config, ça devrait aller"
[Oublie de monitorer pendant 1 mois]
→ Problème invisible jusqu'au crash
✅ Monitoring intensif pendant 1-2 semaines après changements
1. TOUJOURS faire un backup avant changement
sudo cp postgresql.conf postgresql.conf.backup.$(date +%Y%m%d_%H%M%S)2. TOUJOURS tester en non-production d'abord
Dev → Staging → Charge Tests → Production
3. TOUJOURS avoir un plan de rollback
# Si problème en production
sudo cp postgresql.conf.backup.20241122 postgresql.conf
sudo systemctl restart postgresql 4. TOUJOURS documenter
Pourquoi ce changement ?
Quelle était la valeur avant ?
Quel est l'impact attendu ?
Comment revenir en arrière ?
Avant de plonger dans les sections détaillées, voici une checklist pour démarrer :
- Inventaire matériel (RAM, CPU, type stockage)
- Identifier type de workload (OLTP, OLAP, Mixed)
- Estimer nombre de connexions simultanées
- Installer outils de monitoring (pg_stat_statements, pgBadger)
- Établir baseline de performances actuelles
- Utiliser PGTune pour générer config de base
- Backup de postgresql.conf existant
- Appliquer configuration générée
- Redémarrer PostgreSQL
- Vérifier démarrage sans erreurs
- Vérifier que paramètres sont appliqués (SHOW)
- Tester connexion application
- Vérifier logs PostgreSQL (aucune erreur)
- Lancer tests de charge basiques
- Comparer avec baseline
- Surveiller cache hit ratio quotidiennement
- Vérifier absence de clients en attente (connexions)
- Identifier slow queries
- Surveiller bloat tables
- Vérifier fréquence checkpoints
- Identifier 1-2 bottlenecks principaux
- Ajuster paramètres correspondants
- Re-tester et comparer
- Documenter changements
- Répéter si nécessaire
Le tuning de PostgreSQL est un art et une science. Il n'existe pas de formule magique qui fonctionne pour tous les cas. Cependant, en comprenant :
- Les catégories de paramètres et leur impact
- La méthodologie rigoureuse (mesurer → modifier → valider)
- Les outils disponibles (PGTune, monitoring, etc.)
- Les pièges à éviter
Vous pouvez améliorer significativement les performances de votre base de données PostgreSQL, souvent avec des gains de 2× à 10× sur les métriques clés, tout en maintenant la stabilité et la fiabilité.
Les sections suivantes (16.13.1 à 16.13.6) vont vous guider pas à pas à travers les paramètres les plus importants, avec des explications détaillées, des exemples concrets et des cas d'usage réels.
Prochaine étape : Plongeons dans les paramètres critiques (16.13.1) qui auront le plus grand impact sur vos performances !
⏭️ Paramètres critiques (shared_buffers, work_mem, maintenance_work_mem, effective_cache_size)