目录

PostgreSQL实战之分区表

前言

1 分区表的意义

2 传统分区表

2.1 继承表

2.2 创建分区表

2.3 传统分区表注意事项

3 内置分区表

3.1 创建分区表

3.2 内置分区表注意事项

PostgreSQL实战之分区表

前言

分区表是关系型数据库提供的一个亮点特性，比如Oracle对分区表的支持已经非常成熟，广泛使用于生产系统，PostgreSQL也支持分区表，只是道路有些曲折，早在10版本之前PostgreSQL分区表一般通过继承加触发器方式实现，这种分区方式不能算是内置分区表，而且步骤非常烦琐，PostgreSQL10版本一个重量级的新特性是支持内置分区表，在分区表方面前进了一大步，目前支持范围分区和列表分区。为了便于说明，继承加触发器方式实现的分区表称为传统分区表，10版本提供的分区表称为内置分区表，本节将介绍这两种分区表的创建、性能测试和注意点。

1 分区表的意义

分区表主要有以下优势:

  1. 当查询或更新一个分区上的大部分数据时，对分区进行索引扫描代价很大，然而，在分区上使用顺序扫描能提升性能。
  2. 当需要删除一个分区数据时，通过DROP TABLE 删除一个分区，远比 DELETE删除数据高效，特别适用于日志数据场景。
  3. 由于一个表只能存储在一个表空间上，使用分区表后，可以将分区放到不同的表空间上，例如可以将系统很少访问的分区放到廉价的存储设备上，也可以将系统常访问的分区存储在高速存储上。

分区表的优势主要体现在降低大表管理成本和某些场景的性能提升，相比普通表性能有何差异？本章将对传统分区表、内置分区表做性能测试。

2 传统分区表

传统分区表是通过继承和触发器方式实现的，其实现过程步骤多，非常复杂，需要定义父表、定义子表、定义子表约束、创建子表索引、创建分区插入、删除、修改函数和触发器等，可以说是在普通表基础上手工实现的分区表。在介绍传统分区表之前先介绍继承，继承是传统分区表的重要组成部分。

2.1 继承表

PostgreSQL提供继承表，简单地说是首先定义一张父表，之后可以创建子表并继承父表，下面通过一个简单的例子来理解。
创建一张日志模型表tbl_log，如下所示:

mydb=> CREATE TABLE tbl_log(id int4,create_date date,log_type text);CREATE TABLE

之后创建一张子表tbl_log_sql用于存储SQL日志，如下所示:

mydb=> CREATE TABLE tbl_log_sql(sql text) INHERITS(tbl_log);CREATE TABLE

通过INHERITS(tbl_log)表示表tbl_log_sql继承表tbl_log，子表可以定义额外的字段，以上定义了sql为额外字段，其他字段则继承父表tbl_log，查看tbl_log_sql表结构，如下所示:

mydb=> \d tbl_log_sql       Table "pguser.tbl_log_sql"Column        |    Type    |  collation   |   Nullable   | Default--------------+-—----------+-—------------+--------------+--------id            |  integer   |              |              | create_date   |   date     |              |              |log_type      |   text     |              |              |sql           |   text     |              |              |Inherits: tbl_log

从以上看出 tbl_log_sql表有四个字段，前三个字段和父表tbl_log一样，第四个字段sql为自定义字段，以上 Inherits: tbl_log 信息表示继承了表tbl_log。
父表和子表都可以插入数据，接着分别在父表和子表中插入一条数据，如下所示:

mydb=> INSERT INTO tbl_log VALUES (1,'2017-08-26', null) ;INSERT 0 1mydb=> INSERT INTO tbl_log_sql VALUES(2,'2017-08-27', null, 'select 2');INSERT 0 1

这时如果查询父表tbl_log会显示两表的记录，如下所示:

mydb=> SELECT * FROM tbl_log;id       |   create_date    |   log_type---------+------------------+--------------1        |    2017-08-26    |2        |    2017-08-27    |(2 rows)

尽管查询父表会将子表的记录数也列出，但子表自定义的字段没有显示，如果想确定数据来源于哪张表，可通过以下SQL查看表的OID，如下所示:

mydb=> SELECT * FROM tbl_log;tableoid  |  id  |   create_date    |   log_type----------+------------------+-------------- 16854    |  1   |    2017-08-26    | 16860    |  2   |    2017-08-27    |(2 rows)

tableoid是表的隐藏字段，表示表的OID，可通过pg_class系统表关联找到表名，如下所示:

mydb=> SELECT p.relname , c.*FROM tbl_log c, pg_class pWHERE c.tableoid = p.oid; relname     |   id   |    create_date    |   log_type-------------+--------+-------------------+------------tbl_log      |   1    |    2017-08-26     |tbl_log_sql  |   2    |    2017-08-27     |(2 row)

如果只想查询父表的数据，需在父表名称前加上关键字ONLY，如下所示:

rmydb=> SELECT * FROM ONLY tbl_log;id    |  create_date |  log_type1     |  2017-08-26l |(1 row)

因此，对于UPDATE、DELETE、SELECT操作，如果父表名称前没有加ONLY，则会对父表和所有子表进行DML操作，如下所示:

mydb=> DELETE FROM tbl_log;DELETE 2mydb-> SELECT count (* )FROMtbl_log; count----—--0(1 row)

从以上结果可以看出父表和所有子表数据都被删除了。
违对于使用了继承表的场景，对父表的UPDATE、DELETE的操作需谨慎，因为会对父表和所有子表的数据进行DML操作。

2.2 创建分区表

接下来介绍传统分区表的创建，传统分区表创建过程主要包括以下几个步骤。

  1. 步骤1 创建父表，如果父表上定义了约束，子表会继承，因此除非是全局约束，否则不应该在父表上定义约束，另外，父表不应该写入数据。
  2. 步骤2 通过INHERITS方式创建继承表，也称之为子表或分区，子表的字段定义应该和父表保持一致。
  3. 步骤3 给所有子表创建约束，只有满足约束条件的数据才能写入对应分区，注意分区约束值范围不要有重叠。
  4. 步骤4 给所有子表创建索引，由于继承操作不会继承父表上的索引，因此索引需要手工创建。
  5. 步骤5 在父表上定义INSERT、DELETE、UPDATE触发器，将SQL分发到对应分区，这步可选，因为应用可以根据分区规则定位到对应分区进行DML操作。
  6. 步骤6 启用constraint_exclusion参数，如果这个参数设置成off，则父表上的SQL性能会降低，后面会通过示例解释这个参数。

以上六个步骤是创建传统分区表的主要步骤，接下来通过一个示例演示创建一张范围分区表，并且定义年月子表存储月数据。
以上六个步骤是创建传统分区表的主要步骤，接下来通过一个示例演示创建一张范围分区表，并且定义年月子表存储月数据。
首先创建父表，如下所示:

CREATE TABLE log_ins (id serial,user _id int4,create_time timestamp (0) without time zone) ;

创建13张子表，如下所示:

CREATE TABLE log_ins_history(CHECK (create_time = '2017-01-01' and create_time= '2017-02-01' and create_time= '2017-12-01' and create_time<'2018-01-01"))INHERITS(log__ins);中间省略了部分脚本,给子表创建索引，如下所示:CREATE 工NDEX idx_his_ctime oN log_ins_history USING btree (create_time);CREATE 工NDEX idx_log_ins_201701_ctime ON log_ins_201701 USING btree (create_time);CREATE 工NDEX idx_log_ins_201702_ctime oN log_ins_201702 USING btree (create_time);...CREATE INDEX idx_log_ins_201712_ctime ON log_ins_2017120SING btree (create_time);

由于父表上不存储数据，可以不用在父表上创建索引。
创建触发器函数，设置数据插入父表时的路由规则，如下所示:

CREATE OR REPLACE FUNCTION log_ins_insert_trigger ()RETURNS triggerLANGUAGE plpgsqlAS $function$BEGIN    IF   (NEW. create_time ='2017-01-01' and NEw.create_time='2017-02-01' and NEW.create_time='2017-12-01' and NEW.create_time<'2018-01-01' ）THEN        INSERT INTO log_ins_201712 VALUES(NEw.* ); ELSE RAISE EXCEPTION 'create_time out of range. Fix the log_ins_insert_        trigger ( function ! " ; END IF; RETURN NULL;END;$function$ ;

函数中的new.*是指要插人的数据行，在父表上定义插入触发器，如下所示:

CREATE TRIGGER insert_log_ins_trigger BEFORE INSERT ON log_ins FOR EACH ROw  EXECUTE PROCEDURE log_ins_insert_trigger ();

触发器创建完成后，往父表log_ins插入数据时，会执行触发器并触发函数log_ins_insert_trigger()将表数据插入到相应分区中。DELETE、UPDATE触发器和函数创建过程和INSERT方式类似，这里不再列出，这步完成之后，传统分区表的创建步骤已全部完成。

注意：父表和子表都可以定义主键约束，但会带来一个问题，由于父表和子表的主键约束是分别创建的，那么可能在父表和子表中存在重复的主键数据，这对整个分区表说来做不到主键唯一，举个简单的例子，假如在父表和所有子表的user_id字段上创建主键，父表与子表及子表与子表之间可能存在相同的user_id，这点需要注意。

2.3 传统分区表注意事项

传统分区表的使用有以下注意事项:

  • 当往父表上插入数据时，需事先在父表上创建路由函数和触发器，数据才会根据分区键路由规则插人到对应分区中，目前仅支持范围分区和列表分区。
  • 分区表上的索引、约束需要使用单独的命令创建，目前没有办法一次性自动在所有分区上创建索引、约束。
  • 父表和子表允许单独定义主键，因此父表和子表可能存在重复的主键记录，目前不支持在分区表上定义全局主键。
  • UPDATE时不建议更新分区键数据，特别是会使数据从一个分区移动到另一分区的场景，可通过更新触发器实现，但会带来管理上的成本。
  • 性能方面：根据本节的测试数据和测试场景，传统分区表根据非分区键查询相比普通表性能差距较大，因为这种场景下分区表会扫描所有分区;根据分区键查询相比普通表性能有小幅降低，而查询分区表子表性能相比普通表略有提升;

3 内置分区表

PostgreSQL10一个重量级新特性是支持内置分区表，用户不需要预先在父表上定义INSERT、DELETE、UPDATE 触发器，对父表的DML操作会自动路由到相应分区，相比传统分区表大幅度降低了维护成本，目前仅支持范围分区和列表分区，本小节将以创建范围分区表为例，演示 PostgreSQL10内置分区表的创建、使用与性能测试。

3.1 创建分区表

创建分区表的主要语法包含两部分:创建主表和创建分区。创建主表语法如下:

CREATE TABLE table_name ( ... )  { PARTITION BY { RANGE | LIST } ( { column_name | ( expression ) }

创建主表时须指定分区方式，可选的分区方式为RANGE范围分区或LIST列表分区,并指定字段或表达式作为分区键。
创建分区的语法如下:

CREATE TABLE table_namePARTITION OF parent_table [ () ] FOR VALUES partition_bound_spec

创建分区时必须指定是哪张表的分区，同时指定分区策略partition_bound_spec，如果是范围分区，partition_bound_spec须指定每个分区分区键的取值范围，如果是列表分区partition_bound_spec，需指定每个分区的分区键值。
PostgreSQL10创建内置分区表主要分为以下几个步骤：

  1. 创建父表，指定分区键和分区策略。
  2. 创建分区，创建分区时须指定分区表的父表和分区键的取值范围，注意分区键的范围不要有重叠，否则会报错。
  3. 在分区上创建相应索引，通常情况下分区键上的索引是必须的，非分区键的索引可根据实际应用场景选择是否创建。

3.2 内置分区表注意事项

使用内置分区表有以下注意事项:

  1. 当往父表上插入数据时，数据会自动根据分区键路由规则插入到分区中，目前仅支持范围分区和列表分区。
  2. 分区表上的索引、约束需使用单独的命令创建，目前没有办法一次性自动在所有分区上创建索引、约束。
  3. 内置分区表不支持定义（全局）主键，在分区表的分区上创建主键是可以的。
  4. 内置分区表的内部实现使用了继承。
  5. 如果UPDATE语句的新记录违反当前分区键的约束则会报错，UPDAET语句的新记录目前不支持跨分区的情况。
  6. 性能方面:根据本节的测试场景，内置分区表根据非分区键查询相比普通表性能差距较大，因为这种场景分区表的执行计划会扫描所有分区;根据分区键查询相比普通表性能有小幅降低，而查询分区表子表性能相比普通表略有提升。