PostgreSQL 파티션 테이블 2부

파티션 테이블

PostgreSQL에서는 마치 하나의 테이블인 것 처럼 보이지만, 내부적으로는 물리적으로 특정 조건에 따라 나뉘어 있는 테이블을 파티션 테이블이라고 합니다.

엄밀하게 표현하면 그 하나의 테이블은 파티션된 테이블이고, 그 부분 테이블은 파티션 테이블이라고 영어에서는 표현합니다.

이 글에서는 상위 테이블, 하위 테이블 이 단어을 사용합니다.

(부모 테이블, 자식 테이블 이런 표현도 썼으나, 요즘은 잘 안씁니다.)

파티션 테이블을 만들어 쓰는 까닭은

어느 특정 부분만 찾을 때 찾지 않아도 되는 다른 부분을 아에 찾지 않고, 원하는 부분만 꼭 집어 찾아 작업 비용을 줄이는 것과,
그 특정 부분을 쉽고 빠르게 지우고,
아주 큰 단일 테이블을 autovacuum이 트랜잭션ID 겹침 방지 작업을 아주 오랫동안 하다가 결국 데이터베이스를 단일 사용자 모드로 접속해서 하세월 vacuum 해야하는 데이터베이스 최악의 사고를 막기

위함입니다.

파티션 테이블 만들기

공식 설명서의 예제를 그대로 가져오면,

먼저 상위 테이블을 만들고,

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);

다음 하위 테이블을 만들어서 사용합니다.

CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');

핵심 구문은 상위 테이블에서 사용하는 'partition by' 와 하위 테이블에서 사용하는 'partition of' 입니다.

또 하나 기억해야 할 것은 파티션 테이블의 기본키에는 반드시 파티션키가 포함되어야합니다. 유니크 제약조건(유니크 인덱스 포함)도 마찬가지입니다.

파티션 테이블 전체를 통틀어 하나의 칼럼으로 유일성을 보장할 수 없다는 뜻입니다.

이는 테이블 설계 할 때 꽤 주의를 기우려야하는 문제점(?)을 안고 있습니다.

현재로써는 단일 칼럼 값을 유니크하게 처리할 수 있는 방법은 없습니다.

유니크 자료만 담는 부가 테이블을 만들고, 응용프로그램의 힘을 빌려야할 것 같네요.

파티션 테이블 조회하기

PostgreSQL에서 파티션 테이블을 조회 할 때 꼭 기억해야하는 것은

원하는 하위 테이블만 조회하기 위한 선택 작업(Partition Pruning)이 실행계획 전에 먼저 일어난다는 것입니다.

그래서, 단일 테이블에서 성능이 잘 나오던 SQL이 어처구니 없이 느려지는 경우도 발생합니다. 그 원인을 찾기 위해 실행 계획을 보면, 거의 대부분의 문제가 위에서 이야기한 하위 테이블 선택 작업에서 모든 하위테이블을 대상으로 선택되어 발생합니다.

특히 파티션 테이블과 파티션 테이블의 join 작업에 빈번하게 예상치 않게 비효율적인 실행 계획이 만들어집니다.

방법은 아주 단순합니다. 파티션 테이블을 사용하는 SQL 구문을 작성할 때,

꼭 의도한 대로 하위 테이블만 참조하는지 explain 명령으로 꼭 확인하는 것입니다.

원하는 하위 테이블만 참조하도록 SQL을 작성하는 방법은 1부에서도 언급했듯이, where 조건절에 반드시 해당 파티션키 선택 조건을 추가해야합니다.

위 테이블을 예로 들면,

select * from measurement where logdate >= '2006-02-01' and logdate < '2006-03-01' ......

이런식으로 지정해야 measurement_y2006m02 테이블만 선택하게 됩니다.

이는 update, delete 쿼리에서도 같이 적용됩니다. 자료 변경 쿼리를 짤 때도 꼭 신경 써야 합니다.

운영 환경에서의 파티션 테이블 조작

운영 환경에서 하위 파티션 테이블을 새로 추가하거나, 삭제 하는 작업은 생각보다 신경 쓸 거리가 많습니다.

왜냐하면, 이 작업들은 상위 테이블의 배타적 잠금을 유발하기 때문입니다.

생각 하기를 멈추고, 사용 설명서 예제로 나와 있으니, 그거 믿고, 그냥 작업 하면 되겠지 했다가 운영 장애 상황을 만드는 경우를 종종 봅니다.

첫번째 작업: 아주 오래 걸리는 파티션 테이블 조회 작업

두번째 작업: create table partition of 구문으로 하위 테이블 만드는 작업
이 두번째 작업은 첫번째 작업이 끝나야 자신이 상위 테이블을 배타적 잠금을 하고, 하위 파티션 테이블 추가 작업을 진행합니다. 그런데, 첫번째 작업이 오래 걸린다고 가정하면, 두번째 작업은 대기 상태로 빠지고,

세번째, 네번째 작업들도 모두 대기 상태로 빠집니다.

두번째 create 작업 뒤에 이어 오는 작업들이 그냥 단순 아주 빠른 select 쿼리 일지라도 두번째 작업의 대기 때문에 모두 대기 상태로 빠집니다.

아주 치명적인 상황이 발생될 수 있습니다.

이 문제를 피할 수 있는 방법은 alter table attach 구문으로 하위 파티션 테이블을 추가하는 것입니다. 이렇게 하려면, 먼저 해당 하위 테이블을 만들어야겠죠.

하위 테이블을 지우는 작업에서도 이 전체 잠금 문제는 똑 같이 일어납니다.

그냥 하위 테이블을 drop 하는 것은 아주 위험합니다.

다행히 14버전부터 alter table detach ... concurrently 구문이 지원되면서, 하위 파티션 테이블을 삭제하는 작업에서 부담 덜하지만, 13버전 이하에서는 주의가 많이 필요합니다. 하위 테이블 삭제 작업도 detach concurrently -> drop 으로 진행해야 많은 사람들이 편합니다.

다음은 지금까지 이야기를 바탕으로 실무에서 사용할 수 있는 파티션 테이블 추가, 삭제에 대한 SQL 구문들입니다.

-- 하위 테이블이 없는 상위 파티션 테이블 만들기
create table p (a int, b int, c int , d int) partition by range (b);

-- 테이블 전체 잠금을 하지 않는 하위 테이블 만들어 상위 테이블에 붙이기
create table sub1 (like p including all, check (b >= 0 and b < 10));
alter table p attach partition sub1 for values from ('0') to ('10');
alter table sub1 drop constraint if exists sub1_b_check;

-- 자료 넣고, 조회하는 쿼리가 오래 걸리도록 만들기
insert into p values (1,1,1,1);
select a,b,c,d,pg_sleep(60) from p where a=1 and b = 1;

-- 윗 상황에서 다른 연결에서 기본키를 만드는데 테이블 전체 잠금 최소화 하는 방법
create unique index concurrently sub1_pkey on sub1 (a,b);
create unique index concurrently sub2_pkey on sub2 (a,b);
-- 이 두 작업은 위 오래 걸리는 select 쿼리가 끝나기를 기다리겠지만 테이블 전체 잠금은 일어나지 않음
alter table sub1 add primary key  using index sub1_pkey;
alter table sub2 add primary key  using index sub2_pkey;

-- 이제 상위 테이블 기본키 만들기
alter table only p add primary key (a,b);

-- 하지만 이 기본키는 아직 완전한 기본키가 아님
\d p

-- 하위 테이블의 기본키들이 상위 테이블의 기본키 소속임을 지정하기
alter index p_pkey attach partition sub1_pkey;
alter index p_pkey attach partition sub2_pkey;
\d p

-- 14 버전부터 쓸 수 있는 테이블 전체 잠금을 최소화한 하위 테이블 분리
alter table p detach partition sub2 concurrently;

작성일:2023-11-24 02:50, 수정일: 2024-07-19 13:07