PostgreSQL 파티션 테이블 1부
PostgreSQL 파티션 테이블
파티션 테이블 만들기
PostgreSQL 10.x 버전에서 파티션 테이블 구문을 지원하면서 좀 더 쉽게 파티션 테이블을 사용할 수 있지만, 아직까지는 이 버전보다 하위 버전을 사용하는 경우가 많아서 정리합니다.
PostgreSQL 파티션 테이블을 이해하기 위해서는 먼저 PostgreSQL 특유 기능인 테이블 상속에 대해서 어느 정도 알고 있어야 합니다.
테이블의 상속 개념은 객체 지향 프로그래밍 언어에서 소개하고 있는 그것과 많이 비슷합니다. 한 테이블을 만들 때, 이미 있는 어떤 테이블의 칼럼 정보를 상속 받아서 만들 수 있습니다.
상위 테이블을 만듭니다.
CREATE TABLE wwwlog (
seq serial not null,
ctime timestamp not null default current_timestamp,
node bigint not null,
data jsonb not null);
일단 not null과 default를 제외한 어떤 제약 조건도 추가하지 않았습니다. 다음 실재 자료가 담길 테이블은 ctime 칼럼 기준 to_char(ctime, 'YYYYMMDD') 이름의 테이블을 만듭니다. 이 때, 각각의 하위 테이블에는 ctime 칼럼에 저장될 값 기준으로 해당 날의 자료만 입력 받도록 check 제약 조건을 지정합니다. 물론 wwwlog 테이블에서 상속 받아서 만들기 때문에, 칼럼 정의는 하지 않습니다. 또한 기본키 정의도 함께 합니다.
CREATE TABLE wwwlog_20180111 ( PRIMARY KEY (seq), CHECK (ctime >= '2018-01-11 00:00:00' AND ctime < '2018-01-12 00:00:00') ) INHERITS (wwwlog); CREATE TABLE wwwlog_20180110 ( PRIMARY KEY (seq), CHECK (ctime >= '2018-01-10 00:00:00' AND ctime < '2018-01-11 00:00:00') ) INHERITS (wwwlog); CREATE TABLE wwwlog_20180109 ( PRIMARY KEY (seq), CHECK (ctime >= '2018-01-09 00:00:00' AND ctime < '2018-01-10 00:00:00') ) INHERITS (wwwlog); CREATE TABLE wwwlog_20180108 ( PRIMARY KEY (seq), CHECK (ctime >= '2018-01-08 00:00:00' AND ctime < '2018-01-09 00:00:00') ) INHERITS (wwwlog); CREATE TABLE wwwlog_20180107 ( PRIMARY KEY (seq), CHECK (ctime >= '2018-01-07 00:00:00' AND ctime < '2018-01-08 00:00:00') ) INHERITS (wwwlog); CREATE TABLE wwwlog_20180106 ( PRIMARY KEY (seq), CHECK (ctime >= '2018-01-06 00:00:00' AND ctime < '2018-01-07 00:00:00') ) INHERITS (wwwlog); CREATE TABLE wwwlog_20180105 ( PRIMARY KEY (seq), CHECK (ctime >= '2018-01-05 00:00:00' AND ctime < '2018-01-06 00:00:00') ) INHERITS (wwwlog); CREATE TABLE wwwlog_20180104 ( PRIMARY KEY (seq), CHECK (ctime >= '2018-01-04 00:00:00' AND ctime < '2018-01-05 00:00:00') ) INHERITS (wwwlog);
이렇게 만들어진 테이블들을 psql에서 보면 다음과 같습니다.
mydb=> \d+ wwwlog
"ioseph.wwwlog" 테이블
필드명 | 종류 | NULL허용 | 초기값
--------+-----------------------------+----------+-------------------------------------
seq | integer | not null | nextval('wwwlog_seq_seq'::regclass)
ctime | timestamp without time zone | not null | CURRENT_TIMESTAMP
node | bigint | not null |
data | jsonb | not null |
하위 테이블: wwwlog_20180104,
wwwlog_20180105,
wwwlog_20180106,
wwwlog_20180107,
wwwlog_20180108,
wwwlog_20180109,
wwwlog_20180110,
wwwlog_20180111
mydb=> \d+ wwwlog_20180104
"ioseph.wwwlog_20180104" 테이블
필드명 | 종류 | NULL허용 | 초기값
--------+-----------------------------+----------+-------------------------------------
seq | integer | not null | nextval('wwwlog_seq_seq'::regclass)
ctime | timestamp without time zone | not null | CURRENT_TIMESTAMP
node | bigint | not null |
data | jsonb | not null |인덱스들:
"wwwlog_20180104_pkey" PRIMARY KEY, btree (seq)
체크 제약 조건:
"wwwlog_20180104_ctime_check" CHECK (ctime >= '2018-01-04 00:00:00'::timestamp without time zone
AND ctime < '2018-01-05 00:00:00'::timestamp without time zone)
상속: wwwlog
wwwlog 테이블에서는 하위 테이블이 있다는 내용이 있고, 각 하위 테이블에는 각각 기본키와 체크 제약 조건과 wwwlog 에서 상속 받은 테이블임을 보여주고 있습니다.
하위 테이블이 많은 경우 하위 테이블을 만드는 SQL 명령어 자체가 많아집니다.
윗 경우는 generate_series() 함수를 다음과 같이 CREATE TABLE 구문 자체를 만드는 쿼리로 만들었습니다.
SELECT 'CREATE TABLE wwwlog_' ||
to_char(current_date - generate_series, 'YYYYMMDD') || ' (' || E'\n' ||
' PRIMARY KEY (seq),' || E'\n' ||
' CHECK (ctime >= ''' || (current_date - generate_series)::timestamp ||
''' AND ctime < ''' || (current_date - (generate_series - 1))::timestamp || ''')' || E'\n' ||
') INHERITS (wwwlog);' FROM generate_series (-1, 6);
이 SQL 명령어를 실행하고 그 결과를 다시 실행해서 테이블을 만들었습니다.
이렇게 만들어진 테이블들은 타 RDBMS와 달리 자료가 입력될 수 있는 조건만 맞다면 어느 테이블에나 다 입력됩니다.
mydb=> INSERT INTO wwwlog (node, data)
VALUES (1, '{"code": 200, "url": "/index.html"}');
INSERT 0 1
mydb=> INSERT INTO wwwlog_20180110 (node, data)
VALUES (1, '{"code": 200, "url": "/index.html"}');
INSERT 0 1
mydb=> INSERT INTO wwwlog_20180111 (node, data)
VALUES (1, '{"code": 200, "url": "/index.html"}');
오류: 새 자료가 "wwwlog_20180111" 릴레이션의 "wwwlog_20180111_ctime_check" 체크 제약 조건을
위반했습니다
상세정보: 실패한 자료: (3, 2018-01-10 13:03:13.699829, 1, {"url": "/index.html", "code": 200})
mydb=> INSERT INTO wwwlog_20180111 (node, data, ctime)
VALUES (1, '{"code": 200, "url": "/index.html"}', current_timestamp + interval '1 day');
INSERT 0 1위와 같이 wwwlog 테이블에도, wwwlog_20180110 (오늘이 2018-01-10) 테이블에도 자료가 입력되지만, 체크 제약 조건 때문에 오늘 자료를 wwwlog_20180111 테이블에는 입력될 수 없습니다. 그래서, 목요일 자료는 ctime 값까지 지정해서 임의로 넣었습니다.
이렇게 입력된 자료를 살펴보면,
mydb=> SELECT tableoid::regclass,seq,ctime FROM wwwlog;
tableoid | seq | ctime
-----------------+-----+----------------------------
wwwlog | 1 | 2018-01-10 13:02:46.429527
wwwlog_20180110 | 2 | 2018-01-10 13:03:01.773082
wwwlog_20180111 | 4 | 2018-01-11 13:04:36.741096
(3개 행)
mydb=> SELECT tableoid::regclass,seq,ctime FROM ONLY wwwlog;
tableoid | seq | ctime
----------+-----+----------------------------
wwwlog | 1 | 2018-01-10 13:02:46.429527
(1개 행)여기서 tableoid 라는 숨은 칼럼값도 함께 살펴보면, 그 자료가 입력된 실재 테이블이 무엇인지 알 수 있습니다. 또한 상위 테이블만 조회한다면, ONLY 예약어를 사용할 수 있습니다.
LIKE INCLUDING ALL
일반적인 상속 구문에는 다음과 같은 제약들이 있습니다.
- 칼럼 속성(자료형, not null, default)들을 제외한 모든 정보가 상속되지 않는다.
- 즉 모델링을 하면서 이 테이블을 사용하는 쿼리들을 고려해서 상위 테이블에 인덱스를 만들었다고 해도 그 실 자료가 하위 테이블에 있다면, 그 하위 테이블에 인덱스가 있어야하며, 그 인덱스는 CREATE TABLE ... inherits () 명령이 실행 될 때 자동으로 만들지 않습니다. 하위 테이블 각각 인덱스를 만들어 주어야 합니다. 이렇게 각 하위 테이블 별로 각각 작업해야할 것들이, 인덱스 뿐만 아니라, 기본키, 참조키, 트리거 등입니다. - serial 자료형은 그 사용하는 시퀀스 정보를 그대로 상속된다.
- 하위 테이블별 채번 작업이 분리되어야 할 경우라면, 하위 테이블을 만들 때, 해당 칼럼의 default 정의를 재정의해야 합니다. - 상위 테이블 기준 전역 유니크 제약조건(기본키)을 사용할 수 없다.
이런 특성 때문에, 상속용 테이블을 만들 때, 각 하위 테이블에서 사용할 인덱스들이 미리 정의되어 있다면, 그 인덱스들을 상위 테이블에 만들고, LIKE INCLUDING 옵션을 이용해서 만드는 방법도 있습니다.
윗 예제라면, wwwlog.ctime 칼럼에 대해서는 brin 인덱스를, wwwlog.data 칼럼에 대해서는 gin 인덱스를 만듭니다.
CREATE INDEX wwwlog_ctime_i ON wwwlog USING brin (ctime); CREATE INDEX wwwlog_data_i ON wwwlog USING gin (data);
다음 테스트로 wwwlog_20180111 테이블을 지우고, 다시 만들어 보았습니다.
CREATE TABLE wwwlog_20180111 (
LIKE wwwlog INCLUDING ALL,
PRIMARY KEY (seq),
CHECK (ctime >= '2018-01-11 00:00:00'
AND ctime < '2018-01-12 00:00:00')
) INHERITS (wwwlog);
mydb=> \d wwwlog_20180111
"ioseph.wwwlog_20180111" 테이블
필드명 | 종류 | NULL허용 | 초기값
--------+-----------------------------+----------+-------------------------------------
seq | integer | not null | nextval('wwwlog_seq_seq'::regclass)
ctime | timestamp without time zone | not null | CURRENT_TIMESTAMP
node | bigint | not null |
data | jsonb | not null |
인덱스들:
"wwwlog_20180111_pkey" PRIMARY KEY, btree (seq)
"wwwlog_20180111_ctime_idx" brin (ctime)
"wwwlog_20180111_data_idx" gin (data)
체크 제약 조건:
"wwwlog_20180111_ctime_check" CHECK (ctime >= '2018-01-11 00:00:00'::timestamp without time zone
AND ctime < '2018-01-12 00:00:00'::timestamp without time zone)
상속: wwwlogINSERT 트리거
아직 파티션 테이블 만들기가 끝나지 않았습니다.
자료 입력 처리 시간을 최적화 해야하는 상황이라면, 각 하위 테이블에 자료를 각각 넣는 것이 제일 좋습니다. 이렇게 하려면, 응용 프로그램 차원에서 어느 테이블에 자료를 넣어야 할지를 결정해야한다.
이런 요구 조건이 아니라, 조금은 늦어도 괜찮으니, 기존 응용 프로그램에서 입력 SQL 수정 없이 일반 테이블이 파티션 테이블로 바뀌는 경우는 앞에서 설명한대로 INSERT 작업에 대한 입력 분기 처리를 하지 않으면, wwwlog 테이블에만 자료가 쌓이게 됩니다.
wwwlog 테이블에 자료를 입력하려고 한다면, 트리거가 그 자료의 실재로 저장되어야 할 하위 테이블을 결정해서 그 하위 테이블에 자료를 입력하고, wwwlog 테이블에는 자료를 저장하지 않는 트리거가 필요합니다.
PostgreSQL에서 트리거를 사용하려면, 트리거 함수부터 만들어야 합니다.
CREATE OR REPLACE FUNCTION wwwlog_insert()
RETURNS trigger
LANGUAGE plpgsql
AS $body$
BEGIN
if ((new.ctime >= '2018-01-11 00:00:00'::timestamp without time zone)
AND (new.ctime < '2018-01-12 00:00:00'::timestamp without time zone))
then insert into wwwlog_20180111 values (new.*);
elsif ((new.ctime >= '2018-01-10 00:00:00'::timestamp without time zone)
AND (new.ctime < '2018-01-11 00:00:00'::timestamp without time zone))
then insert into wwwlog_20180110 values (new.*);
elsif ((new.ctime >= '2018-01-09 00:00:00'::timestamp without time zone)
AND (new.ctime < '2018-01-10 00:00:00'::timestamp without time zone))
then insert into wwwlog_20180109 values (new.*);
elsif ((new.ctime >= '2018-01-08 00:00:00'::timestamp without time zone)
AND (new.ctime < '2018-01-09 00:00:00'::timestamp without time zone))
then insert into wwwlog_20180108 values (new.*);
elsif ((new.ctime >= '2018-01-07 00:00:00'::timestamp without time zone)
AND (new.ctime < '2018-01-08 00:00:00'::timestamp without time zone))
then insert into wwwlog_20180107 values (new.*);
elsif ((new.ctime >= '2018-01-06 00:00:00'::timestamp without time zone)
AND (new.ctime < '2018-01-07 00:00:00'::timestamp without time zone))
then insert into wwwlog_20180106 values (new.*);
elsif ((new.ctime >= '2018-01-05 00:00:00'::timestamp without time zone)
AND (new.ctime < '2018-01-06 00:00:00'::timestamp without time zone))
then insert into wwwlog_20180105 values (new.*);
elsif ((new.ctime >= '2018-01-04 00:00:00'::timestamp without time zone)
AND (new.ctime < '2018-01-05 00:00:00'::timestamp without time zone))
then insert into wwwlog_20180104 values (new.*);
end if;
RETURN null;
END;
$body$;
CREATE TRIGGER tr_wwwlog_insert
BEFORE INSERT ON wwwlog
FOR EACH ROW EXECUTE PROCEDURE wwwlog_insert();
여기서는 wwwlog_insert()라는 INSERT 이벤트에 대한 트리거 함수를 만들고, wwwlog 테이블(상위 테이블)에 그 트리거 함수를 이용하는 트리거를 만듭니다.
중요한 사항은 그 함수의 반환값은 null 이어야 wwwlog 테이블에 자료가 입력되지 않습니다. 또한 이 트리거는 자료 입력 작업 전에 실행되어야 하기 때문에, BEFORE 트리거여야 합니다.
여기까지 진행되면 파티션 테이블 만들기는 마무리 됩니다.
이 작업에는 다음의 문제점들이 있습니다.
- 매일 내일 자료가 담길 하위 테이블을 만들어 주어야 하고,
- 그 하위 테이블에 정말 자료가 담길 수 있도록 wwwlog_insert() 함수를 매일 수정 해야 하며,
- 더 이상 보관이 필요없는 하위 테이블에 대해서는 wwwlog 테이블의 종속 관계를 끊고, (ALTER TABLE 오래된하위테이블이름 NO INHERIT wwwlog) 그 테이블을 따로 보관하던가 삭제 해야함
결국 이런 작업은 매일 오류 없이 진행되어야 하며, 따로 관리 되어야합니다. 아울러 더 이상 자료 수정이 없는 하위 테이블들에 대해서는 autovacuum 프로세스에서 처리하는 트랜잭션 ID 겹침 방지 작업 (vacuum freeze 작업) 시 몰리는 디스크 I/O를 줄이기 위해 미리 해당 테이블에 대한 freeze 작업을 해 두는 것도 좋습니다.
문제는 wwwlog_insert() 함수를 매번 수정해 주어야 한다는 것은 꽤 성가신 일입니다. 위 예제와 같이 매일 테이블들이 만들어지고, 매일 삭제 되는 경우라면, 이 insert 트리거 함수는 동적 쿼리를 사용하는 것이 오히려 낫습니다.
아주 간단합니다.
CREATE OR REPLACE FUNCTION wwwlog_insert_dynamic()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
execute format('insert into wwwlog_%s select $1.*',
to_char(new.ctime, 'YYYYMMDD')) using new;
RETURN null;
END;
$function$
이 함수를 쓴다면, 트리거 정의도 재정의 해야겠지요.
테이블 관리 예약 작업
이제 남은 것은 앞에서 언급한 매일 작업해야 하는 예약 작업을 등록하는 일입니다.
일단 하위 테이블을 만들고 오래된 하위 테이블을 wwwlog 테이블과의 상속 관계를 끊는 함수 하나를 만듭니다.
CREATE OR REPLACE FUNCTION wwwlog_child_manager()
RETURNS text
LANGUAGE plpgsql
AS $function$
DECLARE
nextday date;
nextday2 date;
purgeday date;
BEGIN
-- 내일 테이블을 미리 만듦
nextday := current_date + 1;
nextday2 := current_date + 2;
-- 7일 이상 오래된 테이블에 정리용 날짜
purgeday := current_date - 7;
execute format('CREATE TABLE IF NOT EXISTS wwwlog_%s' ||
'( LIKE wwwlog INCLUDING ALL, PRIMARY KEY (seq), ' ||
'CHECK (ctime >= ''%s'' and ctime < ''%s'')) INHERITS (wwwlog)',
to_char(nextday,'YYYYMMDD'), nextday::timestamp, nextday2::timestamp);
execute format('ALTER TABLE wwwlog_%s NO INHERIT wwwlog',
to_char(purgeday, 'YYYYMMDD'));
return 'wwwlog_' || to_char(purgeday, 'YYYYMMDD');
EXCEPTION
WHEN undefined_table THEN
raise notice '%, %', SQLSTATE, SQLERRM;
return '';
END;
$function$;
이 함수를 pgagent 나, crontab 에 등록해서 사용하면 되겠죠. 일반적으로 이 작업은 쉡 스크립트에서 psql 명령어로 호출하고, 그것과 함께 vacuum 작업들도 함께 합니다. 그렇게 작성된 쉘 스크립트를 crontab 에 등록합니다. 왜냐하면, vacuum 작업이 plpgsql 함수 안에서는 실행될 수 없기 때문입니다.
윗 관리 함수는 반환값이 있으면 그 테이블에 대해서 후속 작업을 하는 형태의 쉘 스크립트를 만들것을 염두해 두고 만들었습니다.
쉘 스크팁트 내용은 다음과 같습니다.
$ cat wwwlog_manager.sh #!/bin/sh vacuum_table=`psql mydb -t -c "select wwwlog_child_manager()"` if [ $vacuum_table != "" ]; then vacuumdb -t $vacuum_table -F -z mydb fi
이 예약 작업은 서버 상황에 맞게 적당한 시간에 실행 되도록 하면 될 것입니다. 물론 서버의 용량 관리 차원에서 상속 관계를 끊은 하위 테이블에 대한 삭제 작업이나, 다른 호스트로의 백업 관련 작업은 이 글 범위를 벗어나 생략합니다.
드디어 다 만들었습니다!
파티션 테이블 부분 선택 조회
파티션 테이블을 사용하는 이유는
- 자료 조회할 때 검색 범위를 하위 테이블로 한정할 수 있다. - 디스크 읽기 비용을 줄인다.
- 자료량 관리 시 필요 없는 자료를 한꺼번에 지울 수 있다.
입니다. 즉 자료량이 많은데 대부분의 쿼리가 하위 테이블로 한정 지어 쿼리 실행 비용을 최적화 하는 것이 그 첫번째 목적입니다. 이것을 영어 표현으로 파티션 프루닝 partition pruning이라고 합니다.
윗 예제의 경우라면, 어제 저장된 로그 가운데, code 값이 404인 로그만 찾고 싶다면, 일반적으로 그 쿼리는
SELECT * FROM wwwlog
WHERE ctime >= (CURRENT_DATE - 1)
AND ctime < CURRENT_DATE
AND data @> '{"code": 404}';형태가 될 것입니다. @> 연산자는 GIN 인덱스를 사용하기 위한 jsonb 자료형에 대한 검색 연산자입니다. 자세한 이야기는 지난 jsonb 관련 글을 참조하시고.
문제는 ctime에 대한 조건절입니다.
이 쿼리의 실행 계획을 보면, (쿼리 앞에 explain 넣어서 실행하면 실행 계획이 보여요)
mydb=> explain
select * from wwwlog
where ctime >= (current_date - 1)
and ctime < current_date
and data @> '{"code": 404}';
QUERY PLAN
-----------------------------------------------------------------------------------------
Append (cost=0.00..1130.20 rows=9 width=67)
-> Seq Scan on wwwlog (cost=0.00..1.02 rows=1 width=69)
Filter: ((data @> '{"code": 404}'::jsonb)
AND (ctime < CURRENT_DATE) AND (ctime >= (CURRENT_DATE - 1)))
-> Bitmap Heap Scan on wwwlog_20180111 (cost=104.94..138.78 rows=1 width=69)
Recheck Cond: ((ctime >= (CURRENT_DATE - 1))
AND (ctime < CURRENT_DATE) AND (data @> '{"code": 404}'::jsonb))
-> BitmapAnd (cost=104.94..104.94 rows=9 width=0)
-> Bitmap Index Scan on wwwlog_20180111_ctime_i
(cost=0.00..12.04 rows=8719 width=0)
Index Cond: ((ctime >= (CURRENT_DATE - 1))
AND (ctime < CURRENT_DATE))
-> Bitmap Index Scan on wwwlog_20180111_data_i
(cost=0.00..92.65 rows=87 width=0)
Index Cond: (data @> '{"code": 404}'::jsonb)
-> Bitmap Heap Scan on wwwlog_20180110 (cost=124.94..162.39 rows=1 width=69)
Recheck Cond: ((ctime >= (CURRENT_DATE - 1))
AND (ctime < CURRENT_DATE) AND (data @> '{"code": 404}'::jsonb))
-> BitmapAnd (cost=124.94..124.94 rows=10 width=0)
-> Bitmap Index Scan on wwwlog_20180110_ctime_i
(cost=0.00..12.04 rows=9600 width=0)
Index Cond: ((ctime >= (CURRENT_DATE - 1))
AND (ctime < CURRENT_DATE))
-> Bitmap Index Scan on wwwlog_20180110_data_i
(cost=0.00..112.65 rows=86 width=0)
Index Cond: (data @> '{"code": 404}'::jsonb)
...... (20180109부터 쭉 반복)
-> Bitmap Heap Scan on wwwlog_20180104 (cost=124.94..162.39 rows=1 width=69)
Recheck Cond: ((ctime >= (CURRENT_DATE - 1))
AND (ctime < CURRENT_DATE) AND (data @> '{"code": 404}'::jsonb))
-> BitmapAnd (cost=124.94..124.94 rows=10 width=0)
-> Bitmap Index Scan on wwwlog_20180104_ctime_i
(cost=0.00..12.04 rows=9600 width=0)
Index Cond: ((ctime >= (CURRENT_DATE - 1))
AND (ctime < CURRENT_DATE))
-> Bitmap Index Scan on wwwlog_20180104_data_i
(cost=0.00..112.65 rows=86 width=0)
Index Cond: (data @> '{"code": 404}'::jsonb)
이렇게 조회할 테이블은 어제(2018-01-09)만 대상으로 했기 때문에, wwwlog_20180109 테이블만 조회하면 되는데, 실행 계획에서는 전체 하위 테이블을 다 조회하겠다고 합니다. 이런 상황을 DBA 용어로 파티션 테이블 프루닝 오동작이라고 합니다.
PostgreSQL에서는 파티션 테이블 프루닝 조건은 상수값으로 와야합니다. 이 프루닝 계획이 쿼리 실행 단계가 아닌 계획 단계에서 결정하는데, 그 결정 당시 CURRENT_DATE 같은 예약어로는 판단할 수 없기 때문입니다. 윗 쿼리를 조금 수정하면 파티션 테이블 프루닝이 잘 될 것 입니다. 실행 계획은 직접 확인하세요.
SELECT * FROM wwwlog
WHERE ctime >= '2018-01-10'::date - 1
AND ctime < '2018-01-10'::date
AND data @> '{"code": 404}';즉, '2018-01-10' 이라는 문자열은 쿼리를 실행하기 전에 미리 구해 놓아야하는 한계가 있습니다.
이 부분이 응용 프로그램을 개발하면서 가장 흔히 범하는 실수입니다. 꼭 기억해 두어야 할 부분입니다.
상위 테이블의 전역 인덱스 이용 못하는 문제
파티션 테이블을 이용하면서 조회성 쿼리에서 종종 문제가 생기는 것이 다른 RDBMS와 달리 전역 인덱스를 사용할 수 없어 하위 테이블의 개별 인덱스를 모두 조사해야 하는 문제가 발생합니다. 윗 쿼리에서 보듯이 ctime 조건을 빼고, data 칼럼에 대한 전역 인덱스를 사용할 수 있다면, 해당 부분만 가져오면 되는데 PostgreSQL에서는 파티션 프루닝 작업을 하지 않는다고 판단하면, 무조건 상속된 모든 하위 테이블의 인덱스를 모두 조사합니다. 즉, seq = 1 과 같은 기본키를 조회해도 마찬가지입니다. 이 문제는 앞에서 설명한 상위 테이블 기준으로 유니크한 자료를 데이터베이스 차원에서 관리할 수 없다는 것을 의미합니다. 즉 얼마든지 의도하지 않게 같은 값의 기본키 자료가 여러 하위 테이블에 저장 될 수 있음을 의미합니다. 이 예외처리는 INSERT 트리거에서 조금은 예방할 수는 있겠지만, 근본 방지 대책이 되지 못할 뿐만 아니라, INSERT 작업 속도를 저하시키는 원인이 되기 때문에 권장하지 않습니다. 이 문제는 윗 예제처럼 채번을 상위 테이블에서 하고, 그것 기준으로 기본키를 사용하는 방식 선에서 타협점을 찾아야합니다.
파티션 테이블 자료 변경
파티션 테이블을 사용하면서 또 하나 신경 써야 할 부분은 UPDATE/DELETE 구문에 대한 처리 정책입니다.
UPDATE와 DELETE 작업도 앞에서 설명한 SELECT 작업의 테이블 프루닝 정책을 그대로 따르기 때문에, 파티션 키(윗 예제라면 ctime 칼럼값)를 선택할 수 있는 WHERE 절의 검색 조건이 없다면, 전체 하위 테이블을 대상으로 쿼리가 실행됩니다. 하위 테이블이 많고, 자료량이 방대하다면, 기본키에 대한 아주 단순한 UPDATE 작업일지라도 그 비용은 단일 테이블일 때와는 확연히 차이가 납니다.
가장 좋은 방법은 그 해당 자료를 업데이트 하기 전에 그 자료의 파티션 키 값을 미리 알고 있고, 그 값을 UPDATE나 DELETE의 WHERE 절 검색 조건으로 포함하는 방법입니다. 당연히 이때도 그 파티션 키 값은 상수로 지정되어야 정확히 프루닝 할 것입니다.
파티션 키 값 자체를 바꾸는 경우
윗 예제 기준으로 ctime 값 자체가 바뀌어야 하는 상황은 꽤 어려운 문제입니다. 왜냐하면 물리적으로 A테이블에서 B테이블로 자료를 옮겨야하는 부분인데, 이 부분은 응용프로그램의 해당 자료 보관 정책과 관계되기 때문에, 옮겨갈 테이블이 없을 수도 있겠죠.
그래서, 이 문제는 일반적으로 내버려둡니다. ctime이 체크 제약 조건 위반이 되어 쿼리 실패나도록 합니다. 그리고, 이런 업무가 발생하면 수작업으로 진행합니다. 그렇지 않고, 데이터베이스 관리자가 없이 응용프로그램 개발자만으로 운영한다면, wwwlog 테이블 하위 테이블에 체크 제약 조건 위반을 피해 갈 수 있는 트리거를 각각(!) 만들어야 합니다.
그 트리거 함수는 다음과 같습니다.
CREATE OR REPLACE FUNCTION wwwlog_update()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
IF NEW.ctime <> OLD.ctime
AND (NEW.ctime::date - OLD.ctime::date) <> 0 THEN
EXECUTE format('DELETE FROM %s WHERE seq = $1.seq', TG_TABLE_NAME) using old;
INSERT INTO wwwlog values (NEW.*);
END IF;
RETURN NULL;
END;
$function$;체크 제약 조건 검사는 자료가 저장될 실재 테이블에서 발생하기 때문에, 이 트리거를 wwwlog 테이블에 걸어두어도 작동하지 않습니다. 이 트리거 함수를 모든 트리거에 같이 이용해서, 다음과 같이 트리거를 만듭니다.
CREATE TRIGGER tr_wwwlog_update BEFORE UPDATE ON wwwlog_20180111 FOR EACH ROW EXECUTE PROCEDURE wwwlog_update(); CREATE TRIGGER tr_wwwlog_update BEFORE UPDATE ON wwwlog_20180110 FOR EACH ROW EXECUTE PROCEDURE wwwlog_update(); ...... CREATE TRIGGER tr_wwwlog_update BEFORE UPDATE ON wwwlog_20180105 FOR EACH ROW EXECUTE PROCEDURE wwwlog_update();
위의 하위 파티션 테이블을 만드는 함수를 이용한다면, 그 함수에도 트리거를 자동으로 만들어 주는 부분이 추가 되어야겠지요.
한편,
이 업데이트 트리거에서는 앞에서 이야기한 기본키 변경에 따른 자료 중복 문제를 고려조차 하지 않았습니다. 단지 방지책으로 new.seq 값과, old.seq 값이 다르면 무조건 오류 메시지를 보여주는 예외 처리 정도는 할 수 있겠죠.
기존 테이블을 파티션 테이블로 만들기
데이터베이스 서버를 운영하다 보면, 한 테이블에 자료량이 많아지고, 그에 따른 쿼리 실행 시간이 길어지는 문제가 발생해서, 그 테이블을 여러 개의 하위 테이블로 나눠 관리하는 작업을 해야하는 경우가 생깁니다.
물론 가장 손쉬운 방법은 응용 프로그램 접속을 막고, 각 하위 테이블 자료를 파티션 단위로 뽑아서 (COPY (SELECT...) TO) 각 하위 테이블에 한꺼번에 넣고, (COPY 해당하위테이블 FROM
) 상위 테이블에 트리거를, 하위 테이블에 인덱스를 만들고, 응용 프로그램을 다시 사용하는 방법이 제일 안정적이고, 빠른 방법이겠지만, 실 운영 환경에서 이런 작업을 할 기회가 없다면, 기존 서비스를 그대로 제공하면서 이 작업을 진행해야합니다.
이 때, 위에서 언급한 것들을 모두 고려해서 파티션 테이블 전환 전략을 짜야합니다. 일반 적인 순서는 다음과 같습니다.
- 파티션 키를 정한다.
- 응용 프로그램에서 해당 테이블을 사용하는 모든 쿼리를 검토 해서 파티션 테이블로 바뀌었을 때, 쿼리가 수정되어야 할 부분을 수정한다. 관심 가져야 할 부분은 파티션 프루닝 부분입니다. UPDATE/DELETE 도 함께 고려해야합니다.
- 하위 테이블을 만들고, 그것에 속한 각종 인덱스, 트리거, 제약 조건을 모두 준비해서, 그 테이블들을 ALTER TABLE ... INHERIT 상위테이블 명령어로 상속 관계를 추가한다.
- INSERT 트리거 작업을 한다.
- 기존 부모 테이블에 있는 자료를 각 하위 테이블로 옮긴다.
문제는 마지막 작업입니다. 이 작업은 실 운영 환경 내에서 대량 배치작업으로 진행하게 되면 대량의 DML 작업으로 서비스 품질을 많이 떨어뜨립니다.
최대한 영향을 적게 주는 방법은 상위테이블의 자료 하나만 DELETE 하고 그 지운 자료를 하위 테이블로 옮기는 방식을 택합니다.
다행스럽게도 이 작업은 WITH 구문과 DELETE ... RETURNING 구문으로 하나의 쿼리로 하나의 트랜잭션으로 처리할 수 있습니다.
윗 예제를 예로 한다면,
WITH t AS (
DELETE FROM ONLY wwwlog
WHERE ctime >= '2018-01-10'::date
AND ctime < '2018-01-11'::date
AND seq = 1 RETURNING *
)
INSERT INTO wwwlog_20180110
SELECT * FROM t;이 쿼리에서 기본키(seq) 값 기준으로 DELETE & INSERT 하는 쿼리를 SELECT 쿼리로 만들고, 그 만든 결과를 서버 부하를 최소화하는 방식으로 실행합니다. 주의할 사항은 만일을 대비해서 파티션 키는 꼭 사용하도록 쿼리를 만든다는 것입니다.
물론 대량의 DML 작업을 온라인 상에서 진행하는 작업임으로 약간의 부분 작업을 먼저 진행해 보고, 작업 예상 시간과 작업 중 서버 영향도를 꼭 파악하고 전체 작업을 진행하는 것이 바람직 할 것입니다.
오프라인 방식과 온라인 방식을 섞어 지금 사용하고 있는 하위 테이블만 온라인 방식으로 자료를 옮기고 사용 빈도가 낮은 옛날 자료는 COPY 구문으로 대량 이동을 하는 방식도 고려해볼만 합니다.
이 자료 이전 전략에는 정답은 없습니다. 업무 환경에 맞춰 가장 효율적인 방식을 직접 구현해야 할 것입니다.
마치며
이상으로 PostgreSQL 9.6.x 이하 버전에서의 파티션 테이블에 대해서 살펴보았습니다.이제껏 경험 했던 파티션 테이블에 대한 것들을 최대한 모두 담아내려고 했는데, 얼마나 내용이 빠졌는지는 모르겠네요.
이 글에서 언급하지 않는 또 다른 많은 이야기 거리가 있을 수도 있습니다. 그 부분은 각자 알아서 풀어가시고, 기회 닿으면 공유도 해 주시면 보다 많은 이들에게 도움이 될 것으로 기대합니다.
2부는 PostgreSQL 10.x 이상 버전에서의 파티션 테이블 이야기로 꾸밀 예정입니다. 그 글은 꽤 짧을 것 같네요.