LEÇON 4 : Configuration et Optimisation PostgreSQL

4.1 Configuration de Base (postgresql.conf)

Problématique Entreprise

Un serveur PostgreSQL doit supporter 500 connexions simultanées avec des requêtes complexes sur des tables de plusieurs To.

Paramètres Mémoire Critiques

# postgresql.conf - Configuration Production

# ============ MÉMOIRE ============
# Règle : 25% de la RAM disponible (ex: 4GB si 16GB RAM)
shared_buffers = 4GB

# Règle : 50-75% de la RAM disponible
# Aide le planificateur à estimer le cache OS
effective_cache_size = 12GB

# Par connexion/opération de tri
# Trop bas = sorts sur disque (lent)
# Trop haut = risque OOM si beaucoup de connexions
work_mem = 16MB

# Pour VACUUM, CREATE INDEX, etc.
maintenance_work_mem = 512MB

# ============ CONNEXIONS ============
max_connections = 500

# Pool de connexions recommandé (utiliser PgBouncer)
# max_connections = 100  # Au lieu de 500
# PgBouncer gère 500+ connexions → 100 réelles

# ============ LOGGING (Debug Performance) ============
# Logger toutes les requêtes de modification
log_statement = 'mod'  # 'none', 'ddl', 'mod', 'all'

# Logger le temps d'exécution
log_duration = on

# Logger les requêtes > 1 seconde (à ajuster)
log_min_duration_statement = 1000  # millisecondes

# Détails d'exécution (EXPLAIN automatique)
auto_explain.log_min_duration = 1000
auto_explain.log_analyze = on

# ============ CHECKPOINTS (Stabilité) ============
# Éviter les pics I/O lors des checkpoints
checkpoint_completion_target = 0.9

# Taille max WAL avant checkpoint forcé
max_wal_size = 2GB
min_wal_size = 1GB

# ============ QUERY PLANNER ============
# Coût du random I/O (1.0 par défaut, diminuer si SSD)
random_page_cost = 1.1  # SSD: 1.1, HDD: 4.0

# Coût du sequential I/O
seq_page_cost = 1.0

# Statistiques par défaut (augmenter pour grandes tables)
default_statistics_target = 100  # Par défaut 100, max 10000

Application des Changements

# Recharger la config sans redémarrer (pour certains paramètres)
SELECT pg_reload_conf();

# Vérifier les paramètres actuels
SHOW shared_buffers;
SHOW work_mem;
SHOW effective_cache_size;

# Voir tous les paramètres
SELECT name, setting, unit FROM pg_settings WHERE name LIKE '%mem%';

---

4.2 Analyse de Performance (EXPLAIN ANALYZE)

Comprendre les Plans d’Exécution

-- Requête à optimiser
EXPLAIN ANALYZE
SELECT
    o.order_id,
    c.company_name,
    SUM(ol.quantity * ol.unit_price) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_lines ol ON o.order_id = ol.order_id
WHERE o.order_date >= '2025-01-01'
GROUP BY o.order_id, c.company_name
ORDER BY total DESC
LIMIT 10;

/* Résultat EXPLAIN ANALYZE :
Limit  (cost=12345.67..12345.89 rows=10 width=123) (actual time=45.234..45.567 rows=10 loops=1)
  ->  Sort  (cost=12345.67..12456.78 rows=44442) (actual time=45.230..45.560 rows=10 loops=1)
        Sort Key: (sum(...)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  HashAggregate  (cost=11234.56..11567.89 rows=44442) (actual time=42.123..43.456 rows=44442 loops=1)
              ->  Hash Join  (cost=234.56..10123.45 rows=44442) (actual time=5.678..38.901 rows=44442 loops=1)
                    Hash Cond: (ol.order_id = o.order_id)
                    ->  Seq Scan on order_lines ol  (cost=0.00..8901.23 rows=456789) (actual time=0.012..25.678 rows=456789 loops=1)
                    ->  Hash  (cost=123.45..123.45 rows=4444) (actual time=5.432..5.432 rows=4444 loops=1)
                          ->  Hash Join  (cost=12.34..123.45 rows=4444) (actual time=0.234..4.567 rows=4444 loops=1)
                                Hash Cond: (o.customer_id = c.customer_id)
                                ->  Seq Scan on orders o  (cost=0.00..89.01 rows=4444) (actual time=0.012..2.345 rows=4444 loops=1)
                                      Filter: (order_date >= '2025-01-01')
                                ->  Hash  (cost=8.90..8.90 rows=275) (actual time=0.123..0.123 rows=275 loops=1)
                                      ->  Seq Scan on customers c  (cost=0.00..8.90 rows=275) (actual time=0.003..0.067 rows=275 loops=1)
Planning Time: 1.234 ms
Execution Time: 45.789 ms
*/

Interpréter les Résultats

Éléments Clés :

• cost : Estimation (unités arbitraires, pas des ms)
• rows : Nombre de lignes estimé
• actual time : Temps réel en millisecondes
• loops : Nombre d’exécutions (si > 1, multiplier le temps)

Opérations Coûteuses :

• Seq Scan : Parcours complet de table (mauvais si table grande)
• Index Scan : Utilise un index (bon)
• Index Only Scan : Optimal, tout est dans l’index
• Bitmap Heap Scan : Pour requêtes larges sur index
• Hash Join : Bon pour grandes jointures
• Nested Loop : Coûteux si grandes tables

Optimisation avec Index

-- Problème : Seq Scan sur orders (lent)
-- Solution : Index sur order_date
CREATE INDEX idx_orders_date ON orders (order_date);

-- Problème : Nested Loop coûteux
-- Solution : Index sur FK
CREATE INDEX idx_order_lines_order ON order_lines (order_id);

-- Ré-analyser après index
EXPLAIN ANALYZE [même requête];

/* Nouveau plan (optimisé) :
Limit  (cost=1234.56..1234.78 rows=10) (actual time=8.123..8.234 rows=10 loops=1)
  ->  Sort  (cost=1234.56..1245.67 rows=4444) (actual time=8.120..8.230 rows=10 loops=1)
        ->  HashAggregate  (cost=1123.45..1156.78 rows=4444) (actual time=7.890..8.012 rows=4444 loops=1)
              ->  Hash Join  (cost=234.56..1023.45 rows=4444) (actual time=1.234..6.789 rows=4444 loops=1)
                    ->  Index Scan using idx_orders_date on orders o  (cost=0.29..123.45 rows=4444) (actual time=0.012..1.234 rows=4444 loops=1)
                          Index Cond: (order_date >= '2025-01-01')
Execution Time: 8.456 ms  -- 5x plus rapide !
*/

Statistiques et Monitoring

-- Voir les index utilisés
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Tables nécessitant VACUUM
SELECT
    schemaname,
    relname AS tablename,
    n_live_tup AS live_rows,
    n_dead_tup AS dead_rows,
    ROUND(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_vacuum,
    last_autovacuum
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Requêtes lentes en cours
SELECT
    pid,
    now() - query_start AS duration,
    state,
    query
FROM pg_stat_activity
WHERE state != 'idle'
  AND now() - query_start > INTERVAL '5 seconds'
ORDER BY duration DESC;

---

4.3 Maintenance Régulière

VACUUM : Nettoyage Essentiel

-- VACUUM simple : Libère l'espace des lignes supprimées
VACUUM products;

-- VACUUM FULL : Réécrit la table (verrouillage complet, lent)
VACUUM FULL products;  -- À éviter en production !

-- VACUUM ANALYZE : Nettoyage + mise à jour des statistiques
VACUUM ANALYZE products;

-- VACUUM sélectif par table critique
VACUUM ANALYZE orders, order_lines, customers;

-- Planification automatique (recommandé)
-- Dans postgresql.conf :
autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min

ANALYZE : Mise à Jour des Statistiques

-- Mettre à jour les statistiques pour le query planner
ANALYZE products;

-- Tout le schéma
ANALYZE;

-- Statistiques étendues pour colonnes corrélées
CREATE STATISTICS stats_orders_customer_date
ON customer_id, order_date
FROM orders;

-- Rafraîchir après création
ANALYZE orders;

REINDEX : Reconstruction d’Index

-- Reconstruire un index (si fragmenté)
REINDEX INDEX idx_products_name;

-- Toute la table
REINDEX TABLE products;

-- Tout le schéma (ATTENTION : long et verrouillant)
REINDEX SCHEMA public;

-- REINDEX CONCURRENTLY (PostgreSQL 12+) : Sans bloquer
REINDEX INDEX CONCURRENTLY idx_products_name;

Script de Maintenance Automatique

-- Fonction de maintenance quotidienne
CREATE OR REPLACE FUNCTION daily_maintenance()
RETURNS void AS $
BEGIN
    -- 1. VACUUM tables critiques
    VACUUM ANALYZE orders, order_lines, products, customers;

    -- 2. Statistiques étendues
    ANALYZE orders;
    ANALYZE products;

    -- 3. Nettoyer les anciennes données de log
    DELETE FROM activity_log
    WHERE created_at < NOW() - INTERVAL '90 days';

    -- 4. Archiver les anciennes commandes
    INSERT INTO orders_archive
    SELECT * FROM orders
    WHERE created_at < NOW() - INTERVAL '2 years';

    DELETE FROM orders
    WHERE created_at < NOW() - INTERVAL '2 years';

    RAISE NOTICE 'Maintenance terminée à %', NOW();
END;
$ LANGUAGE plpgsql;

-- Planifier avec pg_cron (extension)
CREATE EXTENSION IF NOT EXISTS pg_cron;
SELECT cron.schedule('daily-maintenance', '0 2 * * *', 'SELECT daily_maintenance()');

---