PostgreSQL 内置分区

内置分区表

在 PostgreSQL10 以前，都是通过表的继承和触发器来完成分区功能，在 PostgreSQL10 中提供对内置分区表的支持，目前只支持范围分区表和列表分区表。

范围分区：

范围分区，适用于针对某一字段数据的一定范围(比如时间)进行分区：

创建表：

-- 父表
create table public.user_per(
user_id int4,
user_name text,
create_time date
) partition by range (create_time);
-- 分区表
create table 
public.user_per_201501 partition of user_per 
  for values from (minvalue) to ('2015-01-01');
-- 分区表
create table 
public.user_per_201601 partition of user_per 
  for values from ('2015-01-01') to ('2016-01-01');
-- 分区表
create table public.user_per_201701 
partition of user_per 
  for values from ('2016-01-01') to ('2017-01-01');

插入数据

insert into 
user_per(user_id, user_name, create_time) 
values 
     (1001, 'aladdin', '2000-10-15'), 
     (1002, 'bilib', '2016-04-01');

查询数据

select * from user_per; -- + where create_time ...
-- 验证是否已被分区
select * from user_per_201501;
select * from user_per_201701;

删除分区

-- 将数据一起删除的暴力方法
drop table user_per_201501;
-- 温和的方式是将分区与父分区进行解绑，分区和数据都没有被删除，只是对 user_per 不在适用
alter table user_per detach partition user_per_201701;
-- 将分区重新绑定到父分区
alter table 
user_per attach partition user_per_201701 
  for values from ('2016-01-01') to ('2017-01-01');

创建索引

-- 分区索引
create index 
on user_per_201501 
  using btree(user_name);

列表分区：

列表分区，是针对字段数据的某几个值(比如地区)进行分区：

创建表：

-- 父表
create table user_per(
user_id int4,
user_province text
) partition by list(user_province);
-- 单值分区表
create table 
user_per_ln partition of user_per 
  for values in ('辽宁');
-- 多值分区表
create table 
user_per_hz partition of user_per 
  for values in ('河南', '湖北', '湖南');

插入数据

insert into
user_per(user_id, user_province) 
VALUES 
       (1001, '辽宁'), 
       (1002, '河南');

其他操作

参考范围分区...

关于 constraint_exclusion 参数：

内置分区表的查询计划受 constraint_exclusion 参数的影响，不启动 constraint_exclusion，分区表的查询计划将会对所有分区都进行查询(全表扫描)，失去了分区剪枝的功能。

如果启动 constraint_exclusion，对所有表都会启动，将会增大服务器压力，一般(PG 实战 / 网上)都推荐将 constraint_exclusion 参数设置为 partition：

set constraint_exclusion = partiton;
-- 其它配置
set constraint_exclusion = on;
set constraint_exclusion = off;

内置分区表的性能

在《PostgreSQL 实战》中，作者对启动内置分区表和不启动分区进行了测试。测试结果是，启动内置分区表的性能比没有提高，相比较不启动分区性能好像还有点下降。

但是不启动分区，无法直接对类似于 "user_per_ln" 这样的子表进行查询，而是需要全表扫描数据，各有利弊而已，真正使用需要根据实际情况进行衡量。

PostgreSQL 的继承

PostgreSQL 传统的 (10版本之前) 都是通过表继承+触发器等操作来完成的，虽然 10 版本字后有了改变，但是对于没有接触过表继承相关概念的人来说，很难理解这个过程的实现细节：

-- 父表
create table tbl_user(
  id int4,
  name text
);
-- 子表
create table tbl_user_ln (tags text[]) inherits (tbl_user); -- 继承 tbl_user 的字段，也可以新增字段

插入数据：

insert into 
  tbl_user_ln (id, name, tags) 
  values 
         (1001, 'aladdin', '{"Smoking", "Drinking"}');

查询：

-- 通过查询父表，可以查到数据
select * from tbl_user;
-- 也可以通过查询字表查询数据
select * from tbl_user_ln;

最后编辑于：2019.04.13 09:32:33