PDF page 12
#### 3.2.1 Overview {#pg10-3-2-1-overview} Traditional PostgreSQL used the function of the inheritance table as a method of physically partitioning a large table. The inheritance table creates multiple child tables for the parent table and maintains the consistency of data by CHECK constraints and triggers. The application can access the parent table and transparently use the data of the child table. However, this method had the following disadvantages. - Data consistency depends on the CHECK constraint specified individually in the child table - INSERT statements for the parent table need to redirect to child tables by triggers so that it is slower **Figure 2.** Structure of table partition using inheritance table PDF page 12
In PostgreSQL 10, partition table feature uses a more sophisticated table distribution method. It is the same as the conventional inheritance table that the partition table consists of child tables having the same structure as the parent table accessed by the application. However, INHERIT specification, CHECK constraint, trigger are unnecessary, so that addition or deletion of the child table can be done easily. In PostgreSQL 10, the column (or calculated value) to be specified for partitioning table. RANGE partitions that specify the range of values to be stored and LIST partitions that specify only specific values are available. The type of the partition is determined when creating the parent table.PDF page 13
**Figure 3.** Partitioning table structure PDF page 13
#### 3.2.2 List Partition Table {#pg10-3-2-2-list-partition-table} The LSIT partition table is a way to group multiple partitions that can only store certain values. To create a list partition table, first create a parent table accessed by the application, specifying the PARTITION BY LIST clause in the CREATE TABLE statement. In the LIST clause, specify the column name (or calculated value) to be partitioned. Only one column name can be specified. At this point the INSERT statement for the table fails. For tables created with PARTITION BY clause, the value of the "relkind" column in the pg_class catalog is 'p'. **Example 1.** Create LIST partition table ```text postgres=> CREATE TABLE plist1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST (c1) ; CREATE TABLE ``` ```sql Next, create a child table (partition) where data is actually stored. In doing so, specify the parent table using the PARTITION OF clause and specify the value to include in the partition column using the FOR VALUES IN clause. Multiple values can be specified, separated by commas (,). ``` **Example 2.** Create child table ```text postgres=> CREATE TABLE plist1_v100 PARTITION OF plist1 FOR VALUES IN (100) ; CREATE TABLE postgres=> CREATE TABLE plist1_v200 PARTITION OF plist1 FOR VALUES IN (200) ; CREATE TABLE ``` ```text In the following example, refer to the definition of the partition table which has been created. ```PDF page 14
**Example 3.** Reference table definition ```text postgres=> \d+ plist1 Table "public.plist1" Column | Type | Collation | Nullable | Default | Storage | … --------+-----------------------+-----------+----------+---------+----------+… c1 | numeric | | | | main |… c2 | character varying(10) | | | | extended |… Partition key: LIST (c1) Partitions: plist1_v100 FOR VALUES IN ('100'), plist1_v200 FOR VALUES IN ('200') postgres=> \d+ plist1_v100 Table "public.plist1_v100" Column | Type | Collation | Nullable | Default | … --------+-----------------------+-----------+----------+---------+ … c1 | numeric | | | | … c2 | character varying(10) | | | | … Partition of: plist1 FOR VALUES IN ('100') Partition constraint: ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY['100'::numeric]))) ``` ```sql The INSERT statement for the parent table is automatically distributed to the partitioned child table. An INSERT statement of data not included in the partition will result in an error. ``` **Example 4.** Execution of INSERT statement on parent table ```text postgres=> INSERT INTO plist1 VALUES (100, 'data1') ; INSERT 0 1 postgres=> INSERT INTO plist1 VALUES (200, 'data2') ; INSERT 0 1 postgres=> INSERT INTO plist1 VALUES (300, 'data3') ; ERROR: no partition of relation "plist1" found for row DETAIL: Partition key of the failing row contains (c1) = (300). ``` ```text Partitioned child tables are also directly accessible. However, values other than the values specified in the partition target column cannot be stored. ```PDF page 15
##### Obtaining partition information {#pg10-p15-obtaining-partition-information} The pg_get_partkeydef function can be used to obtain the partition method and column information. Restrictions on each partition can be obtained with the pg_get_partition_constraintdef function. **Example 5.** Obtain partition information ```text postgres=> SELECT pg_get_partkeydef('plist1'::regclass) ; pg_get_partkeydef ------------------- LIST (c1) (1 row) postgres=> SELECT pg_get_partition_constraintdef('plist1_v100'::regclass) ; pg_get_partition_constraintdef ----------------------------------------------------------- ((c1 IS NOT NULL) AND (c1 = ANY (ARRAY['100'::numeric]))) (1 row) ``` #### 3.2.3 Range Partition Table {#pg10-3-2-3-range-partition-table} The RANGE partition table is a way to group multiple partitions that can store a range of specific values. To create a range partition table, first create a parent table accessed by the application. Specify the PARTITION BY RANGE clause in the CREATE TABLE statement. In the RANGE clause, specify the column name (or calculated value) to be partitioned. Multiple column names can be specified by separating them with a comma (,). A NOT NULL constraint is automatically set for the partitioned columns (except for calculated values). At this point the INSERT statement for the table fails. **Example 6.** Create RANGE Partition Table ```text postgres=> CREATE TABLE prange1(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE (c1) ; CREATE TABLE ``` ```sql Next, create a child table (partition) where data is actually stored. Use the PARTITION OF clause to specify the parent table and use the FOR VALUES FROM TO clause to specify the range of values to include in the partition. Only the value of "FROM <= value < TO" can be stored in the partition. ```PDF page 16
**Example 7.** Create child table ```text postgres=> CREATE TABLE prange1_a1 PARTITION OF prange1 FOR VALUES FROM (100) TO (200) ; CREATE TABLE postgres=> CREATE TABLE prange1_a2 PARTITION OF prange1 FOR VALUES FROM (200) TO (300) ; CREATE TABLE ``` ```text In the following example, refer to the definition of the partition table which has been created. ``` **Example 8.** Reference table definition ```text postgres=> \d+ prange1 Table "public.prange1" Column | Type | Collation | Nullable | Default | … --------+-----------------------+-----------+----------+---------+ … c1 | numeric | | not null | | … c2 | character varying(10) | | | | … Partition key: RANGE (c1) Partitions: prange1_a1 FOR VALUES FROM ('100') TO ('200'), prange1_a2 FOR VALUES FROM ('200') TO ('300') postgres=> \d+ prange1_a1 Table "public.prange1_a1" Column | Type | Collation | Nullable | Default | … --------+-----------------------+-----------+----------+---------+ … c1 | numeric | | not null | | … c2 | character varying(10) | | | | … Partition of: prange1 FOR VALUES FROM ('100') TO ('200') Partition constraint: ((c1 >= '100'::numeric) AND (c1 < '200'::numeric)) ``` ```sql The INSERT statement for the parent table is automatically distributed to the partitioned child table. An INSERT statement of data not included in the partition will result in an error. ```PDF page 17
**Example 9.** Execution of INSERT statement on parent table ```text postgres=> INSERT INTO prange1 VALUES (100, 'data1') ; INSERT 0 1 postgres=> INSERT INTO prange1 VALUES (200, 'data2') ; INSERT 0 1 postgres=> INSERT INTO prange1 VALUES (300, 'data3') ; ERROR: no partition of relation "prange1" found for row DETAIL: Partition key of the failing row contains (c1) = (300). ``` ```text Partitioned child tables are also directly accessible. However, values other than the values specified in the partition target column cannot be stored. ``` **Example 10.** Access to child table ```text postgres=> SELECT * FROM prange1_a1 ; c1 | c2 -----+------- 100 | data1 (1 row) postgres=> INSERT INTO prange1_a1 VALUES (200, 'data2') ; ERROR: new row for relation "prange1_a1" violates partition constraint DETAIL: Failing row contains (200, data2). ``` ##### UNBOUNDED specification of range {#pg10-p17-unbounded-specification-of-range} UNBOUNDED can be specified in the FROM clause or TO clause of the RANGE partition in addition to concrete values. This designation can create partitions that do not limit the lower limit (FROM) or upper limit (TO) range. In the example below, two tables are specified with a value less than 100 and a value of 100 or more as the partition of the prange1 table. **Example 11.** UNBOUNDED designation ```text postgres=> CREATE TABLE prange1_1 PARTITION OF prange1 FOR VALUES FROM (UNBOUNDED) TO (100) ; CREATE TABLE postgres=> CREATE TABLE prange1_2 PARTITION OF prange1 FOR VALUES FROM (100) TO (UNBOUNDED) ; CREATE TABLE ```PDF page 18
A partition that specifies "a value to divide a partition containing an existing UNBOUNDED value" can not be added. **Example 12.** Split UNBOUNDED partition ```text postgres=> CREATE TABLE prange1_3 PARTITION OF prange1 FOR VALUES FROM (200) TO (300) ; ERROR: partition "prange1_3" would overlap partition "prange1_2" ``` #### 3.2.4 Existing tables and partitions {#pg10-3-2-4-existing-tables-and-partitions} Validation of how to register an existing table in the partition table and remove it from the partition table. ##### ATTACH of child table {#pg10-p18-attach-of-child-table} An existing table can be attached as a partition (child table) in a parent table. The child table must be created in the same column configuration as the parent table. Also, it is possible to detach a table registered as a partition into a normal table. **Example 13.** Create a table with the same structure as the parent table ```text postgres=> CREATE TABLE plist1_v100 (LIKE plist1) ; CREATE TABLE postgres=> CREATE TABLE plist1_v200 (LIKE plist1) ; CREATE TABLE ``` ```sql Attach the created table as a partition of the parent table. Use the ALTER TABLE ATTACH PARTITION statement. At the same time, specify the value of the partitioning column. In the example below, a plist1_v100 table storing data of LIST partition c1=100 and a plist1_v200 table storing data of c1=200 are registered. ``` **Example 14.** Attach a partition ```text postgres=> ALTER TABLE plist1 ATTACH PARTITION plist1_v100 FOR VALUES IN (100) ; ALTER TABLE postgres=> ALTER TABLE plist1 ATTACH PARTITION plist1_v200 FOR VALUES IN (200) ; ALTER TABLE ```PDF page 19
Objects that can be registered as partitions are limited to tables or FOREIGN TABLE. ##### DETACH of child table {#pg10-p19-detach-of-child-table} To remove the partitioned child table from the parent table, execute the ALTER TABLE DETACH statement **Example 15.** Detach a partition ```text postgres=> ALTER TABLE plist1 DETACH PARTITION plist1_v100 ; ALTER TABLE ``` #### 3.2.5 Operation on partition table {#pg10-3-2-5-operation-on-partition-table} Validation of the behavior when executing DDL or COPY statement for the parent table or child table. ##### TRUNCATE for parent table {#pg10-p19-truncate-for-parent-table} Execution of the TRUNCATE statement for the parent table propagates to all partitions. **Example 16.** TRUNCATE for parent table ```text postgres=> TRUNCATE TABLE part1 ; TRUNCATE TABLE postgres=> SELECT COUNT(*) FROM part1_v1 ; count ------- 0 (1 row) ``` ##### COPY for parent table {#pg10-p19-copy-for-parent-table} The COPY statement for the parent table propagates to the child table.PDF page 20
**Example 17.** COPY for parent table ```text postgres=# COPY part1 FROM '/home/postgres/part1.csv' WITH (FORMAT text) ; COPY 10000 postgres=# SELECT COUNT(*) FROM part1_v1 ; count ------- 10000 (1 row) ``` ##### DROP parent table {#pg10-p20-drop-parent-table} Dropping the parent table also drops all child tables. The DROP TABLE statement for the child table drops only the child table. ##### Add / delete columns to the parent table {#pg10-p20-add-delete-columns-to-the-parent-table} When adding / deleting a column to / from the parent table, the child table is changed in the same way. However, columns that are partition keys cannot be deleted. Also, if the partition is FOREIGN TABLE, column addition is not done automatically.PDF page 21
**Example 18.** Adding and deleting columns from the parent table ```text postgres=> \d part1 Table "public.part1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | Partition key: LIST (c1) Number of partitions: 2 (Use \d+ to list them.) postgres=> ALTER TABLE part1 ADD c3 NUMERIC ; ALTER TABLE postgres=> \d part1_v1 Table "public.part1_v1" Column | Type | Collation | Nullable | Default --------+-----------------------+-----------+----------+--------- c1 | numeric | | | c2 | character varying(10) | | | c3 | numeric | | | Partition of: part1 FOR VALUES IN ('100') postgres=> ALTER TABLE part1 DROP c1 ; ERROR: cannot drop column named in partition key ``` ##### TEMPORARY table {#pg10-p21-temporary-table} A TEMPORARY table can be used for both parent table and partitioned child table. However, if the parent table is a temporary table, the partition table must also be a temporary table. ##### UNLOGGED table {#pg10-p21-unlogged-table} An UNLOGGED table can be used for parent table or partition table. ##### Hierarchical structure {#pg10-p21-hierarchical-structure} By partitioning different columns, hierarchical partition table can be created. In the example below, a table partitioned by column c2 is added under the table partitioned by column c1.PDF page 22
**Example 19.** Hierarchical partition ```text postgres=> CREATE TABLE part2 (c1 NUMERIC, c2 NUMERIC, c3 VARCHAR(10)) PARTITION BY LIST (c1) ; CREATE TABLE postgres=> CREATE TABLE part2_v1 PARTITION OF part2 FOR VALUES IN (100) PARTITION BY LIST (c2) ; CREATE TABLE postgres=> CREATE TABLE part2_v1_v2 PARTITION OF part2_v1 FOR VALUES IN (200) ; CREATE TABLE ``` #### 3.2.6 Execution Plan {#pg10-3-2-6-execution-plan} If there is a condition to specify the partition to be accessed in the WHERE clause, an execution plan that accesses only specific partitions is created. **Example 20.** Partition\-identifiable SQL and execution plan ```text postgres=> EXPLAIN SELECT * FROM plist1 WHERE c1 = 100 ; QUERY PLAN ------------------------------------------------------------------- Append (cost=0.00..20.38 rows=4 width=70) -> Seq Scan on plist1_v100 (cost=0.00..20.38 rows=4 width=70) Filter: (c1 = '100'::numeric) (3 rows) ``` ```text However, when the partition cannot be specified (ex. left side of the WHERE clause is a formula), an execution plan that access all partitions is created. ```PDF page 23
**Example 21.** Partition\-unspecified SQL and execution plan ```text postgres=> EXPLAIN SELECT * FROM plist1 WHERE c1 + 1 = 101 ; QUERY PLAN ------------------------------------------------------------------- Append (cost=0.00..44.90 rows=8 width=70) -> Seq Scan on plist1_v100 (cost=0.00..22.45 rows=4 width=70) Filter: ((c1 + '1'::numeric) = '101'::numeric) -> Seq Scan on plist1_v200 (cost=0.00..22.45 rows=4 width=70) Filter: ((c1 + '1'::numeric) = '101'::numeric) (5 rows) ``` #### 3.2.7 Catalog {#pg10-3-2-7-catalog} Information on the partitioned parent table can be checked in the pg_partitioned_table catalog. Below is the table information of which name part1, LIST partition (partstrat = 'l'), attached table number 2 (partnatts = 2). **Example 22.** Information of the parent table ```text postgres=> SELECT partrelid::regclass, * FROM pg_partitioned_table ; -[ RECORD 1 ]-+------ partrelid | part1 partrelid | 16444 partstrat | l partnatts | 2 partattrs | 1 partclass | 3125 partcollation | 0 partexprs | ``` ```text The table of which the relispartition column in the pg_class catalog is "true" is a child table. When the table is child table, the partition boundary information is stored in the relpartbound column in the pg_class catalog. Information on this column can be converted easily by pg_get_expr function. ```PDF page 24
**Example 23.** Information of child table ```text postgres=> SELECT relname, relispartition, relpartbound FROM pg_class WHERE relname = 'prange1_v1' ; -[ RECORD 1 ]--+---------------------------------------------------------- relname | prange1_v1 relispartition | t relpartbound | {PARTITIONBOUND :strategy r :listdatums <> :lowerdatums ({PARTRANGEDATUM :infinite false :value {CONST :consttype 1700 :consttypmod -1 :constcollid 0 :constlen -1 :constbyval false :constisnull false :location -1 :constvalue 8 [ 32 0 0 0 0 -128 100 0 ]}}) :upperdatums ({PARTRANGEDATUM :infinite false :value {CONST :consttype 1700 :consttypmod -1 :constcollid 0 :constlen -1 :constbyval false :constisnull false :location -1 :constvalue 8 [ 32 0 0 0 0 -128 -56 0 ]}})} postgres=> SELECT relname, relispartition, pg_get_expr(relpartbound, oid) FROM pg_class WHERE relname = 'prange1_v1' ; -[ RECORD 1 ]--+----------------------------------- relname | prange1_v1 relispartition | t pg_get_expr | FOR VALUES FROM ('100') TO ('200') ``` #### 3.2.8 Restriction {#pg10-3-2-8-restriction} The partition table has the following restrictions. ##### Number of partitioning columns {#pg10-p24-number-of-partitioning-columns} Only one column can be specified in the PARTITION BY LIST clause of the CREATE TABLE statement. In the column name part, it is possible to specify a calculation expression enclosed in functions and parentheses. **Example 24.** Partition using function ```text postgres=> CREATE TABLE plist2(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST (upper(c2)) ; CREATE TABLE ```PDF page 25
##### NULL for partitioning column {#pg10-p25-null-for-partitioning-column} Null values cannot be stored in partitioned columns of RANGE partitions. For a list partition, it can be stored by creating a partition containing NULL values. **Example 25.** RANGE partition and NULL value ```text postgres=> CREATE TABLE partnl(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY RANGE (c1) ; CREATE TABLE postgres=> CREATE TABLE partnlv PARTITION OF partnl FOR VALUES FROM (UNBOUNDED) TO (UNBOUNDED) ; CREATE TABLE postgres=> INSERT INTO partnl VALUES (NULL, 'null value') ; ERROR: range partition key of row contains null ``` ##### Restriction of child table {#pg10-p25-restriction-of-child-table} The child table must have the same structure as the parent table. Excess and deficiency or data type mismatch of column are not allowed. **Example 26.** Partition with different structure child table ```text postgres=> CREATE TABLE plist3(c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST (c1) ; CREATE TABLE postgres=> CREATE TABLE plist3_v100 (c1 NUMERIC, c2 VARCHAR(10), c3 NUMERIC) ; CREATE TABLE postgres=> ALTER TABLE plist3 ATTACH PARTITION plist3_v100 FOR VALUES IN (100) ; ERROR: table "plist3_v100" contains column "c3" not found in parent "plist3" DETAIL: New partition should contain only the columns present in parent. postgres=> CREATE TABLE plist3_v200 (c1 NUMERIC); CREATE TABLE postgres=> ALTER TABLE plist3 ATTACH PARTITION plist3_v200 FOR VALUES IN (200) ; ERROR: child table is missing column "c2" ``` ##### Primary Key Constraint / Unique Constraint / Check Constraint {#pg10-p25-primary-key-constraint-unique-constraint-check-constraint} Primary key constraint (or unique constraint) cannot be specified in the parent table. The uniquenessPDF page 26
of the entire partition table depends on the primary key setting of the child table. CHECK constraints on the parent table can be specified. When creating a child table, the CHECK constraint is automatically added to the child table. **Example 27.** Add primary key to parent table ```text postgres=> ALTER TABLE plist1 ADD CONSTRAINT pl_plist1 PRIMARY KEY (c1) ; ERROR: primary key constraints are not supported on partitioned tables LINE 1: ALTER TABLE plist1 ADD CONSTRAINT pl_plist1 PRIMARY KEY (c1)... ^ ``` ##### INSERT ON CONFLICT statement {#pg10-p26-insert-on-conflict-statement} The INSERT ON CONFLICT statement for the parent table cannot be executed. ##### UPDATE of partitioning column {#pg10-p26-update-of-partitioning-column} When updating the value of a partitioned column, it can be updated only to the value contained in the FOR VALUES clause of the child table. It cannot be updated to a value that cannot be included in the child table. **Example 28.** Update partitioned column ```text postgres=> UPDATE plist1 SET c1 = 200 WHERE c1 = 100; ERROR: new row for relation "plist1_v100" violates partition constraint DETAIL: Failing row contains (200, data1). ``` ```sql Since the above error occurs, data movement between child tables cannot be realized by UPDATE statement (use DELETE RETURNING INSERT statement). ``` ##### ATTACHing of already stored data table {#pg10-p26-attaching-of-already-stored-data-table} ATTACHing a child table that stores data already to the parent table is possible. However, in that case, all tuples are checked whether they can be included in the partition.PDF page 27
**Example 29.** ATTACH of the partition containing the tuple ```text postgres=> CREATE TABLE plist2 (c1 NUMERIC, c2 VARCHAR(10)) PARTITION BY LIST (c1) ; CREATE TABLE postgres=> CREATE TABLE plist2_v100 (LIKE plist2) ; CREATE TABLE postgres=> INSERT INTO plist2_v100 VALUES (100, 'data1') ; INSERT 0 1 postgres=> INSERT INTO plist2_v100 VALUES (200, 'data2') ; INSERT 0 1 postgres=> ALTER TABLE plist2 ATTACH PARTITION plist2_v100 FOR VALUES IN (100) ; ERROR: partition constraint is violated by some row ``` ##### Partition where column values overlap {#pg10-p27-partition-where-column-values-overlap} RANGE partitions with overlapping ranges and LIST partitions with the same value cannot be created. In the example below, an attempt is made to attach partitions with column values 100 to 200 and 150 to 300 partitions, but it occurs an error. **Example 30.** Partition where column values overlap ```text postgres=> ALTER TABLE prange2 ATTACH PARTITION prange2_v1 FOR VALUES FROM (100) TO (200) ; ALTER TABLE postgres=> ALTER TABLE prange2 ATTACH PARTITION prange2_v2 FOR VALUES FROM (150) TO (300) ; ERROR: partition "prange2_v2" would overlap partition "prange2_v1" ``` ##### Specify FOREIGN TABLE as a child table {#pg10-p27-specify-foreign-table-as-a-child-table} FOREIGN TABLE can be specified as child table. However, in this case, aggregation push-downis not executed.PDF page 28
**Example 31.** ATTACH for FOREIGN TABLE ```text postgres=# CREATE FOREIGN TABLE datar2(c1 NUMERIC, c2 VARCHAR(10)) SERVER remote1 ; CREATE FOREIGN TABLE postgres=# ALTER TABLE pfor1 ATTACH PARTITION datar2 FOR VALUES IN ('data2') ; ALTER TABLE postgres=# SELECT COUNT(*) FROM pfor1 WHERE c2='data2' ; ``` **Example 32.** SQL executed on the remote instance ```sql statement: START TRANSACTION ISOLATION LEVEL REPEATABLE READ execute