Optimiser PostgreSQL : index, EXPLAIN et requêtes efficaces

PostgreSQL est une base extraordinairement capable, mais ses performances dépendent largement de la façon dont on l'interroge. Avant d'ajouter de la puissance matérielle, il faut comprendre ce que fait réellement le moteur — et lui donner les moyens de bien faire son travail.

Mesurer avant d'optimiser

La première erreur est d'optimiser à l'aveugle. PostgreSQL fournit EXPLAIN ANALYZE, qui révèle le plan d'exécution d'une requête : quels index sont utilisés, combien de lignes sont parcourues, où le temps est passé. Lire ce plan, c'est passer de l'intuition à la mesure.

Les signaux à repérer

• Un Seq Scan (parcours séquentiel) sur une grosse table filtrée : souvent le signe d'un index manquant.
• Un écart important entre le nombre de lignes estimé et réel : des statistiques à rafraîchir.
• Des tris coûteux qui pourraient être évités par un index adapté.

Bien indexer

Un index accélère la lecture au prix d'un surcoût en écriture et en stockage. On indexe les colonnes utilisées dans les clauses WHERE, les jointures et les tris. Pour des conditions portant sur plusieurs colonnes, un index composite bien ordonné est souvent bien plus efficace que plusieurs index séparés.

Un index n'est utile que s'il correspond à la façon dont on interroge réellement les données. Indexer au hasard ne fait qu'alourdir les écritures.

PostgreSQL propose aussi des index spécialisés : partiels (n'indexer qu'un sous-ensemble de lignes), GIN pour la recherche plein texte ou les données JSON, qui répondent à des besoins précis.

Écrire des requêtes efficaces

Au-delà des index, la formulation compte :

• Ne sélectionner que les colonnes nécessaires plutôt qu'un SELECT * systématique.
• Paginer par curseur (clé) plutôt que par OFFSET sur de grandes profondeurs, où l'OFFSET devient coûteux.
• Préférer les opérations ensemblistes à des boucles applicatives qui multiplient les allers-retours.

Le rôle des statistiques

Le planificateur de PostgreSQL choisit son plan à partir de statistiques sur la distribution des données. Après un import massif ou de gros changements, ces statistiques peuvent être périmées et conduire à de mauvais choix. La maintenance automatique (autovacuum) s'en charge en temps normal, mais il faut savoir la déclencher manuellement quand c'est nécessaire.

La connexion : un coût souvent oublié

Ouvrir une connexion à PostgreSQL n'est pas gratuit. Une application qui en ouvre une par requête s'épuise vite. Un pool de connexions réutilise un nombre limité de connexions, ce qui stabilise les performances sous charge et protège la base contre la saturation.

Conclusion

Optimiser PostgreSQL, c'est d'abord comprendre ce qu'il fait grâce aux plans d'exécution, puis lui donner les bons index et des requêtes bien formées. Le meilleur réglage ne rattrapera jamais un schéma mal pensé ou une requête naïve. La démarche est toujours la même : mesurer, comprendre, corriger — dans cet ordre.