PostgreSQL реализует фреймирование RANGE, оценивая логические смещения значений от текущего столбца сортировки строки. Когда границы фрейма вовлекают тип интервал (например, INTERVAL '1 hour' PRECEDING), исполнителю не удается определить принадлежность к фрейму, используя простые физические подсчеты строк, поскольку количество строк, попадающих в это время, динамически меняется по всему набору данных. Для обеспечения корректности движок материализует всю отсортированную партию в рабочей таблице (либо в work_mem, либо сбрасывает на диск), просматривая все строки, чтобы определить, какие значения попадают в указанный диапазон относительно каждой текущей строки, что приводит к O(размер партии) сложности памяти.
Вы можете безопасно заменить фрейм ROWS, только если выражение ORDER BY представляет собой уникальный ключ для каждой строки в рамках партии. Если столбец сортировки не содержит дубликатов (или дополнен вторичным уникальным столбцом, таким как первичный ключ), физический смещение по строкам (ROWS) становится семантически идентичным логическому смещению по значению (RANGE). Эта гарантия уникальности обеспечивает, что фрейм содержит ровно те строки, которые нужны, без необходимости поиска соответствующих значений, что позволяет использовать модель потокового исполнения с фиксированным буфером и O(размеру фрейма) памяти.
Платформа высокочастотной торговли обрабатывала данные рыночных тиков с точностью до наносекунд, требуя скользящего среднего спредов между покупкой и продажей за предыдущие 50 миллисекунд. Исходный аналитический запрос использовал AVG(spread) OVER (PARTITION BY symbol ORDER BY nanos_ts RANGE BETWEEN INTERVAL '50 ms' PRECEDING AND CURRENT ROW). В условиях рыночной волатильности это привело к израсходованию work_mem, заставляя PostgreSQL сбрасывать рабочие таблицы на диск и вызывая задержку выполнения запросов от миллисекунд до десятков секунд, что было неприемлемо для алгоритмической торговли в реальном времени.
Инженерная команда сначала рассматривала вертикальное масштабирование серверов баз данных, чтобы обеспечить достаточное количество ОЗУ для полного размещения самых больших партий (символов с высоким объемом). Хотя это устраняло сброс на диск, стоимость была неподъемной; самые крупные символы содержали сотни миллионов тиков, требуя терабайт ОЗУ на соединение с базой данных, и решение не могло горизонтально масштабироваться до тысяч параллельных торговых алгоритмов.
Второе предложение заключалось в приближении 50-миллисекундного окна с использованием фиксированного смещения ROWS, рассчитанного исходя из средней плотности тиков (например, предполагая, что 1000 строк равны 50мс). Этот подход гарантировал бы постоянное использование памяти независимо от размера партии. Однако плотность тиков сильно варьировалась во время рыночных обрушений (тысячи тиков в миллисекунду) по сравнению с тихими периодами (минуты между тиками), что делало приближение количества строк произвольно неточным и потенциально нарушало финансовые регламенты, требующие точных расчетов временных окон для аудиторских следов.
Выбранное решение использовало тот факт, что nanos_ts в сочетании с tick_id образовывали комбинированный уникальный ключ. Команда переработала запрос на использование ORDER BY nanos_ts, tick_id и переключилась на ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW. Поскольку уникальность временных меток обеспечивала, что логическая 50-миллисекундная граница всегда соответствовала предсказуемому физическому смещению строк в нормальных рыночных условиях, расчет оставался точным, позволяя PostgreSQL потоково передавать строки через ограниченный буфер. Задержка выполнения запросов снизилась до субмиллисекундных значений, объем занимаемой памяти стабилизировался на уровне O(1), и система справлялась с партиями в миллиард строк без сброса на диск.
Почему настройка по умолчанию для фрейма (RANGE UNBOUNDED PRECEDING) дает разные текущие суммы по сравнению с ROWS UNBOUNDED PRECEDING, когда столбец ORDER BY содержит дубликаты?
Когда функция окна опускает явную настройку фрейма, PostgreSQL по умолчанию использует RANGE UNBOUNDED PRECEDING. Этот режим рассматривает все строки с одним и тем же значением ORDER BY как одну группу однотипных значений, включая их все в фрейм одновременно. Следовательно, если у пользователя есть три транзакции в один и тот же день, текущая сумма для всех трех строк будет одинаковой, показывая сумму всех трех плюс предыдущие дни. В отличие от этого, ROWS UNBOUNDED PRECEDING поочередно вычисляет сумму: первая транзакция дня включает только себя и предыдущие дни, вторая включает первые две и так далее. Кандидаты часто упускают это поведение по умолчанию, что приводит к отчетам, где внутридневные текущие суммы кажутся "застрявшими" на конечной сумме дня для всех строк этого дня, разрушая тайм-серийный анализ.
Как PostgreSQL обрабатывает значения NULL в столбце ORDER BY при оценке фреймов RANGE, и почему это может привести к тому, что строки будут беззвучно исключены из вычислений?
В SQL с трехзначной логикой сравнения с NULL дает UNKNOWN, а не равенство. Для фреймов RANGE PostgreSQL обычно исключает строки с NULL значениями сортировки из конечных диапазонов (например, BETWEEN 1 PRECEDING AND 1 FOLLOWING), поскольку арифметические сравнения с NULL терпят неудачу. Эти строки могут образовывать изолированные группы, которые не видны фреймам соседних строк. Если набор данных содержит NULL метки времени (представляющие устаревшие или ожидающие данные), скользящее среднее с использованием RANGE будет беззвучно исключать эти строки, тогда как фреймовка ROWS обрабатывала бы их на основе физического положения независимо от значения NULL, потенциально искажая аналитические суммы.
Когда столбец ORDER BY гарантированно уникален, почему явное фреймирование ROWS все же предпочтительно перед RANGE для больших наборов данных, и какую внутреннюю операцию это избегает?
Даже когда уникальность гарантирует семантическое равенство между ROWS и RANGE, простое наличие ключевого слова RANGE заставляет исполнять PostgreSQL готовиться к потенциальному сканированию групп однотипных значений. Это инициирует узел Materialize, буферизуя всю отсортированную партию в рабочей таблице (потребляя O(N) памяти) перед выводом строк. Явно объявляя ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, вы сигнализируете планировщику, что нужна только скользящая часть физических строк. Это позволяет узлу потока WindowAgg использовать фиксированный размер буфера, избегая затратного шага материализации и снижая потребление памяти до O(размер фрейма), что критично для обработки партий в миллиард строк без сброса на диск.