PostgreSQL implementa il framing RANGE valutando gli offset di valore logico dalla colonna di ordinamento della riga corrente. Quando i limiti del frame coinvolgono un tipo di intervallo (ad es., INTERVAL '1 hour' PRECEDING), l'esecutore non può determinare l'appartenenza al frame utilizzando semplici conteggi fisici delle righe poiché il numero di righe comprese in quella finestra temporale varia dinamicamente nel dataset. Per garantire correttezza, il motore materializza l'intera partizione ordinata in una tabella di lavoro (sia in work_mem che spillata su disco), esaminando tutte le righe per identificare quali valori rientrano nell'intervallo specificato rispetto a ciascuna riga corrente, con una complessità di memoria di O(dim. partizione).
Si può sostituire in sicurezza il framing ROWS solo quando l'espressione ORDER BY costituisce una chiave unica per ogni riga all'interno della partizione. Se la colonna di ordinamento non contiene duplicati (o è estesa con una seconda colonna unica come una chiave primaria), l'offset fisico della riga (ROWS) diventa semanticamente identico all'offset di valore logico (RANGE). Questa garanzia di unicità assicura che il frame contenga esattamente le righe previste senza richiedere al motore di cercare corrispondenze di valori, consentendo un modello di esecuzione in streaming utilizzando un buffer circolare di dimensioni fisse con O(dim. frame) di memoria.
Una piattaforma di trading ad alta frequenza elaborava dati di mercato con precisione al nanosecondo, richiedendo una media mobile degli spread bid-ask negli ultimi 50 millisecondi. La query analitica iniziale utilizzava AVG(spread) OVER (PARTITION BY symbol ORDER BY nanos_ts RANGE BETWEEN INTERVAL '50 ms' PRECEDING AND CURRENT ROW). Durante la volatilità del mercato, questo ha attivato l'esaurimento di work_mem, costringendo PostgreSQL a spillare le tabelle di lavoro su disco e causando un degrado della latenza della query da millisecondi a decine di secondi, inaccettabile per il trading algoritmico in tempo reale.
Il team di ingegneria ha inizialmente considerato di scalare verticalmente i server di database per fornire RAM sufficiente a mantenere le partizioni più grandi (simboli ad alto volume) interamente in memoria. Anche se questo avrebbe eliminato lo spilling su disco, il costo era proibitivo; i simboli più grandi contenevano centinaia di milioni di tick, richiedendo terabyte di RAM per ogni connessione al database, e la soluzione non scalava orizzontalmente a migliaia di algoritmi di trading concorrenti.
Una seconda proposta ha suggerito di approssimare la finestra di 50 millisecondi utilizzando un offset ROWS fisso calcolato dalla densità media dei tick (ad es., assumendo che 1000 righe equivalgano a 50 ms). Questo approccio garantirebbe un utilizzo costante della memoria indipendentemente dalla dimensione della partizione. Tuttavia, la densità dei tick varia selvaggiamente durante i crolli di mercato (migliaia di tick al millisecondo) rispetto ai periodi tranquilli (minuti tra i tick), rendendo l'approssimazione del conteggio delle righe arbitrariamente inaccurata e potenzialmente violando le normative finanziarie che richiedono calcoli precisi delle finestre temporali per le tracce di audit.
La soluzione scelta ha sfruttato il fatto che nanos_ts combinato con tick_id formava una chiave unica composita. Il team ha riformulato la query per utilizzare ORDER BY nanos_ts, tick_id e ha cambiato a ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW. Poiché l'unicità del timestamp garantiva che il confine logico di 50 millisecondi allineasse sempre con un offset fisico delle righe prevedibile nelle normali condizioni di mercato, il calcolo è rimasto accurato consentendo a PostgreSQL di elaborare le righe attraverso un buffer limitato. La latenza della query è scesa a livelli sub-millisecondo, l'impronta di memoria si è stabilizzata a O(1) e il sistema ha gestito partizioni di miliardi di righe senza spillare su disco.
Perché la clausola di frame di default (RANGE UNBOUNDED PRECEDING) produce totali correnti diversi da ROWS UNBOUNDED PRECEDING quando la colonna ORDER BY contiene valori duplicati?
Quando una funzione finestra omette una clausola di frame esplicita, PostgreSQL per default usa RANGE UNBOUNDED PRECEDING. Questo modo tratta tutte le righe che condividono lo stesso valore ORDER BY come un unico gruppo di pari, includendole tutte nel frame contemporaneamente. Di conseguenza, se un utente ha tre transazioni nello stesso giorno, la somma corrente per tutte e tre le righe sarà identica, mostrando il totale di tutte e tre più i giorni precedenti. Al contrario, ROWS UNBOUNDED PRECEDING calcola la somma progressivamente: la prima transazione del giorno include solo se stessa più i giorni precedenti, la seconda include le prime due, e così via. I candidati spesso trascurano questo comportamento predefinito, portando a rapporti in cui i totali correnti intra-giornalieri appaiono "bloccati" all'ultimo totale del giorno per tutte le righe di quel giorno, interrompendo le analisi delle serie temporali.
Come gestisce PostgreSQL i valori NULL nella colonna ORDER BY quando valuta i frame RANGE, e perché questo può causare l'omissione silenziosa di righe dai calcoli?
In SQL, la logica a tre valori, i confronti con NULL producono UNKNOWN, non uguaglianza. Per il framing RANGE, PostgreSQL esclude tipicamente le righe con valori di ordinamento NULL da finestre di rango finite (ad es., BETWEEN 1 PRECEDING AND 1 FOLLOWING) perché i confronti aritmetici contro NULL falliscono. Queste righe possono formare gruppi di pari isolati che sono invisibili ai frame delle righe adiacenti. Se un dataset contiene timestamp NULL (che rappresentano dati legacy o in attesa), una media mobile utilizzando RANGE ometterà silenziosamente queste righe, mentre il framing ROWS le includerebbe in base alla posizione fisica indipendentemente dal valore NULL, potenzialmente distorcendo gli aggregati analitici.
Quando la colonna ORDER BY è garantita unica, perché il framing esplicito delle ROWS è ancora preferibile a RANGE per dataset di grandi dimensioni, e quale operazione interna evita questo?
Anche quando l'unicità garantisce l'equivalenza semantica tra ROWS e RANGE, la semplice presenza della parola chiave RANGE costringe l'esecutore di PostgreSQL a prepararsi per una potenziale scansione di gruppo pari. Ciò attiva il nodo Materialize, memorizzando l'intera partizione ordinata in una tabella di lavoro (consumando O(N) di memoria) prima di emettere le righe. Dichiarando esplicitamente ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, si segnala al pianificatore che è necessaria solo una finestra scorrevole di righe fisiche. Questo consente a un nodo WindowAgg in streaming di utilizzare un buffer circolare di dimensioni fisse, evitando il costoso passaggio di materializzazione e riducendo l'uso di memoria a O(dim. frame), il che è critico per elaborare partizioni di miliardi di righe senza spillare su disco.