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 nth– 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.