CREATE TABLE PARTITION

功能描述

创建分区表。分区表是把逻辑上的一张表根据某种方案分成几张物理块进行存储，这张逻辑上的表称之为分区表，物理块称之为分区。分区表是一张逻辑表，不存储数据，数据实际是存储在分区上的。

常见的分区方案有范围分区（Range Partitioning）、哈希分区（Hash/Key Partitioning）、列表分区（List Partitioning）、列分区（Range/List Columns Partitioning）等。目前行存表支持范围分区、哈希分区、列表分区。

范围分区（Range Partitioning）：

• 范围分区是根据表的一列或者多列，将要插入表的记录分为若干个范围，这些范围在不同的分区里没有重叠。为每个范围创建一个分区，用来存储相应的数据。
• 范围分区的分区策略是指记录插入分区的方式。目前范围分区仅支持范围分区策略。

• 范围分区策略：最常用的分区策略为根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则返回报错和提示信息。

哈希分区（Hash/Key Partitioning）：

• 哈希分区是根据表的一列，为每个分区指定模数和余数，将要插入表的记录划分到对应的分区中，每个分区所持有的行都需要满足条件：分区键的值除以为其指定的模数将产生为其指定的余数。
• 哈希分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则返回报错和提示信息。

列表分区（List Partitioning）：

• 列表分区是根据表的一列，将要插入表的记录通过每一个分区中出现的键值划分到对应的分区中，这些键值在不同的分区里没有重叠。为每组键值创建一个分区，用来存储相应的数据。
• 列表分区策略：根据分区键值将记录映射到已创建的某个分区上，如果可以映射到已创建的某一分区上，则把记录插入到对应的分区上，否则返回报错和提示信息。

分区存在以下优势：

• 某些类型的查询性能可以得到极大提升。特别是表中访问率较高的行位于一个单独分区或少数几个分区上的情况下。分区可以减少数据的搜索空间，提高数据访问效率。
• 当查询或更新一个分区的大部分记录时，连续扫描该分区而不是访问整个表可以获得巨大的性能提升。
• 如果需要大量加载或者删除的记录位于单独的分区上，则可以通过直接读取或删除该分区以获得巨大的性能提升，同时还可以避免由于大量DELETE导致的VACUUM超载（哈希分区不支持删除分区）。

注意事项

• 当为分布表添加唯一约束或主键约束时，如果约束的约束键包含所有分区键，将为约束创建LOCAL索引，否则创建GLOBAL索引。
• 目前哈希分区仅支持单列构建分区键，暂不支持多列构建分区键。
• 对于分区表PARTITION FOR (values)语法，values只能是常量。
• 对于分区表PARTITION FOR (values)语法，values在需要数据类型转换时，建议使用强制类型转换，以防隐式类型转换结果与预期不符。
• 分区数最大值为1048575个，一般情况下业务不可能创建这么多分区，这样会导致内存不足。应参照参数local_syscache_threshold的值合理创建分区，分区表使用内存大致为（分区数 * 3 / 1024）MB。理论上分区占用内存不允许大于local_syscache_threshold的值，同时还需要预留部分空间以供其他功能使用。
• 考虑性能影响，一般建议单表最大分区数不超过2000，子分区数 *（local索引个数 + 1） 不超过10000。
• 当分区数太多导致内存不足时，会间接导致性能急剧下降。
• 指定分区语句目前不能走全局索引扫描。
• 不支持如下数据类型作为分区键、二级分区键：BLOB、TINYBLOB、MEDIUMBLOB、LONGBLOB、TEXT、TINYTEXT、MEDIUMTEXT、LONGTEXT、BIT。

语法格式

CREATE TABLE [ IF NOT EXISTS ] partition_table_name
 ( [ 
    { column_name data_type [ {CHARACTER SET | CHAR SET | CHARSET} charset ] [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table 
] )       
    [ table_option [ [ , ] ... ] ]
    [ WITH ( {storage_parameter = value} [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
     PARTITION BY { 
        {RANGE [COLUMNS] (partition_key) [ PARTITIONS integer ] ( partition_less_than_item [, ... ] ) [partition_options] } |
        {RANGE [COLUMNS] (partition_key) [ PARTITIONS integer ] ( partition_start_end_item [, ... ] ) [partition_options] } |
        {LIST [COLUMNS] (partition_key)  [ PARTITIONS integer ] ( PARTITION partition_name VALUES [IN] (list_values)  [TABLESPACE [=] tablespace_name][, ... ] [partition_options] ）} |
        {{ HASH | KEY } (partition_key) [ PARTITIONS integer ] ( PARTITION partition_name ] [TABLESPACE [=] tablespace_name][partition_options] [, ... ]）}
    }; 

• 其中table_option为：

  AUTO_INCREMENT [=] value
    | [DEFAULT] CHARACTER SET [=] charset_name
    | [DEFAULT] COLLATE [=] collation_name
    | COMMENT [=] 'string'
    | ENGINE [=] engine_name
    | ROW_FORMAT [=] {DEFAULT | DYNAMIC | FIXED | COMPRESSED | REDUNDANT | COMPACT}
  }

  

  • 同一个地方多次设置COMMENT时仅最后一个生效。
  • 同一个地方多次设置AUTO_INCREMENT时仅最后一个生效。
  • 同一个地方多次设置COLLATE时仅最后一个生效。
  • ENGINE、ROW_FORMAT使用时语法不报错也没有提示，但实际不生效。
• 列约束column_constraint：

  [ CONSTRAINT constraint_name ]
  { NOT NULL |
    NULL | 
    CHECK ( expression ) | 
    DEFAULT default_expr | 
    ON UPDATE update_expr |
    [GENERATED ALWAYS] AS ( generation_expr ) [STORED | VIRTUAL] |
    AUTO_INCREMENT |
    COMMENT 'string' |
    UNIQUE [KEY] index_parameters | 
    PRIMARY KEY index_parameters |
    REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL  | MATCH SIMPLE ]
          [ ON DELETE action ] [ ON UPDATE action ] }

• 表约束table_constraint：

  [ CONSTRAINT [ constraint_name ] ]
  { CHECK ( expression ) | 
    UNIQUE [ index_name ][ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters | 
    PRIMARY KEY [ USING method ] ( { column_name [ ASC | DESC ] } [, ... ] ) index_parameters |
    FOREIGN KEY [ index_name ] ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
        [ MATCH FULL  | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }

• 索引存储参数index_parameters：

  [ WITH ( {storage_parameter = value} [, ... ] ) ]
  [ USING method ]
  [ COMMENT 'string']

参数说明

• IF NOT EXISTS

  如果已经存在相同名称的表，不会抛出一个错误，而会发出一个通知，告知表关系已存在。

• partition_table_name

  分区表的名称。

  取值范围：字符串，要符合标识符说明。

• column_name

  新表中要创建的字段名。

  取值范围：字符串，要符合标识符说明。

• data_type

  字段的数据类型。

• CONSTRAINT constraint_name

  列约束或表约束的名称。可选的约束子句用于声明约束，新行或者更新的行必须满足这些约束才能成功插入或更新。

  定义约束有两种方法：

  • 列约束：作为一个列定义的一部分，仅影响该列。
  • 表约束：不和某个列绑在一起，可以作用于多个列。constraint_name为可选项
• index_name

  索引名。

  

  • 对于外键约束，constraint_name和index_name同时指定时，索引名为constraint_name。
  • 对于唯一键约束，constraint_name和index_name同时指定时，索引名以index_name。
• USING method

  指定创建索引的方法。

  取值范围参考参数说明中的USING method。

  

  目前只支持索引方法为btree。

• ASC | DESC

  ASC表示指定按升序排序（默认）。DESC指定按降序排序。

• LIKE source_table

  LIKE子句声明一个表，新表自动从这个表里面继承所有字段名及其数据类型和非空约束。

  和INHERITS不同，新表与原来的表之间在创建动作完毕之后是完全无关的。在源表做的任何修改都不会传播到新表中，并且也不可能在扫描源表的时候包含新表的数据。

• AUTO_INCREMENT [ = ] value

  这个子句为自动增长列指定一个初始值，value必须为正整数，不得超过2¹²⁷-1。

• COMMENT [ = ] 'string'
  • COMMENT [ = ] 'string'子句表示给表添加注释。
  • 在column_constraint中的COMMENT 'string'表示给列添加注释。
  • 在table_constraint中的COMMENT 'string'表示给主键和唯一键对应的索引添加注释。

  具体请参见：•COMMENT[=]'string'。

• {CHARACTER SET | CHAR SET | CHARSET} charset

  指定表字段的字符集。单独指定时会将字段的字符序设置为指定的字符集的默认字符序。

• COLLATE collation
  COLLATE子句指定列的排序规则（字符序）（该列必须是可排列的数据类型）。如果没有指定，则使用默认的排序规则。排序规则可以使用“select * from pg_collation;”命令从pg_collation系统表中查询，默认的排序规则为查询结果中以default开始的行。支持utf8mb4_bin、utf8mb4_general_ci、utf8mb4_unicode_ci、binary、gbk_chinese_ci、gbk_bin、gb18030_chinese_ci、gb18030_bin字符序。具体请参见表级字符集和字符序

  

  • 仅字符类型支持指定字符集，指定为BINARY字符集或字符序实际是将字符类型转化为对应的二进制类型，若类型映射不存在则报错。当前仅有TEXT类型转化为BLOB的映射。
  • 字段字符集或字符序未显式指定时，若指定了表的默认字符集或字符序，字段字符集和字符序将从表上继承。
  • 除BINARY字符集、字符序外，当前仅支持指定与数据库编码相同的字符集。
• WITH ( storage_parameter [= value] [, ... ] )

  这个子句为表或索引指定一个可选的存储参数。参数的详细描述如下所示：

  • FILLFACTOR

    一个表的填充因子（fillfactor）是一个介于10~100的百分数。在Ustore存储引擎下，该值得默认值为92，在Astore存储引擎下默认值为100（完全填充）。如果指定了较小的填充因子，INSERT操作仅按照填充因子指定的百分率填充表页。每个页上的剩余空间将用于在该页上更新行，这就使得UPDATE有机会在同一页上放置同一条记录的新版本，这比把新版本放置在其他页上更有效。对于一个从不更新的表将填充因子设为100是最佳选择，但是对于频繁更新的表，选择较小的填充因子则更加合适。

    取值范围：10~100

  • ORIENTATION

    决定了表的数据的存储方式。当前M-compatibility模式数据库下只支持行存储方式。

    取值范围：

    ROW（缺省值）：表的数据将以行式存储。

    

    orientation不支持修改。

  • STORAGE_TYPE

    指定存储引擎类型，该参数设置成功后就不再支持修改。

    取值范围：

    • USTORE，表示表支持Inplace-Update存储引擎。特别需要注意，使用USTORE表，必须要开启track_counts和track_activities参数，否则会引起空间膨胀。
    • ASTORE，表示表支持Append-Only存储引擎。

    默认值：

    不指定表时，默认是Inplace-Update存储。

  • COMPRESSION

    行存表不支持压缩。

  • segment

    使用段页式的方式存储。本参数仅支持行存表。不支持临时表、unlog表。

    取值范围：on/off

    默认值：off

  • statistic_granularity

    记录该表在分析统计信息时的默认PARTITION_MODE。此参数对非分区表设置无效。

    取值范围：请参见PARTITION_MODE选项说明。

    默认值：AUTO。

    表1 PARTITION_MODE选项说明

    PARTITION_MODE选项	含义
    ALL	收集整表、一级分区的统计信息。
    GLOBAL	收集整表的统计信息。
    PARTITION	收集一级分区的统计信息。
    GLOBAL AND PARTITION	收集整表、一级分区的统计信息。
    ALL COMPLETE	收集整表、一级分区的统计信息。
    AUTO	缺省值。

• TABLESPACE tablespace_name

  指定新表将要在tablespace_name表空间内创建。如果没有声明，将使用默认表空间。

  注：需要在非M-Compatibility数据库中创建或删除tablespace。

• PARTITION BY RANGE [COLUMNS] (partition_key)

  创建范围分区。partition_key为分区键的名称。

  “PARTITION BY RANGE COLUMNS” 语义同 “PARTITION BY RANGE”。

  （1）对于从句是VALUES LESS THAN的语法格式：

  

  对于从句是VALUES LESS THAN的语法格式，范围分区策略的分区键最多支持16列。

  该情形下，分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、TINYINT UNSIGNED、SMALLINT UNSIGNED、INTEGER UNSIGNED、BIGINT UNSIGNED、DATE、YEAR、CHAR[(n)]、VARCHAR[(n)]、MEDIUMINT、MEDIUMINT UNSIGNED、BINARY[(n)]、VARBINARY(n)、DATETIME[(p)]、TIME[(p)]。

  （2）对于从句是START END的语法格式：

  

  对于从句是START END的语法格式，范围分区策略的分区键仅支持1列。

  该情形下，分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、TINYINT UNSIGNED、SMALLINT UNSIGNED、INTEGER UNSIGNED、BIGINT UNSIGNED、DATE。

• PARTITION partition_name VALUES LESS THAN {( { partition_value | MAXVALUE } [,...] ) | MAXVALUE } [TABLESPACE tablespace_name]

  指定各分区的信息。partition_name为范围分区的名称。partition_value为范围分区的上边界，取值依赖于partition_key的类型。MAXVALUE表示分区的上边界，它通常用于设置最后一个范围分区的上边界。

  

  • 每个分区都需要指定一个上边界。
  • 分区上边界的类型应当和分区键的类型一致。
  • 分区列表是按照分区上边界升序排列的，值较小的分区位于值较大的分区之前。
  • 不在括号内的MAXVALUE只能有一个分区键。
  • partition_value不支持使用表达式。
• PARTITION partition_name {START (partition_value) END (partition_value|MAXVALUE)} | {START(partition_value)} | {END (partition_value | MAXVALUE)} [TABLESPACE tablespace_name]

  指定各分区的信息，各参数意义如下：

  • partition_name：范围分区的名称或名称前缀，除以下情形外（假定其中的partition_name是p1），均为分区的名称。

    若该定义是第一个分区定义，且该定义有START值，则范围（MINVALUE, START）将自动作为第一个实际分区，其名称为p1_0，然后该定义语义描述的分区名称依次为p1_1, p1_2, ...。例如对于完整定义“PARTITION p1 START(1), PARTITION p2 START(2)”，则生成的分区是：(MINVALUE, 1), [1, 2) 和 [2, MAXVALUE)，其名称依次为p1_0, p1_1和p2，即此处p1是名称前缀，p2是分区名称。这里MINVALUE表示最小值。

  • partition_value：范围分区的端点值（起始或终点），取值依赖于partition_key的类型，不可是MAXVALUE。
  • MAXVALUE：表示最大值，它通常用于设置最后一个范围分区的上边界。
  

  1. 在创建分区表若第一个分区定义含START值，则范围（MINVALUE，START）将自动作为实际的第一个分区。
  2. START END语法需要遵循以下限制：
     • 每个partition_start_end_item中的START值（如果有的话，下同）必须小于其END值；
     • 相邻的两个partition_start_end_item，第一个的END值必须等于第二个的START值；
     • 每个分区包含起始值，不包含终点值，即形如：[起始值，终点值)，起始值是MINVALUE时则不包含；
     • 一个partition_start_end_item创建的每个分区所属的TABLESPACE一样；
     • partition_name作为分区名称前缀时，其长度不要超过57字节，超过时自动截断；
     • 在创建、修改分区表时请注意分区表的分区总数不可超过最大限制（1048575）；
  3. 在创建分区表时START END与LESS THAN语法不可混合使用。
  4. 即使创建分区表时使用START END语法，备份（gs_dump）出的SQL语句也是VALUES LESS THAN语法格式。
• PARTITION BY LIST [COLUMNS] (partition_key)

  创建列表分区。partition_key为分区键的名称。

  “PARTITION BY LIST COLUMNS” 语义同 “PARTITION BY LIST”。

  • 对于partition_key，不支持表达式。当不指定二级分区时，列表分区策略的分区键最多支持16列，当指定二级分区时，列表分区策略的分区键最多支持1列，且不支持表达式。
  • 对于从句是VALUES [IN] (list_values)的语法格式，list_values中包含了对应分区存在的键值，每个分区的键值数量不超过64个。
  • 从句"VALUES IN"语义同"VALUES"。

  分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、TINYINT UNSIGNED、SMALLINT UNSIGNED、INTEGER UNSIGNED、BIGINT UNSIGNED、DATE、YEAR、CHAR[(n)]、VARCHAR[(n)]、MEDIUMINT、MEDIUMINT UNSIGNED、BINARY[(n)]、VARBINARY(n)、DATETIME[(p)]、TIME[(p)]。分区个数不能超过1048575个。

• PARTITION BY HASH(partition_key)

  创建哈希分区。partition_key为分区键的名称。

  对于partition_key，哈希分区策略的分区键仅支持1列，且不支持表达式。

  分区键支持的数据类型为：TINYINT、SMALLINT、INTEGER、BIGINT、TINYINT UNSIGNED、SMALLINT UNSIGNED、INTEGER UNSIGNED、BIGINT UNSIGNED、DATE、YEAR、CHAR[(n)]、VARCHAR[(n)]、MEDIUMINT、MEDIUMINT UNSIGNED、BINARY[(n)]、VARBINARY(n)、DATETIME[(p)]、TIME[(p)]、TIMESTAMP[(p)]、NUMERIC[(p[,s])]、FLOAT4[(p, s)]、FLOAT8[(p,s)]。分区个数不能超过1048575个。

• PARTITION BY KEY(partition_key)

  语义同“PARTITION BY HASH(partition_key)”。

• PARTITIONS integer

  指定分区个数。

  integer为分区数，必须为大于0的整数，且不得大于1048575。

  • 当在RANGE和LIST分区后指定此子句时，必须显式定义每个分区，且定义分区的数量必须与integer值相等。只能在RANGE和LIST分区后指定此子句。
  • 当在HASH和KEY分区后指定此子句时，若不列出各个分区定义，将自动生成integer个分区，自动生成的分区名为“p+数字”，数字依次为0到integer-1，分区的表空间默认为此表的表空间；也可以显式列出每个分区定义，此时定义分区的数量必须与integer值相等。若既不列出分区定义，也不指定分区数量，将创建唯一一个分区。

范围分区

• VALUES LESS THAN

  --创建分区表test_range1。
  m_db=# CREATE TABLE test_range1(
      id INT, 
      info VARCHAR(20)
  ) PARTITION BY RANGE (id) (
      PARTITION p1 VALUES LESS THAN (200),
      PARTITION p2 VALUES LESS THAN (400),
      PARTITION p3 VALUES LESS THAN (600),
      PARTITION pmax VALUES LESS THAN (MAXVALUE)
  );
  
  --插入1000条数据
  m_db=# INSERT INTO test_range1 VALUES(GENERATE_SERIES(1,1000),'abcd');
  
  --查看p1分区的行数199条，[1,200)。
  m_db=# SELECT COUNT(*) FROM test_range1 PARTITION (p1);
   count 
  -------
     199
  (1 row)
  
  --查看p2分区的行数200条，[200,400)。
  m_db=# SELECT COUNT(*) FROM test_range1 PARTITION (p2);
   count 
  -------
     200
  (1 row)
  
  --查看分区信息。
  m_db=# SELECT a.relname, a.boundaries  
  FROM pg_partition a 
  WHERE a.parentid = 'test_range1'::regclass and a.parttype = 'p';
   relname | boundaries 
  ---------+------------
   pmax    | {NULL}
   p3      | {600}
   p2      | {400}
   p1      | {200}
  (4 rows)
  
  --删除
  m_db=# DROP TABLE test_range1;

• START END

  --创建分区表。
  m_db=# CREATE TABLE test_range2(
      id INT, 
      info VARCHAR(20)
  ) PARTITION BY RANGE (id) (
      PARTITION p1 START(1) END(600),    
      PARTITION p2 START(600) END(800),
      PARTITION pmax START(800) END(MAXVALUE)
  );
  
  --查看分区信息。
  m_db=# SELECT relname, boundaries FROM pg_partition WHERE parentid = 'test_range2'::regclass AND parttype = 'p' ORDER BY 1;
  relname | boundaries 
  ---------+------------
   p1_0    | {1}
   p1_1    | {600}
   p2      | {800}
   pmax    | {NULL}
  (4 rows)
  
  --删除。
  m_db=# DROP TABLE test_range2;

列表分区

--创建列表分区表。
m_db=# CREATE TABLE test_list ( NAME VARCHAR ( 50 ), area VARCHAR ( 50 ) ) 
PARTITION BY LIST (area) (
    PARTITION p1 VALUES ('Beijing'),
    PARTITION p2 VALUES ('Shanghai'),
    PARTITION p3 VALUES ('Guangzhou'),
    PARTITION p4 VALUES ('Shenzhen'),
    PARTITION pdefault VALUES (DEFAULT)
);

--插入数据。
m_db=# INSERT INTO test_list VALUES ('bob', 'Shanghai'),('scott', 'Sichuan');

--查询分区数据。
m_db=# SELECT * FROM test_list PARTITION (p2);
 name |   area   
------+----------
 bob  | Shanghai
(1 row)
m_db=# SELECT * FROM test_list PARTITION (pdefault);
 name  |  area   
-------+---------
 scott | Sichuan
(1 row)

--删除。
m_db=# DROP TABLE test_list;

哈希分区

--创建哈希分区表，指定分区数。
m_db=# CREATE TABLE test_hash1(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;

--创建哈希分区表，并指定分区名。
m_db=# CREATE TABLE test_hash2(c1 int) PARTITION BY HASH(c1)(
    PARTITION pa,
    PARTITION pb,
    PARTITION pc
);

--查看分区信息。
m_db=# SELECT b.relname AS table_name,
       a.relname AS partition_name 
FROM pg_partition a, 
     pg_class b 
WHERE b.relname LIKE 'test_hash%' 
  AND a.parttype = 'p' 
  AND a.parentid = b.oid; 
 table_name | partition_name 
------------+----------------
 test_hash1 | p2
 test_hash1 | p1
 test_hash1 | p0
 test_hash2 | pc
 test_hash2 | pb
 test_hash2 | pa
(6 rows)

--删除。
m_db=# DROP TABLE test_hash1,test_hash2;

相关链接

ALTER TABLE PARTITION，DROP TABLE