Planification de capacité PostgreSQL : guide pratique avec exemples concrets

Introduction

La planification de capacité pour PostgreSQL consiste à déterminer la quantité de CPU, de mémoire, d'I/O disque, de stockage et de marge de connexions dont votre base de données a besoin aujourd'hui et à mesure que votre charge de travail évolue. Ce n'est pas un exercice ponctuel sur tableur. C'est une pratique opérationnelle continue : mesurer ce que vous avez, le comparer avec ce que vos requêtes et clients utilisent réellement, puis apporter de petits changements réversibles et vérifier le résultat.

Ce guide s'adresse aux développeurs, ingénieurs DevOps et fondateurs techniques qui gèrent eux-mêmes PostgreSQL. Il relie les décisions de dimensionnement à des commandes réelles, des sorties attendues, des signaux d'échec et des étapes de récupération. Chaque exemple utilise d'abord des vérifications en lecture seule, des espaces réservés explicites au lieu de secrets, et une commande de vérification avant de considérer un changement comme réussi.

L'objectif est la sécurité opérationnelle : observer avant de modifier, limiter le rayon d'impact, utiliser des espaces réservés au lieu de secrets, vérifier le résultat et documenter comment récupérer si l'état attendu n'est pas atteint.

Inventaire de la version et de l'environnement

Avant de modifier un paramètre, sachez exactement ce que vous exécutez. Commencez par la version de PostgreSQL et le système d'exploitation, car le comportement de capacité change entre les versions majeures. Par exemple, PostgreSQL 14 a introduit des améliorations significatives pour la mise à l'échelle des connexions, tandis que la version 13 et les versions antérieures se comportent différemment sous de nombreuses connexions de courte durée.

Exécutez ces commandes en lecture seule en tant qu'utilisateur système postgres ou tout rôle disposant de privilèges suffisants :

SELECT version();

Sortie attendue (votre chaîne de version variera) :

PostgreSQL 15.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.3.0, 64-bit

Vérifiez la mémoire partagée et les limites de ressources imposées par le système d'exploitation :

cat /proc/sys/kernel/shmmax
cat /proc/sys/kernel/shmall
ulimit -n   # descripteurs de fichiers ouverts par processus

Si votre base de données s'exécute dans Docker, vérifiez les limites du conteneur plutôt que celles de l'hôte. Par exemple :

docker inspect --format '{{.HostConfig.Memory}}' <nom_conteneur>
docker inspect --format '{{.HostConfig.NanoCpus}}' <nom_conteneur>

Enregistrez ces valeurs dans un runbook. Elles ancrent toutes les décisions de dimensionnement ultérieures : si le système d'exploitation n'autorise que 1 Go de mémoire partagée, définir shared_buffers à 4 Go ne fonctionnera pas.

Rayon d'impact et récupération : aucune de ces commandes ne modifie l'état. Si vous exécutez accidentellement une commande d'écriture au lieu d'une lecture, arrêtez immédiatement et restaurez à partir de votre dernière sauvegarde. Connectez-vous toujours avec un rôle en lecture seule pour les vérifications d'inventaire lorsque c'est possible. Par exemple :

CREATE ROLE readonly_user WITH LOGIN PASSWORD 'remplacez_par_mot_de_passe_fort';
GRANT CONNECT ON DATABASE votre_base TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

Utilisez ensuite readonly_user pour toutes les commandes d'observation de ce guide.

Chemin de configuration sécurisé

Le réglage de capacité de PostgreSQL commence généralement par quelques paramètres à fort impact dans postgresql.conf. Le chemin sécurisé signifie :

1. Lire les paramètres actuels.
2. Estimer la nouvelle valeur en fonction des métriques réelles de charge.
3. Modifier un paramètre à la fois.
4. Recharger ou redémarrer selon les besoins.
5. Vérifier que le changement a pris effet et n'a pas causé d'erreurs.

Paramètres actuels

Interrogez la base en direct pour les paramètres que vous avez l'intention de modifier :

SELECT name, setting, unit, context
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'maintenance_work_mem', 'max_connections');

La sortie attendue ressemble à :

         name         | setting | unit |  context
----------------------+---------+------+------------
 max_connections      | 100     |      | postmaster
 shared_buffers       | 16384   | 8kB  | postmaster
 work_mem             | 4096    | kB   | user
 maintenance_work_mem | 65536   | kB   | user
 effective_cache_size | 524288  | 8kB  | user

Notez la colonne unit. shared_buffers est en pages de 8 Ko, donc 16384 signifie 128 Mo. work_mem est en Ko, donc 4096 signifie 4 Mo.

Exemple de dimensionnement pour shared_buffers

Une règle empirique courante est de définir shared_buffers à 25 % de la RAM système pour un serveur de base de données dédié, mais pas plus de 8 Go sur les anciennes versions de PostgreSQL en raison du surcoût de checkpoint et de double mise en cache. Sur PostgreSQL 12 et versions ultérieures, des valeurs plus élevées sont généralement sûres, mais commencez de manière prudente.

Supposons que votre serveur dispose de 16 Go de RAM. 25 % représentent 4 Go. Dans postgresql.conf :

shared_buffers = 4GB

Ce changement nécessite un redémarrage car le contexte est postmaster. Avant de redémarrer, vérifiez à nouveau la valeur actuelle et assurez-vous qu'aucun autre changement n'est en attente.

Exemple de dimensionnement pour work_mem

work_mem contrôle la quantité de mémoire que chaque opération de tri ou de hachage peut utiliser avant de déborder sur le disque. C'est par opération, pas par requête ni par connexion. Une requête avec plusieurs tris peut utiliser plusieurs fois work_mem. Définissez-le trop haut et de nombreuses connexions simultanées peuvent épuiser la RAM.

Une valeur de départ pour une charge OLTP avec 100 connexions max et 16 Go de RAM :

• Gardez shared_buffers à 4 Go.
• Laissez environ 2 Go pour le système d'exploitation et les autres processus.
• Mémoire partagée PostgreSQL plus surcoût par backend : environ 10 à 20 % de la RAM totale pour la sécurité.
• Restant pour work_mem et les caches : environ 10 Go.
• Tris moyens par requête : 2 à 3.
• Requêtes actives simultanées : supposons 20 au pic.

Calcul : 10 Go / (20 requêtes actives x 3 opérations) = 10 Go / 60 = 170 Mo par opération. C'est trop élevé pour la plupart des systèmes ; une valeur plus typique est de 4 à 16 Mo. Si trier 10 Mo de données peut rester en mémoire avec 16 Mo de work_mem, la plupart des requêtes seront rapides sans risquer l'épuisement de la mémoire.

work_mem = 16MB

Ce changement prend effet à la prochaine commande pour une session, ou immédiatement si vous rechargez et reconnectez. Pour appliquer à toutes les sessions sans redémarrage, exécutez :

sudo systemctl reload postgresql

Vérifiez ensuite :

SHOW work_mem;

Sortie attendue :

 work_mem
----------
 16MB

Exemple de dimensionnement pour max_connections

max_connections est une limite stricte sur les connexions client simultanées. L'augmenter sans assez de mémoire peut provoquer du swapping et ralentir les performances. Au lieu de l'augmenter fortement, envisagez d'utiliser un pooler de connexions comme PgBouncer en production.

Si vous devez l'augmenter, estimez la mémoire par connexion : work_mem plus temp_buffers plus la pile et autres surcoûts, généralement 2 à 10 Mo par connexion. Avec 100 connexions, 5 Mo en moyenne, vous avez besoin d'environ 500 Mo. Avec 500 connexions, cela monte à 2,5 Go.

max_connections = 300

Ce changement nécessite un redémarrage. Prévoyez une fenêtre de maintenance. Vérifiez après le redémarrage :

SHOW max_connections;

Rayon d'impact et récupération

Sauvegardez toujours votre postgresql.conf avant de le modifier :

cp /etc/postgresql/15/main/postgresql.conf /etc/postgresql/15/main/postgresql.conf.backup-$(date +%Y%m%d)

Si PostgreSQL ne démarre pas après un changement, vérifiez le fichier journal (généralement dans /var/log/postgresql/ ou via journalctl -u postgresql). Restaurez le fichier précédent depuis la sauvegarde et redémarrez :

sudo cp /etc/postgresql/15/main/postgresql.conf.backup-YYYYMMDD /etc/postgresql/15/main/postgresql.conf
sudo systemctl restart postgresql

Vérification et diagnostics

La vérification ne consiste pas seulement à vérifier qu'un paramètre a changé. Vous devez confirmer que le changement a eu l'effet escompté sur les performances ou l'utilisation des ressources, et n'a pas introduit de nouveaux problèmes.

Nombre de connexions et pooling

Pour voir les connexions actuelles et si vous êtes proche de la limite :

SELECT count(*) AS current_connections, max_conn FROM
  (SELECT count(*) FROM pg_stat_activity) AS current_connections,
  (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') AS max_conn;

Sortie attendue :

 current_connections | max_conn
---------------------+----------
                  45 |      300

Si vous dépassez constamment 80 % de max_connections, envisagez un pooler ou réduisez les délais d'expiration des sessions inactives.

Taux de succès du cache

Le taux de succès du cache de tampons partagés indique la fréquence à laquelle PostgreSQL trouve les données en mémoire plutôt que de les lire depuis le disque. La requête standard :

SELECT
  sum(heap_blks_read) AS heap_read,
  sum(heap_blks_hit)  AS heap_hit,
  round(sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) * 100, 2) AS cache_hit_ratio
FROM pg_statio_user_tables;

Visez un ratio supérieur à 99 % pour une charge OLTP. S'il est inférieur, envisagez d'augmenter shared_buffers ou d'ajouter de la RAM, mais vérifiez aussi que la charge ne scanne pas inutilement d'énormes tables.

Requêtes lentes et utilisation des index

Vérifiez les requêtes lentes ou n'utilisant pas d'index :

SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 5;

Nécessite l'extension pg_stat_statements :

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Si une requête apparaît avec un mean_time élevé et un grand nombre de lignes, exécutez EXPLAIN dessus :

EXPLAIN (ANALYZE, BUFFERS) SELECT ...;

Cherchez Seq Scan sur de grandes tables où un index serait utile. Ajoutez un index et vérifiez que le plan de requête passe à Index Scan ou Bitmap Index Scan.

Utilisation du disque et croissance

Surveillez la taille de la base et la croissance des tables :

SELECT pg_database_size('votre_base');
-- ou
SELECT pg_size_pretty(pg_database_size('votre_base'));

Suivez la croissance dans le temps avec une requête simple :

SELECT
  schemaname,
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
  pg_size_pretty(pg_relation_size(relid)) AS table_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

Si une table croît de plus de 20 % par mois, prévoyez un vacuum et éventuellement un partitionnement avant de manquer de disque.

Modes de défaillance et récupération

Savoir ce qui peut mal tourner rend la planification de capacité moins effrayante. Voici les modes de défaillance courants et comment récupérer.

Manque d'espace disque

Si pg_database_size approche la limite du système de fichiers, PostgreSQL peut cesser d'accepter les écritures, et dans le pire des cas, le serveur peut planter et refuser de redémarrer jusqu'à ce que de l'espace soit libéré.

Étapes de récupération :

1. Vérifiez l'utilisation du disque : df -h et du -sh /var/lib/postgresql si le répertoire de données y est.
2. Supprimez les anciens fichiers WAL si vous avez un archivage fonctionnel : pg_archivecleanup ou utilisez votre outil de sauvegarde.
3. VACUUM FULL ou CLUSTER sur une table gonflée pour récupérer de l'espace, mais notez que cela prend un verrou exclusif et peut causer des interruptions.
4. Augmentez le stockage ou déplacez le répertoire de données vers un volume plus grand.
5. Vérifiez avec df -h et SELECT pg_size_pretty(pg_database_size('votre_base'));.

Épuisement des connexions

Lorsque max_connections est atteint, les nouveaux clients reçoivent l'erreur FATAL: sorry, too many clients already. L'application échoue, même si la base de données elle-même n'est pas à court de mémoire ou de CPU.

Étapes de récupération :

1. Identifiez les connexions inactives : SELECT pid, usename, state, query_start FROM pg_stat_activity WHERE state = 'idle';
2. Terminez les connexions inactives plus anciennes qu'un seuil : SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND query_start < now() - interval '10 minutes';
3. Définissez un délai d'expiration d'inactivité au niveau de l'application ou du pooler.
4. Si la charge nécessite légitimement plus de connexions, envisagez d'augmenter max_connections mais ajoutez d'abord de la mémoire ou un pooler de connexions comme PgBouncer.

Épuisement de la mémoire dû à work_mem

Si work_mem est trop élevé, de nombreux tris simultanés peuvent amener le système d'exploitation à swapper fortement ou à invoquer le tueur OOM.

Étapes de récupération :

1. Surveillez vmstat 1 et free -h pour l'utilisation du swap.
2. Si le swap est élevé, réduisez work_mem immédiatement.
3. Rechargez : sudo systemctl reload postgresql
4. Vérifiez : SHOW work_mem; et surveillez l'utilisation de la mémoire pendant quelques minutes.

Échec d'archivage WAL

Si l'archivage continu échoue, les fichiers WAL s'accumulent et le disque se remplit.

Étapes de récupération :

1. Vérifiez pg_stat_archiver pour les échecs : SELECT * FROM pg_stat_archiver;
2. Réparez la commande d'archive ou le stockage.
3. Si nécessaire, définissez temporairement archive_command = '/bin/true' pour permettre au serveur de continuer, mais cela rompt la récupération ponctuelle jusqu'à correction. Ne le faites qu'en dernier recours et en pleine conscience de l'impact.

Liste de contrôle des opérations

Utilisez cette liste avant, pendant et après tout changement de capacité. Attribuez un responsable et une fréquence de revue pour chaque élément afin de ne rien oublier.

Pour chaque ligne, le responsable est une personne spécifique, pas une équipe. Si vous êtes un développeur solo, le responsable est vous. La fréquence est un rappel pour revisiter l'élément, pas un audit strict. Cochez [ ] lorsque c'est fait.

Pièges courants et comment les éviter

1. Définir shared_buffers trop haut

Pourquoi cela arrive : Une règle empirique dit 25 % de la RAM, donc les gens le définissent à 50 % ou plus, espérant de meilleures performances.

Le problème : Le système d'exploitation met également en cache les fichiers, et PostgreSQL peut double-cacher les données dans les tampons partagés et le cache du système d'exploitation. Trop de mémoire partagée peut réduire l'efficacité du cache du système d'exploitation et augmenter les pics d'écriture de checkpoint.

Comment éviter : Commencez à 25 % de la RAM. Évaluez avant et après. N'augmentez que si le taux de succès du cache s'améliore et que les checkpoints restent gérables. Sur PostgreSQL 12+, vous pouvez essayer des valeurs plus élevées mais surveillez pg_stat_bgwriter pour les statistiques de checkpoint.

2. Ignorer le surcoût des connexions

Pourquoi cela arrive : Les développeurs supposent que chaque connexion est légère. Ils ouvrent une connexion par requête ou par thread et définissent max_connections à 1000.

Le problème : Chaque processus backend consomme de la mémoire et du CPU. La commutation de contexte entre des centaines de processus peut dégrader les performances plus que le travail de base de données lui-même.

Comment éviter : Utilisez un pooler de connexions. PgBouncer en mode transaction peut gérer des milliers de connexions client avec seulement quelques dizaines de backends PostgreSQL réels. Mesurez régulièrement les connexions actives et inactives.

3. Ne pas surveiller le gonflement des tables

Pourquoi cela arrive : L'autovacuum s'exécute silencieusement et l'attention du DBA va aux requêtes lentes, pas à la taille des tables.

Le problème : Les tuples morts s'accumulent, les tables et index gonflent, les requêtes ralentissent et l'espace disque se remplit même si la taille logique des données est inchangée.

Comment éviter : Planifiez une surveillance régulière des tuples morts et de la progression du vacuum. Utilisez des extensions comme pgstattuple ou interrogez pg_stat_user_tables pour n_dead_tup. Définissez des seuils d'autovacuum adaptés à votre taux de rotation. Exécutez un VACUUM manuel sur les tables à forte rotation pendant les périodes de faible trafic.

4. Oublier de tester la restauration

Pourquoi cela arrive : Des scripts de sauvegarde existent, mais personne ne restaure à partir d'eux jusqu'à ce qu'une catastrophe survienne.

Le problème : Les sauvegardes peuvent être corrompues, incomplètes ou non restaurables en raison d'archives WAL manquantes. Une défaillance de capacité qui nécessite une restauration devient une panne totale.

Comment éviter : Effectuez un test de restauration trimestriel. Chronométrez la restauration. Vérifiez l'intégrité des données avec des sommes de contrôle. Documentez les étapes exactes et les objectifs de temps de récupération (RTO) et de point de récupération (RPO) attendus. Considérez une sauvegarde comme non testée jusqu'à ce qu'une restauration réussisse.

5. Modifier plusieurs paramètres à la fois

Pourquoi cela arrive : Un article de blog suggère un groupe de paramètres et l'opérateur les copie tous.

Le problème : Si les performances se dégradent, vous ne pouvez pas dire quel paramètre en est la cause. Le retour en arrière est plus difficile et peut introduire de nouveaux problèmes.

Comment éviter : Modifiez un paramètre à la fois. Attendez au moins un cycle de travail complet (24 heures) pour observer les modèles de charge. Utilisez ALTER SYSTEM SET pour les changements individuels et tenez un journal des modifications. Exemple :

ALTER SYSTEM SET work_mem = '16MB';
SELECT pg_reload_conf();

Vérifiez et surveillez ensuite avant de passer au paramètre suivant.

Conclusion

La planification de capacité PostgreSQL n'est pas une configuration ponctuelle. C'est une boucle : mesurer, estimer, modifier une variable, vérifier et récupérer si nécessaire. Les chiffres exacts varient selon la charge de travail, mais la discipline ne change pas.

Commencez par l'Inventaire de la version et de l'environnement de ce guide. Enregistrez vos paramètres actuels et les limites du système d'exploitation. Choisissez ensuite un changement à faible risque : ajustez work_mem ou ajoutez un index à une requête lente. Utilisez les requêtes de vérification pour voir l'effet. Mettez en place une revue hebdomadaire des connexions et du taux de succès du cache. Attribuez un responsable à chaque élément de la liste de contrôle et planifiez un test de restauration trimestriel.

Un plan de capacité fiable rend les défaillances visibles, protège les valeurs sensibles, limite les changements à la ressource prévue et définit la vérification de récupération avant qu'un incident ne force la décision. Avec les commandes et exemples concrets ci-dessus, vous pouvez garder votre base de données PostgreSQL performante et éviter les erreurs de capacité les plus courantes.