PostgreSQL 에서 파티셔닝 테이블 파라미터 적용

 

해당 페이지에서는 PostgreSQL 내 파티셔닝이 된 특정 테이블에 autovacuum_enabled 등의 파라미터를 설정 시, 

유의 해야 할 사항 및 관련 설정방법을 다룬다.

 

또한 이 게시글은 이전 게시글 및 관련 URL 과 연관되어 있다.

 

[1] 2021.03.07 - [PostgreSQL/파티셔닝] - PostgreSQL 및 PPAS 간의 생성 차이

 

[2] https://dbtut.com/index.php/2019/02/03/postgresql-psql-gexec/ 

 

 

PostgreSQL 에서의 파티셔닝 테이블 구조

 

PostgreSQL 은 다른 DB engine 과 다르게 독립적인 테이블들을 파티셔닝 관계로 묶어서 사용하는

logic 을 가지고 있다. 그래서 이러한 구조를 공식 Document 에서는 parent - child 개념으로 공식명명한다.

이러한 구조는 아래에 후술할 특정 테이블의 파라미터를 설정 할 때도 영향을 미친다.

 

 

PostgreSQL 에서의 파티셔닝 테이블내 파라미터 적용 방법

 

이제 위의 내용을 인지한 상태에서 아래와 같이 test 를 수행할 시, 파티셔닝이 지정되지 않은 table 에는 특정 파라미터 설정이 그대로 적용됨을 확인 할 수 있다.

 

test=> alter table test.test set (autovacuum_enabled = false);
ALTER TABLE

test=> \dS+ test.test
										  Table "test.test"
 Column |       Type        | Collation | Nullable | Default | Storage  | Stats target | Description
--------+-------------------+-----------+----------+---------+----------+--------------+-------------
 text   | character varying |           |          |         | extended |              |
Access method: heap
Options: autovacuum_enabled=false

 

그러나, 파티셔닝이 적용된 table 에 동일한 명령어를 수행 해 보겠다.

그러면 아래와 같이 unrecognized parameter 에러로 우리가 생각하지 못한 특이 에러가 발생 하게 된다.

 

test=> CREATE TABLE city (
test(> id int4 NOT NULL PRIMARY KEY,
test(> name varchar(30) NOT NULL,
test(> state varchar(20),
test(> population int4)
test-> PARTITION BY RANGE (id);
CREATE TABLE

test=> CREATE TABLE city_id1 PARTITION OF city
test-> FOR VALUES FROM (MINVALUE) TO (10);
CREATE TABLE

test=> CREATE TABLE city_id2 PARTITION OF city
test-> FOR VALUES FROM (10) TO (20);
CREATE TABLE

test=> alter table city set (autovacuum_enabled = false);
ERROR:  unrecognized parameter "autovacuum_enabled"
/*에러 발생*/

 

그래서 이를 해결하기 위해서는 둘 중 하나의 방법을 사용해야 한다.

 

- 각 파티션 테이블별로 해당 명령어를 개별적으로 수행

 

- 아래 예시와 같이 With 문으로 파티션 테이블에 일괄 적용

 

test=> WITH partitions AS (
test(>     SELECT  pc.oid
test(>            ,ppt.relid
test(>            ,pc.relname
test(>            ,pn.nspname
test(>            ,pc.reltuples
test(>            ,pc.relpages
test(>            ,pc.relfilenode
test(>            ,pg_catalog.pg_get_expr(pc.relpartbound, pc.oid,false) as bound_expr
test(>       FROM pg_partition_tree('city') ppt
test(>           ,pg_class pc
test(>           ,pg_namespace pn
test(>      WHERE ppt.isleaf
test(>        AND pc.relname = ppt.relid::text
test(>        AND pc.relnamespace = pn.oid
test(>       )
test->   SELECT 'ALTER TABLE "'||nspname||'"."'||relname|| '" SET (autovacuum_enabled = false)'
test->    FROM partitions;
                             ?column?
------------------------------------------------------------------
 ALTER TABLE "public"."city_id1" SET (autovacuum_enabled = false)
 ALTER TABLE "public"."city_id2" SET (autovacuum_enabled = false)
(2 rows)
/*일괄 적용 쿼리문 확인*/

test=> \gexec
ALTER TABLE
ALTER TABLE

test=>  SELECT relname, reloptions FROM pg_class WHERE relname='city_id1';
 relname  |         reloptions
----------+----------------------------
 city_id1 | {autovacuum_enabled=false}
(1 row)

test=>  SELECT relname, reloptions FROM pg_class WHERE relname='city_id2';
 relname  |         reloptions
----------+----------------------------
 city_id2 | {autovacuum_enabled=false}
(1 row)