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 功能增强 - 内置分区表》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章