PostgreSQL 用 CTE语法 + 继承 实现平滑拆分大表
德哥 2017-12-04 20:05:48 浏览70 评论0 发表于： 阿里云数据库ApsaraDB数据存储与数据库 PostgreSQL postgres plpgsql 分区表 Create
摘要： 标签 PostgreSQL , 拆分大表 , 继承 , cte 背景 业务设计初期可能不会考虑到表将来会有多大，或者由于数据日积月累，单表会变得越来越大。 后面在考虑分区的话，应该怎么将单表切换成分区表呢？ 这里可以用到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 10 内置分区 vs pg_pathman perf profiling》
《PostgreSQL 10.0 preview 功能增强 - 内置分区表》
本文为云栖社区原创内容，未经允许不得转载，如需转载请发送邮件至yqeditor@list.alibaba-inc.com；如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件至：yqgroup@service.aliyun.com 进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容。用云栖社区APP，舒服~
【云栖快讯】2017互联网超级工程阿里双11完美落幕，交易额突破1682亿，但阿里工程师如何玩转“超级工程”，背后黑科技又是如何？12月13-14日，12位大咖直播分享揭秘1682亿背后技术实践，马上预约 详情请点击 评论文章 (0) (0) (0)