Convert Oracle partitions and sub-partitions to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL

Organizations are looking to migrate on-premises Oracle database workloads to Amazon Relational Database Service (Amazon RDS) for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. Most heterogeneous database migrations present unique challenges. One among them is converting Oracle schemas with partitioned (and sub-partitioned) tables to PostgreSQL-compatible partitioned tables.

PostgreSQL declarative partitioning is a flexible way to manage your partition tables and their subsequent sub-partitions. You can also use multilevel partitions, which can go to the n^th– level partitions based on your needs. You can create indexes, triggers, and constraints on each individual partition and also on all partitions together.

In this post, we focus on setting up PostgreSQL database schemas by converting a few key partition and sub-partition types. We discuss four use cases and a few recent improvements in Amazon RDS for PostgreSQL and Aurora PostgreSQL table partitioning that can help you design a better partition strategy while migrating from Oracle.

Use case 1: Migrate a range-partitioned table with automatic partition creation

We have a table called ORDERS in the TEST schema in our Oracle database, which is partitioned by range, and the partitioning is taken care of by Oracle automatically. The DDL for this table on Oracle is as follows:

CREATE TABLE TEST.ORDERS
( prodid NUMBER(6)
, customerid NUMBER
, purchase_time DATE
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (purchase_time)
INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
( PARTITION sales_q1_2022 VALUES LESS THAN (TO_DATE('01-FEB-2022','dd-MON-yyyy'))
);

Here, we have configured this table to create a new partition every month. Based on the value that is being inserted into PURCHASE_TIME column, Oracle automatically creates the partition as needed.

We insert a few records into this table to see how Oracle handles the automatic range interval partitions:

SQL> select PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where TABLE_OWNER='TEST' and TABLE_NAME='ORDERS';  
  
PARTITION_NAME HIGH_VALUE  
-------------------- --------------------------------------------------------------------------------  
SALES_Q1_2022 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA  

SQL> insert into TEST.ORDERS values (101,100,'2-Jan-2022',10,10);  

SQL> select PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where TABLE_OWNER='TEST' and TABLE_NAME='ORDERS';  

PARTITION_NAME HIGH_VALUE  
-------------------- --------------------------------------------------------------------------------  
SALES_Q1_2022 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA  

SQL> insert into TEST.ORDERS values (102,101,'2-Feb-2022',10,10);  

SQL> select PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where TABLE_OWNER='TEST' and TABLE_NAME='ORDERS';  

PARTITION_NAME HIGH_VALUE  
-------------------- --------------------------------------------------------------------------------  
SALES_Q1_2022 TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA  
SYS_P8163 TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Let’s implement similar behavior on Amazon RDS for PostgreSQL or Aurora PostgreSQL. The following is the equivalent DDL that we used for the main table:

CREATE TABLE test.orders  
( prodid integer  
, customerid integer
, purchase_time timestamptz
, quantity_sold integer  
, amount_sold numeric(10,2)  
)  
PARTITION BY RANGE (purchase_time);

We can use the pg_partman extension to create the partitioning template for the preceding table on Amazon RDS for PostgreSQL or Aurora PostgreSQL:

SELECT partman.create_parent( p_parent_table => 'test.orders',  
p_control => 'purchase_time',  
p_type => 'native',  
p_interval=> 'monthly',  
p_start_partition=> '2022-08-01 00:00:00',  
p_premake => 1);  
create_parent 
---------------
 t
(1 row)

postgres=> \d+ test.orders;  
                                           Partitioned table "test.orders"
    Column     |            Type             | Collation | Nullable | Default | Storage | Stats target | Description 
---------------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
 prodid        | integer                     |           |          |         | plain   |              | 
 customerid    | integer                     |           |          |         | plain   |              | 
 purchase_time | timestamp with time zone    |           |          |         | plain   |              | 
 quantity_sold | integer                     |           |          |         | plain   |              | 
 amount_sold   | numeric(10,2)               |           |          |         | main    |              | 
Partition key: RANGE (purchase_time)
Partitions: test.orders_p2022_08 FOR VALUES FROM ('2022-08-01 00:00:00') TO ('2022-09-01 00:00:00'),
            test.orders_p2022_09 FOR VALUES FROM ('2022-09-01 00:00:00') TO ('2022-10-01 00:00:00'),
            test.orders_default DEFAULT

After the main table is in place, we must create a partition configuration in the partman schema as this will let you define the retention period for created partitions:

UPDATE partman.part_config   
SET infinite_time_partitions = true,  
retention = '24 months',   
retention_keep_table=true   
WHERE parent_table = 'test.orders';

We use the following key parameters:

infinite_time_partitions = true – Configures the table to be able to automatically create new partitions without any limit.
retention = '24 months' – Configures the table to have a maximum retention of 24 months.
retention_keep_table=true – Configures the table so that when the retention period is due, the table isn’t deleted automatically. Instead, partitions that are older than the retention period are only detached from the parent table.

We can use pg_cron to schedule a job to run every month that creates new partitions automatically:

SELECT cron.schedule('00 00 28 * *', $$CALL partman.run_maintenance_proc()$$);

This is the way to implement automatic interval range partitioning of a table on Amazon RDS for PostgreSQL or Aurora PostgreSQL.

Use case 2: Migrate a list-partitioned table with hash sub-partitions

In this use case, we migrate a list-partitioned table with hash sub-partitions from Oracle to Amazon RDS for PostgreSQL or Aurora PostgreSQL.

The following is the table in the TEST schema in the Oracle database named ORDERS, which is partitioned by list on the CATEGORY column and sub-partitioned by hash on the ORDER_ID column. The DDL for the table as follows:

CREATE TABLE "TEST"."ORDERS" (  
    "ORDER_ID" NUMBER,  
    "CATEGORY" VARCHAR2(255),  
    "ITEM_NAME" VARCHAR2(255),  
    "CREATED_AT" DATE  
)   
PARTITION BY LIST ("CATEGORY") automatic  
SUBPARTITION BY HASH ("ORDER_ID")   
SUBPARTITIONS 6 (  
PARTITION "EMPTY_PARTITION"  
           VALUES ('empty_partition')  
);

You can verify the partitions and sub-partitions after there are a couple of inserts:

SQL> insert into test.orders values (100,'HOME','Vase',current_date);  

SQL> insert into test.orders values (101,'OFFICE','Chair',current_date);  

SQL> select PARTITION_NAME,HIGH_VALUE from dba_tab_partitions where TABLE_OWNER='TEST' and TABLE_NAME='ORDERS';  

PARTITION_NAME       HIGH_VALUE  
-------------------- --------------------  
EMPTY_PARTITION      'empty_partition'  
SYS_P8199            'HOME'  
SYS_P8206            'OFFICE'  

SQL> select PARTITION_NAME,SUBPARTITION_NAME from dba_tab_subpartitions where TABLE_OWNER='TEST' and TABLE_NAME='ORDERS';  

PARTITION_NAME            SUBPARTITION_NAME  
--------------------      --------------------  
SYS_P8206        	         SYS_SUBP8200  
SYS_P8206        	         SYS_SUBP8201  
SYS_P8206        	         SYS_SUBP8202  
SYS_P8206        	         SYS_SUBP8203  
SYS_P8206        	         SYS_SUBP8204  
SYS_P8206        	         SYS_SUBP8205  
EMPTY_PARTITION      	     SYS_SUBP8187  
EMPTY_PARTITION     	     SYS_SUBP8188  
EMPTY_PARTITION      	     SYS_SUBP8189  
EMPTY_PARTITION     	     SYS_SUBP8190  
EMPTY_PARTITION      	     SYS_SUBP8191  
EMPTY_PARTITION     	     SYS_SUBP8192  
SYS_P8199        	         SYS_SUBP8194  
SYS_P8199        	         SYS_SUBP8195  
SYS_P8199        	         SYS_SUBP8196  
SYS_P8199        	         SYS_SUBP8197  
SYS_P8199        	         SYS_SUBP8198  
SYS_P8199        	         SYS_SUBP8193  

18 rows selected.

Let’s implement similar behavior on Amazon RDS for PostgreSQL or Aurora PostgreSQL.

PostgreSQL in general can’t create list partitions automatically. But we can implement a solution where we pre-create the list partitions and the required hash sub-partitions on PostgreSQL. The following is the conversion:

CREATE TABLE IF NOT EXISTS test.orders (  
    Order_id integer,  
    category CHARACTER VARYING(255),  
    item_name CHARACTER VARYING(255),  
    created_at TIMESTAMPTZ
)  
    PARTITION BY LIST (category);  

CREATE TABLE test.orders_home PARTITION OF test.orders FOR VALUES in ('HOME')  partition by hash (category);      
CREATE TABLE test.orders_office PARTITION OF test.orders FOR VALUES in ('OFFICE')  partition by hash (category);    

CREATE TABLE test.orders_home_1 PARTITION OF test.orders_home FOR VALUES WITH (modulus 6, remainder 0);  
CREATE TABLE test.orders_home_2 PARTITION OF test.orders_home FOR VALUES WITH (modulus 6, remainder 1);  
CREATE TABLE test.orders_home_3 PARTITION OF test.orders_home FOR VALUES WITH (modulus 6, remainder 2);  
CREATE TABLE test.orders_home_4 PARTITION OF test.orders_home FOR VALUES WITH (modulus 6, remainder 3);  
CREATE TABLE test.orders_home_5 PARTITION OF test.orders_home FOR VALUES WITH (modulus 6, remainder 4);  
CREATE TABLE test.orders_home_6 PARTITION OF test.orders_home FOR VALUES WITH (modulus 6, remainder 5);  
  
CREATE TABLE test.orders_office_1 PARTITION OF test.orders_office FOR VALUES WITH (modulus 6, remainder 0);  
CREATE TABLE test.orders_office_2 PARTITION OF test.orders_office FOR VALUES WITH (modulus 6, remainder 1);  
CREATE TABLE test.orders_office_3 PARTITION OF test.orders_office FOR VALUES WITH (modulus 6, remainder 2);  
CREATE TABLE test.orders_office_4 PARTITION OF test.orders_office FOR VALUES WITH (modulus 6, remainder 3);  
CREATE TABLE test.orders_office_5 PARTITION OF test.orders_office FOR VALUES WITH (modulus 6, remainder 4);  
CREATE TABLE test.orders_office_6 PARTITION OF test.orders_office FOR VALUES WITH (modulus 6, remainder 5);

Let us review the partition and sub partitions tables that we just created above.

postgres=> \d+ test.orders  
                                          Partitioned table "test.orders"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 order_id   | integer                     |           |          |         | plain    |              | 
 category   | character varying(255)      |           |          |         | extended |              | 
 item_name  | character varying(255)      |           |          |         | extended |              | 
 created_at | timestamp with time zone    |           |          |         | plain    |              | 
Partition key: LIST (category)
Partitions: test.orders_home FOR VALUES IN ('HOME'), PARTITIONED,
            test.orders_office FOR VALUES IN ('OFFICE'), PARTITIONED


postgres=> \d+ test.orders_home  
                                       Partitioned table "test.orders_home"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 order_id   | integer                     |           |          |         | plain    |              | 
 category   | character varying(255)      |           |          |         | extended |              | 
 item_name  | character varying(255)      |           |          |         | extended |              | 
 created_at | timestamp with time zone    |           |          |         | plain    |              | 
Partition of: test.orders FOR VALUES IN ('HOME')
Partition constraint: ((category IS NOT NULL) AND ((category)::text = 'HOME'::character varying(255)))
Partition key: HASH (category)
Partitions: test.orders_home_1 FOR VALUES WITH (modulus 6, remainder 0), 
    test.orders_home_2 FOR VALUES WITH (modulus 6, remainder 1),
            test.orders_home_3 FOR VALUES WITH (modulus 6, remainder 2),
            test.orders_home_4 FOR VALUES WITH (modulus 6, remainder 3),
            test.orders_home_5 FOR VALUES WITH (modulus 6, remainder 4),
            test.orders_home_6 FOR VALUES WITH (modulus 6, remainder 5)


postgres=> \d+ test.orders_office  
                                      Partitioned table "test.orders_office"
   Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 order_id   | integer                     |           |          |         | plain    |              | 
 category   | character varying(255)      |           |          |         | extended |              | 
 item_name  | character varying(255)      |           |          |         | extended |              | 
 created_at | timestamp with time zone    |           |          |         | plain    |              | 
Partition of: test.orders FOR VALUES IN ('OFFICE')
Partition constraint: ((category IS NOT NULL) AND ((category)::text = 'OFFICE'::character varying(255)))
Partition key: HASH (category)
Partitions: test.orders_office_1 FOR VALUES WITH (modulus 6, remainder 0),
            test.orders_office_2 FOR VALUES WITH (modulus 6, remainder 1),
            test.orders_office_3 FOR VALUES WITH (modulus 6, remainder 2),
            test.orders_office_4 FOR VALUES WITH (modulus 6, remainder 3),
            test.orders_office_5 FOR VALUES WITH (modulus 6, remainder 4)
            test.orders_office_6 FOR VALUES WITH (modulus 6, remainder 5)

We can use this approach to migrate list-partitioned tables with hash sub-partitions from Oracle to Amazon RDS for PostgreSQL or Aurora PostgreSQL.

Use case 3: Implement traditional list, range, and hash table partitions in PostgreSQL

In this use case, we elaborate on approach to implement list, range, and hash table partitions in PostgreSQL.

Range partition

The table is partitioned into ranges defined by a key column or set of columns, with no overlap between the ranges of values assigned to different partitions. See the following code:

CREATE TABLE test.orders  
( prodid integer  
, customerid integer
, purchase_time TIMESTAMPTZ
, quantity_sold integer  
, amount_sold numeric(10,2)  
)  
PARTITION BY RANGE (purchase_time);  

create table test.orders_2022_q1 partition of test.orders for values from ('2022-01-01') to ('2022-03-31');    
create table test.orders_2022_q2 partition of test.orders for values from ('2022-04-01') to ('2022-06-30');   
create table test.orders_2022_q3 partition of test.orders for values from ('2022-07-01') to ('2022-09-30');  
create table test.orders_2022_q4 partition of test.orders for values from ('2022-10-01') to ('2022-12-31');

postgres=> \d+ test.orders  
                                         Partitioned table "test.orders"
    Column     |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 prodid        | integer                  |           |          |         | plain   |              | 
 customerid    | integer                  |           |          |         | plain   |              | 
 purchase_time | timestamp with time zone |           |          |         | plain   |              | 
 quantity_sold | integer                  |           |          |         | plain   |              | 
 amount_sold   | numeric(10,2)            |           |          |         | main    |              | 
Partition key: RANGE (purchase_time)
Partitions: test.orders_2022_q1 FOR VALUES FROM ('2022-01-01 00:00:00+00') TO ('2022-03-31 00:00:00+00'),
            test.orders_2022_q2 FOR VALUES FROM ('2022-04-01 00:00:00+00') TO ('2022-06-30 00:00:00+00'),
            test.orders_2022_q3 FOR VALUES FROM ('2022-07-01 00:00:00+00') TO ('2022-09-30 00:00:00+00'),
            test.orders_2022_q4 FOR VALUES FROM ('2022-10-01 00:00:00+00') TO ('2022-12-31 00:00:00+00')

postgres=> \d+ test.orders_2022_q1
                                           Table "test.orders_2022_q1"
    Column     |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 prodid        | integer                  |           |          |         | plain   |              | 
 customerid    | integer                  |           |          |         | plain   |              | 
 purchase_time | timestamp with time zone |           |          |         | plain   |              | 
 quantity_sold | integer                  |           |          |         | plain   |              | 
 amount_sold   | numeric(10,2)            |           |          |         | main    |              | 
Partition of: test.orders FOR VALUES FROM ('2022-01-01 00:00:00+00') TO ('2022-03-31 00:00:00+00')
Partition constraint: ((purchase_time IS NOT NULL) AND (purchase_time >= '2022-01-01 00:00:00+00'::timestamp with time zone) AND (purchase_time < '2022-03-31 00:00:00+00'::timestamp with time zone))
Access method: heap

List partition

The table is partitioned by explicitly listing which key values appear in each partition:

CREATE TABLE IF NOT EXISTS test.orders (  
    Order_id integer,  
    category CHARACTER VARYING(255),  
    item_name CHARACTER VARYING(255),  
    created_at TIMESTAMPTZ
)  
    PARTITION BY LIST (category);  
  
CREATE TABLE test.orders_office PARTITION OF test.orders FOR VALUES in ('office')  partition by hash (order_id);     

CREATE TABLE test.orders_home PARTITION OF test.orders FOR VALUES in ('home')  partition by hash (order_id);

Hash partition

The table is partitioned by specifying a modulus and a remainder for each partition. Each partition holds the rows for which the hash value of the partition key divided by the specified modulus will produce the specified remainder. See the following code:

CREATE TABLE IF NOT EXISTS test.product (  
    product_id integer,  
    category CHARACTER VARYING(255),  
    item_name CHARACTER VARYING(255)  
)  
    PARTITION BY hash (product_id);  

CREATE TABLE test.product_hash_1 PARTITION OF test.product FOR VALUES WITH (modulus 3, remainder 0);  
CREATE TABLE test.product_hash_2 PARTITION OF test.product FOR VALUES WITH (modulus 3, remainder 1);  
CREATE TABLE test.product_hash_3 PARTITION OF test.product FOR VALUES WITH (modulus 3, remainder 2);

Referential and Declarative Partitioning on PostgreSQL

Referential partitioning

In PostgreSQL 12 and above, we can create partitions on both parent and child tables while maintaining referential integrity. This will let you handle volumes of data while preserving referential integrity.

In the following example, we create the table test.products with hash partitioning on its product_id column, and another table test.storageunit with a primary key:

CREATE TABLE test.products (  
    product_id integer PRIMARY KEY,  
    product_name text NOT NULL  
) PARTITION BY hash (product_id);

CREATE TABLE test.products_hash1 PARTITION OF test.products FOR VALUES WITH (modulus 2, remainder 0);  
CREATE TABLE test.products_hash2 PARTITION OF test.products FOR VALUES WITH (modulus 2, remainder 1);  

CREATE TABLE test.storageunit (storageunit_id integer primary key, storageunit_pincode text not null);

We create a partitioned child table test.inventory referencing tables test.products and test.storageunit:

CREATE TABLE test.inventory (  
    product_id integer not null REFERENCES test.products,  
    storageunit_id integer not null REFERENCES test.storageunit,  
    total_amount int not null  
) partition by hash (storageunit_id);  
CREATE TABLE test.inventory_hash1 PARTITION OF test.inventory FOR VALUES WITH (modulus 2, remainder 0);  
CREATE TABLE test.inventory_hash2 PARTITION OF test.inventory FOR VALUES WITH (modulus 2, remainder 1);


postgres=> \d+ test.products;  
                Partitioned table "test.products"  
  Column  | Type  | Collation | Nullable | Default | Storage | Stats target | Description   
--------------+---------+-----------+----------+---------+----------+--------------+-------------  
 product_id   | integer |      | not null |     | plain    |       |   
 product_name | text    |      | not null |     | extended |       |   
Partition key: HASH (product_id)  
Indexes:  
  "products_pkey" PRIMARY KEY, btree (product_id)  
Referenced by:  
  TABLE "test.inventory" CONSTRAINT "inventory_product_id_fkey" FOREIGN KEY (product_id) REFERENCES test.products(product_id)  
Partitions: test.products_hash1 FOR VALUES WITH (modulus 2, remainder 0),  
            test.products_hash2 FOR VALUES WITH (modulus 2, remainder 1)  


postgres=> \d+ test.inventory;  
                Partitioned table "test.inventory"  
   Column       | Type    | Collation | Nullable | Default | Storage | Stats target | Description   
----------------+---------+-----------+----------+---------+---------+--------------+-------------  
 product_id     | integer |           | not null |         | plain  |       |   
 storageunit_id | integer |           | not null |         | plain  |       |   
 total_amount   | integer |           | not null |         | plain  |       |   
Partition key: HASH (storageunit_id)  
Foreign-key constraints:  
  "inventory_product_id_fkey" FOREIGN KEY (product_id) REFERENCES test.products(product_id)
  "inventory_storageunit_id_fkey" FOREIGN KEY (storageunit_id) REFERENCES test.storageunit(storageunit_id)  
Partitions: test.inventory_hash1 FOR VALUES WITH (modulus 2, remainder 0),  
            test.inventory_hash2 FOR VALUES WITH (modulus 2, remainder 1)

Attach and detach partitions in Amazon RDS for PostgreSQL or Aurora PostgreSQL

PostgreSQL declarative partitioning allows you to attach and detach table partitions based on your need.

By default, when you insert any data that is out of the partition range, the data lands in the DEFAULT partition of the respective PostgreSQL table. To avoid this behavior, create a partition in advance, load the data, and attach the partition to the existing partitioned table:

CREATE TABLE test.orders  
( prodid integer  
, customerid integer
, purchase_time TIMESTAMPTZ
, quantity_sold integer  
, amount_sold numeric(10,2)  
)  
PARTITION BY RANGE (purchase_time);  

create table test.orders_2022_q1 partition of test.orders for values from ('2022-01-01') to ('2022-03-31');    
create table test.orders_2022_q2 partition of test.orders for values from ('2022-04-01') to ('2022-06-30');   
create table test.orders_2022_q3 partition of test.orders for values from ('2022-07-01') to ('2022-09-30');    
create table test.orders_2022_q4 partition of test.orders for values from ('2022-10-01') to ('2022-12-31');

postgres=>\d+ test.orders  
                                         Partitioned table "test.orders"
    Column     |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 prodid        | integer                  |           |          |         | plain   |              | 
 customerid    | integer                  |           |          |         | plain   |              | 
 purchase_time | timestamp with time zone |           |          |         | plain   |              | 
 quantity_sold | integer                  |           |          |         | plain   |              | 
 amount_sold   | numeric(10,2)            |           |          |         | main    |              | 
Partition key: RANGE (purchase_time)
Partitions: test.orders_2022_q1 FOR VALUES FROM ('2022-01-01 00:00:00+00') TO ('2022-03-31 00:00:00+00'),
            test.orders_2022_q2 FOR VALUES FROM ('2022-04-01 00:00:00+00') TO ('2022-06-30 00:00:00+00'),
            test.orders_2022_q3 FOR VALUES FROM ('2022-07-01 00:00:00+00') TO ('2022-09-30 00:00:00+00'),
            test.orders_2022_q4 FOR VALUES FROM ('2022-10-01 00:00:00+00') TO ('2022-12-31 00:00:00+00')

insert into test.orders values (100,100,'2022-02-01',10,10);  
  
postgres=> select * from test.orders;  
 prodid | customerid | purchase_time | quantity_sold | amount_sold   
--------+------------+---------------+---------------+-------------  
    100 |        100 | 2022-02-01    |            10 |       10.00  
(1 row)

This solution is useful when you don’t need the old partition data. Instead of deleting data, you can detach the partition. Please note, this requires only a SHARE UPDATE EXCLUSIVE lock on the partitioned table from PostgreSQL v12 and above.

alter table test.orders detach partition test.orders_2022_q1; 
  
postgres=> \d+ test.orders  
Partitioned table "test.orders"
    Column     |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 prodid        | integer                  |           |          |         | plain   |              | 
 customerid    | integer                  |           |          |         | plain   |              | 
 purchase_time | timestamp with time zone |           |          |         | plain   |              | 
 quantity_sold | integer                  |           |          |         | plain   |              | 
 amount_sold   | numeric(10,2)            |           |          |         | main    |              | 
Partition key: RANGE (purchase_time)
Partitions: test.orders_2022_q2 FOR VALUES FROM ('2022-04-01 00:00:00+00') TO ('2022-06-30 00:00:00+00'),
            test.orders_2022_q3 FOR VALUES FROM ('2022-07-01 00:00:00+00') TO ('2022-09-30 00:00:00+00'),
            test.orders_2022_q4 FOR VALUES FROM ('2022-10-01 00:00:00+00') TO ('2022-12-31 00:00:00+00')


postgres=> select * from test.orders;  
 prodid | customerid | purchase_time | quantity_sold | amount_sold   
--------+------------+---------------+---------------+-------------  
(0 rows)

Partition which was detached above can be attached back to the main table using below commands:

postgres=> alter table test.orders attach partition test.orders_2022_q1 FOR VALUES FROM ('2022-01-01') to ('2022-03-31');  
ALTER TABLE  
  
postgres=> \d+ test.orders  
                                         Partitioned table "test.orders"
    Column     |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
---------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 prodid        | integer                  |           |          |         | plain   |              | 
 customerid    | integer                  |           |          |         | plain   |              | 
 purchase_time | timestamp with time zone |           |          |         | plain   |              | 
 quantity_sold | integer                  |           |          |         | plain   |              | 
 amount_sold   | numeric(10,2)            |           |          |         | main    |              | 
Partition key: RANGE (purchase_time)
Partitions: test.orders_2022_q1 FOR VALUES FROM ('2022-01-01 00:00:00+00') TO ('2022-03-31 00:00:00+00'),
            test.orders_2022_q2 FOR VALUES FROM ('2022-04-01 00:00:00+00') TO ('2022-06-30 00:00:00+00'),
            test.orders_2022_q3 FOR VALUES FROM ('2022-07-01 00:00:00+00') TO ('2022-09-30 00:00:00+00'),
            test.orders_2022_q4 FOR VALUES FROM ('2022-10-01 00:00:00+00') TO ('2022-12-31 00:00:00+00')
  
postgres=> select * from test.orders;  
 prodid | customerid | purchase_time | quantity_sold | amount_sold   
--------+------------+---------------+---------------+-------------  
    100 |        100 | 2022-02-01    |            10 |       10.00

Multilevel partitioning in Amazon RDS for PostgreSQL or Aurora PostgreSQL

Multilevel partitions in PostgreSQL can be created up to infinite level partitions and sub-partitions. PostgreSQL supports RANGE-RANGE, RANGE-LIST, RANGE-HASH, HASH-HASH, HASH-LIST, HASH-RANGE, LIST-LIST, LIST-RANGE and LIST-HASH which can be created in declarative partitioning.

The following is an example to demonstrate multilevel table partitioning in PostgreSQL:

CREATE TABLE IF NOT EXISTS test.orders(  
    order_id INTEGER,  
    category CHARACTER VARYING(255),  
    orderd_at TIMESTAMPTZ,  
    price NUMERIC
)  
    PARTITION BY LIST (category);
  
CREATE TABLE test.orders_office PARTITION OF test.orders FOR VALUES in ('office')  partition by hash (order_id);     
CREATE TABLE test.orders_home PARTITION OF test.orders FOR VALUES in ('home')  partition by hash (order_id);           
  
CREATE TABLE test.orders_office_1 PARTITION OF test.orders_office FOR VALUES WITH (modulus 2, remainder 0);  
CREATE TABLE test.orders_office_2 PARTITION OF test.orders_office FOR VALUES WITH (modulus 2, remainder 1);  
  
CREATE TABLE test.orders_home_1 PARTITION OF test.orders_home FOR VALUES WITH (modulus 2, remainder 0);  
CREATE TABLE test.orders_home_2 PARTITION OF test.orders_home FOR VALUES WITH (modulus 2, remainder 1);  


postgres=> \d+ test.orders  
                                         Partitioned table "test.orders"  
  Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description   
-----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------  
 order_id  | integer                     |           |          |         | extended |              |   
 category  | character varying(255)      |           |          |         | extended |              |   
 orderd_at | timestamp with time zone    |           |          |         | plain    |              |   
 price     |  numeric		         |           |          |         | plain    |              |   
Partition key: LIST (category)  
Partitions: test.orders_home FOR VALUES IN ('home'), PARTITIONED,  
            test.orders_office FOR VALUES IN ('office'), PARTITIONED  


postgres=> \d+ test.orders_office  
                                      Partitioned table "test.orders_office"  
  Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description   
-----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------  
 order_id  | integer                     |           |          |         | extended |              |   
 category  | character varying(255)      |           |          |         | extended |              |   
 orderd_at | timestamp with time zone    |           |          |         | plain    |              |   
 price     | numeric                     |           |          |         | plain    |              |   
Partition of: test.orders FOR VALUES IN ('office')  
Partition constraint: ((category IS NOT NULL) AND ((category)::text = 'office'::character varying(255)))  
Partition key: HASH (order_id)  
Partitions: test.orders_office_1 FOR VALUES WITH (modulus 2, remainder 0),  
            test.orders_office_2 FOR VALUES WITH (modulus 2, remainder 1)  


postgres=> \d+ test.orders_office_1  
                                           Table "test.orders_office_1"  
  Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description   
-----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------  
 order_id  | integer                     |           |          |         | extended |              |   
 category  | character varying(255)      |           |          |         | extended |              |   
 orderd_at | timestamp with time zone    |           |          |         | plain    |              |   
 price     | numeric                     |           |          |         | plain    |              |   
Partition of: test.orders_office FOR VALUES WITH (modulus 2, remainder 0)  
Partition constraint: ((category IS NOT NULL) AND ((category)::text = 'office'::character varying(255)) AND satisfies_hash_partition('68925'::oid, 2, 0, order_id))  
Access method: heap  


postgres=> \d+ test.orders_office_2  
                                           Table "test.orders_office_2"  
  Column   |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description   
-----------+-----------------------------+-----------+----------+---------+----------+--------------+-------------  
 order_id  | integer                     |           |          |         | extended |              |   
 category  | character varying(255)      |           |          |         | extended |              |   
 orderd_at | timestamp with time zone    |           |          |         | plain    |              |   
 price     | numeric                     |           |          |         | plain    |              |   
Partition of: test.orders_office FOR VALUES WITH (modulus 2, remainder 1)  
Partition constraint: ((category IS NOT NULL) AND ((category)::text = 'office'::character varying(255)) AND satisfies_hash_partition('68925'::oid, 2, 1, order_id))  
Access method: heap

Summary

In this post, we covered key use cases of migrating partitioned tables from Oracle to Amazon RDS for PostgreSQL or Aurora PostgreSQL. We have walked you through the approach to automatically create the RANGE partition, creating sub-partitions, creating regular table partitions with LIST, HASH and RANGE and implementing declarative partitioning. This post will enable you to define the partitioning strategy while migrating from Oracle to PostgreSQL.

If you have questions or suggestions, please leave them in the comments section below.

About the authors

Dwarka Rao is a Database Migration Specialist Solutions Architect with the Amazon Database Migration Accelerator team at Amazon Web Services. He helps customers and partners build highly scalable, available, and secure databases solutions on cloud migration projects from on-premises to AWS.

Alex Anto is a Data Migration Specialist Solutions Architect with the Amazon Database Migration Accelerator team at Amazon Web Services. He works as an Amazon DMA Advisor to help AWS customers to migrate their on-premises data to AWS Cloud database solutions.

Narendra Tawar is a Database Migration Specialist Solutions Architect on the Amazon Database Migration Accelerator team, with a strong background in database migration. He has carried out many large-scale database migrations and modernization. As a Solutions Architect, he enjoys providing technical assistance to customers that are looking to migrate and modernize their existing databases.