How to exchange partitions and sub-partitions
Article ID: 295582
Updated On:
Products
VMware Tanzu Greenplum
Issue/Introduction
This article explains how to exchange partitions and sub-partitions.
Environment
Cause
During data loading, if the data size is huge, then the insert will take time to load. One advantage with partitioned tables is that we can exchange the partition with another table if the other table has the valid data for that partition.
Resolution
When no sub-partitions exist
When no sub-partitions exist, the command to exchange partitions is:
ALTER TABLE <part_table_name> EXCHANGE PARTITION FOR (RANK(rnk)) WITH TABLE <exchange_table_name> ;
Where:
- part_table_name is the parent table name
- rnk is rank for the partition from
pg_partitions - exchange_table_name is the table to exchange with partition
To check the rank use, run the following query:
SELECT partitiontablename, partitionrank FROM pg_partitions WHERE partitiontablename=<partition_table_name>;
You can add more columns from pg_partitions view if there are multiple tables with the same name in a different schema.
Note: Check column names with the command \d pg_partitions.
Example
Let's look at an example of sales table from Greenplum Admin Guide which is partitioned on month.
Note: We have also created one table identical to sales table exchange_sales and inserted some rows.
gpadmin=# \d+ sales
Table "public.sales"
Column | Type | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
id | integer | | plain |
date | date | | plain |
amt | numeric(10,2) | | main |
Child tables: sales_1_prt_1,
sales_1_prt_10,
sales_1_prt_11,
sales_1_prt_12,
sales_1_prt_2,
sales_1_prt_3,
sales_1_prt_4,
sales_1_prt_5,
sales_1_prt_6,
sales_1_prt_7,
sales_1_prt_8,
sales_1_prt_9
Has OIDs: no
Distributed by: (id)
gpadmin=# \d+ exchange_sales
Table "public.exchange_sales"
Column | Type | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
id | integer | | plain |
date | date | | plain |
amt | numeric(10,2) | | main |
Has OIDs: no
Distributed by: (id)
gpadmin=# SELECT * FROM exchange_sales ;
id | date | amt
----+------------+------
11 | 2008-02-02 | 3.00
10 | 2008-02-02 | 2.00
12 | 2008-02-02 | 4.00
(3 rows)
gpadmin=# SELECT * FROM SALES ;
id | date | amt
----+------+-----
(0 rows)
Time: 47.286 ms
As you have noticed that sales table have no data, we will be exchanging partition "sales_1_prt_2" here with the data content in "exchange_sales".
We will check the rank from the query mentioned above and then exchange the partition:
gpadmin=# \d+ sales_1_prt_2
Table "public.sales_1_prt_2"
Column | Type | Modifiers | Storage | Description
--------+---------------+-----------+---------+-------------
id | integer | | plain |
date | date | | plain |
amt | numeric(10,2) | | main |
Check constraints:
"sales_1_prt_2_check" CHECK (date >= '2008-02-01'::date AND date < '2008-03-01'::date)
Inherits: sales
Has OIDs: no
Distributed by: (id)
gpadmin=# SELECT partitiontablename, partitionrank
gpadmin-# FROM pg_partitions
gpadmin-# WHERE partitiontablename='sales_1_prt_2' ;
partitiontablename | partitionrank
--------------------+---------------
sales_1_prt_2 | 2
(1 row)
Time: 12.813 ms
gpadmin=# ALTER TABLE sales
EXCHANGE PARTITION FOR (RANK(2))
WITH TABLE exchange_sales ;
ALTER TABLE
Time: 129.933 ms
gpadmin=# SELECT * FROM sales_1_prt_2 ;
id | date | amt
----+------------+------
12 | 2008-02-02 | 4.00
10 | 2008-02-02 | 2.00
11 | 2008-02-02 | 3.00
(3 rows)
Time: 54.520 ms
When sub-partitions exist
When sub-partitions exist, the command to exchange partition is:
ALTER TABLE <part_table> ALTER PARTITION FOR (RANK(rnk)) EXCHANGE PARTITION FOR (value) WITH TABLE <exchange_table_name>;
Where:
part_table_nameis the parent table namernkis the partition rank identifying the partition. For example,
pg_partitions.partitionrankvalueis the sub-partition value identifying the sub-partitionexchange_table_nameis the table to exchange with partition
Example
Using the same example mentioned above and use it to exchange a sub-partitioned table.
gpadmin=# SELECT * from exchange_sales ;
trans_id | date | amount | region
----------+------------+--------+--------
12 | 2008-02-02 | 4.00 | usa
11 | 2008-02-02 | 5.00 | usa
10 | 2008-02-02 | 3.00 | usa
(3 rows)
Time: 36.981 ms
gpadmin=# \d+ sales
Table "public.sales"
Column | Type | Modifiers | Storage | Description
----------+--------------+-----------+----------+-------------
trans_id | integer | | plain |
date | date | | plain |
amount | numeric(9,2) | | main |
region | text | | extended |
Child tables: sales_1_prt_10,
sales_1_prt_11,
sales_1_prt_12,
sales_1_prt_13,
sales_1_prt_2,
sales_1_prt_3,
sales_1_prt_4,
sales_1_prt_5,
sales_1_prt_6,
sales_1_prt_7,
sales_1_prt_8,
sales_1_prt_9,
sales_1_prt_outlying_dates
Has OIDs: no
Distributed by: (trans_id)
gpadmin=# \d+ sales_1_prt_3
Table "public.sales_1_prt_3"
Column | Type | Modifiers | Storage | Description
----------+--------------+-----------+----------+-------------
trans_id | integer | | plain |
date | date | | plain |
amount | numeric(9,2) | | main |
region | text | | extended |
Check constraints:
"sales_1_prt_3_check" CHECK (date >= '2008-02-01'::date AND date < '2008-03-01'::date)
Inherits: sales
Child tables: sales_1_prt_3_2_prt_asia,
sales_1_prt_3_2_prt_europe,
sales_1_prt_3_2_prt_other_regions,
sales_1_prt_3_2_prt_usa
Has OIDs: no
Distributed by: (trans_id)
gpadmin=# \d+ sales_1_prt_3_2_prt_usa
Table "public.sales_1_prt_3_2_prt_usa"
Column | Type | Modifiers | Storage | Description
----------+--------------+-----------+----------+-------------
trans_id | integer | | plain |
date | date | | plain |
amount | numeric(9,2) | | main |
region | text | | extended |
Check constraints:
"sales_1_prt_3_2_prt_usa_check" CHECK (region = 'usa'::text)
"sales_1_prt_3_check" CHECK (date >= '2008-02-01'::date AND date < '2008-03-01'::date)
Inherits: sales_1_prt_3
Has OIDs: no
Distributed by: (trans_id)
gpadmin=# SELECT partitiontablename, partitionrank
gpadmin-# FROM pg_partitions
gpadmin-# WHERE partitiontablename='sales_1_prt_3' ;
partitiontablename | partitionrank
--------------------+---------------
sales_1_prt_3 | 2
(1 row)
Time: 20.330 ms
Exchanging the subpartition for rank 2 ( using the rank indicated by the same query as mentioned in the first example. ) with value='usa',
gpadmin=# ALTER TABLE sales
ALTER PARTITION FOR (RANK(2))
EXCHANGE PARTITION FOR ('usa') WITH TABLE exchange_sales ;
NOTICE: exchanged partition "usa" of partition for rank 2 of relation "sales" with relation "exchange_sales"
ALTER TABLE
Time: 186.960 ms
gpadmin=# SELECT * FROM sales_1_prt_3_2_prt_usa ;
trans_id | date | amount | region
----------+------------+--------+--------
12 | 2008-02-02 | 4.00 | usa
10 | 2008-02-02 | 3.00 | usa
11 | 2008-02-02 | 5.00 | usa
(3 rows)
Time: 93.016 ms
Feedback
Yes
No
Powered by
