잠시만 기다려 주세요

HOME   |  여기는?   |  바다물때표   |  여러가지 팁 (888)  |  추천 및 재미 (152)  |  자료실 (20)  |  끄적거림 (519)  |  시나리오 (760)  |  드라마 대본 (248)  |  

2020년 09월 21일
절대 일을 벌이지 마라. 별 탈 없이 소리없이 사는 것, 그 무료함이 바로 사람의 진정한 행복이다. - 바다아이
 




    postgresql

postgresql - Postgresql partitioning table 생성, 파티션 세팅, 사용, 파티셔닝
이 름 : 바다아이   |   조회수 : 2580         짧은 주소 : https://www.bada-ie.com/su/?721591815283
최근엔 버전 10 으로 넘어오면서 좀더 편리하게 파티셔닝하는 방법도 있습니다만...
일단 기존 하던 방식대로 하는 것이 당분간은 좋을 듯 합니다. 
최신 버전은 안정화되기까지 좀 더 지켜볼 필요가 있어서요... ^^;
개인적으로는 DB 는 최대한 단순하게 순수하게 디자인 하고 최대한 어플단에서 해결 하는 것을 권합니다.
DB 뻑나면 일이 커져요... DB 는 넣다 빼는 것에 충실하시고 연산은 최대한 어플단에서 해결보는 것이 좋습니다.



Postgresql 사용 중, 수집되는 양이 많을 경우, 테이블을 파티셔닝하게 된다.
postgresql은 좀 귀찮은게. 각 파티셔닝 테이블이 동적으로 생성되지 않고, 
자동으로 각 파티션된 테이블로 저장되지 않는다.
따라서 아래와 같이 수동으로 구성을 직접하여야 한다.


나는, 기간에 따른 파티셔닝을 생각하여, 월별 파티셔닝 테이블을 생성하였다.

(참조, http://www.postgresql.org/docs/current/interactive/ddl-partitioning.html )


1) 테이블 생성

CREATE SEQUENCE public.voc_item_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1

CREATE TABLE voc_item
(
  voc_item_id bigint default nextval('voc_item_seq'::regclass),
  voc_reg_date date,
  voc_memo character varying(4000),
  reg_cnt character varying(5)
)
ALTER TABLE voc_item ADD CONSTRAINT p_voc_item PRIMARY KEY (voc_item_id);
2) 파티셔닝 테이블 생성 , 난 7월부터 12월까지의 테이블을 생성하였다. 
위에서도 언급했듯이 Postgresql은 동적으로 테이블을 생성해 주지 않는다. 
미리 많들어 놓거나,주기적으로 수작업을 진행하여야 한다.

CREATE TABLE voc_item_201407 (
CHECK (voc_reg_date < date '2014-08-01')
) INHERITS (voc_item);

CREATE TABLE voc_item_201408 (
CHECK (voc_reg_date >= date '2014-08-01' and  voc_reg_date < date '2014-09-01')
) INHERITS (voc_item);

CREATE TABLE voc_item_201409 (
CHECK (voc_reg_date >= date '2014-09-01' and voc_reg_date < date '2014-10-01')
) INHERITS (voc_item);

CREATE TABLE voc_item_201410 (
CHECK (voc_reg_date >= date '2014-10-01' and voc_reg_date < date '2014-11-01')
) INHERITS (voc_item);

CREATE TABLE voc_item_201411 (
CHECK (voc_reg_date >= date '2014-11-01' and voc_reg_date < date '2014-12-01')
) INHERITS (voc_item);

CREATE TABLE voc_item_201412 (
CHECK (voc_reg_date >= date '2014-12-01' and voc_reg_date < date '2015-01-01')
) INHERITS (voc_item);

ALTER TABLE voc_item_201407 ADD CONSTRAINT voc_item_201407_pkey PRIMARY KEY (voc_item_id);
ALTER TABLE voc_item_201408 ADD CONSTRAINT voc_item_201408_pkey PRIMARY KEY (voc_item_id);
ALTER TABLE voc_item_201409 ADD CONSTRAINT voc_item_201409_pkey PRIMARY KEY (voc_item_id);
ALTER TABLE voc_item_201410 ADD CONSTRAINT voc_item_201410_pkey PRIMARY KEY (voc_item_id);
ALTER TABLE voc_item_201411 ADD CONSTRAINT voc_item_201411_pkey PRIMARY KEY (voc_item_id);
ALTER TABLE voc_item_201412 ADD CONSTRAINT voc_item_201412_pkey PRIMARY KEY (voc_item_id);

CREATE INDEX idx_voc_item_01 on voc_item(voc_reg_date);
CREATE INDEX idx_voc_item_201407_01 on voc_item_201407(voc_reg_date);
CREATE INDEX idx_voc_item_201408_01 on voc_item_201408(voc_reg_date);
CREATE INDEX idx_voc_item_201409_01 on voc_item_201409(voc_reg_date);
CREATE INDEX idx_voc_item_201410_01 on voc_item_201410(voc_reg_date);
CREATE INDEX idx_voc_item_201411_01 on voc_item_201411(voc_reg_date);
CREATE INDEX idx_voc_item_201412_01 on voc_item_201412(voc_reg_date);

3) functiontrigger 생성 , 이 역시 postgresql에서 동적으로 수행해주지 않기때문에 
trigger를 통해 각 파티셔닝테이블에 수집이 되도록 해야한다.
여기서 데이터 날짜로 파티셔닝을 했으므로 해당필드에 대한 조건처리를 통해 분산적재한다 

CREATE OR REPLACE FUNCTION func_voc_item_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.voc_reg_date < DATE '2014-08-01') THEN
        INSERT INTO voc_item_201407 VALUES (NEW.*);
    ELSIF ( NEW.voc_reg_date >= DATE '2014-08-01' and NEW.voc_reg_date < DATE '2014-09-01') THEN
        INSERT INTO voc_item_201408 VALUES (NEW.*);
    ELSIF ( NEW.voc_reg_date >= DATE '2014-09-01' and NEW.voc_reg_date < DATE '2014-10-01') THEN
        INSERT INTO voc_item_201409 VALUES (NEW.*);
    ELSIF ( NEW.voc_reg_date >= DATE '2014-10-01' and NEW.voc_reg_date < DATE '2014-11-01') THEN
        INSERT INTO voc_item_201410 VALUES (NEW.*);
    ELSIF ( NEW.voc_reg_date >= DATE '2014-11-01' and NEW.voc_reg_date < DATE '2014-12-01') THEN
        INSERT INTO voc_item_201411 VALUES (NEW.*);
    ELSIF ( NEW.voc_reg_date >= DATE '2014-12-01' and NEW.voc_reg_date < DATE '2015-01-01') THEN
        INSERT INTO voc_item_201412 VALUES (NEW.*);
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_voc_item_insert
    BEFORE INSERT on voc_item

    FOR EACH ROW EXECUTE PROCEDURE func_voc_item_insert_trigger();


만약, 동적으로 테이블이 생성되고, 파티셔닝을 하길 원한다면, 이 역시 function을 통해 가능은 하지만..
이는 테이블 인서트작업 마다, 테이블 존재유무를 체크하고 테이블을 생성하거나, 
그냥 인서트를 하는 비용이 발생하게 되므로
좋은 방법이라 하긴 어렵다. 아래 링크에는 이러한 방법이 소개되어 있다.
(참고, https://blog.engineyard.com/2013/scaling-postgresql-performance-table-partitioning )
혹은 별도의 파티셔닝을 생성하는 트리거를 두어, 특정 기간에 한번 테이블을 자동으로 생성하는것도 좋은 방법이다.
(참고, http://stackoverflow.com/questions/7891320/self-managing-postgresql-partition-pables )

insert into voc_item (voc_reg_date,voc_memo,reg_cnt) values ('2014-07-01', 'bb', 'cc');
insert into voc_item (voc_reg_date,voc_memo,reg_cnt) values ('2014-08-01', 'bb', 'cc');
insert into voc_item (voc_reg_date,voc_memo,reg_cnt) values ('2014-09-01', 'bb', 'cc');

입력 후 각 테이블들 보시면 날짜에 맞게 올라가 있을 겁니다.
만약 조건에 맞는 테이블이 없으면 데이타가 입력되지 않습니다.
voc_item 에서 select 하면 연결된 테이블 데이터 자료가 다 보여져요..
조금 쉽게 설명하면 voc_item 을 구조라고 보시고 데이터는 각 날짜 조건에 맞는
테이블 쪽으로 입력이 되는 겁니다.
mysql 이나 mariadb 하고 파티셔닝이 약간 차이가 있습니다.

출처 : http://m.blog.daum.net/khere/22
| |


      1 page / 2 page
번 호 카테고리 제 목 이름 조회수
44 postgresql , count(*) .... 바다아이 277
43 postgresql How to do an update + join in PostgreSQL?, 바다아이 246
42 postgresql sequence(퀀) 바다아이 279
41 postgresql , , , index create, , 바다아이 226
40 postgresql postgresql log_timezone .... 바다아이 600
39 postgresql postgresql SEQUENCE reset .... 바다아이 1887
38 postgresql [PostgreSql] WITH , , Operator 바다아이 1858
37 postgresql postgresql for, foreach , 바다아이 1979
36 postgresql postgresql , , into ... 바다아이 1929
35 postgresql postgresql PL/pgSQL - SQL Procedural Language, , 바다아이 4241
34 postgresql postgresql ... .. , , 바다아이 2678
33 postgresql postgresql CSV export/import 바다아이 2262
32 postgresql postgresql tablespace , .... 바다아이 3037
31 postgresql postgresql 10 partitioning, ... , ... 바다아이 2663
30 postgresql Using PostgreSQL Arrays, ... ... 바다아이 2452
29 postgresql PostgreSQL (TRIGGER) (function) 바다아이 2261
28 postgresql Optimize and Improve PostgreSQL Performance with VACUUM, ANALYZE, and REINDEX 바다아이 2987
27 postgresql postgresql tuple . vacuumdb .. , . 바다아이 2336
26 postgresql postgresql , .. 바다아이 2443
25 postgresql postgresql , size, 바다아이 3611
24 postgresql postgresql , , .... 바다아이 2126
23 postgresql PostgreSQL Replication, , , master, slave 바다아이 2885
22 postgresql postgresql case 바다아이 2238
21 postgresql postgresql with 바다아이 2243
20 postgresql postgresql , , string 바다아이 2642
현재글 postgresql Postgresql partitioning table , , , 바다아이 2581
18 postgresql PostgreSQL 바다아이 2597
17 postgresql postgresql vacuumdb, psql, pg_dump password crontab , pgpass 바다아이 2930
16 postgresql postgresql sequence 퀀 auto_increment . 바다아이 3406
15 postgresql postgresql , .... 바다아이 3799
| |








Copyright ⓒ 2001.12. bada-ie.com. All rights reserved.
이 사이트는 리눅스에서 firefox 기준으로 작성되었습니다. 기타 브라우저에서는 다르게 보일 수 있습니다.
[ FreeBSD + GoLang + PostgreSQL ]
서버위치 : 구글 클라우드 플랫폼  실행시간 : 0.25363
to webmaster... gogo sea. gogo sea.