Partitioning
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.
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:
-
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.
-
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.
-
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).
-
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.
-
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.
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 theSTART
and theEND
keywords. TheINCLUSIVE
andEXCLUSIVE
keywords are used in conjunction withSTART
andEND
to determine whether boundary values should be included in the range. By default,START
values are inclusive andEND
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 theEND
clauses to specify the boundaries of the entire range. Then write theEVERY
clause to define the partition increment value. For date ranges, this clause requires the interval unit:day
,month
, oryear
(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 thePARTITION <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 standardSTART
andEND
clauses, which define individual intervals for each partition. Notice that there is no need to declare theEND
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
orLIST
) clause is used to define the partition key for the first partitioning level. TheSUBPARTITION BY
(RANGE
orLIST
) clause is used for all other levels. All subpartition specifications are similar to partition specifications, except that theSUBPARTITION
keyword is used instead ofPARTITION
.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 correspondingSUBPARTITION 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
andSUBPARTITION 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
|
Examples
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
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
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
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
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
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
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:
-
Create an original table without partitioning:
CREATE TABLE genre (id INT, name TEXT NOT NULL, fiction CHAR(1) NOT NULL) DISTRIBUTED BY (id);
-
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
IMPORTANTPartition structures are not copied when you create a new table via the
LIKE
clause. -
Load the original table data into the new table:
INSERT INTO genre_partitioned SELECT * FROM genre;
-
Drop the original table:
DROP TABLE genre;
-
Assign the original table name to the partitioned table:
ALTER TABLE genre_partitioned RENAME TO genre;
-
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 (likeINSERT
,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
orlist
); -
partitionlevel
— partition level in the hierarchy (starting with0
for partitions at the first level); -
partitionrank
— rank of the partition compared to other partitions of the same level (starting with1
). 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 tableSELECT 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 tableSELECT 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 tableSELECT 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 theWHERE
clause.Query resultschemaname | 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 with0
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 resultschemaname | 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 with0
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 resultrelname | 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 |
Operations with partitions
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:
-
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
-
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
-
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
-
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 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)).
-
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 namebook_1_prt_2
, where2
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)
-
Rename the partition as follows:
ALTER TABLE book RENAME PARTITION FOR(rank(1)) TO year2013;
The result:
ALTER TABLE
-
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)
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):
-
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
-
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
-
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)
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)):
-
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
-
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"
-
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
-
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
|
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)):
-
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)
-
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)
-
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
-
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
-
Exchange the
book_order_manual_1_prt_dec22
partition with a new table. Use theWITH VALIDATION
clause to ensure that the data in the table you exchange with a partition is valid against the partition constraints. You can also useWITHOUT 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
-
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)
-
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
|
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:
-
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)
-
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
-
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.
|
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)):
-
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)
-
Drop the partition:
ALTER TABLE book_manual DROP PARTITION year2013;
The result:
ALTER TABLE
-
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
|