PostgreSQL 10.x版本 分区表优缺点详细

参考：http://www.postgres.cn/news/viewone/1/271

分区字段选择：

如果分区的目的只是为了切分数据，那么通常分区的字段需要选择与实际入库时间更接近的那个字段，因为更好管理一些

 

1.分区表的说明

  操作性相当便捷。但只能在创建时决定是否为分区表，并决定分区条件字段，普通表创建后，不能在修改为分区表。Note：通过其他方法也可转化为分区表。和其他数据库一样，分区表作为一个DB的特性，优点自不用说，物理分散，逻辑统一。必须要注意的一个缺点是：分区表不允许其他表作为外键引用。只能在真实场景业务逻辑上当作外键，设计时论场景再推敲。同时，在Pg 11以前只能单独为每个分区表建立索引等，且不能在【母表】上建立主键、索引等。Pg 11以后可以针对“逻辑”分区表（母表）建立索引，分区子表自动创建。

 

2.分区表优点

• 某些类型的查询性能得到提升
• 更新的性能也可以得到提升，因为某块的索引要比在整个数据集上的索引要小
• 批量删除可以通过简单的删除某个分区来实现
• 可以将很少用的数据移动到便宜的、转速慢的存储介质上，修改tablespace

 

3.相关语法

• partition by 指定分区表的类型range或list指定分区列，或表达式作为分区键。
• range分区表键：支持指定多列、或多表达式，支持混合（键，非表达式中的列，会自动添加not null的约束）
• list分区表键：支持单个列、或单个表达式

 

4.创建分区主表和子表

--建表是先建主表，再建子表，子表以 PARTITION OF 方式说明和主表关系，约束条件应该就是后面的in里面

--range分区

CREATE TABLE dbo.table01 (
id bigserial NOT NULL,
cre_time timestamp without time zone,
note varchar(30)
) PARTITION BY RANGE (cre_time)
WITH (
OIDS = FALSE
);
CREATE TABLE dbo.table01_2018
PARTITION OF dbo.table01
FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00');
CREATE TABLE dbo.table01_2017
PARTITION OF dbo.table01
FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00');

--list分区

CREATE TABLE list_parted (
a int) 
PARTITION BY LIST (a);
CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN (1);
CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2);

 

5.删除分区

（1）直接删除子表，但是对父表会产生锁access exclusive lock（排它锁）

DROP TABLE dbo.table01_2017;

（2）删除分区

 

ALTER TABLE dbo.table01 DETACH PARTITION dbo.table01_2017;
  但是删除分区之后，子表还是存在的，还是需要删除子表
DROP TABLE dbo.table01_2017;

 

补充：

Greenplum删除分区：

alter table viid_facestatic.face_archive_20190501 DROP partition a20190501;

 

6.10版本分区表注意点

* 建立分区表时必需指定主表。

* 分区表和主表的列数量，定义必须完全一致。

* 分区表的列可以单独增加Default值，或约束。

* 当用户向主表插入数据库时，系统自动路由到对应的分区，如果没有找到对应分区，则抛出错误。

* 指定分区约束的值（范围，LIST值），范围，LIST不能重叠，重叠的路由会卡壳。

* 指定分区的列必需设置成not null,如建立主表时没设置系统会自动加上。

* Range分区范围为 >=最小值 and <最大值……

* 不支持通过更新的方法把数据从一个区移动到另外一个区，这样做会报错。如果要这样做的话需要删除原来的记录，再INSERT一条新的记录。

* 修改主表的字段名，字段类型时，会自动同时修改所有的分区。

* TRUNCATE 主表时，会清除所有继承表分区的记录，如果要清除单个分区，请对分区进行操作。

* DROP主表时会把所有子表一起给DROP掉，如果drop单个分区，请对分区进行操作。

* 使用psql能查看分区表的详细定义。