PostgreSQL은 현재 행의 정렬 열에서 논리적 값 오프셋을 평가하여 RANGE 프레이밍을 구현합니다. 프레임 경계가 interval 유형(예: INTERVAL '1 hour' PRECEDING)을 포함하면, 실행자는 단순한 물리적 행 수를 사용하여 프레임 멤버십을 결정할 수 없습니다. 이는 시간 창 내에 포함되는 행 수가 데이터 세트 전반에 걸쳐 동적으로 변하기 때문입니다. 올바른 결과를 보장하기 위해, 엔진은 전체 정렬된 파티션을 작업 테이블에 물리화하며(작업 메모리 또는 디스크에 흘림), 모든 행을 스캔하여 각 현재 행에 대해 지정된 범위 내에 있는 값을 식별합니다. 이로 인해 O(파티션 크기) 메모리 복잡성이 발생합니다.
ORDER BY 표현식이 파티션 내의 모든 행에 대해 고유 키를 구성할 때에만 ROWS 프레이밍으로 안전하게 대체할 수 있습니다. 정렬 열에 중복이 없거나(또는 기본 키와 같은 이차 고유 열로 확장된 경우) 물리적 행 오프셋(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로 하여금 작업 테이블을 디스크로 흘리게 하여 쿼리 대기 시간이 밀리초 단위에서 수십 초로 악화되었으며, 이는 실시간 알고리즘 거래에는 용납될 수 없는 일이었습니다.
엔지니어링 팀은 먼저 데이터베이스 서버를 수직적으로 확장하여 최대 파티션(대량 거래 심볼)을 전체 메모리에 보관할 수 있는 충분한 RAM을 제공하는 것을 고려했습니다. 이렇게 하면 디스크 흘림이 제거되지만 비용이 너무 높았습니다. 가장 큰 심볼은 수억 개의 틱을 포함하고 있어 각 데이터베이스 연결당 테라바이트의 RAM이 필요했으며, 이 해결책은 수천 개의 동시 거래 알고리즘에 수평적으로 확장되지 않았습니다.
두 번째 제안은 평균 틱 밀도에서 계산된 고정 ROWS 오프셋을 사용하여 50밀리초 창을 근사화하는 것이었습니다(예: 1000행이 50ms에 해당한다고 가정). 이 접근 방식은 파티션 크기와 관계없이 일정한 메모리 사용을 보장할 것입니다. 그러나 틱 밀도는 시장 붕괴 중(밀리초마다 수천 개의 틱)과 조용한 기간(틱 사이에 몇 분이 걸림) 동안 극심하게 변동하여 행 수 근사화가 임의로 부정확해지고, 감사 추적을 위한 정확한 시간 창 계산을 요구하는 금융 규정을 위반할 가능성이 있었습니다.
선택된 해결책은 nanos_ts와 tick_id가 조합되어 복합 고유 키를 형성한다는 사실을 활용했습니다. 팀은 쿼리를 ORDER BY nanos_ts, tick_id로 변경하고 ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW로 전환했습니다. 타임스탬프의 고유성 덕분에 논리적 50밀리초 경계가 정상 시장 조건 하에서 예측 가능한 물리적 행 오프셋과 항상 일치하게 보장되어 계산의 정확성은 유지되면서 PostgreSQL이 제한된 버퍼를 통해 행을 스트리밍할 수 있게 되었습니다. 쿼리 대기 시간은 서브 밀리초 수준으로 감소했으며, 메모리 사용량은 **O(1)**로 안정되었고 시스템은 디스크로 흘림 없이 수십억 개의 행 파티션을 처리했습니다.
왜 기본 프레임 절약(UNBOUNDED PRECEDING)이 ORDER BY 열에 중복 값이 포함될 때 ROWS UNBOUNDED PRECEDING와 다른 실행 총계를 생성합니까?
윈도우 함수가 명시적인 프레임 절이 없는 경우, PostgreSQL은 기본적으로 RANGE UNBOUNDED PRECEDING을 사용합니다. 이 모드는 동일한 ORDER BY 값을 공유하는 모든 행을 단일 동료 그룹으로 취급하여 동시에 프레임에 포함시킵니다. 따라서 사용자가 같은 날에 세 개의 거래를 가진 경우, 세 개의 모든 행의 실행 합계는 동일하게 표시되며, 이전 날짜까지의 총 합계가 나타납니다. 반대로 ROWS UNBOUNDED PRECEDING은 합계를 점진적으로 계산합니다: 날의 첫 번째 거래는 자신과 이전 날짜만 포함하고, 두 번째 거래는 처음 두 개를 포함하는 식으로 진행됩니다. 후보자들은 종종 이러한 기본 동작을 간과하여, 당일의 모든 행에 대해 당일의 최종 총계에서 "정지"된 것처럼 보이는 내부 일별 실행 총계를 보고합니다. 이는 시계열 분석을 깨뜨립니다.
PostgreSQL은 RANGE 프레임을 평가할 때 ORDER BY 열의 NULL 값을 어떻게 처리하며, 이는 계산에서 행이 조용히 생략될 수 있는 이유는 무엇입니까?
SQL의 삼값 논리에서, NULL과의 비교는 UNKNOWN을 산출하며, 동일성이 아닙니다. RANGE 프레이밍의 경우, PostgreSQL은 일반적으로 유한 범위 창에서 NULL 정렬 값을 가진 행을 제외합니다(예: BETWEEN 1 PRECEDING AND 1 FOLLOWING)는 산술 비교가 NULL과 일치하지 않기 때문입니다. 이러한 행은 결과적으로 인접한 행의 프레임에서 보이지 않는 고립된 동료 그룹을 형성할 수 있습니다. 데이터 세트에 NULL 타임스탬프가 포함되어 있으면(레거시 데이터나 대기 중인 데이터를 나타냄), RANGE를 사용하는 이동 평균은 이러한 행을 조용히 삭제하지만, ROWS 프레이밍은 물리적 위치에 따라 무관하게 포함하여 분석 집계를 왜곡할 수 있습니다.
ORDER BY 열이 고유하다고 보장될 때, 왜 대규모 데이터 세트에 대해 명시적인 ROWS 프레이밍이 여전히 RANGE보다 바람직한가요? 그리고 내부적으로 어떤 작업을 피합니까?
고유성이 ROWS와 RANGE 간의 의미적 동등성을 보장하더라도, 단순히 RANGE 키워드가 존재하기만 하면 PostgreSQL 실행기가 잠재적인 동료 그룹 스캐닝을 위해 준비하게 됩니다. 이는 Materialize 노드를 유발하여 전체 정렬된 파티션을 작업 테이블로 버퍼링하게 하여 O(N) 메모리를 소비한 후 행을 방출합니다. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 명시적으로 선언함으로써, 계획자에게 물리적 행의 슬라이딩 윈도우만 필요하다는 신호를 보냅니다. 이는 비싼 물리화 단계를 피하고 메모리 사용량을 **O(프레임 크기)**로 줄여 수십억 개의 행 파티션을 처리할 수 있도록 하는 데 있어 매우 중요합니다.