PostgreSQL implementiert RANGE-Rahmung, indem logische Wertverschiebungen von der Sortierspalte der aktuellen Zeile bewertet werden. Wenn die Rahmengrenzen einen Intervall-Typ betreffen (z. B. INTERVAL '1 Stunde' VORHER), kann der Executor die Zugehörigkeit zum Rahmen nicht anhand einfacher physischer Zeilenanzahlen bestimmen, da die Anzahl der Zeilen, die innerhalb dieses Zeitraums liegen, dynamisch über den Datensatz hinweg variiert. Um die Korrektheit zu gewährleisten, materialisiert die Engine die gesamte sortierte Partition in einer Arbeitstabelle (entweder im work_mem oder auf der Festplatte), indem sie alle Zeilen scannt, um zu bestimmen, welche Werte innerhalb des angegebenen Bereichs im Verhältnis zu jeder aktuellen Zeile liegen, was zu einer O(Partition-Größe)-Speicherkomplexität führt.
Sie können ROWS-Rahmung sicher nur dann substituieren, wenn der ORDER BY-Ausdruck einen eindeutigen Schlüssel für jede Zeile innerhalb der Partition darstellt. Wenn die Sortierspalte keine Duplikate enthält (oder mit einer sekundären eindeutigen Spalte wie einem Primärschlüssel erweitert wird), wird die physische Zeilenverschiebung (ROWS) semantisch identisch zur logischen Wertverschiebung (RANGE). Diese Eindeutigkeitsgarantie sorgt dafür, dass der Rahmen genau die beabsichtigten Zeilen enthält, ohne dass die Engine nach wertematchenden Peers scannen muss, wodurch ein Streaming-Ausführungsmodell mit einem festen Ringpuffer unter Verwendung von O(Rahmengröße)-Speicher ermöglicht wird.
Eine Hochfrequenz-Handelsplattform bearbeitete Marktickdaten mit Nanosekundenpräzision und benötigte einen gleitenden Durchschnitt der Geldkurs-Spreads über die vorhergehenden 50 Millisekunden. Die ursprüngliche Analyseabfrage nutzte AVG(spread) OVER (PARTITION BY symbol ORDER BY nanos_ts RANGE BETWEEN INTERVAL '50 ms' PRECEDING AND CURRENT ROW). Während der Marktvolatilität führte dies zu einem work_mem-Erschöpfungszustand, was dazu führte, dass PostgreSQL Arbeitstabellen auf die Festplatte auslagern musste und die Abfrageverzögerung von Millisekunden auf Zehntelsekunden anstieg, was für den tatsächlichen algorithmischen Handel inakzeptabel war.
Das Engineering-Team erwog zunächst, die Datenbankserver vertikal zu skalieren, um genügend RAM bereitzustellen, um die größten Partitionen (hochvolumige Symbole) vollständig im Speicher zu halten. Obwohl dies das Spilling auf Festplatte beseitigen würde, waren die Kosten prohibitiv; die größten Symbole enthielten Hunderte Millionen Ticks, was Terabytes RAM pro Datenbankverbindung erforderte, und die Lösung ließ sich nicht horizontal auf Tausende gleichzeitiger Handelsalgorithmen skalieren.
Ein zweiter Vorschlag schlug vor, das 50-Millisekunden-Fenster zu approximieren, indem ein fester ROWS-Offset basierend auf der durchschnittlichen Tick-Dichte verwendet wurde (z. B. unter der Annahme, dass 1000 Zeilen 50 ms entsprechen). Dieser Ansatz würde eine konstante Speichernutzung unabhängig von der Partitiongröße gewährleisten. Die Tick-Dichte variierte jedoch während Marktcrashs (tausende von Ticks pro Millisekunde) im Vergleich zu ruhigen Perioden (Minuten zwischen Ticks), was die Zeilenanzahlsschätzung willkürlich ungenau machte und potenziell finanzielle Vorschriften verletzte, die präzise Zeitfensterberechnungen für Prüfpfade erforderten.
Die gewählte Lösung nutzte die Tatsache, dass nanos_ts zusammen mit tick_id einen kompositen eindeutigen Schlüssel bildete. Das Team reformulierte die Abfrage, um ORDER BY nanos_ts, tick_id zu verwenden und wechselte zu ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW. Da die Eindeutigkeit des Zeitstempels sicherstellte, dass die logische 50-Millisekunden-Grenze immer mit einem vorhersehbaren physischen Zeilenoffset unter normalen Marktbedingungen übereinstimmte, blieb die Berechnung genau, während PostgreSQL Zeilen durch einen begrenzten Puffer streamen konnte. Die Abfrageverzögerung fiel auf sub-Millisekunden-Niveaus, der Speicherbedarf stabilisierte sich auf O(1), und das System bewältigte Partitionen mit Milliarden von Zeilen, ohne auf Festplatte zu spillen.
Warum erzeugt die Standardrahmenklausel (RANGE UNBOUNDED PRECEDING) unterschiedliche laufende Summen als ROWS UNBOUNDED PRECEDING, wenn die ORDER BY-Spalte Duplikate enthält?
Wenn eine Fensterfunktion eine explizite Rahmenklausel weglässt, verwendet PostgreSQL standardmäßig RANGE UNBOUNDED PRECEDING. Dieser Modus behandelt alle Zeilen mit dem gleichen ORDER BY-Wert als eine einzige Peer-Gruppe und umfasst sie gleichzeitig im Rahmen. Folglich wird, wenn ein Benutzer drei Transaktionen am selben Tag hat, die laufende Summe für alle drei Zeilen identisch sein, was die Summe aller drei plus der vorhergehenden Tage zeigt. Im Gegensatz dazu berechnet ROWS UNBOUNDED PRECEDING die Summe schrittweise: Die erste Transaktion des Tages umfasst nur sich selbst plus die vorhergehenden Tage, die zweite umfasst die ersten beiden und so weiter. Kandidaten übersehen oft dieses Standardverhalten, was zu Berichten führt, in denen die tagesinternen laufenden Summen "festgefahren" erscheinen, was die Zeitreihenanalyse beeinträchtigt.
Wie geht PostgreSQL mit NULL-Werten in der ORDER BY-Spalte um, wenn es RANGE-Rahmen bewertet, und warum kann dies dazu führen, dass Zeilen stillschweigend aus Berechnungen ausgeschlossen werden?
In der SQL-logik mit drei Wahrheitswerten führen Vergleiche mit NULL zu UNKNOWN, nicht zur Gleichheit. Bei RANGE-Rahmung schließt PostgreSQL typischerweise Zeilen mit NULL-Werten in der Sortierung aus endlichen Bereichsfenstern (z. B. BETWEEN 1 PRECEDING AND 1 FOLLOWING), da die arithmetischen Vergleiche gegen NULL fehlschlagen. Diese Zeilen können isolierte Peer-Gruppen bilden, die für die angrenzenden Rahmen unsichtbar sind. Wenn ein Datensatz NULL-Zeitstempel enthält (die veraltete oder ausstehende Daten darstellen), wird ein gleitender Durchschnitt mit RANGE diese Zeilen stillschweigend ausschließen, während eine ROWS-Rahmung sie basierend auf der physischen Position unabhängig vom NULL-Wert einschließen würde, was analytische Aggregationen potenziell verzerren könnte.
Warum ist explizite ROWS-Rahmung auch bei garantierter Eindeutigkeit der ORDER BY-Spalte für große Datensätze vorzuziehen, und welche interne Operation wird dadurch vermieden?
Selbst wenn die Eindeutigkeit die semantische Äquivalenz zwischen ROWS und RANGE sicherstellt, zwingt die bloße Anwesenheit des RANGE-Schlüsselworts den PostgreSQL-Executor, sich auf potenzielle Gruppenabfragen vorzubereiten. Dies löst den Materialize-Knoten aus, der die gesamte sortierte Partition in eine Arbeitstabelle puffert (und damit O(N) Speicher verbraucht), bevor er Zeilen ausgibt. Durch die explizite Angabe von ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW signalisieren Sie dem Planner, dass nur ein gleitendes Fenster physischer Zeilen benötigt wird. Dies ermöglicht einen Streaming-WindowAgg-Knoten mit einem festen Ringpuffer, vermeidet den kostspieligen Materialisierungs-Schritt und reduziert den Speicherbedarf auf O(Rahmengröße), was entscheidend ist, um Partitionen mit Milliarden von Zeilen ohne Spilling auf Festplatte zu verarbeiten.