PostgreSQL 및 PPAS 간의 생성 차이

• PostgreSQL 은 10 버전 이전까지는 아래와 같이 INHERITS 와 CHECK 을 통하여 부모 자식 관계과 같은 종속관계 테이블 생성 명령어를 통하여 파티션을 생성한다.
  
  
• PPAS (EDB PAS) 는 ORACLE Syntax 를 통하여 생성 할 수 있다.
  
  
• (출처 : https://postgresql.kr/blog/postgresql_partition_table.html)
  
  
  • (예시) 상위 테이블을 만듭니다.
    
    

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

 

• • 주의 할 점은 인덱스 등의 속성을 특정 명령어 없이는 그대로 가져오지 않으므로 각 파티션 테이블에 개별적으로 인덱스를 부여하거나 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)
상속: wwwlog

•  
  • PostgreSQL 10 이상 버전 및 PAS 는 타 RDBMS 와 같은 명령어를 통하여 파티션을 생성 할 수 있다. (단, 부모자식 테이블 간의 종속관계는 변함이 없다.)
    
    
    • 예시와 같이 타 RDBMS 명령어로 사용 가능 (LIST, RANGE,HASH)
      
      
    • (출처 : https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/9.5/Database_Compatibility_for_Oracle_Developers_Guide.1.325.html)
      
      

CREATE TABLE sales_hist(..., country text, ...) 
PARTITION BY LIST(country)
(
    PARTITION americas VALUES('US', 'CA', 'MX'),
    PARTITION europe VALUES('BE', 'NL', 'FR'),
    PARTITION asia VALUES('JP', 'PK', 'CN'),
    PARTITION others VALUES(DEFAULT)
)

•  
  • • 파티션 추가 및 삭제 명령어도 동일하다.

 

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

The table contains three partitions (americas, asia, and europe) :
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |             high_value              
----------------+-------------------------------------
 EUROPE         | FOR VALUES IN ('FRANCE', 'ITALY')
 ASIA           | FOR VALUES IN ('INDIA', 'PAKISTAN')
 AMERICAS       | FOR VALUES IN ('US', 'CANADA')
(3 rows)

The following command adds a partition named east_asia to the sales table:
ALTER TABLE sales ADD PARTITION east_asia 
  VALUES ('CHINA', 'KOREA');

After invoking the command, the table includes the east_asia partition:
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |             high_value              
----------------+-------------------------------------
 EUROPE         | FOR VALUES IN ('FRANCE', 'ITALY')
 ASIA           | FOR VALUES IN ('INDIA', 'PAKISTAN')
 AMERICAS       | FOR VALUES IN ('US', 'CANADA')
 EAST_ASIA      | FOR VALUES IN ('CHINA', 'KOREA')
(4 rows)

10.3.2.2 Example - Adding a Partition to a RANGE Partitioned Table
The example that follows adds a partition to a range-partitioned table named sales:
CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
(
  PARTITION q1_2012 
    VALUES LESS THAN('2012-Apr-01'),
  PARTITION q2_2012 
    VALUES LESS THAN('2012-Jul-01'),
  PARTITION q3_2012 
    VALUES LESS THAN('2012-Oct-01'),
  PARTITION q4_2012 
    VALUES LESS THAN('2013-Jan-01')
);

The table contains four partitions (q1_2012, q2_2012, q3_2012, and q4_2012):
acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |                            high_value                            
----------------+------------------------------------------------------------------
 Q1_2012        | FOR VALUES FROM (MINVALUE) TO ('01-APR-12 00:00:00')
 Q2_2012        | FOR VALUES FROM ('01-APR-12 00:00:00') TO ('01-JUL-12 00:00:00')
 Q3_2012        | FOR VALUES FROM ('01-JUL-12 00:00:00') TO ('01-OCT-12 00:00:00')
 Q4_2012        | FOR VALUES FROM ('01-OCT-12 00:00:00') TO ('01-JAN-13 00:00:00')
(4 rows)

The following command adds a partition named q1_2013 to the sales table:
ALTER TABLE sales ADD PARTITION q1_2013 
  VALUES LESS THAN('01-APR-2013');
After invoking the command, the table includes the q1_2013 partition:

acctg=# SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS;
 partition_name |                            high_value                            
----------------+------------------------------------------------------------------
 Q1_2012        | FOR VALUES FROM (MINVALUE) TO ('01-APR-12 00:00:00')
 Q2_2012        | FOR VALUES FROM ('01-APR-12 00:00:00') TO ('01-JUL-12 00:00:00')
 Q3_2012        | FOR VALUES FROM ('01-JUL-12 00:00:00') TO ('01-OCT-12 00:00:00')
 Q4_2012        | FOR VALUES FROM ('01-OCT-12 00:00:00') TO ('01-JAN-13 00:00:00')
 Q1_2013        | FOR VALUES FROM ('01-JAN-13 00:00:00') TO ('01-APR-13 00:00:00')
(5 rows)