postgresql分妺_postgresql 分表

一：概述

在客户数据积累到一定程度后，尤其是单表数据达到千万级别的时候，随着查询速度越来越慢以及数据管理等需求，就要考虑分表，pg 的分表把逻辑上的一个大表分割成物理上的几块，分表后，不仅带来查询速度的提升，数据管理与维护也方便了许多(把逻辑上很大的一块按照某个维度分成了 N 个小块)。

二：分表

2.1 创建主表

create table t_student(

student_id serial8 primary key,

name varchar(255),

age int4,

create_date date

)

2.2 创建分区表

create table t_student_1_1000(check (student_id >=1 and student_id <1000)) inherits(t_student);

create table t_student_1000_2000(check (student_id >=1000 and student_id <2000)) inherits(t_student);

create table t_student_2000_3000(check (student_id >=2000 and student_id <3000)) inherits(t_student);

2.3 创建触发器函数

create or replace function t_student_insert_trigger()

returns trigger

language plpgsql

as

    begin       if(new.student_id >=1 and new.student_id < 1000) then       insert into t_student_1_1000 values(new.*);      elsif(new.student_id >=1000 and new.student_id < 2000) then       insert into t_student_1000_2000 values(new.*);      elsif(new.student_id >=2000 and new.student_id < 3000) then       insert into t_student_2000_3000 values(new.*);      else       raise exception 'student_id out of range.Fix the t_student_insert_trigger() function!';      end if;     return null;     end;      ；

2.4 创建触发器

create trigger insert_t_student_parttion_trigger before insert on t_student for each row execute procedure t_student_insert_trigger();

三：测试

3.1 插入数据

insert into t_student (student_id,name,age,create_date) values(1,'zhangsan',21,now());

insert into t_student (student_id,name,age,create_date) values(2,'lisi',21,now());

insert into t_student (student_id,name,age,create_date) values(1000,'wangwu',21,now());

insert into t_student (student_id,name,age,create_date) values(1001,'zhaoliu',21,now());

insert into t_student (student_id,name,age,create_date) values(2000,'zhaoqi',21,now());

insert into t_student (student_id,name,age,create_date) values(2001,'wangba`',21,now());

3.2 查看数据

3.2.1 查看主表数据

postgres=# select * from t_student;

student_id | name | age | create_date

------------+----------+-----+-------------

1 | zhangsan | 21 | 2018-09-11

2 | lisi | 21 | 2018-09-11

1000 | wangwu | 21 | 2018-09-11

1001 | zhaoliu | 21 | 2018-09-11

2000 | zhaoqi | 21 | 2018-09-11

2001 | wangba` | 21 | 2018-09-11

3.2.2 查看分区表数据

postgres=# select * from t_student_1_1000 ;

student_id | name | age | create_date

------------+----------+-----+-------------

1 | zhangsan | 21 | 2018-09-11

2 | lisi | 21 | 2018-09-11

(2 rows)

postgres=# select * from t_student_1000_2000 ;

student_id | name | age | create_date

------------+---------+-----+-------------

1000 | wangwu | 21 | 2018-09-11

1001 | zhaoliu | 21 | 2018-09-11

(2 rows)

postgres=# select * from t_student_2000_3000 ;

student_id | name | age | create_date

------------+---------+-----+-------------

2000 | zhaoqi | 21 | 2018-09-11

2001 | wangba` | 21 | 2018-09-11

3.3 执行

postgres=# explain select * from t_student where student_id between 1 and 10;

QUERY PLAN

-------------------------------------------------------------------------

Append (cost=0.00..12.10 rows=2 width=532)

-> Seq Scan on t_student (cost=0.00..0.00 rows=1 width=532)

Filter: ((student_id >= 1) AND (student_id <= 10))

-> Seq Scan on t_student_1_1000 (cost=0.00..12.10 rows=1 width=532)

Filter: ((student_id >= 1) AND (student_id <= 10))

(5 rows)

四：注意事项

4.1 主表一定不能有数据，如果在已有数据的表上做分表操作的话，先对原来的表数据进行备份，然后进行表数据删除处理，等分表跟触发器都创建完毕后，再从备份恢复数据，数据就会插入到各自的分区表里

4.2 一定要注意触发器函数的 range 范围，最好把未来几年的全部创建好，否则插入数据超出范围的话会报 exception，如果插入新的分区表，只需要创建新的分区表跟更新触发器函数即可

4.3 每个业务的 range 定义字段都有所不同，id 分的话，每个分区表的数据都会比较均匀，也可以按照时间字段等去划分

4.4 where 查询如果字段包含分区字段的话，会去相关的分区表里去检索数据，如果有其他常用的检索字段的话，请自行在分区表里增加索引字段提高更快速的检索速度

五：参考资料

pg 官方文档：https://www.postgresql.org/docs/10/static/ddl-partitioning.html