Pivotal Knowledge Base

Follow

How to Exchange Partitions and Subpartitions with example

Goal

During data loading if 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.

Below steps can be referred in case of exchanging partitions and subpartitions.

Solution

-- When no subpartitions exists

The command to exchange partition is:

ALTER TABLE <part_table_name>
EXCHANGE PARTITION FOR (RANK(rnk)) 
WITH TABLE <exchange_table_name> ;

Where:

     part_table_name -> Parent table name
     rnk -> Rank for the partition from pg_partitions
     exchange_table_name -> Table to exchange with partition

To check the rank use:

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 different schema (check column names with \d pg_partitions)

Example :

Lets take an example of sales table from Greenplum Admin Guide which is partitioned on month.

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 subpartitions exists

The command to exchange partiton is

ALTER TABLE <part_table> 
ALTER PARTITION FOR (RANK(rnk)) 
EXCHANGE PARTITION FOR (value) WITH TABLE <exchange_table_name>;

Where:

      part_table_name -> Parent table name
      rnk -> Partition rank (pg_partitions.partitionrank) - identifying the partition
      value -> sub-partition value - identifying the sub-partition
      exchange_table_name -> Table to exchange with partition

Example :

Using the same example mentioned above in the article and using it to exchange a subpartition 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 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

Comments

Powered by Zendesk