PostgreSQL implementeert RANGE framing door logische waarde-offsets te evalueren vanaf de orderingskolom van de huidige rij. Wanneer de framegrenzen een interval-type (bijv. INTERVAL '1 uur' PRECEDING) omvatten, kan de executor geen frame-lidmaatschap bepalen met behulp van eenvoudige fysieke rijtelling omdat het aantal rijen dat binnen dat tijdsvenster valt dynamisch varieert over de dataset. Om de correctheid te waarborgen, materialiseert de engine de gehele gesorteerde partition in een werktafel (of in work_mem of naar schijf gespild), waarbij alle rijen worden gescand om te identificeren welke waarden binnen het opgegeven bereik vallen ten opzichte van elke huidige rij, wat resulteert in O(partition grootte) geheugencapaciteit.
Je kunt ROWS framing veilig vervangen wanneer de ORDER BY-expressie een unieke sleutel voor elke rij binnen de partition vormt. Als de orderingskolom geen duplicaten bevat (of uitgebreid is met een secundaire unieke kolom zoals een primaire sleutel), wordt de fysieke rij-offset (ROWS) semantisch identiek aan de logische waarde-offset (RANGE). Deze uniciteitsgarantie zorgt ervoor dat het frame precies de bedoelde rijen bevat zonder dat de engine moet zoeken naar waarde-matching gelijken, waardoor een streaming-uitvoermodel mogelijk wordt met een ringbuffer van vaste grootte en O(frame grootte) geheugen.
Een platform voor handelen met hoge frequentie verwerkte tick-gegevens van de markt met nanosecond-precisie, waarbij een voortschrijdend gemiddelde van bied-laskapitalen over de voorgaande 50 milliseconden nodig was. De initiële analytische query gebruikte AVG(spread) OVER (PARTITION BY symbol ORDER BY nanos_ts RANGE BETWEEN INTERVAL '50 ms' PRECEDING AND CURRENT ROW). Tijdens marktvolatiliteit leidde dit tot work_mem-uitputting, waardoor PostgreSQL gedwongen werd om werktafels naar de schijf te spillen en de query-latentie te verslechteren van milliseconden naar tientallen seconden, wat onaanvaardbaar was voor realtime algoritmisch handelen.
Het engineeringteam overwoog aanvankelijk de databaseservers verticaal op te schalen om voldoende RAM te voorzien om de grootste partitions (hoogvolume symbolen) geheel in het geheugen te houden. Hoewel dit het debiteren van schijven zou elimineren, waren de kosten prohibitief; de grootste symbolen bevatten honderden miljoenen ticks, wat terabytes RAM per databaseverbinding vereiste, en de oplossing schaalde niet horizontaal naar duizenden gelijktijdige handelsalgoritmes.
Een tweede voorstel stelde voor om het 50-millisecond venster te benaderen door een vaste ROWS offset te gebruiken die was berekend op basis van gemiddelde tick-dichtheid (bijv. aannemende dat 1000 rijen gelijk waren aan 50 ms). Deze aanpak zou constant geheugengebruik garanderen, ongeacht de partitiongrootte. Echter, tick-dichtheid varieert wild tijdens marktinstortingen (duizenden ticks per milliseconde) tegen stille perioden (minuten tussen ticks), waardoor de rijenaantalbenadering willekeurig onnauwkeurig kan zijn en mogelijk financiële voorschriften kan schenden die nauwkeurige tijdvensterberekeningen vereisen voor auditsporen.
De gekozen oplossing maakte gebruik van het feit dat nanos_ts in combinatie met tick_id een samengestelde unieke sleutel vormde. Het team herformuleerde de query om ORDER BY nanos_ts, tick_id te gebruiken en schakelde over naar ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW. Omdat de tijdstempel uniciteit ervoor zorgde dat de logische 50-millisecond grens altijd overeenkwam met een voorspelbare fysieke rij-offset onder normale marktomstandigheden, bleef de berekening nauwkeurig terwijl PostgreSQL rijen door een gebonden buffer stroomde. De query-latentie daalde tot sub-milliseconde-niveaus, het geheugengebruik stabiliseerde op O(1), en het systeem verwerkte partitions van miljard rijen zonder naar de schijf te spillen.
Waarom produceert de standaard frameclausule (RANGE UNBOUNDED PRECEDING) andere lopende totalen dan ROWS UNBOUNDED PRECEDING wanneer de ORDER BY-kolom duplicaten bevat?
Wanneer een windowfunctie een expliciete frameclausule weglaat, geeft PostgreSQL standaard RANGE UNBOUNDED PRECEDING aan. Deze modus beschouwt alle rijen die dezelfde ORDER BY-waarde delen als een enkele groep van gelijken, waardoor ze allemaal tegelijkertijd in het frame worden opgenomen. Gevolg hiervan is dat als een gebruiker drie transacties op dezelfde dag heeft, de lopende som voor alle drie rijen identiek zal zijn, waarbij het totaal van alle drie plus voorgaande dagen wordt getoond. Daarentegen berekent ROWS UNBOUNDED PRECEDING de som geleidelijk: de eerste transactie van de dag omvat alleen zichzelf plus voorgaande dagen, de tweede omvat de eerste twee, enzovoorts. Kandidaten missen deze standaardgedrag vaak, wat leidt tot rapporten waarbij intra-day lopende totalen "vast" lijken te zitten op het uiteindelijke totaal van de dag voor alle rijen van die dag, waardoor tijdreeksanalyses worden verstoord.
Hoe behandelt PostgreSQL NULL-waarden in de ORDER BY-kolom bij het evalueren van RANGE-frames, en waarom kan dit ertoe leiden dat rijen stilletjes worden weggelaten uit berekeningen?
In SQL-drievoudige logica leveren vergelijkingen met NULL ONBEKEND op, geen gelijkheid. Voor RANGE framing sluit PostgreSQL doorgaans rijen met NULL orderingswaarden uit van eindige bereikvensters (bijv. BETWEEN 1 PRECEDING AND 1 FOLLOWING) omdat de rekenkundige vergelijkingen tegen NULL falen. Deze rijen kunnen geïsoleerde groepen van gelijken vormen die onzichtbaar zijn voor de frames van aangrenzende rijen. Als een dataset NULL timestamps bevat (die legacy of uitstaande gegevens vertegenwoordigen), zal een voortschrijdend gemiddelde dat RANGE gebruikt deze rijen stilletjes laten vallen, terwijl ROWS framing ze zou opnemen op basis van fysieke positie, ongeacht de NULL-waarde, wat mogelijk analytische aggregaten vertekent.
Wanneer de ORDER BY-kolom gegarandeerd uniek is, waarom is expliciete ROWS framing dan nog steeds verkieslijk boven RANGE voor grote datasets, en welke interne operatie vermijdt dit?
Zelfs wanneer uniciteit semantische equivalentie tussen ROWS en RANGE waarborgt, dwingt de loutere aanwezigheid van het RANGE-sleutelwoord de PostgreSQL executor om zich voor te bereiden op mogelijke scanning van gelijken. Dit triggert de Materialize-node, die de gehele gesorteerde partition in een werktafel buffert (consumptie van O(N) geheugen) voordat rijen worden uitgegeven. Door expliciet ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW aan te geven, geef je signaal aan de planner dat alleen een glijdend venster van fysieke rijen nodig is. Dit maakt een streaming WindowAgg-node mogelijk met een ringbuffer van vaste grootte, waardoor de kostbare materialisatiestap wordt vermeden en het geheugengebruik wordt verminderd tot O(frame grootte), wat cruciaal is voor de verwerking van miljard-rij partitions zonder schijf spilling.