7 postgresql 10版本 分区表使用场景、创建删除、注意事项

pg10版本支持的内置分区：范围分区（range），列表分区(list)，另外传统分区为触发器方式实现

为什么引入分区表

可以理解为分表，有这几种优势：

1、分区上顺序扫描提升性能，因为不会整体去扫描

2、可直接drop分区，delete分区是不一定清理数据的

3、使用分区表可以把数据放在不同磁盘上

使用分区表的时候要根据分区键来查询，才能提高效率，不使用分区键，会扫描所有子分区

重要参数：

constraint_exclusion:控制优化器，是否根据约束来优化查询

off:所有表不通过约束优化查询

partition:只对继承表和union all 子查询检查通过检索约束来优化查询

建议设置为partition

创建内置分区表

创建内置分区表的几个步骤：

1.创建父表，指定分区键和分区策略

2.创建分区，创建分区时须指定分区表的父表和子表的取值范围，注意分区键的范围不要有重叠

3.在分区上创建对应的索引，通常分区键上创建索引是必须的，非分区键的索引可以根据业务操作

创建内置分区表

指定分区策略为范围分区，分区键为create_time

postgres=# create table log_par (id serial ,user_id int4,create_time timestamp(0) without time zone) partition by range(create_time);
CREATE TABLE

创建分区，并设置分区的分区键范围

postgres=# create table log_par201706 partition of log_par for values from ( '2017-06-01') to ('2017-07-01');
CREATE TABLE
postgres=# create table log_par201707 partition of log_par for values from ( '2017-07-01') to ('2017-08-01');
CREATE TABLE
postgres=# create table log_par201708 partition of log_par for values from ( '2017-08-01') to ('2017-09-01');
CREATE TABLE
postgres=# create table log_par201709 partition of log_par for values from ( '2017-09-01') to ('2017-10-01');
CREATE TABLE
postgres=# create table log_par201710 partition of log_par for values from ( '2017-10-01') to ('2017-11-01');
CREATE TABLE
postgres=# create table log_par201711 partition of log_par for values from ( '2017-11-01') to ('2017-12-01');
CREATE TABLE
postgres=# create table log_par201712 partition of log_par for values from ( '2017-12-01') to ('2018-01-01');
CREATE TABLE

给所有分区创建分区键创建索引

postgres=# create index idx_log_par_201701_ctime on log_par201701 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_par_201702_ctime on log_par201702 using btree (create_time);
CREATE INDEX
postgres=# create index idx_log_par_201703_ctime on log_par201703 using btree (create_time);
CREATE INDEX

插入数据

postgres=#  insert into log_par(user_id,create_time) select round(100000000*random()),generate_series('2016-12-01'::date,'2017-12-01'::date, '1 minute');
INSERT 0 525601

查看表如下

postgres=# select count(*) from log_par;
 count
--------
 525601
(1 row)

postgres=# select count(*) from only log_par;
 count
-------
     0
(1 row)

添加分区

给log_par增加一个分区

postgres=# create table log_par_201801 partition of log_par for values from ( '2018-01-01') to ('2018-02-01');
CREATE TABLE
创建索引
postgres=# create index idx_log_par_par_201801_ctime on log_par_201801 using btree (create_time);
CREATE INDEX

删除分区

2种方法

drop table log_par_201801

另外一种解绑分区

alter table log_par attach partition log_par_201801 for values from ( '2018-01-01') to ('2018-02-01') ;

分区表删除分区，传统分区注意事项

删除分区

1.drop table 分区表表名；

2.将分区的继承关系去掉(比较稳妥)

postgres=# alter table log_ins_201801 no inherit log_ins;
ALTER TABLE

传统分区表注意事项

1.目前支持范围分区和列表分区

2.分区表上的索引，约束需要使用单独命令创建

3.不支持在分区表上定义全局组件

4.update时不建议更新分区键，数据会一个区移动到另一个区

5.性能方面，传统分区表性能差一点