📍 Article principal : PostgreSQL en surcharge pour PME. Ce tutoriel détaille le tuning précis des paramètres mémoire et autovacuum pour soutenir 100 000 requêtes par seconde sur un VPS modeste.
Les paramètres par défaut de PostgreSQL sont conservateurs et conviennent à un poste de développement. Sur un serveur de production, ils gaspillent les ressources et limitent significativement les performances. Ce tutoriel passe en revue les sept paramètres qui changent vraiment quelque chose, propose des valeurs précises selon le profil du VPS, montre comment mesurer l’impact de chaque modification via pgbench, et installe une supervision continue qui détecte les dérives à venir.
Prérequis
- Instance PostgreSQL 17 fonctionnelle sur Coolify ou installation classique
- Accès root ou superuser PostgreSQL pour modifier
postgresql.conf - VPS dédié à PostgreSQL (idéalement 4 vCPU et 16 Go RAM minimum pour ce tutoriel)
- Outil pgbench installé pour le benchmark
- Niveau : avancé
- Temps estimé : 4 à 6 heures avec mesures
Étape 1 — Mesurer la baseline avec pgbench
Avant tout tuning, mesurer la performance courante avec un benchmark reproductible. pgbench est l’outil standard pour cela : simulation TPC-B-like, 4 types de requêtes (SELECT, UPDATE, INSERT), métrique en transactions par seconde (TPS). Initialiser une base de test avec un facteur d’échelle représentatif du volume réel attendu — facteur 100 crée environ 1,5 Go de données, facteur 1000 environ 15 Go.
createdb bench pgbench -i -s 100 bench # Initial baseline pgbench -c 50 -j 4 -T 60 bench # Capture le TPS, latence moyenne, latence p99
Sur un VPS Hetzner CCX13 avec PostgreSQL en configuration par défaut, attendre typiquement 1 500 à 2 500 TPS avec 50 clients concurrents. Cette baseline servira de point de comparaison pour mesurer l’impact de chaque modification de paramètre. Toujours répéter le benchmark trois fois et prendre la médiane pour éliminer le bruit.
Étape 2 — Tuner shared_buffers
shared_buffers est le paramètre le plus influent. Il définit la mémoire dédiée au cache des pages de données. Par défaut à 128 Mo, il faut absolument l’augmenter en production. La règle empirique : 25 % de la RAM totale du serveur dédié à Postgres. Sur un VPS 16 Go consacré à Postgres, allouer 4 Go.
Modifier dans postgresql.conf : shared_buffers = 4GB. Redémarrer PostgreSQL (ce paramètre ne peut être modifié à chaud). Relancer pgbench : gain typique de 30 à 50 % sur le TPS. La différence vient du fait que les pages chaudes sont servies depuis la RAM au lieu du disque, divisant la latence par 100. Pour les très grosses RAM (64 Go+), monter à 16 Go suffit généralement — au-delà, le gain marginal diminue car le système d’exploitation cache déjà les fichiers.
Étape 3 — Configurer effective_cache_size
effective_cache_size n’alloue pas de mémoire mais informe le planificateur de requêtes de la mémoire totale disponible (cache OS inclus). Sur un VPS dédié, régler à 75 % de la RAM totale : effective_cache_size = 12GB sur 16 Go. Ce paramètre influence le choix entre seq_scan et index_scan : avec une mémoire élevée déclarée, le planificateur préfère les index scans qui assument que les pages d’index sont en cache.
Modifier en hot reload via SELECT pg_reload_conf() sans redémarrage. Relancer pgbench, observer le changement de plans via EXPLAIN ANALYZE sur les requêtes complexes. Sur des charges OLTP standards, l’impact reste modéré (5 à 10 % de gain) mais s’accentue significativement sur les requêtes analytiques avec jointures multiples.
Étape 4 — Régler work_mem prudemment
work_mem alloue la mémoire utilisable par opération de tri ou hash dans une requête. Par défaut à 4 Mo, c’est insuffisant pour des requêtes complexes — les opérations de tri spillent sur disque, divisant les performances par 50. Mais attention : cette mémoire est multipliée par chaque opération concurrente. À 100 connexions et 64 Mo de work_mem, on peut consommer jusqu’à 6 Go de RAM rien qu’en travail.
La règle pragmatique : monter work_mem à 32 ou 64 Mo pour la majorité des charges, à condition que shared_buffers + (max_connections * work_mem) reste sous 70 % de la RAM totale. Pour des cas spécifiques nécessitant plus de mémoire (rapports analytiques mensuels par exemple), surcharger ponctuellement avec SET LOCAL work_mem = '512MB' au début de la transaction : la mémoire reste dans la session sans impacter les autres connexions.
Étape 5 — Tuner autovacuum agressif
L’autovacuum par défaut se déclenche quand 20 % des lignes d’une table sont mortes — trop tard pour les tables très actives, qui accumulent du bloat avant le passage. Sur des tables qui reçoivent des milliers de UPDATE/DELETE par heure, abaisser le seuil à 5 % : autovacuum_vacuum_scale_factor = 0.05 globalement, ou par table via ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02).
Augmenter aussi le nombre de workers et la mémoire allouée. autovacuum_max_workers = 6 permet à plusieurs workers de tourner en parallèle sur différentes tables. autovacuum_work_mem = 256MB accélère significativement le travail de chaque worker. Ces ajustements réduisent drastiquement le bloat et préviennent la dégradation progressive des performances qui afflige les bases mal entretenues.
Étape 6 — Pénalité sur les checkpoints
Les checkpoints sont les opérations qui flush les écritures depuis shared_buffers vers le disque. Trop fréquents, ils saturent les I/O. Trop espacés, ils génèrent des pics énormes au moment du flush. Régler checkpoint_timeout = 15min et max_wal_size = 4GB donne un bon équilibre pour la majorité des charges PME. Le paramètre checkpoint_completion_target = 0.9 étale les écritures sur 90 % de l’intervalle, lissant l’impact I/O.
Surveiller via pg_stat_bgwriter : la colonne checkpoints_req doit rester proche de zéro ; si elle augmente, les checkpoints sont déclenchés par saturation WAL au lieu du timeout, signe qu’il faut augmenter max_wal_size. Cette supervision proactive évite les surprises de performance.
Étape 7 — Mesurer le gain final
Après tous les tunings, relancer pgbench dans les mêmes conditions que la baseline. Sur un VPS Hetzner CCX13, attendre typiquement 8 000 à 12 000 TPS — soit un gain de 4 à 6 fois sur la configuration par défaut. Cette amélioration vient sans changement matériel et sans intervention sur le code applicatif. Pour une PME, c’est l’équivalent d’avoir multiplié par cinq la capacité de son backend pour un investissement de quelques heures de travail.
Documenter chaque paramètre dans un fichier tuning-notes.md versionné : valeur appliquée, raison, gain mesuré, date. Ce journal vaut de l’or six mois plus tard quand on revient sur la configuration ou quand on essaie de comprendre une régression. Sans cette traçabilité, le savoir-faire de tuning reste personnel et fragile, et disparaît avec la rotation des équipes.
Profils de charge et templates de tuning
Au-delà des règles génériques, les valeurs optimales dépendent du profil de charge. Trois templates couvrent la majorité des cas. Profil OLTP standard (Hasura ou Dolibarr classique avec beaucoup de petites transactions) : shared_buffers 25 %, work_mem 32 Mo, max_connections 200, random_page_cost 1.1 (SSD). Profil mixte OLTP+analytique (PME e-commerce avec rapports lourds) : shared_buffers 25 %, work_mem 64 Mo, maintenance_work_mem 1 Go, max_parallel_workers 4. Profil read-heavy (catalogue produits avec beaucoup de SELECT) : shared_buffers 35 %, effective_cache_size 80 %, autovacuum standard, max_connections 300.
Démarrer avec le template le plus proche du cas d’usage, mesurer pendant deux semaines en production, ajuster finement selon les observations pg_stat_statements. Cette approche méthodique évite la sur-optimisation prématurée et donne une trajectoire claire d’amélioration progressive.
Connection pooling au-delà du tuning
Quand le nombre de connexions concurrentes dépasse 200, le tuning PostgreSQL atteint ses limites — chaque connexion consomme 5 à 10 Mo de RAM et impose un overhead. La solution : PgBouncer en transaction pooling devant PostgreSQL. PgBouncer multiplexe efficacement plusieurs centaines de connexions client en quelques dizaines de connexions Postgres réelles. La configuration recommandée : pool_mode = transaction, default_pool_size = 25, max_client_conn = 1000, server_idle_timeout = 600.
Avec PgBouncer en place, abaisser max_connections côté Postgres à 100 environ — moins de mémoire consommée, plus de RAM disponible pour shared_buffers et work_mem. Le combo PgBouncer + Postgres tuné absorbe sans broncher des charges qui auraient nécessité un cluster Patroni complet sans cette optimisation. Pour une PME, c’est l’approche la plus rentable : gain massif sans complexité opérationnelle additionnelle.
Erreurs fréquentes
| Erreur | Cause | Solution |
|---|---|---|
| Out of memory après changement work_mem | Calcul max_connections × work_mem oublié | Diviser work_mem par 2 ou réduire max_connections, utiliser PgBouncer |
| Aucun gain après tuning shared_buffers | Oubli du redémarrage Postgres | shared_buffers nécessite restart, vérifier avec SHOW |
| autovacuum trop agressif perturbe les charges | scale_factor trop bas pour des tables géantes | Configurer par table avec ALTER TABLE pour les exceptions |
| Performance dégradée après quelques semaines | Bloat accumulé sur tables très actives | Activer monitoring du bloat, planifier pg_repack si nécessaire |
Adaptation au contexte ouest-africain
Pour les PME ouest-africaines hébergées sur des VPS modestes, le tuning PostgreSQL fait souvent la différence entre un service rapide et un service inutilisable. Sur un VPS Hetzner CX22 (2 vCPU, 4 Go RAM), même avec moins de marge que sur un CCX13, un tuning bien conduit (shared_buffers 1 Go, work_mem 16 Mo, autovacuum 0.05) double les performances par rapport aux valeurs par défaut. C’est suffisant pour passer d’une expérience utilisateur frustrante à une expérience fluide sans changer de gamme. Pour les déploiements souverains chez Wagaden ou Sonatel Cloud, les mêmes principes s’appliquent — le tuning est portable et ne dépend pas de l’hébergeur.
Surveillance continue post-tuning
Le tuning n’est pas un acte ponctuel mais un processus continu. Mettre en place des dashboards Grafana qui surveillent en temps réel le cache hit ratio (cible > 99 %), le nombre de tuples morts par table, les requêtes en attente, les buffers dirty. Configurer des alertes : cache hit ratio sous 95 % pendant plus de 30 minutes (signe de manque de mémoire ou de requêtes lourdes), bloat supérieur à 30 % sur une table critique, replication lag supérieur à 1 minute.
Ces métriques évoluent dans le temps avec la charge applicative. Une PME qui passe de 1 000 à 10 000 commandes par jour devra retoucher son tuning au moins deux fois en cours de croissance. Documenter chaque ajustement avec contexte (date, raison, mesure avant/après) construit un savoir-faire précieux qui se transmet entre équipes et évite de réinventer la roue à chaque génération technique.
Optimisation des requêtes applicatives
Aucun tuning serveur ne compense des requêtes mal écrites. Trois patterns problématiques fréquents dans les applications PME. Premier : le N+1 query, où une boucle dans le code applicatif déclenche une requête par itération au lieu d’une seule requête avec JOIN. Pour 100 commandes affichées avec leurs items, on déclenche 101 requêtes au lieu d’une seule. Solution : utiliser des JOINs explicites dans la requête initiale, ou des outils comme DataLoader qui regroupent automatiquement.
Deuxième pattern : les SELECT * partout. Récupérer toutes les colonnes alors que l’application n’en utilise que cinq gaspille du réseau et de la mémoire, surtout pour les tables avec colonnes JSONB volumineuses. Cibler explicitement les colonnes nécessaires accélère les requêtes et réduit la pression mémoire. Troisième pattern : les requêtes sans LIMIT sur des tables qui peuvent grossir indéfiniment. Une page de listing sans LIMIT sur une table de 500 000 lignes ramène toutes les lignes à chaque chargement — performance désastreuse et risque de saturation mémoire.
L’audit régulier des requêtes via pg_stat_statements et un outil comme pgBadger qui produit des rapports HTML lisibles permet d’identifier ces anti-patterns. Corriger les top 5 requêtes consommatrices peut diviser par 10 la charge globale du serveur — gain équivalent à un changement de gamme de VPS sans coût additionnel.
Compression et stockage
PostgreSQL 17 propose la compression LZ4 sur les colonnes TOAST (les valeurs trop grosses qui ne tiennent pas dans une page). Activer cette compression sur les colonnes JSONB volumineuses ou les TEXT longs peut réduire de 50 à 70 % l’espace disque utilisé, avec un overhead CPU négligeable. Configurer au niveau de la colonne : ALTER TABLE orders ALTER COLUMN metadata SET COMPRESSION lz4. Le gain s’applique aux nouvelles écritures ; pour réécrire les anciennes, lancer VACUUM FULL ou pg_repack sur la table.
Pour les très grosses bases avec données peu accédées (archives, logs anciens), considérer aussi la compression au niveau du système de fichiers via ZFS avec compression LZ4 ou ZSTD. Le gain sur disque atteint 60 % sans changement applicatif. ZFS apporte aussi des snapshots instantanés très utiles pour les sauvegardes — un snapshot ZFS prend une fraction de seconde même sur une base de 100 Go.
L’écosystème PostgreSQL évolue vite et chaque version majeure apporte de nouvelles capacités d’optimisation. Suivre les release notes annuelles et lire au moins un livre sérieux sur l’optimisation tous les deux ans maintient les compétences à niveau et permet de capitaliser sur les nouvelles fonctionnalités sans être dépassé par la communauté technique mondiale.
Cette curiosité technique combinée à la rigueur opérationnelle constitue le marqueur des équipes qui durent et des plateformes qui restent performantes au fil des années sans accumulation insurmontable de dette.
Pour aller plus loin
🔝 Retour à l’article principal : PostgreSQL en surcharge pour PME. Documentation officielle PostgreSQL tuning : postgresql.org/docs/17/runtime-config-resource, calculateur en ligne PgTune : pgtune.leopard.in.ua qui propose des valeurs initiales selon le profil du serveur.
Le tuning PostgreSQL est un art qui s’affine avec l’expérience. La première vague de tuning suffit pour la majorité des PME, mais les gains marginaux deviennent plus durs à atteindre ensuite — ils demandent une connaissance fine du profil de charge applicatif. Investir dans la formation continue ou dans des consultants spécialisés rentabilise rapidement quand la base devient critique. Cette discipline transforme PostgreSQL d’un composant technique en levier de croissance opérationnel pour la PME.