PostgreSQL wdraża ramy RANGE przez ocenę logicznych przesunięć wartości z kolumny porządkowej bieżącego wiersza. Gdy granice ramki obejmują typ interwałowy (np. INTERVAL '1 hour' PRECEDING), wykonawca nie jest w stanie określić przynależności ramki przy użyciu prostych liczników wierszy, ponieważ liczba wierszy mieszczących się w tym przedziale czasowym dynamicznie zmienia się w zbiorze danych. Aby zapewnić poprawność, silnik materializuje całą posortowaną partycję w tabeli roboczej (albo w work_mem, albo na dysku), skanując wszystkie wiersze, aby zidentyfikować, które wartości mieszczą się w określonym zakresie w odniesieniu do każdego bieżącego wiersza, co skutkuje złożonością pamięci O(rozmiar partycji).
Możesz bezpiecznie zastąpić ramy ROWS tylko wtedy, gdy wyrażenie ORDER BY stanowi unikalny klucz dla każdego wiersza w partycji. Jeśli kolumna porządkowa nie zawiera duplikatów (lub jest rozszerzona o dodatkową unikalną kolumnę, taką jak klucz główny), fizyczne przesunięcie wiersza (ROWS) staje się semantycznie identyczne z logicznym przesunięciem wartości (RANGE). Ta gwarancja unikalności zapewnia, że ramka zawiera dokładnie zamierzone wiersze bez potrzeby skanowania silnika w poszukiwaniu wartości-matching, co umożliwia model wykonawczy o zmiennym rozmiarze z użyciem stałego bufora o rozmiarze O(rozmiar ramki).
Platforma handlu o wysokiej częstotliwości przetwarzała dane o cenach rynkowych o precyzji nanosekund, wymagając średniej ruchomej dla spreadów bid-ask w przeciągu ostatnich 50 milisekund. Początkowe zapytanie analityczne wykorzystywało AVG(spread) OVER (PARTITION BY symbol ORDER BY nanos_ts RANGE BETWEEN INTERVAL '50 ms' PRECEDING AND CURRENT ROW). Podczas zmienności rynkowej spowodowało to wyczerpanie work_mem, zmuszając PostgreSQL do przelania tabel roboczych na dysk i powodując wydłużenie latencji zapytań z milisekund do dziesiątek sekund, co było nieakceptowalne dla handlu algorytmicznego w czasie rzeczywistym.
Zespół inżynieryjny najpierw rozważył pionowe skalowanie serwerów baz danych, aby zapewnić wystarczającą pamięć RAM na przechowanie największych partycji (symboli o dużym wolumenie) w całości w pamięci. Choć to wyeliminowałoby przelanie na dysk, koszt byłby prohibicyjny; największe symbole zawierały setki milionów ticków, co wymagało terabajtów RAM na każde połączenie z bazą danych, a rozwiązanie to nie skalowałoby się poziomo na tysiące równoległych algorytmów handlowych.
Druga propozycja zasugerowała przybliżenie 50-milisekundowego okna, używając stałego przesunięcia ROWS wyliczonego na podstawie średniej gęstości ticków (np. zakładając, że 1000 wierszy odpowiada 50 ms). To podejście gwarantowałoby stałe zużycie pamięci niezależnie od rozmiaru partycji. Niemniej jednak, gęstość ticków zmieniała się dramatycznie podczas kryzysów rynkowych (tysiące ticków na milisekundę) w porównaniu do cichych okresów (minuty między tickami), co sprawiało, że przybliżenie liczby wierszy mogło być arbitralnie niedokładne i potencjalnie naruszać przepisy finansowe, które wymagały precyzyjnych obliczeń okna czasowego dla śladów audytowych.
Wybrano rozwiązanie, które wykorzystało fakt, że nanos_ts w połączeniu z tick_id tworzyło złożony unikalny klucz. Zespół przekształcił zapytanie, aby użyć ORDER BY nanos_ts, tick_id i przeszedł do ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW. Ponieważ unikalność znacznika czasu zapewniła, że logiczna granica 50-milisekundowa zawsze odpowiadała przewidywalnemu fizycznemu przesunięciu wiersza w normalnych warunkach rynkowych, obliczenia pozostały dokładne, umożliwiając PostgreSQL przesyłanie wierszy przez ograniczony bufor. Latencja zapytań spadła do poziomów poniżej milisekundy, zużycie pamięci ustabilizowało się na poziomie O(1), a system przetwarzał partycje miliarda wierszy bez przelania na dysk.
Dlaczego domyślny klauzul ramowy (RANGE UNBOUNDED PRECEDING) produkuje różne sumy bieżące w porównaniu z ROWS UNBOUNDED PRECEDING, gdy kolumna ORDER BY zawiera wartości duplikowane?
Gdy funkcja okna pomija wyraźną klauzulę ramową, PostgreSQL domyślnie przechodzi do RANGE UNBOUNDED PRECEDING. Ten tryb traktuje wszystkie wiersze, które mają tę samą wartość ORDER BY, jako jedną grupę równorzędną, włączając je wszystkie do ramki jednocześnie. W konsekwencji, jeśli użytkownik ma trzy transakcje w tym samym dniu, suma bieżąca dla wszystkich trzech wierszy będzie identyczna, pokazując łączną sumę wszystkich trzech oraz dni poprzednich. W przeciwieństwie do tego, ROWS UNBOUNDED PRECEDING oblicza sumę progresywnie: pierwsza transakcja dnia obejmuje tylko siebie oraz dni poprzednie, druga obejmuje pierwsze dwa i tak dalej. Kandydaci często nie dostrzegają tego domyślnego zachowania, co prowadzi do raportów, w których bieżące sumy w ciągu dnia wydają się "utknięte" na ostatecznej sumie dnia dla wszystkich wierszy tego dnia, łamiąc analizy szeregów czasowych.
Jak PostgreSQL obsługuje wartości NULL w kolumnie ORDER BY, gdy ocenia ramki RANGE i dlaczego może to spowodować, że wiersze są cicho pomijane w obliczeniach?
W SQL logice trójwartościowej, porównania z NULL dają wynik UNKNOWN, a nie równość. Dla ram RANGE, PostgreSQL zazwyczaj wyklucza wiersze z wartościami porządkowymi NULL z finitywnych okien zakresowych (np. BETWEEN 1 PRECEDING AND 1 FOLLOWING), ponieważ arytmetyczne porównania z NULL nie powiodą się. Te wiersze mogą tworzyć izolowane grupy równorzędne, które są niewidoczne dla ramek sąsiednich wierszy. Jeśli zbiór danych zawiera NULL znaczniki czasu (reprezentujące dane historyczne lub oczekujące), średnia ruchoma używająca RANGE cicho odrzuci te wiersze, podczas gdy ramki ROWS uwzględniłyby je na podstawie pozycji fizycznej, niezależnie od wartości NULL, co potencjalnie zniekształcałoby analityczne agregaty.
Gdy kolumna ORDER BY jest gwarantowana jako unikalna, dlaczego jawne ramy ROWS są nadal preferowane nad RANGE dla dużych zbiorów danych i jakie wewnętrzne operacje to unika?
Nawet gdy unikalność zapewnia semantyczną równoważność między ROWS a RANGE, sama obecność słowa kluczowego RANGE zmusza wykonawcę PostgreSQL do przygotowania się na potencjalne skanowanie grup równorzędnych. To wyzwala węzeł Materialize, buforując całą posortowaną partycję w tabeli roboczej (zużywając O(N) pamięci) przed emitowaniem wierszy. Deklarując wyraźnie ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, sygnalizujesz planista, że potrzebna jest tylko przesuwna okno fizycznych wierszy. Umożliwia to użycie strumieniowego węzła WindowAgg z użyciem stałego bufora o rozmiarze, unikając kosztownego kroku materializacji i redukując zużycie pamięci do O(rozmiar ramki), co jest krytyczne dla przetwarzania partycji miliarda wierszy bez przelania na dysk.