PostgreSQL implementa el enmarcado de RANGO evaluando desplazamientos de valor lógico desde la columna de ordenamiento de la fila actual. Cuando los límites del marco implican un tipo de intervalo (por ejemplo, INTERVAL '1 hora' ANTECEDENTE), el ejecutor no puede determinar la pertenencia al marco utilizando simples conteos físicos de filas porque el número de filas que caen dentro de esa ventana de tiempo varía dinámicamente a través del conjunto de datos. Para asegurar la corrección, el motor materializa toda la partición ordenada en una tabla de trabajo (ya sea en work_mem o desbordada en disco), explorando todas las filas para identificar qué valores caen dentro del rango especificado en relación con cada fila actual, resultando en una complejidad de memoria de O(tamaño de la partición).
Se puede sustituir de manera segura el enmarcado de FILAS solo cuando la expresión ORDER BY constituye una clave única para cada fila dentro de la partición. Si la columna de ordenamiento no contiene duplicados (o se extiende con una columna única secundaria, como una clave primaria), el desplazamiento de fila físico (FILAS) se vuelve semánticamente idéntico al desplazamiento de valor lógico (RANGO). Esta garantía de unicidad asegura que el marco contenga exactamente las filas pretendidas sin requerir que el motor busque pares con valores coincidentes, lo que permite un modelo de ejecución en streaming utilizando un búfer de anillo de tamaño fijo con O(tamaño del marco) de memoria.
Una plataforma de trading de alta frecuencia procesaba datos de mercado con precisión de nanosegundos, requiriendo un promedio móvil de los diferenciales entre oferta y demanda durante los 50 milisegundos anteriores. La consulta de análisis inicial utilizó AVG(spread) OVER (PARTITION BY symbol ORDER BY nanos_ts RANGO ENTRE INTERVALO '50 ms' ANTECEDENTE Y FILA ACTUAL). Durante la volatilidad del mercado, esto provocó el agotamiento de work_mem, obligando a PostgreSQL a desbordar tablas de trabajo en disco y provocando que la latencia de la consulta se degradara de milisegundos a decenas de segundos, inaceptable para el trading algorítmico en tiempo real.
El equipo de ingeniería consideró primero escalar verticalmente los servidores de base de datos para provisionar suficiente RAM para mantener las particiones más grandes (símbolos de alto volumen) completamente en memoria. Aunque esto eliminaría el desbordamiento de disco, el costo era prohibitivo; los símbolos más grandes contenían cientos de millones de ticks, requiriendo terabytes de RAM por conexión de base de datos, y la solución no se escalaba horizontalmente a miles de algoritmos de trading concurrentes.
Una segunda propuesta sugería aproximar la ventana de 50 milisegundos utilizando un desplazamiento FILAS fijo calculado a partir de la densidad promedio de ticks (por ejemplo, suponiendo que 1000 filas equivalían a 50 ms). Este enfoque garantizaría un uso constante de memoria independientemente del tamaño de la partición. Sin embargo, la densidad de ticks varía enormemente durante los colapsos del mercado (miles de ticks por milisegundo) frente a períodos tranquilos (minutos entre ticks), haciendo que la aproximación del conteo de filas fuera arbitrariamente inexacta y potencialmente violando regulaciones financieras que requieren cálculos de ventana de tiempo precisos para auditorías.
La solución elegida explotó el hecho de que nanos_ts combinado con tick_id formaba una clave única compuesta. El equipo reformuló la consulta para utilizar ORDER BY nanos_ts, tick_id y cambió a FILAS ENTRE 1000 ANTECEDENTE Y FILA ACTUAL. Dado que la unicidad de las marcas de tiempo aseguraba que el límite lógico de 50 milisegundos siempre alineara con un desplazamiento físico de filas predecible en condiciones normales del mercado, el cálculo se mantuvo preciso mientras permitía que PostgreSQL transmitiera filas a través de un búfer limitado. La latencia de la consulta se redujo a niveles de sub-milisegundos, la huella de memoria se estabilizó en O(1), y el sistema manejó particiones de mil millones de filas sin desbordamiento a disco.
¿Por qué la cláusula de marco predeterminada (RANGO SIN LÍMITES ANTECEDENTES) produce totales acumulativos diferentes que FILAS SIN LÍMITES ANTECEDENTES cuando la columna ORDER BY contiene valores duplicados?
Cuando una función de ventana omite una cláusula de marco explícita, PostgreSQL por defecto utiliza RANGO SIN LÍMITES ANTECEDENTES. Este modo trata a todas las filas que comparten el mismo valor de ORDER BY como un único grupo de pares, incluyéndolas todas en el marco simultáneamente. En consecuencia, si un usuario tiene tres transacciones en el mismo día, la suma acumulativa de las tres filas será idéntica, mostrando el total de las tres más los días anteriores. En contraste, FILAS SIN LÍMITES ANTECEDENTES calcula la suma progresivamente: la primera transacción del día incluye solo sí misma más los días anteriores, la segunda incluye las dos primeras, y así sucesivamente. Los candidatos suelen pasar por alto este comportamiento predeterminado, lo que lleva a informes donde los totales acumulativos intra-día parecen "atascados" en el total final del día para todas las filas de ese día, rompiendo el análisis de series temporales.
¿Cómo maneja PostgreSQL los valores NULL en la columna ORDER BY al evaluar marcos de RANGO, y por qué esto puede causar que las filas sean omitidas silenciosamente de los cálculos?
En la lógica de tres valores de SQL, las comparaciones con NULL dan como resultado DESCONOCIDO, no igualdad. Para el enmarcado de RANGO, PostgreSQL típicamente excluye filas con valores de ordenamiento NULL de ventanas de rango finitas (por ejemplo, ENTRE 1 ANTECEDENTE Y 1 SIGUIENTE) porque las comparaciones aritméticas contra NULL fallan. Estas filas pueden formar grupos de pares aislados que son invisibles para los marcos de filas adyacentes. Si un conjunto de datos contiene marcas de tiempo NULL (que representan datos heredados o pendientes), un promedio móvil utilizando RANGO descartará silenciosamente estas filas, mientras que el enmarcado de FILAS las incluiría en función de la posición física independientemente del valor NULL, lo que podría sesgar agregados analíticos.
Cuando la columna ORDER BY está garantizada como única, ¿por qué todavía se prefiere el enmarcado FILAS explícito en lugar de RANGO para grandes conjuntos de datos, y qué operación interna evita esto?
Incluso cuando la unicidad asegura la equivalencia semántica entre FILAS y RANGO, la mera presencia de la palabra clave RANGO obliga al ejecutor de PostgreSQL a prepararse para un posible escaneo de grupos de pares. Esto activa el nodo Materialize, almacenando toda la partición ordenada en una tabla de trabajo (consumiendo O(N) de memoria) antes de emitir filas. Al declarar explícitamente FILAS ENTRE SIN LÍMITES ANTECEDENTES Y FILA ACTUAL, se le indica al planificador que solo se necesita una ventana deslizante de filas físicas. Esto permite un nodo WindowAgg en streaming utilizando un búfer de anillo de tamaño fijo, evitando el costoso paso de materialización y reduciendo el uso de memoria a O(tamaño del marco), lo cual es crítico para procesar particiones de mil millones de filas sin desbordamiento a disco.