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