Optimisation des bases de données : indexation, partitionnement et réplication

La base de données est souvent le bottleneck

Dans la plupart des applications, la base de données est le goulot d'étranglement principal. Une requête mal optimisée peut prendre 10 secondes au lieu de 10 millisecondes — un facteur 1000x. Les trois leviers principaux sont l'indexation, le partitionnement et la réplication.

Indexation : l'arme n°1

Un index est une structure de données (B-tree, Hash, GIN, GiST) qui permet à la base de trouver des lignes sans scanner toute la table. Sans index, une recherche sur 10M de lignes nécessite un full table scan. Avec un index B-tree, c'est un parcours logarithmique — O(log n) au lieu de O(n).

• Index B-tree : le plus courant, optimal pour =, <, >, BETWEEN, ORDER BY
• Index Hash : uniquement pour l'égalité exacte (=), plus rapide que B-tree pour ce cas
• Index GIN : pour les recherches full-text, JSONB et arrays (PostgreSQL)
• Index partiel : indexe uniquement un sous-ensemble de lignes (WHERE status = 'active')
• Index composite : plusieurs colonnes, l'ordre des colonnes est crucial (règle du leftmost prefix)

EXPLAIN ANALYZE : lire un plan d'exécution

EXPLAIN ANALYZE est votre meilleur ami pour l'optimisation. Il montre exactement comment PostgreSQL exécute une requête : quels index sont utilisés, combien de lignes sont scannées, et où se trouve le bottleneck.

-- Avant optimisation : Seq Scan (5.2 secondes)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;
-- Seq Scan on orders (cost=0.00..185432.00 rows=100 width=64)
--   Filter: (user_id = 42)
--   Rows Removed by Filter: 9999900
--   Execution Time: 5234.567 ms

-- Après CREATE INDEX idx_orders_user_id ON orders(user_id);
-- Index Scan using idx_orders_user_id (cost=0.43..12.45 rows=100 width=64)
--   Index Cond: (user_id = 42)
--   Execution Time: 0.234 ms   ← 22000x plus rapide

Partitionnement et Sharding

Quand une table dépasse les dizaines de millions de lignes, même les index ne suffisent plus. Le partitionnement divise logiquement une table en sous-tables, le sharding distribue physiquement les données sur plusieurs serveurs.

• Partitionnement par range : par date (logs mensuels), par ID (0-1M, 1M-2M)
• Partitionnement par hash : distribution uniforme, bon pour l'écriture parallèle
• Partitionnement par liste : par région, par catégorie
• Sharding : chaque shard est une base indépendante, complexité de routing et de joins cross-shard

Réplication : lecture scalable

La réplication crée des copies de la base pour distribuer la charge de lecture. Le pattern classique est un primary (écritures) avec N replicas (lectures). PostgreSQL supporte la réplication streaming synchrone et asynchrone.

• Réplication asynchrone : latence minimale en écriture, risque de lecture stale (read-after-write inconsistency)
• Réplication synchrone : cohérence forte mais latence d'écriture accrue (attente de l'ACK du replica)
• Read replicas : router les SELECT vers les replicas, les INSERT/UPDATE vers le primary
• Connection pooling (PgBouncer) : réduire le coût des connexions, indispensable à >100 connexions