PostgreSQL en surcharge : tuning, autovacuum, partitioning pour 100k req/s — guide 2026

Une PME ouest-africaine qui réussit voit sa base PostgreSQL grossir vite. La table orders qui faisait 10 000 lignes en année 1 dépasse 500 000 lignes en année 3. Les requêtes qui répondaient en 50 ms tombent à 5 secondes. Les rapports comptables nocturnes consomment toute la mémoire du serveur. La tentation est forte de monter en gamme — passer de 16 Go à 64 Go, ajouter des cores. Cette approche fonctionne mais coûte cher et masque les vrais problèmes : index manquants, autovacuum mal réglé, requêtes mal écrites, paramètres par défaut inadaptés. Bien tuner PostgreSQL permet de soutenir 100 000 requêtes par seconde sur un VPS modeste. Mal le tuner, c’est saturer un serveur 64 Go avec 1 000 requêtes/seconde.

Ce guide pose les bases d’un PostgreSQL en surcharge soutenue : paramètres clés à tuner, autovacuum agressif, partitioning de tables qui dépassent 10 millions de lignes, monitoring via pg_stat_statements, identification des requêtes lentes et création des index manquants. Quatre tutoriels techniques creusent les sujets sensibles : tuning shared_buffers et work_mem, autovacuum aggressive et bloat, partitioning par hash et range, pg_stat_statements et auto_explain pour diagnostic.

Pourquoi PostgreSQL plutôt que MySQL ou MongoDB en 2026

Le choix de la base de données conditionne durablement les capacités d’évolution d’une PME. PostgreSQL s’est imposé en 2026 comme le standard de fait pour les applications d’entreprise grâce à plusieurs forces structurelles. D’abord, sa fiabilité — aucune perte de données silencieuse documentée depuis des décennies, contrairement à MySQL où le moteur InnoDB a connu plusieurs incidents historiques sur des configurations de réplication particulières. Ensuite, sa richesse fonctionnelle : JSON natif avec indexation GIN, fonctions de fenêtre, recherche plein texte, types géographiques (PostGIS), et désormais vecteurs avec pgvector pour les applications d’IA — toutes ces fonctionnalités natives évitent l’introduction d’autres briques qui complexifient l’architecture.

Face à MongoDB, PostgreSQL gagne sur la cohérence transactionnelle et la maturité des outils analytiques. Le piège MongoDB : les premiers développements semblent rapides grâce à la flexibilité du schéma, puis les besoins de jointures et d’agrégations complexes apparaissent et l’architecture devient ingérable. PostgreSQL avec ses colonnes JSONB offre la flexibilité MongoDB tout en gardant la rigueur SQL — meilleur des deux mondes.

Pour 95 % des cas d’usage PME en 2026, PostgreSQL 17 est le choix sans regret. Les rares 5 % restants concernent des charges spécifiques (séries temporelles à très haute fréquence, recherche vectorielle massive, OLAP à pétabytes) qui justifient une base spécialisée en complément : TimescaleDB pour les time-series, ClickHouse pour l’analytique massive, Qdrant pour les vecteurs à très grande échelle. Ces bases complémentaires se déploient en parallèle de PostgreSQL principal, jamais en remplacement.

Les paramètres critiques à tuner

Cinq paramètres dans postgresql.conf conditionnent 80 % des performances. shared_buffers définit la mémoire allouée au cache des données — règle empirique 25 % de la RAM totale. Sur un VPS 16 Go dédié à Postgres, allouer 4 Go. Cette mémoire stocke les pages chaudes et évite les lectures disque répétées. Trop bas, les requêtes ralentissent dramatiquement ; trop haut, le système d’exploitation manque de mémoire pour son propre cache.

effective_cache_size ne réserve pas de mémoire mais informe le planificateur de la mémoire disponible globalement (cache OS + shared_buffers). Régler à 75 % de la RAM totale aide le planificateur à choisir des plans optimaux qui assument le cache. work_mem alloue de la mémoire par opération de tri ou de hash — par défaut 4 Mo, à monter à 32 ou 64 Mo pour les requêtes analytiques lourdes. Attention : cette mémoire est allouée par opération concurrente, multiplier par le nombre de requêtes simultanées avant de fixer la valeur.

max_connections par défaut à 100 doit être dimensionné selon les clients. Hasura ouvre 50 connexions par défaut, plus quelques services custom — viser 200 minimum. Au-delà de 200, basculer sur PgBouncer en pooling pour multiplexer. maintenance_work_mem alloue la mémoire pour CREATE INDEX, VACUUM, ANALYZE — monter à 1 Go pour accélérer significativement les opérations de maintenance.

Ces paramètres se modifient dans postgresql.conf et nécessitent un redémarrage pour shared_buffers et max_connections, un simple reload pour les autres. Toujours mesurer avant et après chaque changement avec un benchmark représentatif (pgbench ou un script qui rejoue le trafic réel) plutôt que de copier-coller des valeurs trouvées sur internet sans contexte.

Autovacuum : l’ennemi méconnu

Le bloat est l’ennemi silencieux de PostgreSQL. Chaque UPDATE ou DELETE laisse une trace de ligne morte (le MVCC de PostgreSQL conserve les anciennes versions pour les transactions concurrentes). Ces lignes mortes s’accumulent jusqu’au passage de l’autovacuum qui les nettoie. Les paramètres par défaut d’autovacuum sont conservateurs et conviennent à des bases de quelques gigaoctets ; ils deviennent insuffisants au-delà.

Symptômes du bloat : tables qui font 50 Go alors que les données utiles tiennent en 5 Go, requêtes qui ralentissent progressivement sans changement applicatif, index qui ne sont plus utilisés. Le diagnostic passe par la requête SQL SELECT pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10 qui identifie les tables les plus polluées par les lignes mortes.

Le tuning autovacuum agressif consiste à abaisser les seuils de déclenchement. Au lieu d’attendre 20 % de lignes mortes (par défaut), déclencher dès 5 %. Au lieu d’autoriser 3 workers concurrents, en autoriser 6. Augmenter la mémoire allouée à 256 Mo. Ces paramètres se mettent dans postgresql.conf ou par table via ALTER TABLE ... SET (autovacuum_vacuum_scale_factor = 0.05). Le tutoriel dédié détaille la configuration optimale selon le profil de charge.

Partitioning : découper les tables géantes

Quand une table dépasse 10 à 50 millions de lignes, les performances dégradent sensiblement même avec les meilleurs index. La solution : le partitioning natif de PostgreSQL. Découper la table en plusieurs partitions selon une clé (date, hash de l’utilisateur, région) permet à PostgreSQL de ne scanner que les partitions pertinentes pour chaque requête.

Le partitioning par range est le plus courant : une table orders partitionnée par mois (orders_2026_04, orders_2026_05, etc.) permet d’archiver les anciens mois (DETACH PARTITION) et de les déplacer vers un stockage froid sans toucher aux performances de la table active. Les requêtes avec filtre WHERE created_at > ... exploitent automatiquement le partition pruning.

Le partitioning par hash répartit les données uniformément entre N partitions selon le hash d’une clé (par exemple customer_id). Utile pour les tables sans dimension temporelle évidente où on veut paralléliser les écritures. Le partitioning par list assigne chaque valeur à une partition spécifique — utile pour le multi-tenants où chaque client a sa propre partition isolée. Le tutoriel dédié détaille chaque mode avec exemples concrets.

Tutoriels techniques de cette série

• Tuner shared_buffers, work_mem et autovacuum pour 100k req/s — calcul des valeurs optimales, benchmark, ajustements progressifs.
• Partitioning PostgreSQL : range, hash et list — guide pratique — découpage, archivage, attachement de partitions, gestion ALTER TABLE.
• pg_stat_statements + auto_explain : diagnostiquer les requêtes lentes — installation, lecture des plans, top 10 requêtes consommatrices.
• Index PostgreSQL avancés : BRIN, GIN, partiels, expressions — quand utiliser chaque type, mesurer le gain, maintenance.

Adaptation au contexte ouest-africain

Pour les PME ouest-africaines, le tuning PostgreSQL prend une dimension économique particulière. L’écart entre une base bien tunée et une base par défaut peut représenter 50 à 70 % d’économies sur l’infrastructure : un VPS CCX13 à 30 €/mois suffit avec une base bien tunée pour servir une charge qui demanderait un CCX33 à 130 €/mois sans tuning. Sur trois ans, l’économie atteint plusieurs milliers d’euros — équivalent à plusieurs nouveaux postes commerciaux pour développer le business.

L’enjeu est aussi opérationnel : une base lente bloque l’équipe et les utilisateurs, érode la réputation, démotive les développeurs. Investir un mois de travail dans le tuning PostgreSQL au moment où la base atteint 10 à 50 Go évite des années de douleur. Pour les PME qui n’ont pas les compétences en interne, les freelances expérimentés en PostgreSQL facturent 200 à 500 000 XOF la journée à Dakar ou Abidjan — investissement qui se rentabilise en quelques semaines.

Erreurs fréquentes

FAQ

Quand passer de PostgreSQL mono-nœud à un cluster ?

Quand l’indisponibilité ponctuelle devient inacceptable pour le business : typiquement au-dessus de 50 millions de XOF de chiffre d’affaires annuel, ou pour les services réglementés. Avant ce seuil, un mono-nœud bien tuné avec sauvegardes testées suffit largement.

Faut-il vraiment partitionner une table de 10 millions de lignes ?

Pas nécessairement. Avec de bons index, une table de 50 millions de lignes peut tenir sans partitioning. Le partitioning devient pertinent quand on veut archiver facilement (ex. retirer le mois de janvier), quand on a des charges très différentes par région ou client, ou quand les requêtes scannent systématiquement des sous-ensembles. Mesurer avant de décider.

PostgreSQL 17 vs 16 : faut-il migrer ?

Oui pour les nouveaux déploiements. La 17 apporte des améliorations significatives sur le COPY, le VACUUM incrémental, et la performance des planificateurs. Pour les bases existantes en 16, attendre la 17.2 ou 17.3 avant de migrer pour la stabilité.

Choisir le bon type d’index

PostgreSQL propose six types d’index distincts, chacun adapté à des cas précis. B-Tree est l’index par défaut et convient pour 90 % des cas — recherche d’égalité, intervalle, tri. Hash est limité à l’égalité stricte, intéressant uniquement pour des cas très spécifiques. GIN excelle sur les colonnes JSONB, les tableaux et la recherche plein texte : créer un index GIN sur la colonne metadata JSON permet des requêtes ultra-rapides sur les clés et valeurs imbriquées. GIST sert pour les types géométriques et le full-text search avec ranking.

BRIN (Block Range Index) est la perle méconnue pour les très grosses tables. Au lieu de stocker un entry par ligne comme B-Tree, BRIN stocke un entry par bloc de pages — l’index final est 1000 fois plus petit. Idéal pour des données naturellement triées par insertion (timestamps, ID auto-incrémenté). Une table de 100 Go peut avoir un index BRIN de 10 Mo qui répond aux requêtes en quelques millisecondes. Enfin, les index partiels et fonctionnels permettent d’optimiser des cas spécifiques : index uniquement sur les commandes active, ou index sur LOWER(email) pour les recherches insensibles à la casse.

La règle d’or : ne jamais créer un index sans données quantifiées. Mesurer avant avec EXPLAIN ANALYZE, créer l’index, mesurer après. Un index inutile coûte en espace disque et ralentit les écritures. Auditer trimestriellement les index non utilisés via pg_stat_user_indexes et supprimer ceux dont idx_scan reste à zéro depuis des semaines.

Réplication et lecture distribuée

Quand le cluster Patroni est en place (voir le tutoriel HA Hasura associé), les réplicas en lecture seule deviennent une ressource précieuse pour décharger le primaire. Les rapports analytiques, les exports comptables nocturnes, les workers de traitement asynchrone peuvent tous être routés vers les réplicas via une connection string distincte. Le primaire conserve sa capacité pour les écritures transactionnelles critiques.

PgBouncer ne supporte pas nativement le routage read/write — il faut deux pools distincts ou utiliser un middleware comme HAProxy ou Pgpool-II. Une approche pragmatique pour PME : configurer deux URLs Postgres dans le code applicatif, l’une pour le primaire (POSTGRES_URL_RW), l’autre pour les réplicas (POSTGRES_URL_RO). Le code applicatif route explicitement les requêtes selon leur nature. Cette discipline évite les complications du middleware et donne un contrôle fin sur la charge.

Extensions PostgreSQL incontournables

L’écosystème d’extensions PostgreSQL multiplie les capacités natives de la base. Cinq extensions à connaître pour toute PME en croissance. pg_stat_statements trace les requêtes les plus lentes et leur fréquence — installation obligatoire en production. auto_explain capture automatiquement les plans d’exécution des requêtes au-delà d’un seuil de durée, idéal pour diagnostiquer les régressions. pgvector ajoute le support natif des embeddings vectoriels pour les applications d’IA — recherche sémantique, recommandations, RAG. pg_repack permet de défragmenter une table sans bloquer les écritures, alternative moderne à VACUUM FULL. TimescaleDB ajoute des fonctionnalités spécialisées pour les séries temporelles — utile dès qu’on stocke des métriques ou des événements à haute fréquence.

Toutes ces extensions s’installent en quelques minutes via APT ou via une simple commande SQL CREATE EXTENSION pgvector. Sur Coolify, elles s’activent au moment du déploiement de l’image PostgreSQL via les variables d’environnement ou un script d’initialisation. Pour une PME qui démarre, activer pg_stat_statements et auto_explain dès le premier jour : l’historique de performance accumulé sur plusieurs mois est précieux quand vient le moment d’optimiser.

Sauvegarde et plan de restauration PostgreSQL

La performance de PostgreSQL ne sert à rien sans une stratégie de sauvegarde robuste. Pour une PME, trois mécanismes complémentaires s’imposent. Premier : pg_basebackup ou pgBackRest pour les sauvegardes physiques complètes hebdomadaires. pgBackRest est l’outil de référence en 2026 — il gère le delta backup, la compression, le chiffrement, les répertoires distants S3, et permet le PITR (Point-In-Time Recovery) à la seconde près en combinant backup full et WAL archiving.

Deuxième mécanisme : pg_dump en parallèle pour les sauvegardes logiques quotidiennes. Le format custom (-Fc) permet une restauration sélective table par table — précieux quand on veut récupérer seulement une table précise après un drop accidentel. Le dump quotidien part vers Hetzner Storage Box ou un bucket S3 distinct, avec rétention 14 jours/8 semaines/12 mois pour environ deux ans d’historique.

Troisième mécanisme : snapshot du volume Docker une fois par mois pour archive longue durée. Ce snapshot rejoint un coffre cloud distinct (par exemple AWS Glacier ou Backblaze B2) hors du même fournisseur que la production. Cette redondance protège contre la défaillance complète d’un fournisseur ou contre un attaquant qui aurait compromis le compte principal. La règle 3-2-1 : trois copies, deux supports différents, une copie hors site.

Tests de restauration trimestriels

Une sauvegarde non testée est une promesse vide. Mettre en place une routine trimestrielle qui valide chaque mécanisme. Premier test : restauration complète depuis pgBackRest sur un VPS de bac à sable, mesurer le RTO réel, valider que la base reconstruite est cohérente. Deuxième test : restauration sélective depuis pg_dump custom, vérifier qu’une seule table peut être ramenée sans toucher au reste. Troisième test : restauration depuis le snapshot mensuel pour valider la chaîne longue durée.

Documenter chaque test avec timestamp, durée, problèmes rencontrés, actions correctives. Cette discipline transforme la sauvegarde d’une formalité administrative en assurance opérationnelle réelle. La PME qui découvre lors d’un incident que sa procédure de restauration ne fonctionne pas perd souvent ses données — mieux vaut découvrir les problèmes en bac à sable.

Sécurité PostgreSQL en production

PostgreSQL en production demande plusieurs couches de sécurité. Première couche : ne jamais exposer le port 5432 sur internet. Le binder uniquement sur localhost ou le réseau Docker interne, protéger l’accès via SSH tunnel ou VPN pour les administrateurs distants. Deuxième couche : SSL obligatoire pour les connexions distantes, certificats valides, désactivation explicite des connexions non chiffrées dans pg_hba.conf.

Troisième couche : principe du moindre privilège pour les utilisateurs PostgreSQL. Le compte applicatif (utilisé par Hasura ou Dolibarr) ne doit avoir que les permissions strictement nécessaires sur les tables métier — pas SUPERUSER, pas CREATEDB. Un compte d’administration séparé existe pour les opérations de maintenance, jamais utilisé par les applications. Quatrième couche : row-level security (RLS) PostgreSQL pour les données extrêmement sensibles, activée au niveau de la base et compatible avec les permissions Hasura applicatives.

Migration majeure entre versions

PostgreSQL publie une version majeure par an environ. La migration entre versions majeures (15 → 16 → 17) demande une attention particulière. La méthode classique pg_dumpall + restore prend plusieurs heures pour une base de 100 Go et nécessite une coupure de service. La méthode logical replication permet une migration sans coupure : déployer la nouvelle version en parallèle, configurer la réplication logique depuis l’ancienne, basculer le trafic une fois la synchronisation terminée.

L’outil pg_upgrade offre une troisième voie : upgrade in-place avec lien physique des fichiers, fenêtre de coupure réduite à quelques minutes. Recommandé pour les migrations rapides quand le downtime est tolérable. Quelle que soit la méthode, tester systématiquement sur un VPS bac à sable identique à la production avant la bascule réelle. Documenter chaque migration dans un journal d’opérations qui mentionne version source, version cible, méthode utilisée, problèmes rencontrés.

Cas concret : e-commerce sénégalais à 50 000 commandes/an

Pour ancrer le tuning dans un cas réel, prenons une PME e-commerce sénégalaise qui passe de 5 000 à 50 000 commandes par an en deux ans. La table orders grossit de 50 000 à 500 000 lignes. La table order_items avec en moyenne 3 lignes par commande dépasse 1,5 million de lignes. Les pages de gestion deviennent lentes : chargement de la liste des commandes en 8 secondes au lieu de 200 ms initiales, exports comptables nocturnes qui consomment toute la mémoire.

Diagnostic rapide via pg_stat_statements : la requête SELECT * FROM orders ORDER BY created_at DESC LIMIT 50 ne tape pas l’index sur created_at car l’index n’existe pas. Création de l’index, gain immédiat de 30 fois sur cette requête. Diagnostic suivant : les jointures order → order_items déclenchent un seq_scan car l’index sur la foreign key order_id manque. Ajout de l’index, gain de 100 fois sur les requêtes détaillées. Tuning de shared_buffers de 8 Mo (par défaut) à 4 Go, cache des données chaudes en mémoire — gain global perceptible sur l’ensemble des écrans.

Total de l’optimisation : deux jours de travail, gain global de 50 à 100 fois sur les pages les plus utilisées. La PME a évité de monter en gamme un VPS qui aurait coûté 100 €/mois supplémentaires : ROI de 1 200 €/an pour un investissement initial de 200 000 XOF. Cette histoire vraie résume l’enjeu du tuning PostgreSQL pour une PME en croissance — le levier le plus rentable de toute l’infrastructure technique.

Quand basculer en cluster Patroni

Le tuning mono-nœud tient bien jusqu’à des charges significatives — typiquement 50 à 100 000 utilisateurs actifs mensuels pour une PME e-commerce, ou plusieurs millions de lignes par table principale. Au-delà, la bascule en cluster Patroni avec réplication devient pertinente, autant pour la résilience que pour distribuer la charge en lecture. Le seuil de bascule typique : quand les heures ouvrées dépassent 70 % d’utilisation CPU PostgreSQL en moyenne, ou quand l’équipe IT considère qu’une coupure de plusieurs heures aurait un coût business inacceptable.

Le passage en cluster s’anticipe dès la conception applicative. Concevoir le code pour supporter le routage read/write distinct, externaliser la configuration de connection string en variables d’environnement, éviter les long-running transactions qui compliquent le failover. Une PME qui démarre proprement avec ces principes peut basculer en cluster en quelques semaines au moment opportun, sans refonte applicative coûteuse. Cette anticipation simple constitue l’un des marqueurs d’une équipe technique mature qui pense au-delà du MVP initial.

Pour aller plus loin

Documentation officielle PostgreSQL : postgresql.org/docs, livre de référence PostgreSQL Administration Cookbook de Simon Riggs (2024 update), wiki performance : wiki.postgresql.org/wiki/Performance_Optimization. Les tutoriels suivants de cette série creusent chaque sujet : tuning des paramètres avec benchmark, autovacuum aggressive, partitioning par hash et range, pg_stat_statements pour le diagnostic.

Pour une PME qui veut maîtriser PostgreSQL en profondeur, prévoir une formation interne ou un consultant pendant deux à trois jours. L’investissement initial autour d’un million de XOF se rentabilise en quelques mois grâce à la réduction de la facture d’infrastructure et à la stabilité opérationnelle. Cette discipline transforme la base de données d’un goulot d’étranglement potentiel en avantage compétitif structurel — et constitue l’une des meilleures décisions techniques qu’une PME en croissance puisse prendre.