Deep EVM #27 : Performance PostgreSQL à grande échelle — Index, VACUUM et optimisation de requêtes

PostgreSQL à grande échelle

PostgreSQL est remarquablement rapide par défaut, mais à grande échelle vous devez comprendre ses mécanismes internes pour maintenir les performances. Cet article couvre les trois piliers : les index, VACUUM et l’optimisation de requêtes.

Index : au-delà du B-tree basique

Index partiels

Indexez uniquement les lignes qui comptent :

-- Au lieu d'indexer toute la table
CREATE INDEX idx_orders_status ON orders(status);

-- Indexez seulement les commandes actives (5% de la table)
CREATE INDEX idx_orders_active ON orders(created_at)
    WHERE status = 'active';

L’index partiel est 20x plus petit, 20x plus rapide à maintenir, et couvre 95 % des requêtes.

Index couvrants (covering indexes)

-- La requête lit seulement account_id et balance
-- Un index couvrant évite de toucher la table (index-only scan)
CREATE INDEX idx_accounts_covering ON accounts(account_id)
    INCLUDE (balance, updated_at);

BRIN pour les séries temporelles

Block Range INdex — idéal pour les données naturellement ordonnées :

-- 1000x plus petit qu'un B-tree pour les données temporelles
CREATE INDEX idx_events_time ON events USING BRIN (created_at)
    WITH (pages_per_range = 32);

BRIN stocke les valeurs min/max par groupe de pages. Pour les données insérées chronologiquement, il est extrêmement efficace.

VACUUM : comprendre le MVCC

PostgreSQL utilise le MVCC (Multi-Version Concurrency Control). Chaque UPDATE crée une nouvelle version de la ligne ; l’ancienne version (dead tuple) reste jusqu’à ce que VACUUM la nettoie.

Pourquoi VACUUM est critique

Sans VACUUM :

• La table grossit indéfiniment (bloat)
• Les index pointent vers des dead tuples
• Les requêtes scannent des pages mortes
• Les statistiques deviennent obsolètes

Configuration d’autovacuum

-- Pour les tables à fort débit d'écriture
ALTER TABLE transactions SET (
    autovacuum_vacuum_scale_factor = 0.01,     -- Déclencher à 1% de dead tuples
    autovacuum_vacuum_cost_delay = 2,           -- 2ms entre les I/O
    autovacuum_vacuum_cost_limit = 1000          -- Budget d'I/O élevé
);

Monitoring du bloat

SELECT
    schemaname, tablename,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup::numeric / greatest(n_live_tup, 1) * 100, 1) AS dead_pct,
    last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM transactions
WHERE account_id = 42 AND created_at > '2024-01-01';

Points clés à lire :

• Actual time vs estimated time — Un écart important indique des statistiques obsolètes
• Buffers: shared hit vs read — Hit = en cache, read = disque
• Rows Removed by Filter — Beaucoup de lignes filtrées = index manquant
• Seq Scan sur une grande table — Probablement un index manquant

Anti-patterns courants

1. **SELECT *** — Lisez seulement les colonnes nécessaires
2. Fonctions sur les colonnes indexées — WHERE YEAR(created_at) = 2024 ne peut pas utiliser l’index
3. OR dans WHERE — Souvent empêche l’utilisation de l’index ; utilisez UNION
4. Pas de LIMIT — Toujours limiter les résultats en production
5. Transactions longues — Bloquent VACUUM et accumulent les dead tuples

Conclusion

La performance PostgreSQL à grande échelle repose sur trois piliers : des index ciblés (partiels, couvrants, BRIN), un VACUUM bien configuré (autovacuum agressif pour les tables à fort débit), et l’interprétation méthodique d’EXPLAIN ANALYZE pour identifier et corriger les problèmes.