PostgreSQL은 10버전에서 번거로운 상속 기반 파티션 모델을 대체하기 위해 선언적 파티셔닝을 도입했습니다. 쿼리 플래너는 쿼리 조건을 파티션 경계와 비교하여 계획 단계에서 정적 파티션 프루닝을 수행하지만, 어떤 파티션을 제거할 수 있는지 결정하기 위해서는 표현식이 계획 시에 상수로 평가할 수 있어야 합니다.
핵심 아키텍처적 제약은 STABLE 함수, 예를 들어 now() 또는 current_timestamp와 같은 함수는 계획 중에 평가되지 않기 때문에, 그 결과가 계획과 실행 사이 또는 쿼리 실행 중에 다를 수 있습니다. 결과적으로, 플래너는 이러한 함수를 포함하는 조건을 블랙 박스로 취급하여 특정 파티션에 일치하는 행이 포함되어 있지 않다는 것을 증명할 수 없어 모든 파티션을 스캔해야 합니다.
해결책은 조건을 IMMUTABLE 함수나 리터럴 상수를 사용하도록 재작성하거나, PostgreSQL 11 이상에서 사용할 수 있는 런타임 파티션 프루닝에 의존하는 것입니다. enable_partition_pruning을 on으로 설정하면, 실행자가 실행 시에 STABLE 함수 결과를 파티션 경계와 비교하여 초기 계획 단계 후에 관련 없는 파티션을 동적으로 건너뛰게 됩니다.
한 금융 분석 회사는 execution_time 열에 대해 TIMESTAMPTZ로 거래 테이블을 일별 범위로 파티셔닝하여 테라바이트의 틱 데이터를 관리했습니다. 분석가들은 자주 최근 활동을 쿼리했으며 **WHERE execution_time >= now() - interval '1 hour'**를 사용했으나, 이러한 쿼리가 모든 365개의 일일 파티션을 스캔하여 성능이 심각하게 저하되는 것을 관찰했습니다.
첫 번째 접근법은 애플리케이션 레이어에서 타임스탬프 경계를 계산하고 그것을 리터럴 상수로 주입하는 것이었습니다. 이는 즉각적인 정적 프루닝을 가능하게 하였고, 쿼리 시간을 45초에서 80밀리초로 줄였습니다. 그러나 이는 쉽게 수정할 수 없는 서드파티 BI 도구에 삽입된 기존 SQL 쿼리를 깨뜨렸습니다.
두 번째 접근법은 현재 날짜를 기반으로 고정된 타임스탬프를 반환하는 사용자 정의 불변 함수를 만드는 것이었습니다. 그러나 이는 데이터베이스 트랜잭션이 자정 경계를 넘어 열려 있을 경우 잘못된 결과를 생성할 수 있기 때문에 거부되었습니다. 이는 **now()**가 트랜잭션 내에서 제공하는 STABLE 계약을 위반하는 것이며, 플래너가 오래된 파티션 범위를 캐시한 경우 잘못된 쿼리 결과로 이어질 수 있습니다.
선택된 해결책은 PostgreSQL 12의 런타임 파티션 프루닝 기능을 활용하는 것이었습니다. 데이터베이스 관리자는 enable_partition_pruning이 활성화되어 있는지 확인하고 애플리케이션을 리팩토링하여 시간 경계를 SQL 문자열에 연결하는 대신 파라미터로 전달하는 준비된 문을 사용하도록 하였습니다. 이렇게 하면 실행자는 실행 시 실제 파라미터 값을 사용하여 파티션을 동적으로 프루닝할 수 있어, 레거시 보고 도구에서 SQL 텍스트 생성을 변경하지 않고도 최적에 가까운 성능을 달성할 수 있었습니다.
상속된 테이블에 대한 PostgreSQL의 제약 배제 메커니즘은 기본 파티션 프루닝과 어떻게 다르며, 전자는 명시적인 GUC 구성을 요구하고 후자는 그렇지 않은 이유는 무엇인가요?
제약 배제는 선언적 파티셔닝 이전에 사용되던 파티셔닝 방법으로, 자식 테이블에 대한 CHECK 제약 조건을 활용하여 테이블에 관련 행이 포함되지 않을 수 있음을 증명했습니다. 계획 중에 상속된 모든 테이블에 대해 이러한 제약 조건을 평가하는 것은 수백 개의 테이블이 존재할 경우 비용이 많이 들기 때문에, 이는 constraint_exclusion 매개변수로 제어되며 기본값은 partition(상속을 통해 쿼리할 때만 확인)입니다. 기본 파티션 프루닝은 플래너에서 파티션 계층 구조를 직접 이해하는 전문 데이터 구조를 사용하므로 더 빠르고 항상 활성화되어 있어, 올바른 기능을 위해 GUC 조정이 필요하지 않습니다.
파티션 키를 수정하여 행을 파티션 간에 이동하는 경우, 왜 PostgreSQL은 내부적으로 DELETE와 INSERT를 실행하고 인라인 업데이트를 하지 않으며, 이로 인해 어떤 트리거에 영향을 미치나요?
각 파티션이 별도의 물리적 저장소를 가진 개별 힙 관계이기 때문에, 파티션 키를 변경하려면 튜플을 하나의 파일에서 다른 파일로 이동해야 합니다. PostgreSQL은 이 전환을 소스 파티션에서 행을 삭제하고 이를 목적지 파티션에 삽입하는 방식으로 구현합니다. 이는 행 수준의 BEFORE DELETE, AFTER DELETE, BEFORE INSERT, AFTER INSERT 트리거가 단일 UPDATE 작업처럼 보이는 동안 모두 발생하게 됩니다. 또한, 논리적 복제는 이를 두 개의 별도 WAL 항목(삭제 및 삽입)으로 스트리밍하므로, 복제 정체성이 올바르게 구성되어 있지 않으면 구독자에서 충돌이 발생할 수 있습니다.
기본 파티션이 있는 테이블에 새로운 파티션을 추가할 때 어떤 특정 잠금 및 검증 오버헤드가 발생하며, 기본 파티션의 전체 스캔을 피하려면 어떻게 해야 하나요?
기본 파티션이 있는 범위 또는 목록 파티셔닝 테이블에 새로운 파티션을 추가할 때, PostgreSQL은 새로운 더 구체적인 파티션에 속해야 할 행이 존재하지 않는지 확인하기 위해 전체 DEFAULT 파티션을 스캔해야 합니다. 이 검증 스캔은 파티션된 테이블에 대해 ACCESS EXCLUSIVE 잠금을 획득하며, 큰 기본 파티션의 경우 몇 시간이 걸릴 수 있습니다. 이를 피하려면 새로운 파티션을 추가하기 전에 DEFAULT 파티션을 분리하고, 충돌하는 행이 존재하지 않는 것을 확인한 후에만 DEFAULT 파티션을 다시 붙일 수 있습니다. 또는 CREATE TABLE ... PARTITION OF를 사용하여 새로운 빈 파티션을 생성하고, INSERT ... SELECT를 사용하여 특정 파티션 범위에 필터를 적용하여 데이터를 마이그레이션하여 기본 파티션의 내용에 대한 검증 스캔을 우회할 수 있습니다.