轻松搞定PostgreSQL表分区

---

前言

之前使用PostgreSQL数据库实现表分区需要用继承的方式来实现，还需要定义一个trigger或者rule把对主表的数据插入操作重定向到对应的分区表，极其繁琐。今天，重点介绍PostgreSQL10 以后版本的一个重量级新特性——分区表。

---

一、什么是分区表？

　数据库表分区实际上是把一个大的物理表分成若干个小的物理表，并使得这些小物理表在逻辑上可以被当成一张表来使用。
PostgreSQL 内置分区表目前支持以下两种形式分区：
1、列表分区（List Partitioning）
　　通过列出每个分区中出现的键值，可以对表进行分区。
2、范围分区（Range Partitioning）
　　每个分区表包含一个或多个字段组合的一部分，并且每个分区表的范围互不重叠。

二、列表分区

1.创建主表

代码如下：这里设置是根据create_time进行表分区

CREATE TABLE info_list (
    id bigint NOT NULL,
    protocol varchar(16),
    ip varchar(50),
    create_time timestamp
) partition by list(create_time);

2.创建分表

代码如下：数据会按照规则插入到不同分区表中,如果插入一条不符合规则的数据,则会报错:no partition of relation “info_list” found for row

create table info_list20200801 partition of info_list for values in ('2020-08-01');
create table info_list20200802 partition of info_list for values in ('2020-08-02');
create table info_list20200803 partition of info_list for values in ('2020-08-03');

3.分表创建索引

实际上，以上2步已经完成分区表的创建。索引并不是严格要求必须创建的，但在大部分场景下，为每一个分表在主要的列上创建索引是非常必要的。

CREATE INDEX idx_info_list20200801 ON info_list20200801 (create_time);
CREATE INDEX idx_info_list20200802 ON info_list20200802 (create_time);
CREATE INDEX idx_info_list20200803 ON info_list20200803 (create_time);

三、范围分区

范围分区与列表分区类似，重复列表分区的3步，做些小修改，参见下面代码。
注意:如分表的范围为2020-08-01至2020-08-02，则包含前者，不包含后者。相当于时a<=create_time<b。

--1、创建主表(根据create_time进行范围分区)
CREATE TABLE info_range (
    id bigint NOT NULL,
    protocol varchar(16),
    ip varchar(50),
    create_time timestamp
) partition by range(create_time);
--2、创建分表（根据下面表范围，如果插入2020-08-04,则会报错;如范围为2020-08-01至2020-08-02，则包含前者，不包含后者。相当于时a<=create_time<b;）
create table info_range20200801 partition of info_range for values from ('2020-08-01') to ('2020-08-02');
create table info_range20200802 partition of info_range for values from ('2020-08-02') to ('2020-08-03');
create table info_range20200803 partition of info_range for values from ('2020-08-03') to ('2020-08-04');

--3、创建索引
CREATE INDEX idx_info_range20200801 ON info_range20200801 (create_time);
CREATE INDEX idx_info_range20200802 ON info_range20200802 (create_time);
CREATE INDEX idx_info_range20200803 ON info_range20200803 (create_time);

四、分区管理

常用的分区表管理操作：断开分区、连接分区、删除分区。

1.断开分区

alter table info_range detach partition info_range20200803;

2.连接分区

alter table info_range attach partition info_range20200803 for values from ('2020-08-03') to ('2020-08-04');

3.删除分区

drop table info_range20200803;

---

总结

• PostgreSQL10之前的版本不支持内置分区表，若要实现分区功能，需通过继承的方式实现。
• 如果要充分使用分区表的查询优势，必须使用分区时的字段作为过滤条件。
• 除了在查询上的优势，分区表的使用也可提高删除数据的性能，因为删除一个分区要比删除分区上的所有数据要快的多。