Partitioning

Daria Barysheva

Overview

Partitioning is a way to increase query performance by logical dividing large tables (such as fact tables) into smaller, more manageable parts called partitions. Due to partitioning query optimizers can read the limited number of table rows (based on predicate conditions) instead of scanning all the table contents each time.

The exact partition that is selected to assign a new table record depends on the partition key — a column (or a set of columns for multi-level partitioning) that you define when creating a partitioned table.

Partitioning is performed only at the logical level. Unlike table distribution, partitioning does not divide tables physically. Both partitioned and non-partitioned tables are distributed across cluster segments.

book dark
Partitioning and distribution
book light
Partitioning and distribution

Despite the advantages of partitioning, not all tables are good candidates for its application. Also, the more partitions you create — the slower cluster management can become in the future. The following are the conditions under which partitioning can improve the performance of data queries. Use partitioning if all or most of these points can be applied to your database:

  1. There is a large table of facts. Large fact tables with millions of rows are good candidates for partitioning. On the other hand, smaller tables with thousands records or less are unlikely to impact query performance significantly.

  2. You are not satisfied with the current performance of data queries. Apply partitioning to the table only if queries against this table are far slower than required. This is a general condition for all performance tuning initiatives.

  3. There is a field in which the table can be divided into approximately equal parts. Try to select the partition key in such a way that the numbers of rows in partitions are close. The more evenly the division into small chunks is made, the greater the benefits of partitioning you can get. For example, by dividing a table into 10 identical partitions, you can increase the query speed to 10 times than that of the original non-partitioned table (provided that the partition key is used in predicates — see the next item below).

  4. Most queries that you want to speed up use the partition key in predicates. Partitioning improves query performance only if the query optimizer can select partitions based on the query predicates. Data queries that scan all partitions work even slower than if the table was not partitioned at all. Make sure that your query plans contain partition elimination.

  5. There are business requirements for maintaining historical data. Partitioning is the best choice if you need to store data for a limited time period. For example, if you require data for the last 12 months to be stored in the database, it is very easy to drop the oldest partition and load actual data into the new one.

Partitioning limitations

 
There are some limitations that you should consider before using table partitioning:

  • A partitioned table can have a maximum of 32767 partitions for each partition level.

  • A primary key or a unique constraint of a partitioned table should contain the partition key.

  • Tables having the DISTRIBUTED REPLICATED distribution policy cannot be partitioned.

  • Loading data into the partitioned tables is extremely inefficient. Therefore, it is recommended to insert new data into staging tables and then apply to the partitioned table the EXCHANGE PARTITION command.

  • The query optimizer can selectively scan partitioned tables only when the query predicate contains:

    • Immutable operators such as: =, <, <=, >, >=, and <>.

    • STABLE and IMMUTABLE functions (not VOLATILE).

  • There are additional limitations for partitioned tables when a leaf partition is an external table.

Create a partitioned table

Syntax

You can create a partitioned table using the CREATE TABLE command with the PARTITION BY (and optionally the SUBPARTITION BY) clause. The command syntax depends on the partitioning type: range, list, or combination of both types (multi-level partitioning):

  • Range partitioning. Range partitioning performs data division based on a numerical or date/timestamp range. You should use the PARTITION BY RANGE clause, then define the column to be used as a partition key, and then describe the intervals for partitions via the START and the END keywords. The INCLUSIVE and EXCLUSIVE keywords are used in conjunction with START and END to determine whether boundary values should be included in the range. By default, START values are inclusive and END values are exclusive.

     

    There are two ways to define range partitions — automatically and manually:

    • Generate partitions automatically. To generate partitions automatically, use the START and the END clauses to specify the boundaries of the entire range. Then write the EVERY clause to define the partition increment value. For date ranges, this clause requires the interval unit: day, month, or year (e.g. EVERY(INTERVAL '1 month')). For numeric ranges, enter the number only (e.g. EVERY(1)). The DBMS automatically creates the required number of partitions, splitting the entire range of values of the specified column with a given step.

    • Define partitions manually. An alternative way of specifying a partitioned table is to describe each partition individually. In this case you should add a comma-separated list of partition specifications in the parentheses after the PARTITION BY RANGE clause. Every specification should start with the PARTITION <partition_name> clause, where <partition_name> — a text to be used instead of the automatically generated number in the full partition name. The rest part of the specification contains the standard START and END clauses, which define individual intervals for each partition. Notice that there is no need to declare the END value for each partition, only for the last one.

  • List partitioning. List partitioning performs data division based on a predefined list of partition key values. Use the PARTITION BY LIST clause, then define the column to be used as a partition key, and then add a comma-separated list of partition specifications in the parentheses. Each specification should use the following template: PARTITION <partition_name> VALUES (<list_value> [,…​]), where <partition_name> — a text to be used instead of the automatically generated number in the full partition name; <list_value> — the partition key values required for the current partition.

  • Multi-level partitioning. Multi-level partitioning is a combination of range and/or list types on multiple levels. It allows you to divide partitions at the first level into subpartitions at the second level, and each of them in turn — into subpartitions at the third level and so on. The PARTITION BY (RANGE or LIST) clause is used to define the partition key for the first partitioning level. The SUBPARTITION BY (RANGE or LIST) clause is used for all other levels. All subpartition specifications are similar to partition specifications, except that the SUBPARTITION keyword is used instead of PARTITION.

     

    There are two ways to define subpartitions — with a subpartition template or without:

    • Use a subpartition template. In this case you specify subpartitions for each hierarchy level only once via the SUBPARTITION TEMPLATE clause. Each template is defined just after the corresponding SUBPARTITION BY clause. Using a subpartition template ensures that every partition has the same design, including the partitions that you add later.

    • Define subpartitions manually. In this case you describe every partition and subpartition individually. At first, set all partition keys via the PARTITION BY and SUBPARTITION BY clauses. Then add partition and subpartition specifications. The hierarchy is defined by parentheses.

For all partitioning types, you can define the default partition using the DEFAULT PARTITION (or DEFAULT SUBPARTITION) clause after each partition (or subpartition) specification. The default partition is designed to add data that does not fit into a given range (or list) of values.

IMPORTANT

  • If data beyond the specified range/list is possible in the table — it is recommended to specify default partitions. Otherwise, there will be errors when inserting data. But remember that default partitions are always scanned by query optimizers. Therefore, default partitions that contain data can slow down the overall scan time.

  • Be careful when using multi-level partitioning. A large number of subpartitions can significantly increase the query processing time. Try to reduce the number of subpartitions created, especially with no or little data.

Examples

Date range partitioning (automatically)

 
The following example creates twelve partitions based on the date range — one partition for each month of the 2022 year. The default partition is also created.

CREATE TABLE book_order
  (id INT,
   book_id INT,
   client_id INT,
   book_count SMALLINT,
   order_date DATE,
   CHECK(book_count >= 1)
  )
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(order_date)
(START(date '2022-01-01') INCLUSIVE
 END(date '2023-01-01') EXCLUSIVE
 EVERY(INTERVAL '1 month'),
 DEFAULT PARTITION other);

The result:

NOTICE:  CREATE TABLE will create partition "book_order_1_prt_other" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_2" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_3" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_4" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_5" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_6" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_7" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_8" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_9" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_10" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_11" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_12" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_13" for table "book_order"
CREATE TABLE
Numeric range partitioning (automatically)

 
The following example creates ten partitions based on the numeric range — one partition for each year (since 2013 to 2022). It also adds the default partition. Compared to using date ranges, you should not specify explicitly the data type in the START, END clauses and define the interval unit in the EVERY clause. The rest syntax is the same.

CREATE TABLE book
  (id INT,
   title TEXT,
   author_id INT NOT NULL,
   public_year SMALLINT NULL,
   type_id INT NOT NULL,
   cover_id INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(public_year)
(START(2013)
 END(2023)
 EVERY(1),
 DEFAULT PARTITION other);

The result:

NOTICE:  CREATE TABLE will create partition "book_1_prt_other" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_2" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_3" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_4" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_5" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_6" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_7" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_8" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_9" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_10" for table "book"
NOTICE:  CREATE TABLE will create partition "book_1_prt_11" for table "book"
CREATE TABLE
Date range partitioning (manually)

 
The following example creates the table book_order_manual with the structure completely identical to the book_order table created above, except that each partition is defined individually.

CREATE TABLE book_order_manual
  (id INT,
   book_id INT,
   client_id INT,
   book_count SMALLINT,
   order_date DATE,
   CHECK(book_count >= 1)
  )
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(order_date)
(PARTITION Jan22 START(date '2022-01-01') INCLUSIVE,
 PARTITION Feb22 START(date '2022-02-01') INCLUSIVE,
 PARTITION Mar22 START(date '2022-03-01') INCLUSIVE,
 PARTITION Apr22 START(date '2022-04-01') INCLUSIVE,
 PARTITION May22 START(date '2022-05-01') INCLUSIVE,
 PARTITION Jun22 START(date '2022-06-01') INCLUSIVE,
 PARTITION Jul22 START(date '2022-07-01') INCLUSIVE,
 PARTITION Aug22 START(date '2022-08-01') INCLUSIVE,
 PARTITION Sep22 START(date '2022-09-01') INCLUSIVE,
 PARTITION Oct22 START(date '2022-10-01') INCLUSIVE,
 PARTITION Nov22 START(date '2022-11-01') INCLUSIVE,
 PARTITION Dec22 START(date '2022-12-01') INCLUSIVE END(date '2023-01-01') EXCLUSIVE,
 DEFAULT PARTITION other);

The result:

NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_other" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_jan22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_feb22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_mar22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_apr22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_may22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_jun22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_jul22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_aug22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_sep22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_oct22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_nov22" for table "book_order_manual"
NOTICE:  CREATE TABLE will create partition "book_order_manual_1_prt_dec22" for table "book_order_manual"
CREATE TABLE
Numeric range partitioning (manually)

 
The following example creates the table book_manual with the structure completely identical to the book table created above, except that each partition is defined individually.

CREATE TABLE book_manual
  (id INT,
   title TEXT,
   author_id INT NOT NULL,
   public_year SMALLINT NULL,
   type_id INT NOT NULL,
   cover_id INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE(public_year)
(PARTITION Year2013 START(2013),
 PARTITION Year2014 START(2014),
 PARTITION Year2015 START(2015),
 PARTITION Year2016 START(2016),
 PARTITION Year2017 START(2017),
 PARTITION Year2018 START(2018),
 PARTITION Year2019 START(2019),
 PARTITION Year2020 START(2020),
 PARTITION Year2021 START(2021),
 PARTITION Year2022 START(2022) END(2023),
 DEFAULT PARTITION other);

The result:

NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_other" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2013" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2014" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2015" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2016" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2017" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2018" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2019" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2020" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2021" for table "book_manual"
NOTICE:  CREATE TABLE will create partition "book_manual_1_prt_year2022" for table "book_manual"
CREATE TABLE
List partitioning

 
The following example defines two partitions based on the gender column value. The default partition is also created.

CREATE TABLE client (id INT, name TEXT, gender CHAR(1))
DISTRIBUTED BY (id)
PARTITION BY LIST (gender)
(PARTITION girls VALUES ('F'),
 PARTITION boys VALUES ('M'),
 DEFAULT PARTITION other);

The result:

NOTICE:  CREATE TABLE will create partition "client_1_prt_girls" for table "client"
NOTICE:  CREATE TABLE will create partition "client_1_prt_boys" for table "client"
NOTICE:  CREATE TABLE will create partition "client_1_prt_other" for table "client"
CREATE TABLE
Multi-level partitioning (with a subpartition template)

 
The following example shows three-level partitioning. The command defines three range partitions at the first level and then divides each of them into three list subpartitions at the second level — based on the region column value. These subpartitions, in turn, are divided into two list subpartitions at the third level — based on the fiction column value. At each level of the hierarchy, the default partition/subpartition is also added.

CREATE TABLE sales
 (id INT,
  date DATE,
  fiction CHAR(1),
  region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
   SUBPARTITION BY LIST (region)
     SUBPARTITION TEMPLATE (
       SUBPARTITION rus VALUES ('rus'),
       SUBPARTITION asia VALUES ('asia'),
       SUBPARTITION europe VALUES ('europe'),
       DEFAULT SUBPARTITION other_rg
     )

     SUBPARTITION BY LIST (fiction)
       SUBPARTITION TEMPLATE (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )

(START(date '2022-01-01') INCLUSIVE
 END(date '2022-04-01') EXCLUSIVE
 EVERY(INTERVAL '1 month'),
 DEFAULT PARTITION other_dt
);

The result:

NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_rus" for table "sales_1_prt_other_dt"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_rus_3_prt_fiction" for table "sales_1_prt_other_dt_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_rus_3_prt_non_fiction" for table "sales_1_prt_other_dt_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_rus_3_prt_other_tp" for table "sales_1_prt_other_dt_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_asia" for table "sales_1_prt_other_dt"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_asia_3_prt_fiction" for table "sales_1_prt_other_dt_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_asia_3_prt_non_fiction" for table "sales_1_prt_other_dt_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_asia_3_prt_other_tp" for table "sales_1_prt_other_dt_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_europe" for table "sales_1_prt_other_dt"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_europe_3_prt_fiction" for table "sales_1_prt_other_dt_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_europe_3_prt_non_fiction" for table "sales_1_prt_other_dt_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_europe_3_prt_other_tp" for table "sales_1_prt_other_dt_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_other_rg" for table "sales_1_prt_other_dt"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_other_rg_3_prt_fiction" for table "sales_1_prt_other_dt_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_other_rg_3_prt_non_fiction" for table "sales_1_prt_other_dt_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_other_dt_2_prt_other_rg_3_prt_other_tp" for table "sales_1_prt_other_dt_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_rus" for table "sales_1_prt_2"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_rus_3_prt_fiction" for table "sales_1_prt_2_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_rus_3_prt_non_fiction" for table "sales_1_prt_2_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_rus_3_prt_other_tp" for table "sales_1_prt_2_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_asia" for table "sales_1_prt_2"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_asia_3_prt_fiction" for table "sales_1_prt_2_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_asia_3_prt_non_fiction" for table "sales_1_prt_2_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_asia_3_prt_other_tp" for table "sales_1_prt_2_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_europe" for table "sales_1_prt_2"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_europe_3_prt_fiction" for table "sales_1_prt_2_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_europe_3_prt_non_fiction" for table "sales_1_prt_2_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_europe_3_prt_other_tp" for table "sales_1_prt_2_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_rg" for table "sales_1_prt_2"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_rg_3_prt_fiction" for table "sales_1_prt_2_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_rg_3_prt_non_fiction" for table "sales_1_prt_2_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_2_2_prt_other_rg_3_prt_other_tp" for table "sales_1_prt_2_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_rus" for table "sales_1_prt_3"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_rus_3_prt_fiction" for table "sales_1_prt_3_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_rus_3_prt_non_fiction" for table "sales_1_prt_3_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_rus_3_prt_other_tp" for table "sales_1_prt_3_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_asia" for table "sales_1_prt_3"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_asia_3_prt_fiction" for table "sales_1_prt_3_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_asia_3_prt_non_fiction" for table "sales_1_prt_3_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_asia_3_prt_other_tp" for table "sales_1_prt_3_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_europe" for table "sales_1_prt_3"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_europe_3_prt_fiction" for table "sales_1_prt_3_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_europe_3_prt_non_fiction" for table "sales_1_prt_3_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_europe_3_prt_other_tp" for table "sales_1_prt_3_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_other_rg" for table "sales_1_prt_3"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_other_rg_3_prt_fiction" for table "sales_1_prt_3_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_other_rg_3_prt_non_fiction" for table "sales_1_prt_3_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_3_2_prt_other_rg_3_prt_other_tp" for table "sales_1_prt_3_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4" for table "sales"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_rus" for table "sales_1_prt_4"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_rus_3_prt_fiction" for table "sales_1_prt_4_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_rus_3_prt_non_fiction" for table "sales_1_prt_4_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_rus_3_prt_other_tp" for table "sales_1_prt_4_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_asia" for table "sales_1_prt_4"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_asia_3_prt_fiction" for table "sales_1_prt_4_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_asia_3_prt_non_fiction" for table "sales_1_prt_4_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_asia_3_prt_other_tp" for table "sales_1_prt_4_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_europe" for table "sales_1_prt_4"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_europe_3_prt_fiction" for table "sales_1_prt_4_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_europe_3_prt_non_fiction" for table "sales_1_prt_4_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_europe_3_prt_other_tp" for table "sales_1_prt_4_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_other_rg" for table "sales_1_prt_4"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_other_rg_3_prt_fiction" for table "sales_1_prt_4_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_other_rg_3_prt_non_fiction" for table "sales_1_prt_4_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_1_prt_4_2_prt_other_rg_3_prt_other_tp" for table "sales_1_prt_4_2_prt_other_rg"
CREATE TABLE
Multi-level partitioning (manually)

 
The following example creates the table sales_manual with the structure completely identical to the sales table created above, except that all subpartitions are defined individually at every level. You can see that the command text is much longer, but the result is the same.

CREATE TABLE sales_manual
 (id INT,
  date DATE,
  fiction CHAR(1),
  region text)
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
  SUBPARTITION BY LIST (region)
    SUBPARTITION BY LIST (fiction)

(PARTITION Jan2022 START(date '2022-01-01') INCLUSIVE
  (
    SUBPARTITION rus VALUES ('rus')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION asia VALUES ('asia')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION europe VALUES ('europe')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    DEFAULT SUBPARTITION other_rg
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )
  ),
 PARTITION Feb2022 START(date '2022-02-01') INCLUSIVE
  (
    SUBPARTITION rus VALUES ('rus')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION asia VALUES ('asia')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION europe VALUES ('europe')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    DEFAULT SUBPARTITION other_rg
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )
  ),
 PARTITION Mar2022 START(date '2022-03-01') INCLUSIVE
  (
    SUBPARTITION rus VALUES ('rus')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION asia VALUES ('asia')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION europe VALUES ('europe')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    DEFAULT SUBPARTITION other_rg
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )
  ),
 DEFAULT PARTITION other_dt
  (
    SUBPARTITION rus VALUES ('rus')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION asia VALUES ('asia')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    SUBPARTITION europe VALUES ('europe')
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       ),
    DEFAULT SUBPARTITION other_rg
       (
         SUBPARTITION fiction VALUES ('f'),
         SUBPARTITION non_fiction VALUES ('n'),
         DEFAULT SUBPARTITION other_tp
       )
  )
);

The result:

NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt" for table "sales_manual"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_rus" for table "sales_manual_1_prt_other_dt"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_rus_3_prt_fiction" for table "sales_manual_1_prt_other_dt_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_rus_3_prt_non_fiction" for table "sales_manual_1_prt_other_dt_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_rus_3_prt_other_tp" for table "sales_manual_1_prt_other_dt_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_asia" for table "sales_manual_1_prt_other_dt"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_asia_3_prt_fiction" for table "sales_manual_1_prt_other_dt_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_asia_3_prt_non_fiction" for table "sales_manual_1_prt_other_dt_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_asia_3_prt_other_tp" for table "sales_manual_1_prt_other_dt_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_europe" for table "sales_manual_1_prt_other_dt"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_europe_3_prt_fiction" for table "sales_manual_1_prt_other_dt_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_europe_3_prt_non_fiction" for table "sales_manual_1_prt_other_dt_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_europe_3_prt_other_tp" for table "sales_manual_1_prt_other_dt_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_other_rg" for table "sales_manual_1_prt_other_dt"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_other_rg_3_prt_fiction" for table "sales_manual_1_prt_other_dt_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_other_rg_3_prt_non_fiction" for table "sales_manual_1_prt_other_dt_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_other_dt_2_prt_other_rg_3_prt_other_tp" for table "sales_manual_1_prt_other_dt_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022" for table "sales_manual"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_rus" for table "sales_manual_1_prt_jan2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_rus_3_prt_fiction" for table "sales_manual_1_prt_jan2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_rus_3_prt_non_fiction" for table "sales_manual_1_prt_jan2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_rus_3_prt_other_tp" for table "sales_manual_1_prt_jan2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_asia" for table "sales_manual_1_prt_jan2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction" for table "sales_manual_1_prt_jan2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_asia_3_prt_non_fiction" for table "sales_manual_1_prt_jan2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_asia_3_prt_other_tp" for table "sales_manual_1_prt_jan2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_europe" for table "sales_manual_1_prt_jan2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_europe_3_prt_fiction" for table "sales_manual_1_prt_jan2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_europe_3_prt_non_fiction" for table "sales_manual_1_prt_jan2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_europe_3_prt_other_tp" for table "sales_manual_1_prt_jan2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_other_rg" for table "sales_manual_1_prt_jan2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_other_rg_3_prt_fiction" for table "sales_manual_1_prt_jan2022_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_other_rg_3_prt_non_fiction" for table "sales_manual_1_prt_jan2022_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_jan2022_2_prt_other_rg_3_prt_other_tp" for table "sales_manual_1_prt_jan2022_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022" for table "sales_manual"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_rus" for table "sales_manual_1_prt_feb2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_rus_3_prt_fiction" for table "sales_manual_1_prt_feb2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_rus_3_prt_non_fiction" for table "sales_manual_1_prt_feb2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_rus_3_prt_other_tp" for table "sales_manual_1_prt_feb2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_asia" for table "sales_manual_1_prt_feb2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_asia_3_prt_fiction" for table "sales_manual_1_prt_feb2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_asia_3_prt_non_fiction" for table "sales_manual_1_prt_feb2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_asia_3_prt_other_tp" for table "sales_manual_1_prt_feb2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_europe" for table "sales_manual_1_prt_feb2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_europe_3_prt_fiction" for table "sales_manual_1_prt_feb2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_europe_3_prt_non_fiction" for table "sales_manual_1_prt_feb2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_europe_3_prt_other_tp" for table "sales_manual_1_prt_feb2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_other_rg" for table "sales_manual_1_prt_feb2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_other_rg_3_prt_fiction" for table "sales_manual_1_prt_feb2022_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_other_rg_3_prt_non_fiction" for table "sales_manual_1_prt_feb2022_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_feb2022_2_prt_other_rg_3_prt_other_tp" for table "sales_manual_1_prt_feb2022_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022" for table "sales_manual"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_rus" for table "sales_manual_1_prt_mar2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_rus_3_prt_fiction" for table "sales_manual_1_prt_mar2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_rus_3_prt_non_fiction" for table "sales_manual_1_prt_mar2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_rus_3_prt_other_tp" for table "sales_manual_1_prt_mar2022_2_prt_rus"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_asia" for table "sales_manual_1_prt_mar2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_asia_3_prt_fiction" for table "sales_manual_1_prt_mar2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_asia_3_prt_non_fiction" for table "sales_manual_1_prt_mar2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_asia_3_prt_other_tp" for table "sales_manual_1_prt_mar2022_2_prt_asia"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_europe" for table "sales_manual_1_prt_mar2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_europe_3_prt_fiction" for table "sales_manual_1_prt_mar2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_europe_3_prt_non_fiction" for table "sales_manual_1_prt_mar2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_europe_3_prt_other_tp" for table "sales_manual_1_prt_mar2022_2_prt_europe"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_other_rg" for table "sales_manual_1_prt_mar2022"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_other_rg_3_prt_fiction" for table "sales_manual_1_prt_mar2022_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_other_rg_3_prt_non_fiction" for table "sales_manual_1_prt_mar2022_2_prt_other_rg"
NOTICE:  CREATE TABLE will create partition "sales_manual_1_prt_mar2022_2_prt_other_rg_3_prt_other_tp" for table "sales_manual_1_prt_mar2022_2_prt_other_rg"
CREATE TABLE

Internal details

Regardless of the partitioning type, Greenplum creates a top-level (or parent) table with one or more levels of sub-tables (or child tables). Internally, there is an inheritance relationship between the parent table and its underlying partitions, similar to the functionality of the PostgreSQL INHERITS clause. If you use psql, you can run \dt or \dt+ commands to list all relations and make sure that all created partitions are listed as regular tables.

Top-level parent tables are always empty. Data is loaded to the bottom-level (leaf) partitions. In a multi-level partition design, only the subpartitions located at the hierarchy bottom contain data.

To limit the data that each partition can contain, Greenplum uses the CHECK constraints. Rows that cannot be mapped to any child table partition are rejected and the load fails (unless the default partition is specified). The query optimizer also uses CHECK constraints to determine which partitions to scan according to the given query predicate.

To insert data into a partitioned table, you should specify the parent table created via the CREATE TABLE command. You can also specify a leaf partition directly. But you cannot insert data into a non-leaf or a non-root table.

For example, you can insert data into the sales_manual table created above (see Multi-level partitioning (manually)):

INSERT INTO sales_manual(id, date, fiction, region)
VALUES(1, date '2022-01-01', 'f', 'asia');

You can also insert new data into the leaf partition sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction:

INSERT INTO sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction(id, date, fiction, region)
VALUES(2, date '2022-01-02', 'f', 'asia');

But you cannot insert rows into non-leaf partitions, for example sales_manual_1_prt_jan2022:

INSERT INTO sales_manual_1_prt_jan2022(id, date, fiction, region) VALUES(3, date '2022-01-03', 'f', 'asia');

This query returns the error:

ERROR:  directly modifying intermediate part of a partitioned table is disallowed
HINT:  Modify either the root or a leaf partition instead.

You also cannot insert into leaf partitions the data that does not meet the partition specification:

INSERT INTO sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction(id, date, fiction, region)
VALUES(4, date '2022-01-02', 'n', 'asia');

The result:

ERROR:  trying to insert row into wrong partition  (seg7 10.92.16.100:10007 pid=19273)
DETAIL:  Expected partition: sales_manual_1_prt_jan2022_2_prt_asia_3_prt_non_fiction, provided partition: sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction.

To select data from the partitioned table, you can use either the parent table name or the leaf partition name:

SELECT * FROM  sales_manual;
SELECT * FROM  sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction;

Both queries return the same result:

 id |    date    | fiction | region
----+------------+---------+--------
  1 | 2022-01-01 | f       | asia
  2 | 2022-01-02 | f       | asia
(2 rows)

Partition an existing table

You can partition tables only at creation. If you need to partition an existing table, you should create a new partitioned table and load data into it from the original table. The example is listed below:

  1. Create an original table without partitioning:

    CREATE TABLE genre (id INT, name TEXT NOT NULL, fiction CHAR(1) NOT NULL)
DISTRIBUTED BY (id);

  2. Create a new partitioned table with the same structure using the LIKE clause:

    CREATE TABLE genre_partitioned (LIKE genre)
PARTITION BY LIST (fiction)
(PARTITION fiction VALUES ('F'),
 PARTITION non_fiction VALUES ('N'),
 DEFAULT PARTITION other
);

    The result:

    NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
NOTICE:  CREATE TABLE will create partition "genre_partitioned_1_prt_fiction" for table "genre_partitioned"
NOTICE:  CREATE TABLE will create partition "genre_partitioned_1_prt_non_fiction" for table "genre_partitioned"
NOTICE:  CREATE TABLE will create partition "genre_partitioned_1_prt_other" for table "genre_partitioned"
CREATE TABLE
    IMPORTANT

    Partition structures are not copied when you create a new table via the LIKE clause.

  3. Load the original table data into the new table:

    INSERT INTO genre_partitioned SELECT * FROM genre;

  4. Drop the original table:

    DROP TABLE genre;

  5. Assign the original table name to the partitioned table:

    ALTER TABLE genre_partitioned RENAME TO genre;

  6. Re-grant all necessary permissions for the partitioned table. For example:

    GRANT ALL PRIVILEGES ON genre TO gpadmin;

View partitioned tables

To get information on partitioned tables in the current database, use one of the following ways:

  • To show the partition design of the specified table, run the SQL query against the pg_catalog.pg_partitions system view. Replace <schema_name> by schema name,<table_name> by the parent table name.

    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = '<schema_name>'
AND   tablename  = '<table_name>';

    The command returns the following columns:

    • partitiontablename — object table name that is used to access the partition directly in DML commands (like INSERT, COPY, SELECT, etc.);

    • partitionname — partition name that is given at create time and can be referred to in the ALTER TABLE commands;

    • partitiontype — partition type (range or list);

    • partitionlevel — partition level in the hierarchy (starting with 0 for partitions at the first level);

    • partitionrank — rank of the partition compared to other partitions of the same level (starting with 1). Defined only for range partitions.

     

    You can also use the partitionboundary column to get partition specifications.

    The query results for tables with different partitioning types are listed below.

    Range partitioned table
    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
AND   tablename  = 'book_order';

    The result:

       partitiontablename   | partitionname | partitiontype | partitionlevel | partitionrank
------------------------+---------------+---------------+----------------+---------------
 book_order_1_prt_2     |               | range         |              0 |             1
 book_order_1_prt_3     |               | range         |              0 |             2
 book_order_1_prt_4     |               | range         |              0 |             3
 book_order_1_prt_5     |               | range         |              0 |             4
 book_order_1_prt_6     |               | range         |              0 |             5
 book_order_1_prt_7     |               | range         |              0 |             6
 book_order_1_prt_8     |               | range         |              0 |             7
 book_order_1_prt_9     |               | range         |              0 |             8
 book_order_1_prt_10    |               | range         |              0 |             9
 book_order_1_prt_11    |               | range         |              0 |            10
 book_order_1_prt_12    |               | range         |              0 |            11
 book_order_1_prt_13    |               | range         |              0 |            12
 book_order_1_prt_other | other         | range         |              0 |
(13 rows)
    List partitioned table
    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
AND   tablename  = 'client';

    The result:

     partitiontablename | partitionname | partitiontype | partitionlevel | partitionrank
--------------------+---------------+---------------+----------------+---------------
 client_1_prt_girls | girls         | list          |              0 |
 client_1_prt_boys  | boys          | list          |              0 |
 client_1_prt_other | other         | list          |              0 |
(3 rows)
    Multi-level partitioned table
    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
AND   tablename  = 'sales'
ORDER BY partitiontablename;

    The result:

                      partitiontablename                   | partitionname | partitiontype | partitionlevel | partitionrank
-------------------------------------------------------+---------------+---------------+----------------+---------------
 sales_1_prt_2                                         |               | range         |              0 |             1
 sales_1_prt_2_2_prt_asia                              | asia          | list          |              1 |
 sales_1_prt_2_2_prt_asia_3_prt_fiction                | fiction       | list          |              2 |
 sales_1_prt_2_2_prt_asia_3_prt_non_fiction            | non_fiction   | list          |              2 |
 sales_1_prt_2_2_prt_asia_3_prt_other_tp               | other_tp      | list          |              2 |
 sales_1_prt_2_2_prt_europe                            | europe        | list          |              1 |
 sales_1_prt_2_2_prt_europe_3_prt_fiction              | fiction       | list          |              2 |
 sales_1_prt_2_2_prt_europe_3_prt_non_fiction          | non_fiction   | list          |              2 |
 sales_1_prt_2_2_prt_europe_3_prt_other_tp             | other_tp      | list          |              2 |
 sales_1_prt_2_2_prt_other_rg                          | other_rg      | list          |              1 |
 sales_1_prt_2_2_prt_other_rg_3_prt_fiction            | fiction       | list          |              2 |
 sales_1_prt_2_2_prt_other_rg_3_prt_non_fiction        | non_fiction   | list          |              2 |
 sales_1_prt_2_2_prt_other_rg_3_prt_other_tp           | other_tp      | list          |              2 |
 sales_1_prt_2_2_prt_rus                               | rus           | list          |              1 |
 sales_1_prt_2_2_prt_rus_3_prt_fiction                 | fiction       | list          |              2 |
 sales_1_prt_2_2_prt_rus_3_prt_non_fiction             | non_fiction   | list          |              2 |
 sales_1_prt_2_2_prt_rus_3_prt_other_tp                | other_tp      | list          |              2 |
 sales_1_prt_3                                         |               | range         |              0 |             2
 sales_1_prt_3_2_prt_asia                              | asia          | list          |              1 |
 sales_1_prt_3_2_prt_asia_3_prt_fiction                | fiction       | list          |              2 |
 sales_1_prt_3_2_prt_asia_3_prt_non_fiction            | non_fiction   | list          |              2 |
 sales_1_prt_3_2_prt_asia_3_prt_other_tp               | other_tp      | list          |              2 |
 sales_1_prt_3_2_prt_europe                            | europe        | list          |              1 |
 sales_1_prt_3_2_prt_europe_3_prt_fiction              | fiction       | list          |              2 |
 sales_1_prt_3_2_prt_europe_3_prt_non_fiction          | non_fiction   | list          |              2 |
 sales_1_prt_3_2_prt_europe_3_prt_other_tp             | other_tp      | list          |              2 |
 sales_1_prt_3_2_prt_other_rg                          | other_rg      | list          |              1 |
 sales_1_prt_3_2_prt_other_rg_3_prt_fiction            | fiction       | list          |              2 |
 sales_1_prt_3_2_prt_other_rg_3_prt_non_fiction        | non_fiction   | list          |              2 |
 sales_1_prt_3_2_prt_other_rg_3_prt_other_tp           | other_tp      | list          |              2 |
 sales_1_prt_3_2_prt_rus                               | rus           | list          |              1 |
 sales_1_prt_3_2_prt_rus_3_prt_fiction                 | fiction       | list          |              2 |
 sales_1_prt_3_2_prt_rus_3_prt_non_fiction             | non_fiction   | list          |              2 |
 sales_1_prt_3_2_prt_rus_3_prt_other_tp                | other_tp      | list          |              2 |
 sales_1_prt_4                                         |               | range         |              0 |             3
 sales_1_prt_4_2_prt_asia                              | asia          | list          |              1 |
 sales_1_prt_4_2_prt_asia_3_prt_fiction                | fiction       | list          |              2 |
 sales_1_prt_4_2_prt_asia_3_prt_non_fiction            | non_fiction   | list          |              2 |
 sales_1_prt_4_2_prt_asia_3_prt_other_tp               | other_tp      | list          |              2 |
 sales_1_prt_4_2_prt_europe                            | europe        | list          |              1 |
 sales_1_prt_4_2_prt_europe_3_prt_fiction              | fiction       | list          |              2 |
 sales_1_prt_4_2_prt_europe_3_prt_non_fiction          | non_fiction   | list          |              2 |
 sales_1_prt_4_2_prt_europe_3_prt_other_tp             | other_tp      | list          |              2 |
 sales_1_prt_4_2_prt_other_rg                          | other_rg      | list          |              1 |
 sales_1_prt_4_2_prt_other_rg_3_prt_fiction            | fiction       | list          |              2 |
 sales_1_prt_4_2_prt_other_rg_3_prt_non_fiction        | non_fiction   | list          |              2 |
 sales_1_prt_4_2_prt_other_rg_3_prt_other_tp           | other_tp      | list          |              2 |
 sales_1_prt_4_2_prt_rus                               | rus           | list          |              1 |
 sales_1_prt_4_2_prt_rus_3_prt_fiction                 | fiction       | list          |              2 |
 sales_1_prt_4_2_prt_rus_3_prt_non_fiction             | non_fiction   | list          |              2 |
 sales_1_prt_4_2_prt_rus_3_prt_other_tp                | other_tp      | list          |              2 |
 sales_1_prt_other_dt                                  | other_dt      | range         |              0 |
 sales_1_prt_other_dt_2_prt_asia                       | asia          | list          |              1 |
 sales_1_prt_other_dt_2_prt_asia_3_prt_fiction         | fiction       | list          |              2 |
 sales_1_prt_other_dt_2_prt_asia_3_prt_non_fiction     | non_fiction   | list          |              2 |
 sales_1_prt_other_dt_2_prt_asia_3_prt_other_tp        | other_tp      | list          |              2 |
 sales_1_prt_other_dt_2_prt_europe                     | europe        | list          |              1 |
 sales_1_prt_other_dt_2_prt_europe_3_prt_fiction       | fiction       | list          |              2 |
 sales_1_prt_other_dt_2_prt_europe_3_prt_non_fiction   | non_fiction   | list          |              2 |
 sales_1_prt_other_dt_2_prt_europe_3_prt_other_tp      | other_tp      | list          |              2 |
 sales_1_prt_other_dt_2_prt_other_rg                   | other_rg      | list          |              1 |
 sales_1_prt_other_dt_2_prt_other_rg_3_prt_fiction     | fiction       | list          |              2 |
 sales_1_prt_other_dt_2_prt_other_rg_3_prt_non_fiction | non_fiction   | list          |              2 |
 sales_1_prt_other_dt_2_prt_other_rg_3_prt_other_tp    | other_tp      | list          |              2 |
 sales_1_prt_other_dt_2_prt_rus                        | rus           | list          |              1 |
 sales_1_prt_other_dt_2_prt_rus_3_prt_fiction          | fiction       | list          |              2 |
 sales_1_prt_other_dt_2_prt_rus_3_prt_non_fiction      | non_fiction   | list          |              2 |
 sales_1_prt_other_dt_2_prt_rus_3_prt_other_tp         | other_tp      | list          |              2 |
(68 rows)

  • To show all subpartitions created with a subpartition template, run the SQL query against the pg_catalog.pg_partition_templates system view.

    SELECT
  schemaname,
  tablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partition_templates;

    In addition to the columns described above, this query returns the schema name <schemaname> and the parent table name <tablename>. If necessary, you can also select data from the specified table using the WHERE clause.

    Query result
     schemaname | tablename | partitionname | partitiontype | partitionlevel | partitionrank
------------+-----------+---------------+---------------+----------------+---------------
 public     | sales     | rus           | list          |              1 |
 public     | sales     | asia          | list          |              1 |
 public     | sales     | europe        | list          |              1 |
 public     | sales     | other_rg      | list          |              1 |
 public     | sales     | fiction       | list          |              2 |
 public     | sales     | non_fiction   | list          |              2 |
 public     | sales     | other_tp      | list          |              2 |
(7 rows)

  • To show all partition key columns, get information from the pg_catalog.pg_partition_columns system view.

    SELECT * FROM pg_catalog.pg_partition_columns;

    The command returns the following columns:

    • schemaname — schema name;

    • tablename — top-level parent table name;

    • columnname — column used as a partition key;

    • partitionlevel — partition level in the hierarchy (starting with 0 for partitions at the first level);

    • position_in_partition_key — position of the column in a composite key. Composite (multi-column) partition keys are possible for list partitions. By default, 1.

    Query result
     schemaname |     tablename     | columnname  | partitionlevel | position_in_partition_key
------------+-------------------+-------------+----------------+---------------------------
 public     | book_order        | order_date  |              0 |                         1
 public     | book              | public_year |              0 |                         1
 public     | book_order_manual | order_date  |              0 |                         1
 public     | book_manual       | public_year |              0 |                         1
 public     | client            | gender      |              0 |                         1
 public     | sales             | date        |              0 |                         1
 public     | sales             | fiction     |              2 |                         1
 public     | sales             | fiction     |              2 |                         1
 public     | sales             | region      |              1 |                         1
 public     | sales             | region      |              1 |                         1
 public     | sales_manual      | date        |              0 |                         1
 public     | sales_manual      | fiction     |              2 |                         1
 public     | sales_manual      | region      |              1 |                         1
 public     | genre             | fiction     |              0 |                         1
(14 rows)

  • To view all partitioned tables and their inheritance level relationships, use the pg_catalog.pg_partition system catalog table.

    SELECT
  t2.relname,
  t1.parkind,
  t1.parlevel,
  t1.paristemplate
FROM pg_partition t1
LEFT JOIN pg_class t2 ON t2.oid = t1.parrelid;

    The command output contains the following columns:

    • relname — top-level parent table name;

    • parkind — partition type (r for range, l for list);

    • parlevel — partition level in the hierarchy (starting with 0 for partitions at the first level);

    • paristemplate — whether or not the current row represents a subpartition template definition (true) or an actual partitioning level (false).

    Query result
          relname      | parkind | parlevel | paristemplate
-------------------+---------+----------+---------------
 book_order        | r       |        0 | f
 book              | r       |        0 | f
 book_order_manual | r       |        0 | f
 book_manual       | r       |        0 | f
 client            | l       |        0 | f
 sales             | l       |        2 | f
 sales             | l       |        2 | t
 sales             | l       |        1 | f
 sales             | l       |        1 | t
 sales             | r       |        0 | f
 sales_manual      | l       |        2 | f
 sales_manual      | l       |        1 | f
 sales_manual      | r       |        0 | f
 genre             | l       |        0 | f
(14 rows)
NOTE

The last two queries return duplicated records with levels 1 and 2 for the parent table sales. This is due to the first row corresponds to the subpartition template definition, while the second row corresponds directly to the partitioning level.

Operations with partitions

Add a new partition

 
To add a new partition to the partitioned table, you should use the ALTER TABLE command with the ADD PARTITION clause. You cannot apply this command at one level with a default partition in the partition design. The following example creates a new partitioned table and adds some partitions to it:

  1. Create a two-level partitioned table without default partitions:

    CREATE TABLE genre_stat
  (id SERIAL,
   jenre text NOT NULL,
   year INT NOT NULL,
   count INT NOT NULL)
WITH (appendoptimized=true, orientation=row, compresstype=ZLIB, compresslevel=5)
DISTRIBUTED BY(id)
PARTITION BY RANGE (year)
 SUBPARTITION BY LIST (jenre)
  SUBPARTITION TEMPLATE (
    SUBPARTITION poetry VALUES ('poetry'),
    SUBPARTITION fantasy VALUES ('fantasy'),
    SUBPARTITION detective VALUES ('detective')
  )
(START(2013)
 END(2015)
 EVERY(1));

    The result:

    NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_1" for table "genre_stat"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_1_2_prt_poetry" for table "genre_stat_1_prt_1"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_1_2_prt_fantasy" for table "genre_stat_1_prt_1"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_1_2_prt_detective" for table "genre_stat_1_prt_1"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_2" for table "genre_stat"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_2_2_prt_poetry" for table "genre_stat_1_prt_2"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_2_2_prt_fantasy" for table "genre_stat_1_prt_2"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_2_2_prt_detective" for table "genre_stat_1_prt_2"
CREATE TABLE

  2. Add a new partition as follows:

    ALTER TABLE genre_stat ADD PARTITION START(2012) END(2013);

    The result is listed below. As you used a subpartition template when creating a parent table, the newly added partition is subpartitioned according to that template as well. Otherwise, you should explicitly define subpartitions when you add a new partition.

    NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_r188375637" for table "genre_stat"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_r188375637_2_prt_poetry" for table "genre_stat_1_prt_r188375637"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_r188375637_2_prt_fantasy" for table "genre_stat_1_prt_r188375637"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_r188375637_2_prt_detective" for table "genre_stat_1_prt_r188375637"
ALTER TABLE

  3. You can also add subpartitions to existing partitions. To do this, define the full path to the specific partition via ALTER PARTITION clauses — one clause for each partition level above the target partition:

    ALTER TABLE genre_stat ALTER PARTITION FOR (RANK(1))
      ADD PARTITION fantastic VALUES ('fantastic');

    The result:

    NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_r188375637_2_prt_fantastic" for table "genre_stat_1_prt_r188375637"
ALTER TABLE

  4. You can view the updated partition design using the following query:

    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
AND   tablename  = 'genre_stat' ORDER BY partitiontablename;

    The result:

                 partitiontablename              | partitionname | partitiontype | partitionlevel | partitionrank
---------------------------------------------+---------------+---------------+----------------+---------------
 genre_stat_1_prt_1                          |               | range         |              0 |             2
 genre_stat_1_prt_1_2_prt_detective          | detective     | list          |              1 |
 genre_stat_1_prt_1_2_prt_fantasy            | fantasy       | list          |              1 |
 genre_stat_1_prt_1_2_prt_poetry             | poetry        | list          |              1 |
 genre_stat_1_prt_2                          |               | range         |              0 |             3
 genre_stat_1_prt_2_2_prt_detective          | detective     | list          |              1 |
 genre_stat_1_prt_2_2_prt_fantasy            | fantasy       | list          |              1 |
 genre_stat_1_prt_2_2_prt_poetry             | poetry        | list          |              1 |
 genre_stat_1_prt_r188375637                 |               | range         |              0 |             1
 genre_stat_1_prt_r188375637_2_prt_detective | detective     | list          |              1 |
 genre_stat_1_prt_r188375637_2_prt_fantastic | fantastic     | list          |              1 |
 genre_stat_1_prt_r188375637_2_prt_fantasy   | fantasy       | list          |              1 |
 genre_stat_1_prt_r188375637_2_prt_poetry    | poetry        | list          |              1 |
(13 rows)
NOTE

  • To add a new partition at the same level as the default partition, you should split the default partition.

  • When you add a range partition, the ranks of the rest partitions at the same level are changed (see partitionrank above).
Rename a partition

 
To change a partition name, you can use the ALTER TABLE command with the RENAME PARTITION clause. The following example renames the partition with rank 1 in the parent table book created above (see Numeric range partitioning (automatically)).

  1. Check the current partition name:

    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
AND   tablename  = 'book';

    The result is listed below. The partition with rank 1 has the table object name book_1_prt_2, where 2 is an automatically generated number:

     partitiontablename | partitionname | partitiontype | partitionlevel | partitionrank
--------------------+---------------+---------------+----------------+---------------
 book_1_prt_2       |               | range         |              0 |             1
 book_1_prt_3       |               | range         |              0 |             2
 book_1_prt_4       |               | range         |              0 |             3
 book_1_prt_5       |               | range         |              0 |             4
 book_1_prt_6       |               | range         |              0 |             5
 book_1_prt_7       |               | range         |              0 |             6
 book_1_prt_8       |               | range         |              0 |             7
 book_1_prt_9       |               | range         |              0 |             8
 book_1_prt_10      |               | range         |              0 |             9
 book_1_prt_11      |               | range         |              0 |            10
 book_1_prt_other   | other         | range         |              0 |
(11 rows)

  2. Rename the partition as follows:

    ALTER TABLE book RENAME PARTITION FOR(rank(1)) TO year2013;

    The result:

    ALTER TABLE

  3. Check the partition name again (using the query from the step 1). You can see that the partition name and the table object name have changed:

     partitiontablename  | partitionname | partitiontype | partitionlevel | partitionrank
---------------------+---------------+---------------+----------------+---------------
 book_1_prt_year2013 | year2013      | range         |              0 |             1
 book_1_prt_3        |               | range         |              0 |             2
 book_1_prt_4        |               | range         |              0 |             3
 book_1_prt_5        |               | range         |              0 |             4
 book_1_prt_6        |               | range         |              0 |             5
 book_1_prt_7        |               | range         |              0 |             6
 book_1_prt_8        |               | range         |              0 |             7
 book_1_prt_9        |               | range         |              0 |             8
 book_1_prt_10       |               | range         |              0 |             9
 book_1_prt_11       |               | range         |              0 |            10
 book_1_prt_other    | other         | range         |              0 |
(11 rows)

Notice that changes in the parent table name are also propagated to the child partitions. For example, the following command renames the parent table book to magazine:

ALTER TABLE book RENAME TO magazine;

The command applies changes to all table partitions as well. You can check it via the following query:

SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
AND   tablename  = 'magazine';

The result:

   partitiontablename    | partitionname | partitiontype | partitionlevel | partitionrank
-------------------------+---------------+---------------+----------------+---------------
 magazine_1_prt_year2013 | year2013      | range         |              0 |             1
 magazine_1_prt_3        |               | range         |              0 |             2
 magazine_1_prt_4        |               | range         |              0 |             3
 magazine_1_prt_5        |               | range         |              0 |             4
 magazine_1_prt_6        |               | range         |              0 |             5
 magazine_1_prt_7        |               | range         |              0 |             6
 magazine_1_prt_8        |               | range         |              0 |             7
 magazine_1_prt_9        |               | range         |              0 |             8
 magazine_1_prt_10       |               | range         |              0 |             9
 magazine_1_prt_11       |               | range         |              0 |            10
 magazine_1_prt_other    | other         | range         |              0 |
(11 rows)
Add a default partition

 
To add a default partition to the partitioned table, you should use the ALTER TABLE command with the ADD DEFAULT PARTITION clause. The following example adds a default partition to the genre_stat table created above (see Add a new partition):

  1. Add a default partition at the first level of the partition design:

    ALTER TABLE genre_stat ADD DEFAULT PARTITION other;

    The result is listed below. All subpartitions are created as well, according to the subpartition template that you used when you created a table.

    NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_other" for table "genre_stat"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_other_2_prt_poetry" for table "genre_stat_1_prt_other"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_other_2_prt_fantasy" for table "genre_stat_1_prt_other"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_other_2_prt_detective" for table "genre_stat_1_prt_other"
NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_other_2_prt_fantastic" for table "genre_stat_1_prt_other"
ALTER TABLE

  2. You can also add default partitions to other hierarchy levels. To do this, define the full path to the specific partition via ALTER PARTITION clauses — one clause for each partition level above the target default partition. The following query creates a default partition at the second hierarchy level:

    ALTER TABLE genre_stat ALTER PARTITION FOR (RANK(1)) ADD DEFAULT PARTITION other;

    The result:

    NOTICE:  CREATE TABLE will create partition "genre_stat_1_prt_r188375637_2_prt_other" for table "genre_stat_1_prt_r188375637"
ALTER TABLE

  3. To view the updated partition design, run the following query:

    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
AND   tablename  = 'genre_stat' ORDER BY partitiontablename;

    The result:

                 partitiontablename              | partitionname | partitiontype | partitionlevel | partitionrank
---------------------------------------------+---------------+---------------+----------------+---------------
 genre_stat_1_prt_1                          |               | range         |              0 |             2
 genre_stat_1_prt_1_2_prt_detective          | detective     | list          |              1 |
 genre_stat_1_prt_1_2_prt_fantasy            | fantasy       | list          |              1 |
 genre_stat_1_prt_1_2_prt_poetry             | poetry        | list          |              1 |
 genre_stat_1_prt_2                          |               | range         |              0 |             3
 genre_stat_1_prt_2_2_prt_detective          | detective     | list          |              1 |
 genre_stat_1_prt_2_2_prt_fantasy            | fantasy       | list          |              1 |
 genre_stat_1_prt_2_2_prt_poetry             | poetry        | list          |              1 |
 genre_stat_1_prt_other                      | other         | range         |              0 |
 genre_stat_1_prt_other_2_prt_detective      | detective     | list          |              1 |
 genre_stat_1_prt_other_2_prt_fantastic      | fantastic     | list          |              1 |
 genre_stat_1_prt_other_2_prt_fantasy        | fantasy       | list          |              1 |
 genre_stat_1_prt_other_2_prt_poetry         | poetry        | list          |              1 |
 genre_stat_1_prt_r188375637                 |               | range         |              0 |             1
 genre_stat_1_prt_r188375637_2_prt_detective | detective     | list          |              1 |
 genre_stat_1_prt_r188375637_2_prt_fantastic | fantastic     | list          |              1 |
 genre_stat_1_prt_r188375637_2_prt_fantasy   | fantasy       | list          |              1 |
 genre_stat_1_prt_r188375637_2_prt_other     | other         | list          |              1 |
 genre_stat_1_prt_r188375637_2_prt_poetry    | poetry        | list          |              1 |
(19 rows)
Split a partition

 
To divide a partition into two partitions, you should use the ALTER TABLE command with the SPLIT PARTITION clause. The following example splits partitions of the book_order table created above (see Date range partitioning (automatically)):

  1. Split the partition allocated for January 2022:

    ALTER TABLE book_order SPLIT PARTITION FOR ('2022-01-01')
AT ('2022-01-16')
INTO (PARTITION jan1to15, PARTITION jan16to31);

    The result is shown below. The split value (2022-01-16) goes into the latter partition. Therefore, the first partition contains dates from January 1 to 15, and the second partition — from January 16 to 31.

    NOTICE:  CREATE TABLE will create partition "book_order_1_prt_jan1to15" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_jan16to31" for table "book_order"
ALTER TABLE

  2. The splitting operation is also used to add new partitions at the same level with the default partitions. Try to add a new monthly partition (for December 2021) to the book_order table:

    ALTER TABLE book_order ADD PARTITION
START ('2021-12-01')
END ('2022-01-01');

    You get the following error:

    ERROR:  cannot add RANGE partition to relation "book_order" with DEFAULT partition "other"
HINT:  need to SPLIT partition "other"

  3. Split the default partition other as follows:

    ALTER TABLE book_order SPLIT DEFAULT PARTITION
START ('2021-12-01')
END ('2022-01-01')
INTO (PARTITION dec21, DEFAULT PARTITION);

    The result is listed below:

    NOTICE:  exchanged partition "other" of relation "book_order" with relation "pg_temp_61865"
NOTICE:  dropped partition "other" for relation "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_dec21" for table "book_order"
NOTICE:  CREATE TABLE will create partition "book_order_1_prt_other" for table "book_order"
ALTER TABLE

  4. To view the updated partition design, run the following query:

    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public' AND   tablename  = 'book_order';

    The result:

         partitiontablename     | partitionname | partitiontype | partitionlevel | partitionrank
----------------------------+---------------+---------------+----------------+---------------
 book_order_1_prt_dec21     | dec21         | range         |              0 |             1
 book_order_1_prt_jan1to15  | jan1to15      | range         |              0 |             2
 book_order_1_prt_jan16to31 | jan16to31     | range         |              0 |             3
 book_order_1_prt_3         |               | range         |              0 |             4
 book_order_1_prt_4         |               | range         |              0 |             5
 book_order_1_prt_5         |               | range         |              0 |             6
 book_order_1_prt_6         |               | range         |              0 |             7
 book_order_1_prt_7         |               | range         |              0 |             8
 book_order_1_prt_8         |               | range         |              0 |             9
 book_order_1_prt_9         |               | range         |              0 |            10
 book_order_1_prt_10        |               | range         |              0 |            11
 book_order_1_prt_11        |               | range         |              0 |            12
 book_order_1_prt_12        |               | range         |              0 |            13
 book_order_1_prt_13        |               | range         |              0 |            14
 book_order_1_prt_other     | other         | range         |              0 |
(15 rows)
NOTE

  • You can split only the partitions that are located at the hierarchy bottom (leaf partitions).

  • For multi-level partitions, you can split only range partitions, not list ones.
Exchange a partition

 
Partition exchange allows you to replace a partition with a table having the same structure. This intermediate table is usually called a staging table. After the operation is applied, the staging table contains the partition data, and the partition contains the staging table data. This feature is especially useful for data loading, since inserting data into partitioned tables is inefficient. You can also use partition exchange to change the storage type of existing partitions.

To exchange a partition, you should use the ALTER TABLE command with the EXCHANGE PARTITION clause. The following example exchanges a partition of the book_order_manual table created above (see Date range partitioning (manually)):

  1. List the parent table with all partitions via the psql \dt+ command:

    \dt+ book_order_manual*

    The result:

                                           List of relations
 Schema |             Name              | Type  |  Owner  |   Storage   |  Size  | Description
--------+-------------------------------+-------+---------+-------------+--------+-------------
 public | book_order_manual             | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_apr22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_aug22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_dec22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_feb22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_jan22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_jul22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_jun22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_mar22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_may22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_nov22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_oct22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_other | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_sep22 | table | gpadmin | append only | 544 kB |
(14 rows)

  2. Check that the book_order_manual_1_prt_dec22 partition does not contain data:

    SELECT * FROM book_order_manual_1_prt_dec22;

    The result:

     id | book_id | client_id | book_count | order_date
----+---------+-----------+------------+------------
(0 rows)

  3. Create a table with the same structure as the partitioned table book_order_manual has. Choose another storage type:

    CREATE TABLE dec22 (LIKE book_order_manual) WITH (appendoptimized=false);

    The result:

    NOTICE:  table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
CREATE TABLE

  4. Insert some data into the new table:

    INSERT INTO dec22(id, book_id, client_id, book_count, order_date)
VALUES(1, 1, 1, 1, '2022-12-01');

    The result:

    INSERT 0 1

  5. Exchange the book_order_manual_1_prt_dec22 partition with a new table. Use the WITH VALIDATION clause to ensure that the data in the table you exchange with a partition is valid against the partition constraints. You can also use WITHOUT VALIDATION to disable such verification:

    ALTER TABLE book_order_manual EXCHANGE PARTITION FOR (DATE '2022-12-01')
WITH TABLE dec22 WITH VALIDATION;

    The result:

    NOTICE:  exchanged partition "dec22" of relation "book_order_manual" with relation "dec22"
ALTER TABLE

  6. Check that the partition contains data (using the query from the step 2):

    adb=# SELECT * FROM book_order_manual_1_prt_dec22;
 id | book_id | client_id | book_count | order_date
----+---------+-----------+------------+------------
  1 |       1 |         1 |          1 | 2022-12-01
(1 row)

  7. Check that the storage type of the partition has changed as well (using the psql command from the step 1):

    adb=# \dt+ book_order_manual*
                                       List of relations
 Schema |             Name              | Type  |  Owner  |   Storage   |  Size  | Description
--------+-------------------------------+-------+---------+-------------+--------+-------------
 public | book_order_manual             | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_apr22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_aug22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_dec22 | table | gpadmin | heap        | 32 kB  |
 public | book_order_manual_1_prt_feb22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_jan22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_jul22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_jun22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_mar22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_may22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_nov22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_oct22 | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_other | table | gpadmin | append only | 544 kB |
 public | book_order_manual_1_prt_sep22 | table | gpadmin | append only | 544 kB |
(14 rows)

By default, you cannot exchange default partitions. But you can change this behavior by setting the server configuration parameter gp_enable_exchange_default_partition to on. This allows you to use the EXCHANGE DEFAULT PARTITION clause in the ALTER TABLE command. When exchanging default partitions, be careful. Ensure the data in the new default partition does not contain values that would be valid for other leaf partitions of the partitioned table.

NOTE

  • You can exchange only the partitions that are located at the hierarchy bottom (leaf partitions).

  • You cannot exchange partitions with partitioned tables or child partitions of partitioned tables.

  • You cannot exchange partitions with tables having the DISTRIBUTED REPLICATED distribution policy.

  • When using the WITHOUT VALIDATION or EXCHANGE DEFAULT PARTITION clauses, make sure that the staging table data meets the partition specification requirements. Otherwise, queries against the partitioned table might return invalid results or even cause data corruption.

  • You can exchange partitions with external tables, yet with some limitations.
Truncate a partition

 
To delete all data from the specified partition without dropping the partition itself, you should use the ALTER TABLE command with the TRUNCATE PARTITION clause. The following example truncates the partition sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction, to which we added the data above:

  1. Check that the partition contains data:

    SELECT * FROM sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction;

    The result:

     id |    date    | fiction | region
----+------------+---------+--------
  2 | 2022-01-02 | f       | asia
  1 | 2022-01-01 | f       | asia
(2 rows)

  2. Truncate the partition. Pay attention that for a multi-level partitioned table, you should identify the full path to the specific leaf partition via ALTER PARTITION clauses. Describe every partition level in the table hierarchy that is above the target partition:

    ALTER TABLE sales_manual
ALTER PARTITION jan2022
ALTER PARTITION asia
TRUNCATE PARTITION fiction;

    The result:

    NOTICE:  truncated partition "fiction" for partition "asia" of partition "jan2022" of relation "sales_manual"
ALTER TABLE

  3. Check that the truncated partition does not contain data (using the query from the step 1):

    adb=# SELECT * FROM sales_manual_1_prt_jan2022_2_prt_asia_3_prt_fiction;
id | date | fiction | region
----+------+---------+--------
(0 rows)
NOTE
When you truncate a partition with subpartitions, the latter are automatically truncated as well.
Drop a partition

 
To drop the specified partition, you should use the ALTER TABLE command with the DROP PARTITION clause. The following example drops the partition Year2013 from the book_manual table created above (see Numeric range partitioning (manually)):

  1. Check that the partition exists:

    SELECT
  partitiontablename,
  partitionname,
  partitiontype,
  partitionlevel,
  partitionrank
FROM pg_catalog.pg_partitions
WHERE schemaname = 'public'
AND   tablename  = 'book_manual';

    The result:

         partitiontablename     | partitionname | partitiontype | partitionlevel | partitionrank
----------------------------+---------------+---------------+----------------+---------------
 book_manual_1_prt_year2013 | year2013      | range         |              0 |             1
 book_manual_1_prt_year2014 | year2014      | range         |              0 |             2
 book_manual_1_prt_year2015 | year2015      | range         |              0 |             3
 book_manual_1_prt_year2016 | year2016      | range         |              0 |             4
 book_manual_1_prt_year2017 | year2017      | range         |              0 |             5
 book_manual_1_prt_year2018 | year2018      | range         |              0 |             6
 book_manual_1_prt_year2019 | year2019      | range         |              0 |             7
 book_manual_1_prt_year2020 | year2020      | range         |              0 |             8
 book_manual_1_prt_year2021 | year2021      | range         |              0 |             9
 book_manual_1_prt_year2022 | year2022      | range         |              0 |             10
 book_manual_1_prt_other    | other         | range         |              0 |
(11 rows)

  2. Drop the partition:

    ALTER TABLE book_manual DROP PARTITION year2013;

    The result:

    ALTER TABLE

  3. Check that the partition does not exist anymore (using the query from the step 1):

         partitiontablename     | partitionname | partitiontype | partitionlevel | partitionrank
----------------------------+---------------+---------------+----------------+---------------
 book_manual_1_prt_year2014 | year2014      | range         |              0 |             1
 book_manual_1_prt_year2015 | year2015      | range         |              0 |             2
 book_manual_1_prt_year2016 | year2016      | range         |              0 |             3
 book_manual_1_prt_year2017 | year2017      | range         |              0 |             4
 book_manual_1_prt_year2018 | year2018      | range         |              0 |             5
 book_manual_1_prt_year2019 | year2019      | range         |              0 |             6
 book_manual_1_prt_year2020 | year2020      | range         |              0 |             7
 book_manual_1_prt_year2021 | year2021      | range         |              0 |             8
 book_manual_1_prt_year2022 | year2022      | range         |              0 |             9
 book_manual_1_prt_other    | other         | range         |              0 |
(10 rows)
NOTE

  • When you drop a partition with subpartitions, the latter are automatically dropped as well (with all data in them).

  • When you drop a range partition, the ranks of the rest partitions at the same level are changed (see partitionrank above).
Found a mistake? Seleсt text and press Ctrl+Enter to report it