PostgreSQL用CTE語法+繼承實現拆分大表

背景
業務設計初期可能不會考慮到表將來會有多大，或者由於資料日積月累，單表會變得越來越大。

後面在考慮分割槽的話，應該怎麼將單表切換成分割槽表呢？

這裡可以用到PostgreSQL的CTE語法，以及繼承功能，還有內建的分割槽表功能。

例子
具體步驟

1、建立分割槽表

2、建立繼承關係，分割槽表繼承自需要拆分的表

3、用cte轉移資料

4、全部轉移完成後，在事務中切換表名

例子，將tbl_big切換成雜湊分割槽

1、建立被遷移的大表

create table tbl_big (id int primary key, info text, crt_time timestamp);

create index idx_tbl_big on tbl_big (crt_time);

insert into tbl_big select generate_series(1,10000000);
2、建立分割槽表

create table tbl ( like tbl_big including all ) ;

do language plpgsql

$$

declare
parts int := 4;
begin
for i in 0..parts-1 loop
execute format(`create table tbl%s (like tbl including all) inherits (tbl)`, i);
execute format(`alter table tbl%s add constraint ck check(mod(id,%s)=%s)`, i, parts, i);
end loop;
end;

$$

;

create or replace function ins_tbl() returns trigger as

$$

declare
begin
case abs(mod(NEW.id,4))
when 0 then
insert into tbl0 values (NEW.*);
when 1 then
insert into tbl1 values (NEW.*);
when 2 then
insert into tbl2 values (NEW.*);
when 3 then
insert into tbl3 values (NEW.*);
else
return NEW; — 如果是NULL則寫本地父表
end case;
return null;
end;

$$

language plpgsql strict;

create trigger tg1 before insert on tbl for each row when (NEW.id is not null) execute procedure ins_tbl();
3、分割槽表，繼承自被遷移的表

alter table tbl inherit tbl_big;
4、遷移資料

with tmp as (delete from only tbl_big returning ) insert into tbl select from tmp;
— 如果覺得這樣做太久了（一次遷移了所有記錄），可以拆成一個個小任務來做

— 一次遷移10萬條，多次呼叫來完成遷移。
with tmp as (delete from only tbl_big where ctid = any(array(select ctid from only tbl_big limit 100000)) returning ) insert into tbl select from tmp;
5、遷移完成後，切換表名。

postgres=# begin;

postgres=# lock table tbl_big in access exclusive mode ;

postgres=# select count(*) from only tbl_big;

count

0
(1 row)

postgres=# alter table tbl_big rename to tmp_tbl_big;

postgres=# alter table tbl no inherit tmp_tbl_big;

postgres=# alter table tbl rename to tbl_big;

postgres=# end;
參考
《PostgreSQL 傳統 hash 分割槽方法和效能》

《PostgreSQL 10 內建分割槽 vs pg_pathman perf profiling》

《PostgreSQL 10.0 preview 功能增強 – 內建分割槽表》