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

標籤

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 功能增強 – 內建分割槽表》