Migrating Oracle Reference Partitioned Tables to Amazon RDS or Aurora PostgreSQL with AWS DMS: Challenges and Solutions

Oracle reference partitioning allows child tables to inherit the partitioning scheme from their parent table. However, PostgreSQL doesn't have a direct equivalent, making migration challenging. This post demonstrates how to migrate Oracle reference-partitioned tables to Amazon RDS or Aurora PostgreSQL using AWS Database Migration Service (AWS DMS). A PostgreSQL BEFORE INSERT trigger is used to fetch the partition key from the parent table and insert the record in the child table with the partition key, ensuring that child records are stored physically in the same partition as their corresponding parent records. This solution may introduce performance overhead for high-volume transactional workloads.