[PostgreSQL] 일반 테이블 -> 파티션 테이블로 변경하기

● 준비 (단일 트랜잭션)
 (1) 새 파티션 테이블 생성
 (2) 기존 테이블명 변경
 (3) 기존 테이블과 이름이 같은 뷰 생성. 
 	-> 새 파티션 테이블과 이름이 바뀐 옛 일반 테이블을 모두 함께 조회하는 뷰
 (4) 새로 생성한 뷰에 insert, delete rule 추가
 (5) update에서 사용할 트리거 함수 생성, 새로만든 뷰에 트리거 지정

● 자료 이동 (단일 트랜잭션)
 (1) 기본키 단위로 하나씩 모든 자료에 대해서 update 구문을 실행

● 뒷정리
 (1) 뷰 삭제
 (2) 파티션 테이블을 뷰 이름 변경

 

다음은 뷰에 insert, delete 룰과 update 트리거를 지정하는 작업을 pgbench 테이블을 대상으로 한 것입니다.

 

-- 새 해쉬 파티션 하위 테이블 생성

BEGIN;
CREATE TABLE new_pgbench_accounts (
  aid INT NOT NULL PRIMARY KEY, 
  bid INT, 
  abalance INT, 
  filler CHAR(84)
) PARTITION BY hash (aid);

 

-- 하위 테이블 5개 생성

CREATE TABLE pgbench_accounts_0 PARTITION OF new_pgbench_accounts FOR 
VALUES 
  WITH (modulus 5, remainder 0);
CREATE TABLE pgbench_accounts_1 PARTITION OF new_pgbench_accounts FOR 
VALUES 
  WITH (modulus 5, remainder 1);
CREATE TABLE pgbench_accounts_2 PARTITION OF new_pgbench_accounts FOR 
VALUES 
  WITH (modulus 5, remainder 2);
CREATE TABLE pgbench_accounts_3 PARTITION OF new_pgbench_accounts FOR 
VALUES 
  WITH (modulus 5, remainder 3);
CREATE TABLE pgbench_accounts_4 PARTITION OF new_pgbench_accounts FOR 
VALUES 
  WITH (modulus 5, remainder 4);

 

-- 기존 테이블 rename

ALTER TABLE 
  pgbench_accounts RENAME TO old_pgbench_accounts;

 

-- 기존 테이블명으로 뷰 생성

CREATE VIEW pgbench_accounts AS 
SELECT 
  * 
FROM 
  old_pgbench_accounts 
UNION ALL 
SELECT 
  * 
FROM 
  new_pgbench_accounts;

 

-- 생성한 view에 insert rule / delete rule 추가

--insert rule
CREATE RULE pgbench_accounts_insert AS 
ON INSERT TO pgbench_accounts 
DO INSTEAD 
INSERT INTO new_pgbench_accounts (aid, bid, abalance, filler) 
VALUES 
  (new.*);

--delete rule
CREATE RULE pgbench_accounts_delete AS ON DELETE TO pgbench_accounts 
DO INSTEAD (
  DELETE FROM 
    new_pgbench_accounts 
  WHERE 
    (
      new_pgbench_accounts.aid = old.aid
    );
DELETE FROM 
  old_pgbench_accounts 
WHERE 
  (
    old_pgbench_accounts.aid = old.aid
  );
);

 

 

-- update 트리거추가  (with delete returing insert on conflict 룰을 지원하지 않아 트리거 생성)

CREATE FUNCTION tr_pgbench_accounts_update() RETURNS TRIGGER 
LANGUAGE plpgsql AS $$
begin 
delete from 
  old_pgbench_accounts 
where 
  aid = old.aid;
insert into new_pgbench_accounts 
values 
  (new.*) ON CONFLICT (aid) do 
update 
set 
  (aid, bid, abalance, filler) = row(EXCLUDED.*);
return new;
end;
$$;

CREATE TRIGGER tr_pgbench_accounts_update INSTEAD OF 
UPDATE 
  ON pgbench_accounts FOR EACH ROW 
  EXECUTE PROCEDURE tr_pgbench_accounts_update();
END;