11 버전에서 일반 테이블을 파티션 테이블로 바꾸기

11 버전에서 일반 테이블을 파티션 테이블로 바꾸기

운영 환경에서 기존 테이블을 파티션 테이블로 바꾸기는 작업은 생각보다 까다롭습니다.

이 글은 지난 PGConf APAC 2019 에서 대형 테이블 다루기 발표자료에 대한 글쓴이 나름대로의 해석입니다.

시나리오는 다음과 같습니다.

• 준비 (단일 트랜잭션)
  
• 새 파티션 테이블을 만든다
  
• 기존 테이블을 다른 이름으로 바꾼다
• 기존 테이블과 이름이 같은 뷰를 만드는데, 그 내용은 새 파티션 테이블과 이름이 바뀐 옛 일반 테이블을 모두 함께 조회하는 것이다
  
• 새로 만든 뷰에 insert, delete rule을 추가하고
• update 에서 사용할 트리거 함수를 만들고, 새로 만든 뷰에 트리거를 지정한다.
• 자료 이동 (단일 트랜잭션)
  
• 기본키 단위로 하나씩 모든 자료에 대해서 update 구문을 실행한다
• 뒷 정리 (단일 트랜잭션)
  
• 뷰를 지우고,
  
• 파티션 테이블을 뷰 이름으로 바꾼다

BEGIN;
-- 새 해쉬 파티션 상위 테이블을 만들고
CREATE TABLE new_pgbench_accounts (
  aid INT NOT NULL PRIMARY KEY, 
  bid INT, 
  abalance INT, 
  filler CHAR(84)
) PARTITION BY hash (aid);

-- 다섯개의 하위 테이블을 만들고
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);

-- 기존 테이블의 이름을 바꾸고
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;

-- 그 뷰에 insert 룰 추가
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 룰 추가
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;