How to Create Subpartition Template in EDB Postgres Advanced Server 14

This article covers the new feature to auto-create subpartitions in EDB Postgres Advanced Server 14.

1. Setting the template
2. Usage
3. Modifying Template
4. Catalog Information
5. Tablespace Handling

Creating a large number of subpartitions can be time-consuming as all the details - name, bound, tablespace - needs to be specified for each partition separately. Using this feature, a blueprint can be set with pre-defined details for subpartitions which can be used whenever a partition is added to the table. 

1. Setting the template

To set template during the CREATE TABLE command, use SUBPARTITION TEMPLATE clause after the SUBPARTITION BY detail.

CREATE TABLE <table_name> ( <col_list> )   
  PARTITION BY <partition_strategy> ( <col> )
        SUBPARTITION BY <subpart_strategy>( <col> )
     SUBPARTITION TEMPLATE
         ( <template_description> )
    ( <partition_description> )

The template_description is a list of the following format. 

<subpart_name> <subpart_bound> [<tablespace_info>]

• subpart_name is mandatory and each name has to be unique in the template list. 
• tablespace_info is optional and the name should be valid when specified. 
• subpart_bound values are dependent on the subpart_strategy and the bounds in the template list should not be overlapping.

List bound is defined as VALUES (<value_list>);

    SUBPARTITION TEMPLATE
         (SUBPARTITION a VALUES (1),
          SUBPARTITION b VALUES (2,7) TABLESPACE ts2
         )

Range bound is defined by VALUES LESS THAN (value)

SUBPARTITION TEMPLATE
         (SUBPARTITION s1 VALUES LESS THAN (100) TABLESPACE ts1,
        SUBPARTITION s2 VALUES LESS THAN (200)       
 )

Hash does not define a bound as they are auto-generated based on the number of partitions defined. 

SUBPARTITION TEMPLATE
         (SUBPARTITION x TABLESPACE ts1,
          SUBPARTITION y TABLESPACE ts1
         )

A  partition number can also be used to define hash subpartitions which are explained in this blog.

2. Usage

Once the subpartition template is set it is used whenever a new partition is created without any subpartition description. In CREATE TABLE and ADD PARTITION command, to keep the subpartition names distinct between partitions, the partition name is prepended to the template name. The corresponding table will have the parent table name prepended to the subpartition name.

Example with CREATE TABLE:

CREATE TABLE tbl_ll (col1 NUMBER(4) NOT NULL, col2 number(4))   
     PARTITION BY LIST (col1)
 SUBPARTITION BY LIST(col2)
     SUBPARTITION TEMPLATE
         (SUBPARTITION a VALUES (1),
          SUBPARTITION b VALUES (2)
         )
    (PARTITION p1 VALUES (10));

SELECT partition_name, subpartition_name, backing_table, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P1' ORDER BY 1,2;
 partition_name | subpartition_name | backing_table | high_value 
----------------+-------------------+---------------+------------
 P1             | P1_A              | tbl_ll_p1_a   | 1
 P1             | P1_B              | tbl_ll_p1_b   | 2
(2 rows)

Example with ADD PARTITION:

ALTER TABLE tbl_ll ADD PARTITION P2 VALUES (25); 

SELECT partition_name, subpartition_name, backing_table, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P2' ORDER BY 1,2;
 partition_name | subpartition_name | backing_table | high_value 
----------------+-------------------+---------------+------------
 P2             | P2_A              | tbl_ll_p2_a   | 1
 P2             | P2_B              | tbl_ll_p2_b   | 2
(2 rows)

In the case of dynamic partition, since the partition name is auto-generated, the subpartitions created using the template also use auto-generated names but retain the bound and tablespace information.

CREATE TABLE tbl_dyn (col1 NUMBER(4) NOT NULL, col2 number(4))   
     PARTITION BY LIST (col1) AUTOMATIC
 SUBPARTITION BY LIST(col2)
     SUBPARTITION TEMPLATE
         (SUBPARTITION a VALUES (1) TS1,
          SUBPARTITION b VALUES (2)
         )
    (PARTITION p1 VALUES (10));

 INSERT INTO tbl_dyn VALUES (90, 1);

 SELECT partition_name, subpartition_name, backing_table, tablespace_name, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_DYN' ORDER BY 1,2;
 partition_name | subpartition_name |    backing_table     | tablespace_name | high_value 
----------------+-------------------+----------------------+-----------------+------------
 P1             | P1_A              | tbl_dyn_p1_a         | TS1             | 1
 P1             | P1_B              | tbl_dyn_p1_b         | PG_DEFAULT      | 2
 SYS213140104   | SYS213140105      | tbl_dyn_sys213140105 | TS1             | 1
 SYS213140104   | SYS213140106      | tbl_dyn_sys213140106 | PG_DEFAULT      | 2
(4 rows)

It is to be noted that the dynamic partition is not created if the inserted value does not fit any of the subpartitions created with the template. It is advisable to have one default subpartition in template definition to catch such entries.

INSERT INTO tbl_dyn VALUES (90, 5);
ERROR:  no partition of relation "tbl_dyn_sys213140104" found for row
DETAIL:  Partition key of the failing row contains (col2) = (5).

3. Modifying Template

To modify or set a subpartition template use the ALTER TABLE… SET SUBPARTITION TEMPLATE command. This would overwrite the existing subpartition template information and all the partitions created after this command would use the new template. The subpartition description of the existing partitions will be unchanged. 

ALTER TABLE tbl_ll SET SUBPARTITION TEMPLATE (
SUBPARTITION s1 VALUES (100,500),
SUBPARTITION s2 VALUES (200)
);

ALTER TABLE tbl_ll ADD PARTITION P3 VALUES (50);

SELECT partition_name, subpartition_name, backing_table, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P3' ORDER BY 1,2;
 partition_name | subpartition_name | backing_table | high_value 
----------------+-------------------+---------------+------------
 P3             | P3_S1             | tbl_ll_p3_s1  | 100, 500
 P3             | P3_S2             | tbl_ll_p3_s2  | 200
(2 rows)

A new template can be added only if a subpartition strategy was specified during CREATE TABLE and the bounds should be according to the existing subpartition strategy.

ALTER TABLE tbl_ll SET SUBPARTITION TEMPLATE (
SUBPARTITION s1 VALUES LESS THAN (100),
SUBPARTITION s2 VALUES LESS THAN (200)
);
ERROR:  subpartition specification type does not match subpartition definition

To reset the subpartition template, simply give an empty template description, this would show the default behavior of creating a single subpartition.

ALTER TABLE tbl_ll SET SUBPARTITION TEMPLATE ();

ALTER TABLE tbl_ll ADD PARTITION P4 VALUES (150);

SELECT partition_name, subpartition_name, backing_table, high_value FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P4' ORDER BY 1,2;
 partition_name | subpartition_name | backing_table  | high_value 
----------------+-------------------+----------------+------------
 P4             | SYS0110           | tbl_ll_sys0110 | DEFAULT
(1 row)

4. Catalog Information

The template data is stored in a pg_node_tree column called partsubparttemplate of pg_partitioned_table. The function pg_get_expr or edb_get_subpart_template can be used to print the template in a readable form:

SELECT pg_get_expr(partsubparttemplate, 'tbl_ll'::regclass) FROM pg_partitioned_table WHERE partrelid = 'tbl_ll'::regclass;
                           pg_get_expr                           
-----------------------------------------------------------------
 SUBPARTITION s1 VALUES (100, 500), SUBPARTITION s2 VALUES (200)
(1 row)


SELECT edb_get_subpart_template('tbl_ll'::regclass);
                     edb_get_subpart_template                      
-------------------------------------------------------------------
  SUBPARTITION s1 VALUES (100, 500), SUBPARTITION s2 VALUES (200) 
(1 row)

This subpartition template information is also visible in the \d and \d+ output for the parent table

\d tbl_ll
           Partitioned table "public.tbl_ll"
 Column |     Type     | Collation | Nullable | Default 
--------+--------------+-----------+----------+---------
 col1   | numeric(4,0) |           | not null | 
 col2   | numeric(4,0) |           |          | 
Partition key: LIST (col1) AUTOMATIC
Subpartiton template:  SUBPARTITION s1 VALUES (100, 500), SUBPARTITION s2 VALUES (200) 
Number of partitions: 6 (Use \d+ to list them.)

When the subpart template is reset, the partsubparttemplate column is set to NULL.

SELECT partsubparttemplate FROM pg_partitioned_table WHERE partrelid = 'tbl_ll'::regclass; 
 partsubparttemplate 
---------------------
 
(1 row)

SELECT edb_get_subpart_template('tbl_ll'::regclass);
 edb_get_subpart_template 
--------------------------
 
(1 row)

5. Tablespace Handling

The tablespace used in the subpartition template cannot be dropped. This is to prevent further addition of partitions from failing due to the non-availability of the tablespace.

ALTER TABLE tbl_ll SET SUBPARTITION TEMPLATE (
SUBPARTITION s1 VALUES (1500) TABLESPACE ts1,
SUBPARTITION s2 VALUES (2000)
);

DROP TABLESPACE ts1;
ERROR:  tablespace "ts1" cannot be dropped because some objects depend on it
DETAIL:  subpartition template of table tbl_ll

ALTER TABLE tbl_ll ADD PARTITION P5 VALUES (300);

SELECT partition_name, subpartition_name, high_value, tablespace_name FROM sys.all_tab_subpartitions WHERE TABLE_NAME='TBL_LL' AND PARTITION_NAME = 'P5' ORDER BY 1,2;
 partition_name | subpartition_name | high_value | tablespace_name 
----------------+-------------------+------------+-----------------
 P5             | P5_S1             | 1500       | TS1
 P5             | P5_S2             | 2000       | PG_DEFAULT
(2 rows)

This feature is very useful when creating a table where partitioning has a performance advantage. It saves a lot of time as the subpartition definition can be written once and used multiple times even in cases like dynamic partition where specifying the subpartition description is not possible.