🚀 Top 7 des astuces d’optimisation sous PostgreSQL

Vous travaillez sur PostgreSQL et vos requêtes tournent au ralenti ? L’optimisation SQL est une compétence clef pour tout analyste ou ingénieur data. Voici quelques stratégies essentielles pour booster la performance de vos requêtes. 🔥

🏎️ 1. Utilisez EXPLAIN ANALYZE avant d’optimiser

Avant toute modification, analysez votre requête avec :

EXPLAIN ANALYZE 
SELECT * FROM transactions WHERE montant > 1000;

📊 Cela vous donnera des insights sur l’exécution et où PostgreSQL passe du temps.

🚨 Détecter les cost trop élevés et les seq scan, et adaptez votre requête avec des ajouts d’index, du partitionnement ou des CTE (voir astuces suivantes).

🛠️ 2. Évitez les SELECT *

Plus évident mais pourtant très souvent oublié, sélectionnez avec soin les données requêtés. Sur de grandes volumétries le gain n’est pas anodin!

Mauvaise pratique :

SELECT * FROM clients;

Préférez :

SELECT nom, prenom FROM clients;

👉 Réduit de plus la charge réseau et accélère le traitement.

🏗️ 3. Indexez intelligemment vos tables

L’indexation est la clé de la rapidité. Pensez aux :

✅ Index B-tree pour les recherches classiques

✅ Index GIN pour les recherches textuelles ou JSON

✅ Index BRIN pour les très grosses tables

Exemple d’index classique :

CREATE INDEX idx_montant ON transactions(montant);

Attention n’en abusez pas non plus ! Trop d’index peut ralentir les INSERT/UPDATE. 📉

🔄 4. Optimisez les jointures (JOIN)

Les JOIN mal conçus sont des tueurs de performance. Préférez les JOIN explicites et assurez-vous que les colonnes jointes sont bien indexées. 🚀

Exemple :

SELECT c.nom, t.montant 
FROM clients c 
INNER JOIN transactions t ON c.id = t.client_id;

✅ Ici, assurez-vous que client_id est indexé.

✅ Assurez-vous également d’utiliser un inner join lorsque les données ajoutés ne sont pas optionnelles

🏋️‍♂️ 5. Utilisez les vues matérialisées pour des calculs lourds

Si une requête coûteuse est souvent exécutée, pré-calculer les résultats peut être salvateur :

CREATE MATERIALIZED VIEW ventes_par_mois AS
SELECT date_trunc('month', date_transaction) AS mois, SUM(montant) 
FROM transactions 
GROUP BY 1;

💡 Un simple REFRESH MATERIALIZED VIEW ventes_par_mois; mettra à jour les données.

🕵️ 6. Filtrez tôt avec des WHERE et des CTE

Appliquez vos conditions au plus tôt avec les CTE (surtout si cette requête est ré-utilisée plusieurs fois):

WITH transactions_filtrees AS (
    SELECT * FROM transactions WHERE montant > 1000
)
SELECT * FROM transactions_filtrees;

📌 Cela permet à PostgreSQL de travailler sur moins de données dès le départ. De plus, cela augmente grandement la lisibilité de votre code.

7. Optimiser les chargements avec COPY

Je vois trop souvent des analystes/dev foncer tête baissée et charger les données avec une simple boucle d’INSERT… dans le meilleur des cas avec une gestion des CHUNK mais cela n’est pas pertinent dans le cadre de chargements de masse.

✅ Sous Postgresql (mais cela est également valable pour d’autres SGBD, via d’autres outil d’import spécifiques) il faut favoriser l’utilisation de COPY. Les temps d’ingestion en seront grandement amélioré.

COPY ma_table FROM '/chemin/fichier.csv' WITH (FORMAT csv, HEADER true);

✅ Il est préférable également de désactiver temporairement les contraintes et les index le temps des COPY puis de reconstruire les index

✅ Pour optimiser encore, un ANALYZE de la ou les tables chargées permettra un plan d’exécution optimal.

Bonus: 8. Pensez à la parallélisation

PostgreSQL peut exécuter des requêtes en parallèle si vous l’activez :

SET max_parallel_workers_per_gather = 4;

Utile pour les analyses de gros volumes ! 📊

💡 PostgreSQL est puissant, mais nécessite une maîtrise dans la configuration de son environnement ! 💡

Quels sont vos trucs et astuces pour optimiser vos requêtes SQL ? Partagez-les en commentaire ! ⬇️