CREATE TABLE PARTITION

Description

Creates a partitioned table. Partitioning refers to splitting what is logically one large table into smaller physical pieces based on specific schemes. The table based on the logic is called a partitioned table, and each physical piece is called a partition. Data is stored on these physical partitions, instead of the logical partitioned table.

The common forms of partitioning include range partitioning, hash/key partitioning, list partitioning, and range/list columns partitioning. Currently, row-store tables support range partitioning, hash partitioning, and list partitioning.

Range Partitioning:

• In range partitioning, a table is partitioned based on ranges defined by one or more columns, with no overlap between the ranges of values assigned to different partitions. Each range has a dedicated partition for data storage.
• The partitioning policy for range partitioning refers to how data is inserted into partitions. Currently, only the range partitioning policy can be used.

• In range partitioning, a table is partitioned based on partition key values. If a record can be mapped to a partition, it is inserted into the partition; if it cannot, an error message is returned.

Hash/Key Partitioning:

• In hash partitioning, a modulus and a remainder are specified for each partition based on a column in the table, and records to be inserted into the table are allocated to the corresponding partition, the rows in each partition must meet the following condition: The value of the partition key divided by the specified modulus generates the remainder specified for the partition key.
• In hash partitioning, a table is partitioned based on partition key values. If a record can be mapped to a partition, it is inserted into the partition; if it cannot, an error message is returned.

List Partitioning:

• List partitioning is to allocate the records to be inserted into a table to the corresponding partition based on the key values in each partition. The key values do not overlap in different partitions. Each group of key values has a dedicated partition for data storage.
• In list partitioning, a table is partitioned based on partition key values. If a record can be mapped to a partition, it is inserted into the partition; if it cannot, an error message is returned.

Partitioning has the following advantages:

• Query performance can be improved drastically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning narrows the range of data search and improves data access efficiency.
• When you query or update most of the records in a partition, performance can be improved by taking advantage of continuous scan of that partition instead of partitions scattered across the whole table.
• Frequent loading or deletion operations on records in a separate partition can be accomplished by reading or deleting that partition. This not only improves performance but also avoids the VACUUM overload caused by bulk DELETE operations (hash partitions cannot be deleted).

Precautions

• When a unique constraint or primary key constraint is added to a distributed table, a local index is created for the constraint if the constraint key contains all partition keys. Otherwise, a global index is created.
• Currently, hash partitioning supports only single-column partition keys, and does not support multi-column partition keys.
• In the PARTITION FOR (values) syntax for partitioned tables, values can only be constants.
• In the PARTITION FOR (values) syntax for partitioned tables, if data type conversion is required for values, you are advised to use forcible type conversion to prevent the implicit type conversion result from being inconsistent with the expected result.
• The maximum number of partitions is 1048575. Generally, it is impossible to create so many partitions, because too many partitions may cause insufficient memory. Create partitions based on the value of local_syscache_threshold. The memory used by the partitioned tables is about (number of partitions x 3/1024) MB. Theoretically, the memory occupied by the partitions cannot be greater than the value of local_syscache_threshold. In addition, some space must be reserved for other functions.
• Considering the impact on performance, it is recommended that the maximum number of partitions in a single table be less than or equal to 2,000 and the number of subpartitions multiplied by (Number of local indexes + 1) be less than or equal to 10,000.
• If the memory is insufficient due to too many partitions, the performance deteriorates sharply.
• Currently, the statement specifying a partition cannot perform global index scan.
• The following data types cannot be used as partition keys or level-2 partition keys: BLOB, TINYBLOB, MEDIUMBLOB, LONGBLOB, TEXT, TINYTEXT, MEDIUMTEXT, LONGTEXT, and BIT.

Syntax

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 is as follows:

  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}
  }

  

  • If COMMENT is set for multiple times in the same place, only the last value takes effect.
  • If AUTO_INCREMENT is set for multiple times in the same place, only the last value takes effect.
  • If COLLATE is set for multiple times in the same place, only the last value takes effect.
  • When ENGINE and ROW_FORMAT are used, no error message is displayed. However, the settings do not take effect.
• column_constraint is as follows:

  [ 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 is as follows:

  [ 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']

Parameters

• IF NOT EXISTS

  Sends a notice instead of an error if tables have identical names. The notice prompts that the table relationship already exists.

• partition_table_name

  Specifies the name of a partitioned table.

  Value range: character strings complying with Identifier Description.

• column_name

  Specifies the name of a column to be created in the target table.

  Value range: character strings complying with Identifier Description.

• data_type

  Specifies the data type of a column.

• CONSTRAINT constraint_name

  Specifies the name of a column or table constraint. The optional constraint clauses specify constraints that new or updated rows must satisfy for an INSERT or UPDATE operation to succeed.

  There are two ways to define constraints:

  • A column constraint is defined as part of a column definition, and it is bound to a particular column.
  • A table constraint is not bound to a particular column and can be applied to more than one column. constraint_name is optional.
• index_name

  Index name.

  

  • For a foreign key constraint, if constraint_name and index_name are specified at the same time, constraint_name is used as the index name.
  • If both constraint_name and index_name are specified for the UNIQUE KEY constraint, the value of index_name will be used as the index name.
• USING method

  Specifies the method of creating an index.

  For details about the value range, see "USING method" in Parameters.

  

  Currently, only the B-tree index method is supported.

• ASC | DESC

  ASC specifies an ascending (default) sort order. DESC specifies a descending sort order.

• LIKE source_table

  Specifies a table from which the new table automatically copies all column names, their data types, and their NOT NULL constraints.

  Unlike INHERITS, the new table and original table are decoupled after creation is complete. Changes to the original table will not be applied to the new table, and it is not possible to include data of the new table in scans of the original table.

• AUTO_INCREMENT [ = ] value

  This clause specifies an initial value for an auto-increment column. The value must be a positive integer and cannot exceed 2¹²⁷ – 1.

• COMMENT [ = ] 'string'
  • The COMMENT [ = ] 'string' clause is used to add comments to a table.
  • The COMMENT 'string' in column_constraint indicates that comments are added to a column.
  • The COMMENT 'string' in table_constraint indicates that comments are added to the indexes corresponding to the primary key and unique key.

  For details, see •COMMENT[=]'string'.

• {CHARACTER SET | CHAR SET | CHARSET} charset

  Specifies the character set of a table column. If this parameter is specified separately, the collation of the table column is set to the default collation of the specified character set.

• COLLATE collation
  Assigns a collation to the column (which must be of a collatable data type). If no collation is specified, the default collation is used. You can run the select * from pg_collation; command to query collation rules from the pg_collation system catalog. The default collation rule is the row starting with default in the query result. The utf8mb4_bin, utf8mb4_general_ci, utf8mb4_unicode_ci, binary, gbk_chinese_ci, gbk_bin, gb18030_chinese_ci, and gb18030_bin collations are supported. For details, see Table-level Character Sets and Collations.

  

  • Only the character type supports the specified character set. If the BINARY character set or collation is specified, the character type is converted to the corresponding binary type. If the type mapping does not exist, an error is reported. Currently, only the mapping from the TEXT type to the BLOB type is available.
  • If the character set or collation of a column is not explicitly specified and the default character set or collation of the table is specified, the character set or collation of the column is inherited from the table.
  • Except the BINARY character set and collation, only the character set that is the same as the database encoding can be specified.
• WITH ( storage_parameter [= value] [, ... ] )

  Specifies an optional storage parameter for a table or an index. The description of parameters is as follows:

  • FILLFACTOR

    The fill factor of a table is a percentage from 10 to 100. If the Ustore is used, the default value is 92. If the Astore is used, the default value is 100 (completely filled). When a smaller fill factor is specified, INSERT operations fill table pages only to the indicated percentage. The remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page, which is more efficient than placing it on a different page. For a table whose entries are never updated, setting the fill factor to 100 (complete filling) is the best choice, but in heavily updated tables a smaller fill factor would be appropriate.

    Value range: 10–100

  • ORIENTATION

    Determines the data storage mode of the table. Currently, an M-compatible database supports only the row-store mode.

    Value range:

    ROW (default value): The data will be stored in rows.

    

    orientation cannot be modified.

  • STORAGE_TYPE

    Specifies the storage engine type. This parameter cannot be modified once it is set.

    Value range:

    • USTORE indicates that tables support the in-place update storage engine. Note that the track_counts and track_activities parameters must be enabled when the Ustore table is used. Otherwise, space bloating may occur.
    • ASTORE indicates that tables support the append-only storage engine.

    Default value:

    If no table is specified, data is stored in in-place update mode by default.

  • COMPRESSION

    Row-store tables do not support compression.

  • segment

    The data is stored in segment-page mode. This parameter supports only row-store tables. Temporary tables and unlogged tables are not supported.

    Value range: on and off

    Default value: off

  • statistic_granularity

    Records the default PARTITION_MODE when the table analyzes statistics. This parameter is invalid for non-partitioned tables.

    Value range: See the options of PARTITION_MODE.

    Default value: AUTO.

    Table 1 PARTITION_MODE options

    PARTITION_MODE Option	Description
    ALL	Specifies whether to collect statistics about the entire table and level-1 partition.
    GLOBAL	Specifies whether to collect statistics about the entire table.
    PARTITION	Specifies whether to collect statistics about the level-1 partition.
    GLOBAL AND PARTITION	Specifies whether to collect statistics about the entire table and level-1 partition.
    ALL COMPLETE	Specifies whether to collect statistics about the entire table and level-1 partition.
    AUTO	Default value.

• TABLESPACE tablespace_name

  Specifies that the target table will be created in the tablespace_name tablespace. If not specified, the default tablespace is used.

  Note: You need to create or delete tablespaces in a non-M-compatible database.

• PARTITION BY RANGE [COLUMNS] (partition_key)

  Creates a range partition. partition_key is the name of the partition key.

  The semantics of PARTITION BY RANGE COLUMNS is the same as that of PARTITION BY RANGE.

  (1) Assume that the VALUES LESS THAN syntax is used.

  

  If the VALUES LESS THAN clause is used, a range partitioning policy supports a partition key with up to 16 columns.

  Data types supported by the partition key are as follows: 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)], and TIME[(p)].

  (2) Assume that the START END syntax is used.

  

  If the START END clause is used, a range partitioning policy supports only a one-column partition key.

  Data types supported by the partition key are as follows: TINYINT, SMALLINT, INTEGER, BIGINT, TINYINT UNSIGNED, SMALLINT UNSIGNED, INTEGER UNSIGNED, BIGINT UNSIGNED, and DATE.

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

  Specifies the information of partitions. partition_name is the name of a range partition. partition_value is the upper limit of a range partition, and the value depends on the type of partition_key. MAXVALUE usually specifies the upper limit of the last range partition.

  

  • Each partition requires an upper limit.
  • The data type of the upper limit must be the same as that of the partition key.
  • In a partition list, partitions are arranged in ascending order of upper limits. A partition with a smaller upper limit value is placed before another partition with a larger one.
  • MAXVALUE that is not in parentheses can have only one partition key.
  • partition_value does not support expressions.
• PARTITION partition_name {START (partition_value) END (partition_value|MAXVALUE)} | {START(partition_value)} | {END (partition_value | MAXVALUE)} [TABLESPACE tablespace_name]

  Specifies the information of partitions.

  • partition_name: name or name prefix of a range partition. It is the name prefix only in the following cases (assuming that partition_name is p1):

    If the defined statement is in the first place and has START specified, the range (MINVALUE, START) will be automatically used as the first actual partition, and its name will be p1_0. The other partitions are then named p1_1, p1_2, and the like. For example, if PARTITION p1 START(1), PARTITION p2 START(2) is defined, generated partitions are (MINVALUE, 1), [1, 2), and [2, MAXVALUE), and their names will be p1_0, p1_1, and p2. In this case, p1 is a name prefix and p2 is a partition name. MINVALUE means the minimum value.

  • partition_value: start value or end value of a range partition. The value depends on partition_key and cannot be MAXVALUE.
  • MAXVALUE usually specifies the upper limit of the last range partition.
  

  1. If the defined statement is in the first place and has START specified, the range (MINVALUE, START) will be automatically used as the first actual partition.
  2. The START END syntax must comply with the following rules:
     • The value of START (if any, same for the following situations) in each partition_start_end_item must be smaller than that of END.
     • In two adjacent partition_start_end_item statements, the value of the first END must be equal to that of the second START.
     • Each partition includes the start value (unless it is MINVALUE) and excludes the end value. The format is as follows: [START, END).
     • Partitions created by the same partition_start_end_item belong to the same tablespace.
     • If partition_name is a name prefix of a partition, the length must not exceed 57 bytes. If there are more than 57 bytes, the prefix will be automatically truncated.
     • When creating or modifying a partitioned table, ensure that the total number of partitions in the table does not exceed the maximum value 1048575.
  3. In statements for creating partitioned tables, START END and LESS THAN cannot be used together.
  4. The START END syntax in a partitioned table creation SQL statement will be replaced by the VALUES LESS THAN syntax when gs_dump is executed.
• PARTITION BY LIST [COLUMNS] (partition_key)

  Creates a list partition. partition_key is the name of the partition key.

  The semantics of PARTITION BY LIST COLUMNS is the same as that of PARTITION BY LIST.

  • partition_key does not support expressions. If no level-2 partition is specified, a list partitioning policy supports a partition key with up to 16 columns. If a level-2 partition is specified, the list partitioning policy supports a partition key with only one column and does not support expressions.
  • For the clause syntax VALUES [IN] (list_values), if list_values contains the key values of the corresponding partition, it is recommended that the number of key values of each partition be less than or equal to 64.
  • The semantics of VALUES IN is the same as that of VALUES.

  Data types supported by the partition key are as follows: 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)], and TIME[(p)]. The number of partitions cannot exceed 1048575.

• PARTITION BY HASH(partition_key)

  Creates a hash partition. partition_key is the name of the partition key.

  For partition_key, the partition key of the hash partitioning policy supports only one column and does not support expressions.

  Data types supported by the partition key are as follows: 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)], and FLOAT8[(p,s)]. The number of partitions cannot exceed 1048575.

• PARTITION BY KEY(partition_key)

  The semantics is the same as that of PARTITION BY HASH(partition_key).

• PARTITIONS integer

  Specifies the number of partitions.

  integer indicates the number of partitions. The value must be an integer greater than 0 and cannot be greater than 1048575.

  • When this clause is specified after the range and list partitions, each partition must be explicitly defined, and the number of defined partitions must be equal to the integer value. This clause can be specified after RANGE and LIST partitions.
  • When this clause is specified after the hash and key partitions, if the definition of each partition is not listed, an integer number of partitions are automatically generated. The automatically generated partition name is "p+number", and the number ranges from 0 to integer minus 1. The tablespace of the partition is the tablespace of the table by default. If each partition definition is explicitly defined, the number of defined partitions must be the same as the value of integer. If neither the partition definition nor the number of partitions is specified, a unique partition is created.

Range Partitioning

• VALUES LESS THAN

  -- Create a partitioned table 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)
  );
  
  -- Insert 1000 data records.
  m_db=# INSERT INTO test_range1 VALUES(GENERATE_SERIES(1,1000),'abcd');
  
  -- View the number of rows (199) in the p1 partition. The value range is [1, 200).
  m_db=# SELECT COUNT(*) FROM test_range1 PARTITION (p1);
   count 
  -------
     199
  (1 row)
  
  -- View the number of rows (200) in the p2 partition. The value range is [200, 400).
  m_db=# SELECT COUNT(*) FROM test_range1 PARTITION (p2);
   count 
  -------
     200
  (1 row)
  
  -- View the partition information.
  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)
  
  -- Drop.
  m_db=# DROP TABLE test_range1;

• START END

  -- Create a partitioned table.
  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)
  );
  
  -- View the partition information.
  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)
  
  -- Drop.
  m_db=# DROP TABLE test_range2;

List Partitioning

-- Create a list partitioned table.
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)
);

-- Insert data.
m_db=# INSERT INTO test_list VALUES ('bob', 'Shanghai'),('scott', 'Sichuan');

-- Query partition data.
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)

-- Drop.
m_db=# DROP TABLE test_list;

Hash Partitioning

-- Create a hash partitioned table and specify the number of partitions.
m_db=# CREATE TABLE test_hash1(c1 int) PARTITION BY HASH(c1) PARTITIONS 3;

-- Create a hash partitioned table and specify the partition name.
m_db=# CREATE TABLE test_hash2(c1 int) PARTITION BY HASH(c1)(
    PARTITION pa,
    PARTITION pb,
    PARTITION pc
);

-- View the partition information.
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)

-- Drop.
m_db=# DROP TABLE test_hash1,test_hash2;

Helpful Links

ALTER TABLE PARTITION and DROP TABLE