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:
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:
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:
We can use the pg_partman extension to create the partitioning template for the preceding table on Amazon RDS for PostgreSQL or Aurora PostgreSQL:
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:
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:
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:
You can verify the partitions and sub-partitions after there are a couple of inserts:
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:
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.
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:
The table is partitioned by explicitly listing which key values appear in each 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:
Referential and Declarative Partitioning on PostgreSQL
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:
We create a partitioned child table test.inventory referencing tables test.products and test.storageunit:
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:
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.
Partition which was detached above can be attached back to the main table using below commands:
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:
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.
Read MoreAWS Database Blog