Migrate Oracle reference partitioned tables to Amazon RDS or Aurora PostgreSQL with AWS DMS
Database migrations from Oracle to PostgreSQL are becoming increasingly common as organizations seek to optimize database costs while leveraging the benefits of open-source database solutions. However, these migrations present specific challenges, particularly when dealing with an Oracle-specific feature such as reference partitioning, which doesn’t have a direct equivalent in PostgreSQL.
Oracle reference partitioning, introduced in Oracle 11g, allows a child table to inherit the partitioning scheme from its parent table through a referential integrity constraint. This feature ensures that child records are stored physically in the same partition as their corresponding parent records, significantly improving query performance of partition-level joins on these tables. Let’s consider the following reference partition example in Oracle, where a sales tracking system has sales records linked to multiple line items:
A SALES table partitioned by sale_date (monthly)
An ITEMS table reference-partitioned based on its foreign key relationship to SALES
This reference partitioning design ensures that when sales and the items in those sales are accessed together, the database performs optimized partition-level joins, reducing I/O and improving query performance.
Challenges when replicating reference-partitioned data
When migrating reference-partitioned data to PostgreSQL, you will face the following challenges:
Feature absence – PostgreSQL doesn’t offer native reference partitioning. Although PostgreSQL supports table partitioning, it doesn’t automatically align child records with their parent partitions.
Partition key requirements – In PostgreSQL, each partitioned table must be partitioned based on columns in that table. You can’t directly partition a child table based on a parent table’s column.
Data replication – Oracle’s reference partitioning allows child tables to inherit partitioning schemes from their parents. Though AWS DMS migrates the child table data to PostgreSQL, it replicates it without the partition key data, making standard migration approaches ineffective without special handling.
Solution overview
We demonstrate an approach for migrating Oracle reference-partitioned tables to PostgreSQL. This solution relies on a PostgreSQL BEFORE INSERT trigger that does the following:
Fetches the partition key from the parent table
Inserts the record in the child table with the partition key
The trigger ensures that any records inserted in the child table during the migration are stored in the same partition as their corresponding parent, mimicking Oracle’s reference partitioning behavior. Without this trigger, data replication for the child table will fail because of missing partition key information that’s needed for PostgreSQL’s partitioning scheme.
It’s important to note that this trigger-based solution may introduce performance overhead, especially for high-volume transactional workloads. At scale, the trigger can potentially become a performance bottleneck due to the additional database operations required for each insert. Before implementing this solution in a production environment, thoroughly test its performance impact and consider alternative approaches for very large or high-throughput systems.
Prerequisites
You must meet the following prerequisites before proceeding to implement this post’s solution:
An Oracle instance (for this post, we use Amazon RDS for Oracle) or an on-premises Oracle database.
An Aurora PostgreSQL or Amazon RDS for PostgreSQL database (for this post, we use Aurora PostgreSQL-Compatible). If you don’t already have an Aurora cluster for your PostgreSQL instance, you can create one. For instructions, see Creating an Amazon Aurora DB cluster.
AWS DMS endpoints for the source and target databases. AWS DMS endpoints provide the connection, data-store type, and location information about your data store.
Create reference-partitioned tables in Oracle database
This post uses an Amazon RDS for Oracle database and creates two sample tables called SALES and ITEMS. In the sample data model, the SALES table is partitioned by sale_date, and the ITEMS table is a reference-partitioned table based on its foreign key relationship with SALES. When a row is inserted into the SALES table, it goes to a specific partition based on its sale_date value. Any corresponding ITEMS rows are automatically placed in the matching partition, maintaining data locality for JOIN operations.
Complete the following steps to create the source tables:
Create the SALES table, partitioned by sale_date:
-- Create the SALES parent table partitioned by sale_date
CREATE TABLE sales (
sale_id NUMBER PRIMARY KEY,
customer_id NUMBER,
sale_date DATE NOT NULL,
total_amount NUMBER(10,2),
payment_method VARCHAR2(50)
)
PARTITION BY RANGE (sale_date) (
PARTITION p_2024_09 VALUES LESS THAN (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
PARTITION p_2024_10 VALUES LESS THAN (TO_DATE('2024-11-01', 'YYYY-MM-DD')),
PARTITION p_2024_11 VALUES LESS THAN (TO_DATE('2024-12-01', 'YYYY-MM-DD')),
PARTITION p_2024_12 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
PARTITION p_2025_01 VALUES LESS THAN (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
PARTITION p_2025_02 VALUES LESS THAN (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
PARTITION p_2025_03 VALUES LESS THAN (TO_DATE('2025-04-01', 'YYYY-MM-DD')),
PARTITION p_2025_04 VALUES LESS THAN (TO_DATE('2025-05-01', 'YYYY-MM-DD')),
PARTITION p_2025_05 VALUES LESS THAN (TO_DATE('2025-06-01', 'YYYY-MM-DD')),
PARTITION p_2025_06 VALUES LESS THAN (TO_DATE('2025-07-01', 'YYYY-MM-DD')),
PARTITION p_2025_07 VALUES LESS THAN (TO_DATE('2025-08-01', 'YYYY-MM-DD')),
PARTITION p_2025_08 VALUES LESS THAN (TO_DATE('2025-09-01', 'YYYY-MM-DD')),
PARTITION p_2025_09 VALUES LESS THAN (TO_DATE('2025-10-01', 'YYYY-MM-DD'))
);
Create the ITEMS table with reference partitioning based on the SALES table:
CREATE TABLE items (
item_id NUMBER PRIMARY KEY,
sale_id NUMBER NOT NULL,
product_id NUMBER,
quantity NUMBER,
price NUMBER(10,2),
CONSTRAINT fk_sale FOREIGN KEY (sale_id) REFERENCES sales(sale_id)
)
PARTITION BY REFERENCE (fk_sale);
Use the following procedure to insert sample data:
-- First, create a sequence for sale_id
CREATE SEQUENCE sale_seq START WITH 1 INCREMENT BY 1;
-- Create a sequence for item_id
CREATE SEQUENCE item_seq START WITH 1 INCREMENT BY 1;
DECLARE
v_sale_id NUMBER;
v_customer_id NUMBER;
v_sale_date DATE;
v_total_amount NUMBER(10,2);
v_payment_methods VARCHAR2(50);
v_products_per_sale NUMBER;
v_product_id NUMBER;
v_quantity NUMBER;
v_price NUMBER(10,2);
-- Array of payment methods
TYPE payment_array IS TABLE OF VARCHAR2(50);
payment_methods payment_array := payment_array('Credit Card', 'Cash', 'Debit Card', 'PayPal', 'Apple Pay');
BEGIN
-- Generate approximately 100,000 sales (this will result in roughly 500,000 items)
FOR i IN 1..100000 LOOP
-- Generate sale record
v_sale_id := sale_seq.NEXTVAL;
v_customer_id := TRUNC(DBMS_RANDOM.VALUE(1, 10001)); -- 10,000 customers
v_sale_date := TO_DATE('2024-09-01', 'YYYY-MM-DD') +
TRUNC(DBMS_RANDOM.VALUE(0, 395)); -- Random date between Sep 2024 and Sep 2025
v_payment_methods := payment_methods(TRUNC(DBMS_RANDOM.VALUE(1, 6)));
-- Random number of products per sale (3-8 items)
v_products_per_sale := TRUNC(DBMS_RANDOM.VALUE(3, 9));
-- Initialize total amount
v_total_amount := 0;
-- Insert into SALES table
INSERT INTO sales (
sale_id,
customer_id,
sale_date,
total_amount,
payment_method
) VALUES (
v_sale_id,
v_customer_id,
v_sale_date,
0, -- Will update this after calculating items
v_payment_methods
);
-- Generate items for this sale
FOR j IN 1..v_products_per_sale LOOP
v_product_id := TRUNC(DBMS_RANDOM.VALUE(1, 1001)); -- 1000 different products
v_quantity := TRUNC(DBMS_RANDOM.VALUE(1, 6)); -- 1-5 quantities
v_price := ROUND(DBMS_RANDOM.VALUE(10, 1000), 2); -- Price between 10 and 1000
-- Insert into ITEMS table
INSERT INTO items (
item_id,
sale_id,
product_id,
quantity,
price
) VALUES (
item_seq.NEXTVAL,
v_sale_id,
v_product_id,
v_quantity,
v_price
);
-- Add to total amount
v_total_amount := v_total_amount + (v_quantity * v_price);
END LOOP;
-- Update the total amount in SALES table
UPDATE sales
SET total_amount = v_total_amount
WHERE sale_id = v_sale_id;
-- Commit every 1000 sales to avoid redo log issues
IF MOD(i, 1000) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
Verify the data has been inserted:
SELECT count(1) FROM SALES;
SELECT * FROM SALES WHERE ROWNUM=1;
SELECT count(1) FROM ITEMS;
SELECT * FROM ITEMS WHERE ROWNUM=1;
Create target tables in the PostgreSQL database
Now that we have created our source tables in Oracle, we need to create corresponding target tables in PostgreSQL that will store our migrated data.
Create a sales table partitioned by sale_date:
-- Create parent table with monthly partitioning
CREATE TABLE sales (
sale_id INTEGER,
customer_id INTEGER,
sale_date DATE NOT NULL,
total_amount NUMERIC(10,2),
payment_method VARCHAR(50),
primary key (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);
-- Create partitions for the sales table
CREATE TABLE sales_2024_09 PARTITION OF sales
FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');
CREATE TABLE sales_2024_10 PARTITION OF sales
FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
CREATE TABLE sales_2024_11 PARTITION OF sales
FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE sales_2024_12 PARTITION OF sales
FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');
CREATE TABLE sales_2025_01 PARTITION OF sales
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE sales_2025_02 PARTITION OF sales
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE sales_2025_03 PARTITION OF sales
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
CREATE TABLE sales_2025_04 PARTITION OF sales
FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');
CREATE TABLE sales_2025_05 PARTITION OF sales
FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');
CREATE TABLE sales_2025_06 PARTITION OF sales
FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');
CREATE TABLE sales_2025_07 PARTITION OF sales
FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
CREATE TABLE sales_2025_08 PARTITION OF sales
FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE sales_2025_09 PARTITION OF sales
FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
-- Create default partition for sales table for any values outside the defined ranges
CREATE TABLE sales_default PARTITION OF sales DEFAULT;
Create an ITEMS table partitioned by sale_date:
-- Create child table with same partitioning scheme
CREATE TABLE items (
sale_date DATE not null,
item_id INTEGER,
sale_id INTEGER NOT NULL,
product_id INTEGER,
quantity INTEGER,
price NUMERIC(10,2),
primary key (item_id, sale_date),
FOREIGN KEY (sale_id,sale_date) REFERENCES sales(sale_id, sale_date)
) PARTITION BY RANGE (sale_date);
-- Create corresponding partitions for items
CREATE TABLE items_2024_09 PARTITION OF items
FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');
CREATE TABLE items_2024_10 PARTITION OF items
FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
CREATE TABLE items_2024_11 PARTITION OF items
FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE items_2024_12 PARTITION OF items
FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');
CREATE TABLE items_2025_01 PARTITION OF items
FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');
CREATE TABLE items_2025_02 PARTITION OF items
FOR VALUES FROM ('2025-02-01') TO ('2025-03-01');
CREATE TABLE items_2025_03 PARTITION OF items
FOR VALUES FROM ('2025-03-01') TO ('2025-04-01');
CREATE TABLE items_2025_04 PARTITION OF items
FOR VALUES FROM ('2025-04-01') TO ('2025-05-01');
CREATE TABLE items_2025_05 PARTITION OF items
FOR VALUES FROM ('2025-05-01') TO ('2025-06-01');
CREATE TABLE items_2025_06 PARTITION OF items
FOR VALUES FROM ('2025-06-01') TO ('2025-07-01');
CREATE TABLE items_2025_07 PARTITION OF items
FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');
CREATE TABLE items_2025_08 PARTITION OF items
FOR VALUES FROM ('2025-08-01') TO ('2025-09-01');
CREATE TABLE items_2025_09 PARTITION OF items
FOR VALUES FROM ('2025-09-01') TO ('2025-10-01');
-- Create default partition for items table for any values outside the defined ranges
CREATE TABLE items_default PARTITION OF items DEFAULT;
Next, we create the BEFORE INSERT trigger that maintains the relationship between sales and items data in PostgreSQL. This trigger serves as a replacement for Oracle’s reference partitioning:
-- Step 1: Create a function that will be called by the trigger
CREATE OR REPLACE FUNCTION set_item_date_from_sale()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
sale_date_value DATE;
BEGIN
-- Fetch the sale_date based on the sale_id
SELECT sale_date INTO sale_date_value
FROM sales
WHERE sale_id = NEW.sale_id;
-- If sale found, set the item_date to match the sale_date
IF FOUND THEN
insert into ITEMS values (sale_date_value, new.item_id, new.sale_id, new.product_id, new.quantity, new.price);
ELSE
RAISE EXCEPTION 'No sale found with ID %', NEW.sale_id;
END IF;
RETURN NULL;
END;
$$
-- Step 2: Create a trigger that runs before insert
CREATE or replace TRIGGER before_item_insert
BEFORE INSERT ON items
FOR EACH row
WHEN (pg_trigger_depth() = 0)
EXECUTE FUNCTION set_item_date_from_sale()
Configure AWS DMS
Now that you have created your source tables in Oracle database and your target tables in PostgreSQL database, you are ready to migrate your data with the help of AWS DMS. This section of the post walks you through the steps to migrate your data.
Create AWS DMS endpoints for the source and target database. AWS DMS endpoints provide the connection, data store type, and location information about your data store.
Alternatively, you can add AddSupplementalLogging=true in the source endpoint setting to allow AWS DMS to set up table-level supplemental logging for the Oracle database, if you have not explicitly added supplemental logging.
Create the AWS DMS tasks
Use the following steps to create the AWS DMS task:
Open the AWS DMS console
In the navigation pane on the left, under Migrate or Replicate click on Tasks
Click create task
For Task identifier, enter an identifiable name
For Source database endpoint, select the Oracle endpoint you created.
For Target database endpoint, select the Aurora PostgreSQL endpoint you created.
Select Task Mode as Provisioned
For Replication instance, choose the replication instance you created.
For Migration type, select Migrate.
For Target table preparation mode, choose Do nothing (because you’ve already created your tables).
Under Advanced task settings, set Maximum number of tables to load in parallel to 1. This ensure sequential loading of the tables, which is critical because the SALES table must be loaded before the ITEMS table for the trigger to work properly.
Under Table mappings, for Editing mode, select JSON editor. The following JSON shows an example of the table mapping rules for the AWS DMS task. Replace the schema name (“BLOGTEST”) with the name under which you created the table.
The load-order setting controls the table migration sequence; tables and views with higher values are loaded first. SALES is set to “load-order: 2” and ITEMS is set to “load-order: 1”, ensuring SALES table is loaded first.
Uncheck the Premigration assessment checkbox. Though this feature is typically recommended, you disable it as part of this blog post’s solution to simplify the setup process and avoid additional configuration requirements.
Leave everything else as default and choose Create task to start the task.
Data validation
To verify that your data has been successfully migrated without errors, follow these steps:
Verify that AWS DMS has replicated the data without any errors or failures.
Execute following queries on target databases to verify data was loaded:
SELECT count(1) FROM SALES
SELECT count(1) FROM ITEMS;
Compare a sample record between source and target databases:
SELECT * FROM SALES WHERE SALE_ID = 65531;
Source database:
Target database:
Clean up
To avoid unnecessary charges, clean up the resources that you built as part of this architecture that you are no longer using:
If you’ve conducted this test on your existing database and plan to keep it operational, make sure to explicitly drop the test tables from both the source and target databases after you’ve completed this experiment.
Conclusion
Migrating Oracle reference-partitioned tables to PostgreSQL presents unique challenges because of the fundamental difference in how these databases implement partitioning. In this post, we presented a solution that bridges the migration gap between Oracle’s reference partitioning and PostgreSQL’s declarative partitioning by:
Creating an equivalent partitioning scheme in PostgreSQL.
Using a trigger mechanism to maintain data colocation.
This approach helps to ensure that your migrated data maintains the same performance benefits of partitioning while smoothly transitioning from Oracle to PostgreSQL by using AWS DMS.
For more information on AWS DMS and its capabilities, refer to AWS DMS documentation. If you have any questions or suggestions about these migration techniques, leave your feedback in the comments.
