Les signaux faibles autour des fenêtres SQL

Les fenêtres SQL sont devenues un standard incontournable en analyse de données, pourtant beaucoup de développeurs les utilisent sans saisir les pièges subtils qui les entourent. Après des années à optimiser des requêtes en production, j'ai observé que les problèmes ne viennent jamais des cas d'usage évidentes, mais des détails qu'on ne voit qu'en mettant les mains dans le cambouis.

Cet article documente les signaux faibles – ces avertissements silencieux que votre base de données envoie avant de devenir instable. Pas de théorie abstraite, juste du retour d'expérience sur ce qui a réellement cassé en production.

Le piège du PARTITION BY sans ORDER BY

Les fenêtres SQL sans clause ORDER BY existent légalement. Elles calculent une agrégation sur l'ensemble de la partition sans notion de séquence. C'est utile pour certains cas, mais j'ai vu des développeurs l'utiliser par oubli, créant du non-déterminisme invisible.

Le signal faible : les tests passent, mais les résultats fluctuent en prod. L'ORDER BY doit TOUJOURS être explicite. Je recommande même une règle linting : refuser les fonctions de rang/numérotation sans ORDER BY.

Les frames implicites et l'illusion de la performance

Par défaut, les frames (ROWS BETWEEN ...) suivent des règles complexes selon que vous utilisez ORDER BY. Sans le spécifier, PostgreSQL/MySQL vont utiliser RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Cela semble raisonnable jusqu'au jour où vous traitez des données avec doublons.

J'ai passé 3 jours à déboguer une divergence entre notre environnement de test (PostgreSQL) et la prod (Oracle). Les résultats cumulatifs n'étaient pas identiques à cause des frames implicites. Le signal faible : si vos données ont des patterns répétitifs (timestamps batch, IDs groupés), spécifiez ALWAYS la frame explicitement avec ROWS, pas RANGE.

La malédiction des NULL dans les classements

Les NULL se comportent différemment selon les bases de données dans les fenêtres. PostgreSQL les place en fin par défaut, Oracle en début. Si votre applicatif assume un comportement et que la base change, c'est catastrophe silencieuse.

Le signal faible : les tests avec données synthétiques manquent souvent les NULL. Refusez tout code de fenêtre sans gestion explicite des NULL. Si les données réelles en contiennent, vos tests doivent aussi.

L'effet domino de la mémoire sur les agrégations fenêtrées

Les fenêtres demandent de garder des états en mémoire. Sur une grande partition, SUM() OVER (PARTITION BY x ORDER BY y ROWS BETWEEN 1000 PRECEDING AND CURRENT) devient soudain trivial. Mais SUM() OVER (PARTITION BY x) sans ROWS, c'est charger l'entière partition en RAM.

J'ai vu une requête qui passait en 2 secondes sur 1M de lignes échouer sur 100M : memory exhausted. Le même code, zéro modification. Le signal faible : profilez avec du vrai data volume. Les fenêtres sans frame explicite sont des mines anti-personnel latentes. Un EXPLAIN ANALYZE doit montrer si c'est unbounded ou non.

Conclusion : être paranoia pour la production

Les fenêtres SQL ne sont pas complexes techniquement, mais elles cachent des pièges de déterminisme, de performance et de compatibilité. Le code fonctionne en test et casse en prod – le pire scénario en système distribué. Mes règles en pratique : TOUJOURS un ORDER BY explicite, TOUJOURS une frame explicite en ROWS, TOUJOURS tester les NULL, TOUJOURS profiler sur du vrai volume. Ce qui semble de la paranoia au départ devient hygiène après la première panne 3h du matin. Les signaux faibles de la fenêtre SQL, c'est d'abord reconnaître qu'il n'y a pas de comportement par défaut accepté – chaque détail doit être intentionnel.