SQL en conditions réelles

Un atelier PostgreSQL de praticien : chaque requête part d'un symptôme réel — rapport vide, jointure lente, index fantôme — et montre la requête exacte avec sa sortie psql commentée. Forte densité de pièges corrigés (NOT IN/NULL, non-sargable, OFFSET profond) et de requêtes-diagnostic sur les catalogues système que les DBA gardent vraiment sous la main.

19 snippets phares

• →FIRST_VALUE / LAST_VALUE : le piège du cadre par défautLAST_VALUE renvoie la ligne courante si on ne change pas le cadre par défaut (jusqu'à CURRENT ROW). Le correctif : étendre le cadre à UNBOUNDED FOLLOWING.
• →Détecter les cycles dans un graphe : clause CYCLEUne boucle dans les données (A parent de B, B parent de A) fait tourner une CTE récursive à l'infini. La clause CYCLE (PostgreSQL 14+) arrête la récursion et marque les lignes fautives.
• →Fusionner des intervalles de dates qui se chevauchentRéduire des périodes chevauchantes en plages continues : on détecte un nouveau groupe quand le début dépasse le max des fins précédentes, puis on agrège par groupe.
• →Indexer du jsonb : GIN + opérateur de containment @>Un index GIN jsonb_path_ops accélère drastiquement les requêtes de containment @> sur de gros volumes de documents. Vérifier l'usage avec EXPLAIN.
• →Détecter les clés étrangères sans indexPostgreSQL n'indexe pas automatiquement les FK : chaque DELETE sur le parent déclenche alors un seq scan de l'enfant. Cette requête catalogue liste les FK dont la première colonne n'est indexée nulle part.
• →Anti-pattern : NOT IN face aux NULLUn seul NULL dans la sous-requête et NOT IN renvoie zéro ligne, silencieusement (logique ternaire SQL). NOT EXISTS est NULL-safe et généralement mieux planifié.
• →Anti-pattern : fonction sur colonne indexée (non sargable)DATE_TRUNC ou EXTRACT appliqué à la colonne interdit l'usage de l'index. Réécrire le filtre en plage de valeurs le rend sargable, et l'index B-tree redevient utilisable.
• →Anti-pattern : OFFSET profond — passer au keysetOFFSET 20000 lit et jette 20 000 lignes à chaque page. La pagination keyset (seek) repart du dernier élément vu : coût constant quelle que soit la profondeur.
• →DISTINCT ON : dernière ligne par clé en une passeSpécifique PostgreSQL : DISTINCT ON garde la première ligne de chaque groupe selon l'ORDER BY — souvent plus rapide que ROW_NUMBER avec le bon index.
• →File de jobs concurrente : FOR UPDATE SKIP LOCKEDChaque worker réserve un lot de jobs en sautant les lignes déjà verrouillées par les autres : une file de travail fiable en SQL pur, sans broker externe.
• →Funnel de conversion : taux par étape en une requêteCompter les utilisateurs distincts à chaque étape avec FILTER, puis dérouler le funnel en lignes via LATERAL VALUES pour calculer le taux global et le taux étape par étape.
• →Séquences proches du dépassement (overflow int4)Mesure du pourcentage de consommation de chaque séquence par rapport à son max_value : une séquence int4 qui déborde bloque toutes les insertions du jour au lendemain.
• →Contraintes NOT VALID et triggers désactivés : les garde-fous endormisInventaire des contraintes CHECK/FK créées NOT VALID (jamais re-validées) et des triggers désactivés : autant de règles métier que la base n'applique plus.
• →Waterfall MRR : new, expansion, contraction, churn en un rapportDécomposition mensuelle du revenu récurrent en quatre mouvements via un FULL JOIN du MRR de chaque client avec son mois précédent — le rapport standard de tout SaaS, en pur SQL.
• →Scoring RFM complet : récence, fréquence, montant en quintilesPipeline en deux CTE : agrégation des trois axes RFM par client, scoring en quintiles avec NTILE, puis segmentation lisible (Champion, VIP à réactiver, Perdu) prête pour le CRM.
• →Détection de fraude : transactions multi-pays rapprochées sur une même carteFenêtre LAG par carte pour comparer chaque transaction à la précédente : un changement de pays en moins d'une heure est physiquement impossible — signal classique de carte compromise.
• →Détecter le pattern N+1 dans pg_stat_statementsLes requêtes au temps unitaire dérisoire mais appelées des centaines de milliers de fois trahissent un ORM en boucle N+1 : individuellement invisibles, collectivement dominantes.
• →Corrélation physique des colonnes : candidats BRIN et CLUSTERLecture de pg_stats.correlation pour les colonnes indexées : proche de 1, un BRIN minuscule suffit ; proche de 0 sur une colonne de tri fréquent, un CLUSTER réordonne la table et divise les I/O.
• →Bloat réel des index B-tree avec pgstatindexMesure exacte (pas estimée) de la densité des pages feuilles de chaque index : sous 50 % de densité, l'index a doublé de volume pour rien et mérite un REINDEX CONCURRENTLY.