PostgreSQL implémente le cadre RANGE en évaluant des décalages de valeur logiques à partir de la colonne d'ordre de la ligne actuelle. Lorsque les limites du cadre impliquent un type interval (par exemple, INTERVAL '1 heure' PRECEDING), l'exécuteur ne peut pas déterminer l'appartenance au cadre en utilisant de simples comptages de lignes physiques, car le nombre de lignes tombant dans cette fenêtre temporelle varie dynamiquement à travers l'ensemble de données. Pour assurer l'exactitude, le moteur matérialise toute la partition triée dans une table de travail (soit dans work_mem soit déversée sur disque), scannant toutes les lignes pour identifier quelles valeurs tombent dans la plage spécifiée par rapport à chaque ligne actuelle, ce qui entraîne une complexité mémoire de O(taille de la partition).
Vous pouvez substituer en toute sécurité le cadre ROWS uniquement lorsque l'expression ORDER BY constitue une clé unique pour chaque ligne au sein de la partition. Si la colonne d'ordre ne contient pas de doublons (ou est complétée par une colonne unique secondaire telle qu'une clé primaire), le décalage de ligne physique (ROWS) devient sémantiquement identique au décalage de valeur logique (RANGE). Cette garantie d'unicité assure que le cadre contient exactement les lignes souhaitées sans nécessiter que le moteur scanne pour des pairs correspondant aux valeurs, permettant un modèle d'exécution en streaming utilisant un tampon circulaire de taille fixe avec O(taille du cadre) en mémoire.
Une plateforme de trading à haute fréquence traitait des données de marché en précision nanoseconde, nécessitant une moyenne mobile des écarts entre achat et vente sur les 50 millisecondes précédentes. La requête d'analyse initiale utilisait AVG(spread) OVER (PARTITION BY symbol ORDER BY nanos_ts RANGE BETWEEN INTERVAL '50 ms' PRECEDING AND CURRENT ROW). Pendant la volatilité du marché, cela provoquait une exhaustion de work_mem, forçant PostgreSQL à déverser des tables de travail sur disque et causant une latence de requête passant de millisecondes à des dizaines de secondes, inacceptable pour le trading algorithmique en temps réel.
L'équipe d'ingénierie a d'abord envisagé de dimensionner verticalement les serveurs de base de données pour provisionner suffisamment de RAM afin de garder les plus grandes partitions (symboles à fort volume) entièrement en mémoire. Bien que cela éliminerait le déversement sur disque, le coût était prohibitif ; les plus grands symboles contenaient des centaines de millions d'écarts, nécessitant des téraoctets de RAM par connexion de base de données, et la solution ne se prêtait pas à une montée en charge horizontale pour des milliers d'algorithmes de trading concurrents.
Une deuxième proposition a suggéré d'approximer la fenêtre de 50 millisecondes en utilisant un décalage ROWS fixe calculé à partir de la densité moyenne des écarts (par exemple, en supposant que 1000 lignes équivalait à 50 ms). Cette approche garantirait une utilisation mémoire constante, quelle que soit la taille de la partition. Cependant, la densité des écarts varie énormément durant les effondrements du marché (des milliers d'écarts par milliseconde) par rapport aux périodes calmes (des minutes entre les écarts), rendant l'approximation du compte de lignes arbitrairement inexacte et potentiellement en violation des réglementations financières nécessitant des calculs précis des fenêtres temporelles pour les pistes de vérification.
La solution choisie exploitait le fait que nanos_ts combiné avec tick_id formait une clé unique composite. L'équipe a reformulé la requête pour utiliser ORDER BY nanos_ts, tick_id et est passée à ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW. Puisque l'unicité des horodatages garantissait que la limite logique de 50 millisecondes s'alignait toujours avec un décalage de ligne physique prévisible dans des conditions de marché normales, le calcul restait précis tout en permettant à PostgreSQL de traiter les lignes à travers un tampon borné. La latence des requêtes est tombée à des niveaux inférieurs à une milliseconde, l'empreinte mémoire s'est stabilisée à O(1), et le système a géré des partitions de milliards de lignes sans déversement sur disque.
Pourquoi la clause de cadre par défaut (RANGE UNBOUNDED PRECEDING) produit-elle des totaux cumulés différents de ROWS UNBOUNDED PRECEDING lorsque la colonne ORDER BY contient des valeurs dupliquées ?
Lorsqu'une fonction de fenêtre omet une clause de cadre explicite, PostgreSQL utilise par défaut RANGE UNBOUNDED PRECEDING. Ce mode traite toutes les lignes partageant la même valeur ORDER BY comme un seul groupe de pairs, les incluant toutes dans le cadre simultanément. Par conséquent, si un utilisateur a trois transactions le même jour, la somme cumulée pour les trois lignes sera identique, montrant le total des trois plus les jours précédents. En revanche, ROWS UNBOUNDED PRECEDING calcule la somme de manière progressive : la première transaction de la journée n'inclut que celle-ci plus les jours précédents, la deuxième inclut les deux premières, et ainsi de suite. Les candidats manquent souvent ce comportement par défaut, conduisant à des rapports où les totaux cumulés intra-jour semblent « coincés » au total final de la journée pour toutes les lignes de cette journée, rompant l'analyse des séries temporelles.
Comment PostgreSQL gère-t-il les valeurs NULL dans la colonne ORDER BY lors de l'évaluation des cadres RANGE, et pourquoi cela peut-il entraîner l'omission silencieuse de lignes des calculs ?
Dans la logique à trois valeurs de SQL, les comparaisons avec NULL donnent UNKNOWN, pas d'égalité. Pour le cadre RANGE, PostgreSQL exclut généralement les lignes avec des valeurs d'ordre NULL des fenêtres de plage finies (par exemple, BETWEEN 1 PRECEDING AND 1 FOLLOWING) parce que les comparaisons arithmétiques contre NULL échouent. Ces lignes peuvent former des groupes de pairs isolés qui sont invisibles aux cadres de lignes adjacentes. Si un ensemble de données contient des horodatages NULL (représentant des données héritées ou en attente), une moyenne mobile utilisant RANGE supprimera silencieusement ces lignes, tandis que le cadre ROWS les inclurait en fonction de la position physique indépendamment de la valeur NULL, pouvant potentiellement fausser les agrégats analytiques.
Lorsque la colonne ORDER BY est garantie unique, pourquoi le cadre ROWS explicite reste-t-il préférable à RANGE pour de grands ensembles de données, et quelle opération interne cela évite-t-il ?
Même lorsque l'unicité garantit l'équivalence sémantique entre ROWS et RANGE, la simple présence du mot-clé RANGE oblige l'exécuteur de PostgreSQL à se préparer à un éventuel scan de groupe de pairs. Cela déclenche le nœud Materialize, tamponnant l'intégralité de la partition triée dans une table de travail (consommant O(N) mémoire) avant de diffuser les lignes. En déclarant explicitement ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, vous signalez au planificateur qu'un simple glissement de lignes physiques est requis. Cela permet un nœud WindowAgg de streaming utilisant un tampon circulaire de taille fixe, évitant l'étape coûteuse de matérialisation et réduisant l'utilisation de la mémoire à O(taille du cadre), ce qui est critique pour le traitement de partitions de milliards de lignes sans déversement sur disque.