Postgres分区表 二

Postgres 分区表二

一、背景

  在上一篇《Postgres分区表一》中，我们提到了Postgres在10.0版本后创建及使用分区表的方式有了一定的差异，本文将主要介绍Postgres 10.0后分区表的创建和使用。
  在10.0版本之前我们创建分区后，数据的写入需要直接指定分区表、如果写入操作发生在主表上，那么数据不能自动存储到对应的分区表（不使用触发器）、即使使用触发器，对于分区发生变化，需要频繁更改触发器,为解决该问题,有大神提出了识别分区字段并在写入时检测分区，从而动态创建分区并写入数据到分区表。但实际使用发现，插入性能较差，作者对通过触发器自动创建分区表的方式入库数据进行性能测试，插入1000w数据到10个分区，耗时超过1200s，然而使用新版本（10.0及其之后的版本）的方式创建的分区表，同等的数据入库，耗时7s。

二、分区表管理

2.1分区表概述

  10.0版本及其之后创建分区表需要指定分区键，分区支持range分区和List分区两种，11.0之后还增加了Hash分区。
  range分区用于对数据根据分区键进行范围分区，例如我们数据为每天更新的数据天粒度数据，此时要将数据按照月份进行分区时，可以采用range分区，指定分区字段在该分区的最小值以及最大值即可。
  List分区则是根据分区键的枚举值进行分区，例如我们的数据是学生的成绩信息，此时可以根据不同年级进行分区，如：每个年级一个分区，或者可以将多个年级合成一个分区，比如，1年级和2年级放到同一个分区，此时将分区的枚举值中存入1年级和2年级的枚举值即可。
  Hash分区则是根据分区键的hash值进行分区，hash分区需要在创建主表的时候设定分区的数目，且分区创建后，不能删除、新增。
  注意的是，不管是以上哪种分区方式，都要手动创建分区，11.0之后的版本还要额外创建默认分区。当写入的数据不满足任何一个手动创建的分区（非默认分区）时，数据会写入到默认分区当中。

​ 所有分区均支持直接写入对应的分区（指定子分区表名）以及不指定分区（指定主分区表名），指定子分区写入时，需注意分区键的值是否在子分区的分区范围内。

2.2 创建分区表

2.2.1 range 分区

语法：

-- 主表
CREATE TABLE table_name ( column_name data_type )
    PARTITION BY RANGE ( { column_name } [, ... ] )
 
-- 子表
CREATE TABLE table_name
    PARTITION OF parent_table
FOR VALUES
    FROM ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] )
      TO ( { numeric_literal | string_literal | TRUE | FALSE | MINVALUE | MAXVALUE } [, ...] ) 

示例1（单个字段分区）：

--创建主表 test_range 使用date字段作为分区键--
create table test_range(id int, date varchar(255)) partition by range ( date );
--创建分区，需指定分区存储的分区键的值的范围，分区范围包含上界不包含下界，所有的分区表都应创建默认分区，避免分区键的值不包含在任意一个分区时，导致写入失败，创建默认分区后，不在已定义的分区范围内的数据，将会写入默认分区--
create table test_range_default partition of test_range default;
create table test_range_202101 partition of test_range for values from ('2021-01-01') to ('2021-02-01');
create table test_range_202102 partition of test_range for values from ('2021-02-01') to ('2021-03-01');
--向主表写入测试数据，注：向主表写入数据时，会自动将数据根据分区键写入到对应的分区中，若上一步中默认分区未创建，则本条insert语句会执行失败原因是(3,'2021-03-01')中分区值2021-03-01不在任何一个分区中--
insert into test_range(id,date)values (1,'2021-02-01'),(2,'2021-01-01'), (3,'2021-03-01');
--向单个分区写入数据，注：单个分区写入时，需确保分区键对应的值在写入分区的分区键值范围内--
insert into test_range_202102(id,date)values (4,'2021-02-02');

示例2（多个字段分区）：

--创建主表 test_range_2 使用x,y两个字段作为分区键--
create table test_range_2(x int, y int) partition by range ( x,y );
--创建分区，需指定分区存储的分区键的值的范围,分区范围为：((x IS NOT NULL) AND (y IS NOT NULL) AND ((x > x_min) OR ((x = x_min) AND (y >= y_min))) AND ((x < x_max) OR ((x = x_max) AND (y < y_max))))，所有的分区表都应创建默认分区，避免分区键的值不包含在任意一个分区时，导致写入失败，创建默认分区后，不在已定义的分区范围内的数据，将会写入默认分区--
create table test_range_2_default partition of test_range_2 default;
create table test_range_2_20101 partition of test_range_2 for values from (2010,1) to (2010,7);
create table test_range_2_20102 partition of test_range_2 for values from (2010,7) to (2010,12);
create table test_range_2_20111 partition of test_range_2 for values from (2011,1) to (2011,7);
create table test_range_2_20112 partition of test_range_2 for values from (2011,7) to (2011,12);
--向主表写入测试数据
insert into test_range_2(x,y)values (2010,1),(2010,7), (2011,1),(2011,8);

2.2.2 List 分区

语法：

-- 主表
CREATE TABLE table_name ( column_name data_type )
    PARTITION BY LIST ( { column_name } )
-- 子表
CREATE TABLE table_name
    PARTITION OF parent_table
FOR VALUES
    IN ( { numeric_literal | string_literal | TRUE | FALSE | NULL } [, ...] )

示例：

--创建主表以date字段作为分区--
create table test_list(id int, date varchar(255)) partition by list( date );
--创建默认分区，默认分区作为缺省分区，若写入的数据的分区键未能匹配已创建的分区的分区范围，则数据写入到默认分区中--
create table test_list_default partition of test_list default;
create table test_list_20210101 partition of test_list for values in ('2021-01-01'); 
create table test_list_20210102 partition of test_list for values in ('2021-01-02');
--写入数据--
insert into test_list(id,date)values (1,'2021-01-01'),(2,'2021-01-02'), (3,'2021-03-01');

2.2.3 Hash 分区

语法：

-- 主表
CREATE TABLE table_name ( column_name data_type )
    PARTITION BY HASH ( { column_name } [, ... ] )
-- 子表
CREATE TABLE table_name
    PARTITION OF parent_table 
FOR VALUES
    WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

Hash分区不支持默认分区，且最大分区=modulus值，每个分区的modulus 需保持一致

示例：

--创建主表以date字段作为分区--
create table test_hash(id int, date varchar(255)) partition by hash( id );
--创建分区
create table test_hash_0 partition of test_hash for values with(modulus 4, remainder 0);
create table test_hash_1 partition of test_hash for values with(modulus 4, remainder 1);
create table test_hash_2 partition of test_hash for values with(modulus 4, remainder 2);
create table test_hash_3 partition of test_hash for values with(modulus 4, remainder 3);
--报错：remainder for hash partition must be less than modulus-
create table test_hash_4 partition of test_hash for values with(modulus 4, remainder 4);
--写入数据--
insert into test_hash(id,date)values (1,'2021-01-01'),(2,'2021-01-02'), (3,'2021-03-01');

2.3 分区管理（ATTACH/DETACH）

2.3.1 DETACH（解除主表和分区表的关联）

语法：

ALTER TABLE name DETACH PARTITION partition_name

示例：

ALTER TABLE test_hash DETACH PARTITION test_hash_3

2.3.1 ATTACH（关联主表和分区）

语法：

ALTER TABLE name ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }

示例：

--创建分区--
create table test_hash_attach (date varchar(255));
--关联分区到主表--
alter table test_hash attach partition test_hash_attach for values with (modulus 4, remainder 3);

2.4 注意事项

1. 仅range分区和list分区支持默认分区，hash分区则不支持默认分区，默认分区适用于根据分区键找不到对应的分区时，避免写入失败，实际使用中应排查找不到对应分区的原因。
2. 分区表也可以作为主表，即：分区也分为1级分区、2级分区、三级分区…，每个分区又可作为主表，在其下创建子分区
3. 分区主表可以有索引
4. 分区主表可以创建唯一约束，需要包含分区键
5. 分区主表可以创建外键，但是不能参照分区表创建外键
6. 分区主表可以创建 FOR EACH ROW 触发器
7. 当更新语句执行结果修改了数据行的分区键，如果数据行分区键仍然满足当前分区的约束，则数据行原分区的记录数无变化，如果数据行的分区键不满足当前分区的约束，则会从原分区移除，写入到分区约束满足更新后的分区键的分区中