Calculateur PostgreSQL basé sur la charge réelle : l'Optimiseur Intelligent PWR

Calculateur PostgreSQL : du réglage générique au tuning basé sur votre charge

La configuration de PostgreSQL est un art trop souvent négligé : la plupart des administrateurs laissent les paramètres par défaut, sans savoir que cela peut à lui seul ralentir les performances de 50 à 80 %.

Le Tuning Advisor (Optimiseur Intelligent) de PWR est un calculateur PostgreSQL basé sur votre charge réelle : il analyse 30 jours de snapshots, puis propose des réglages adaptés à votre workload, sans intervention manuelle ni DBA dédié.

L'architecture de l'Optimiseur : comment ça marche ?

Le moteur de recommandations suit un pipeline en 5 étapes, entièrement basé sur l'historique réel de votre base (tables perfhist.*_hist alimentées par les snapshots) plutôt que sur des suppositions génériques.

Données brutes (30 jours)
        |
        v
Collecte des metriques (perfhist.*_hist)
        |
        v
Analyse intelligente (tendance, seuils, type de workload)
        |
        v
7 parametres PostgreSQL analyses
        |
        v
Recommandations avec score de confiance
        |
        v
Actions proposees (a valider avant application)

1) shared_buffers — le cache principal

shared_buffers est le cache partagé de PostgreSQL : il stocke en RAM les blocs de données les plus fréquemment utilisés — un peu comme le cache CPU de votre serveur, mais pour PostgreSQL.

Formule Pgtune de référence : shared_buffers = RAM × 25 %. Exemple : pour 16 Go de RAM, la valeur recommandée est 4 Go (16 × 0,25).

• 99 % de cache hit ratio = excellent, presque tout est servi depuis le cache
• 90 % = bon, peu d'accès disque
• 70 % = problématique, beaucoup d'accès disque
• Moins de 50 % = critique, configuration trop petite
• Impact typique en passant de 256 Mo à 4 Go : cache hit ratio 80 % → 95 %, requêtes jusqu'à +150 % plus rapides, I/O disque -70 %, CPU -40 %

SELECT shared_buffers INTO v_current_setting
FROM perfhist.settings_hist
ORDER BY date_extract DESC LIMIT 1;

Cache Hit Ratio = blks_hit / (blks_hit + blks_read) x 100

Trend UP (95% -> 98%)                 -> config optimale, pas de changement
Trend DOWN (98% -> 70%)               -> URGENT : augmenter de 35%
Trend STABLE (90%)                    -> recommander +15%
INSUFFICIENT (moins de 200 snapshots) -> attendre 30 jours de collecte

-- Si cache hit > 93% et stable
severity = 'low'
confidence = 96%
reason = 'Config optimale, pas de changement'

-- Si cache hit en baisse
v_ideal_mb = v_ideal_mb * 1.35  -- +35%
severity = 'critical'
confidence = 90%
reason = 'Performance en declin, augmentation urgente'

2) work_mem — la mémoire des opérations de tri

work_mem contrôle la mémoire allouée à chaque opération de tri (ORDER BY) ou de jointure. Si elle est insuffisante, PostgreSQL bascule sur le disque — 100 à 1000 fois plus lent qu'en mémoire.

Formule Pgtune : work_mem = (RAM × 3 %) / nombre de cœurs CPU. Exemple pour 16 Go de RAM et 8 cœurs : (16 × 1024 × 0,03) / 8 ≈ 61,44 Mo.

• 0 fichier temporaire/jour = excellent
• 1 à 100 fichiers/jour = acceptable
• 100 à 5000 fichiers/jour = problématique
• Plus de 5000 fichiers/jour = critique
• Impact typique en passant de 4 Mo à 61 Mo : temp_files 1000/jour → 0/jour, requêtes +30 % plus rapides, I/O disque -90 %, meilleure concurrence (moins de contention)

SELECT AVG(temp_files) INTO v_avg_metric
FROM perfhist.pg_stat_database_hist
WHERE datname = v_db_name
  AND date_extract >= NOW() - INTERVAL '30 days';

v_increase_pct = (v_ideal_mb - v_current_mb) / v_current_mb * 100

-- Exemple : actuel 4 Mo, ideal 61.44 Mo
-- augmentation = ((61.44 - 4) / 4) * 100 = +1436%

-- CAS 1 : beaucoup de temp_files (plus de 5000/jour)
IF v_avg_metric > 5000 THEN
    v_ideal_mb = v_ideal_mb * 1.5;  -- +50%
    severity = 'high';
    reason = 'Debordement disque detecte. Augmenter de 50%';

-- CAS 2 : quelques temp_files (100 a 5000/jour)
ELSIF v_avg_metric > 100 THEN
    v_ideal_mb = v_ideal_mb * 1.1;  -- +10%
    severity = 'medium';
    reason = 'Debordements occasionnels. Augmenter de 10%';

-- CAS 3 : pas de temp_files
ELSE
    IF v_increase_pct > 50 THEN
        severity = 'low';
        confidence = 90;
        reason = 'Pas de debordement. Config conservative, augmentation sans risque.';
    ELSE
        severity = 'low';
        confidence = 95;
        reason = 'Config adequate pour le workload courant';
    END IF;
END IF;

3) effective_cache_size — l'indicateur du planificateur

effective_cache_size ne réserve aucune mémoire : il indique au planificateur de requêtes combien de RAM est réellement disponible pour du cache (shared_buffers + cache disque de l'OS). Le planificateur s'en sert pour choisir entre un scan séquentiel et un scan d'index.

Formule Pgtune : effective_cache_size = RAM × 75 %. Exemple pour 16 Go : 12 Go. Logique : shared_buffers couvre 25 % (cache données), effective_cache_size couvre 75 % (cache total + OS), le reste sert à l'OS, aux connexions et au reste du système.

• Impact typique en passant de 1 Go à 12 Go : meilleurs plans de requête, utilisation des index +40 %, requêtes +20 % plus rapides, CPU -25 %

SELECT effective_cache_size INTO v_current_setting
FROM perfhist.settings_hist
ORDER BY date_extract DESC LIMIT 1;

v_ideal_mb = p_ram_gb * 1024 * 0.75;

v_severity = CASE
    WHEN v_current_mb < (v_ideal_mb * 0.9) THEN 'medium'
    ELSE 'low'
END;

-- reason : 'Pgtune recommande 75% de la RAM. Ce parametre indique
-- au planificateur combien de memoire est disponible pour le cache.
-- Sous-estime, le planificateur choisit des plans plus lents.'

4) max_connections — la capacité de concurrence

max_connections est le nombre maximal de connexions simultanées à PostgreSQL. Une valeur mal ajustée provoque soit des rejets de connexion ("too many connections"), soit un gaspillage de mémoire pour des connexions inutilisées.

La formule Pgtune dépend du type de workload observé, pas d'une valeur fixe : elle se base sur le nombre de cœurs CPU et le volume de transactions par jour.

• Exemple réel : 8 302 204 transactions/jour ≈ 96 transactions/seconde → classé Heavy OLTP
• Bien configuré : pas d'erreurs "too many connections", pas de gaspillage de ressources, performance stable, meilleure scalabilité

Heavy OLTP     (plus de 100k trans/jour) : max_connections = coeurs CPU x 25
Moderate OLTP  (50k a 100k trans/jour)   : max_connections = coeurs CPU x 12
Light OLTP     (10k a 50k trans/jour)    : max_connections = coeurs CPU x 6
Very light     (moins de 10k trans/jour) : max_connections = coeurs CPU x 4

SELECT AVG(xact_commit + xact_rollback) INTO v_avg_metric
FROM perfhist.pg_stat_database_hist
WHERE datname = v_db_name
  AND date_extract >= NOW() - INTERVAL '30 days';

v_ideal_mb = p_cpu_cores * 25;  -- exemple Heavy OLTP : 8 coeurs x 25 = 200 connexions

IF v_current_mb > (v_ideal_mb * 2) THEN
    severity = 'medium';
    reason = 'SURDIMENSIONNE : ' || v_current_mb || ' vs ' || v_ideal_mb || ' necessaires. Reduire pour economiser de la memoire.';
ELSIF v_current_mb < (v_ideal_mb * 0.8) THEN
    severity = 'high';
    reason = 'RISQUE : ' || v_current_mb || ' vs ' || v_ideal_mb || ' recommandes. Risque de rejet de connexion.';
ELSE
    severity = 'low';
    reason = 'Configuration appropriee.';
END IF;

5) maintenance_work_mem — la vitesse de VACUUM et CREATE INDEX

maintenance_work_mem est la mémoire utilisée par VACUUM, CREATE INDEX et ANALYZE. Plus elle est élevée, plus ces opérations de maintenance sont rapides.

Formule Pgtune : maintenance_work_mem = RAM × 15 %. Exemple pour 16 Go : 2,4 Go.

• Impact typique : VACUUM -70 % de temps, CREATE INDEX -60 %, ANALYZE -50 %, maintenance nocturne beaucoup plus rapide

v_ideal_mb = p_ram_gb * 1024 * 0.15;

-- reason : 'Pgtune recommande 15% de la RAM pour les operations de maintenance.
-- Utilise par VACUUM FULL (supprime les lignes mortes), CREATE INDEX
-- (construit les index), ANALYZE (collecte les statistiques).
-- Des valeurs plus elevees accelerent la maintenance.'

v_severity = CASE
    WHEN v_current_mb < (v_ideal_mb * 0.8) THEN 'medium'
    ELSE 'low'
END;

6) random_page_cost — l'utilisation des index

random_page_cost est le coût relatif d'un accès disque aléatoire par rapport à un accès séquentiel ; il influence directement les choix du planificateur de requêtes.

Par défaut, random_page_cost vaut 4.0 : un accès aléatoire est considéré 4 fois plus coûteux qu'un accès séquentiel. Si le planificateur privilégie trop souvent des scans séquentiels plutôt que les index, réduire cette valeur (1.1 à 1.5) force l'utilisation des index et peut accélérer les requêtes de 15 à 40 %.

• Impact typique en passant de 4.0 à 1.1 : utilisation des index +40 %, scans séquentiels -50 %, requêtes +15 à 40 % plus rapides, CPU -25 %

SELECT
    SUM(seq_scan)::NUMERIC / NULLIF(SUM(idx_scan)::NUMERIC, 0)
INTO v_first_metric
FROM perfhist.pg_stat_tables_hist
WHERE date_extract >= NOW() - INTERVAL '30 days';

-- Interpretation :
-- 2:1  = equilibre (bon)
-- 5:1  = a ameliorer
-- 10:1 = pression elevee (critique)

IF v_first_metric > 10 THEN
    v_ideal_mb = 1.1;
    severity = 'high';
    reason = 'Forte pression de scans sequentiels. Reduire a ' || v_ideal_mb || ' pour forcer l''usage des index. Gain attendu : +15 a 40%.';
ELSIF v_first_metric > 5 THEN
    v_ideal_mb = 1.5;
    severity = 'medium';
    reason = 'Usage de scans modere. Reduire a ' || v_ideal_mb || '.';
ELSE
    v_ideal_mb = 4.0;
    severity = 'low';
    reason = 'Repartition equilibree. Aucune action necessaire.';
END IF;

7) wal_buffers — le débit des transactions

wal_buffers est le tampon utilisé pour le Write-Ahead Log (WAL). Il regroupe les écritures (moins de flushes disque), ce qui améliore le débit transactionnel.

• Exemple 1 — charge 8,3 M transactions/jour (Heavy OLTP), actuel 16 Mo, idéal 16 Mo, écart 0 Mo → "Already optimal"
• Exemple 2 — même charge, actuel 4 Mo, idéal 16 Mo, écart 12 Mo → "Augmenter à 16 Mo"
• Impact typique en Heavy OLTP : écritures mieux regroupées, flushes disque -50 %, débit +10 à 20 %, latence -5 ms

Heavy OLTP     : 16 Mo
Moderate OLTP  : 4 Mo
Light OLTP     : auto (valeur par defaut)

IF v_ideal_mb = -1 THEN
    -- charge legere -> 'auto' convient
    recommended = 'auto';
ELSIF ABS(v_current_mb - v_ideal_mb) < 0.5 THEN
    -- deja optimal (ecart < 0.5 Mo)
    recommended = 'Already optimal';
    severity = 'low';
    confidence = 95;
    reason = 'wal_buffers est DEJA configure de maniere optimale. Aucun changement necessaire.';
ELSE
    recommended = ROUND(v_ideal_mb, 2) || 'MB';
    severity = 'high or medium';
    reason = 'Changement de ' || v_current_mb || ' vers ' || v_ideal_mb || 'MB';
END IF;

La règle générale : "déjà optimal ?"

Sans cette règle, une recommandation peut paraître illogique : une valeur actuelle et une valeur idéale identiques, mais affichées avec une sévérité "élevée". L'Optimiseur applique donc systématiquement un seuil d'écart avant de recommander un changement, pour chacun des 7 paramètres.

AVANT (incoherent) :
  wal_buffers
    Actuel      : 16MB
    Recommande  : 16MB
    Severite    : ELEVEE   <- contradiction

APRES (coherent) :
  wal_buffers
    Actuel      : 16MB
    Recommande  : Already optimal
    Severite    : LOW
    Confiance   : 95%

v_change_pct = ABS(v_current - v_ideal) / v_ideal * 100;

IF v_change_pct < 5 THEN
    -- ecart < 5% = deja optimal
    recommended = 'Already optimal';
    severity = 'low';
    confidence = confidence + 5;
    reason = 'Config deja optimale. ' || reason;
ELSE
    -- ecart >= 5% = changement recommande
    recommended = v_ideal;
    -- severity et reason d'origine conserves
END IF;

Sévérité et score de confiance

Chaque recommandation est accompagnée d'un niveau de sévérité (l'urgence d'agir) et d'un score de confiance (la fiabilité de la recommandation compte tenu des données disponibles).

• Critique — le système est en danger, action recommandée le jour même (exemple : max_connections trop bas sur un workload Heavy OLTP)
• Élevée — impact performance important, action recommandée sous une semaine (exemple : work_mem avec des milliers de fichiers temporaires par jour)
• Moyenne — impact notable mais non urgent, action dans les prochaines semaines (exemple : shared_buffers mal dimensionné)
• Faible — impact minimal, non urgent (exemple : configuration déjà adéquate)

Confiance = f(volume de donnees, stabilite de la tendance, formule utilisee)

95%+   : 200+ snapshots, configuration stable, formule Pgtune eprouvee
         -> appliquer la recommandation des maintenant

80-95% : 150+ snapshots, tendance claire, formule Pgtune
         -> appliquer puis surveiller

60-80% : donnees insuffisantes ou tendance variable, estimation ad hoc
         -> tester en environnement de dev d'abord

<60%   : moins de 100 snapshots, tendance incoherente
         -> attendre 30 jours de collecte supplementaires

Cas d'usage réel

Exemple sur un serveur de 16 Go de RAM et 8 cœurs CPU, avec une charge de 8,3 millions de transactions par jour (Heavy OLTP) — les 7 recommandations générées par l'Optimiseur :

Parametre               Actuel   Recommande        Severite  Confiance  Action
shared_buffers           128MB    4096MB            MEDIUM    80%        Augmenter
work_mem                 4MB      61.44MB           LOW       90%        Considerer
effective_cache_size     1GB      12GB              LOW       80%        Augmenter
max_connections          150      200               LOW       80%        OK
maintenance_work_mem     256MB    2400MB            MEDIUM    75%        Augmenter
random_page_cost         4.0      1.1               HIGH      85%        Reduire
wal_buffers              16MB     Already optimal   LOW       95%        Aucun changement

Avant :
  Cache hit    : 70%
  Temp files   : 1000/jour
  Requetes     : 100 TPS
  Latence      : 500ms

Apres (7 jours) :
  Cache hit    : 94%
  Temp files   : 0/jour
  Requetes     : 300 TPS (+200%)
  Latence      : 150ms (-70%)

Les avantages du Tuning Advisor

Au-delà du gain de performance mesuré, l'Optimiseur Intelligent change la façon dont la configuration PostgreSQL est abordée au quotidien.

• Pas besoin d'un DBA dédié pour le tuning de premier niveau : l'analyse est automatique et incluse dans PWR
• Basé sur vos données réelles, pas sur des suppositions ni du "trial and error" — chaque recommandation s'appuie sur 30 jours d'historique effectif
• Transparence totale : chaque recommandation affiche sa justification technique, la formule utilisée, son score de confiance et son impact estimé
• Risque maîtrisé : chaque recommandation reste une proposition à valider — l'usage recommandé est de l'appliquer d'abord en environnement de test, puis en production, après vérification des sauvegardes

Conclusion

Le Tuning Advisor de PWR transforme la configuration PostgreSQL en démarche mesurable plutôt qu'en intuition : en analysant 30 jours de données réelles, il recommande des changements précis, justifiés et chiffrés pour 7 des paramètres les plus déterminants pour la performance.

Résultat observé : des bases de données 2 à 5 fois plus rapides, sans faire appel à un DBA dédié pour ce premier niveau d'optimisation.