Table partitioning is a useful functionality that most organizations use to store huge data, and it is quite common for users to come across partition tables in Oracle that they need to migrate to PostgreSQL. Before the introduction of declarative partitioning, partition tables in PostgreSQL were created with the help of Inheritance, check constraints, and triggers combinations — quite a tedious task. In this article we will discuss migrating Oracle partition tables to PostgreSQL declarative partition tables.
PostgreSQL 12 supports list, range, hash, and composite partitioning, which is quite similar to Oracle’s partitioning methods of the same name. Let's explore how each of these methods works in both databases.
LIST PARTITION
List partitions are created with predefined values to hold in a partitioned table along with a default partition (optional) to hold all those that are not part of any specified partition. List partition syntax in Oracle and PostgreSQL are given below.
Oracle syntax
SQL> CREATE TABLE sales
(
salesman_id INTEGER Primary Key,
salesman_name VARCHAR2(30),
sales_region VARCHAR2(30),
sales_date DATE,
sales_amount INTEGER
)
PARTITION BY LIST (sales_region)
(
PARTITION p_asia VALUES ('INDIA','CHINA'),
PARTITION p_euro VALUES ('FRANCE','UK'),
PARTITION p_america VALUES ('USA','CANADA'),
PARTITION p_rest VALUES (DEFAULT)
);
Table created.
PostgreSQL syntax
postgres=# CREATE TABLE sales
(
postgres(# salesman_id INTEGER,
postgres(# salesman_name VARCHAR(30),
postgres(# sales_region VARCHAR(30),
postgres(# sales_date DATE,
postgres(# sales_amount INTEGER
postgres(# )
postgres-# PARTITION BY LIST (sales_region);
CREATE TABLE
postgres=# CREATE TABLE sales_p_asia PARTITION OF sales FOR VALUES IN ('INDIA','CHINA');
CREATE TABLE
postgres=# CREATE TABLE sales_p_euro PARTITION OF sales FOR VALUES IN ('FRANCE','UK');
CREATE TABLE
postgres=# CREATE TABLE sales_p_america PARTITION OF sales FOR VALUES IN ('USA','CANADA');
CREATE TABLE
postgres=# CREATE TABLE sales_p_rest PARTITION OF sales DEFAULT;
CREATE TABLE
Key Points
Before starting migration of partition tables and data, consider these important points.
1. Unique constraints on partitioned tables must include all the partition key columns. It is not possible to create the above partition on sales_region if salesman_id is the primary key. A work around to implement it is to create unique constraints on each partition instead of a partitioned table.
2. PostgreSQL declarative partition does not support BEFORE ROW triggers on partitioned tables. If they are necessary, they must be defined on individual partitions, not the partitioned table.
3. Oracle does not allow you to add a partition table if a DEFAULT partition is present in a partitioned table, but the same user can do this in PostgreSQL either by using the commands CREATE TABLE...PARTITION OF… or ALTER TABLE...ATTACH PARTITION….
RANGE PARTITION
Range partitions are created to hold values between a range provided on the partition key. In Oracle, only the maximum range is given while creating partition tables, whereas in PostgreSQL both minimum and maximum values of the range need to be specified. Range Partition syntax in Oracle and PostgreSQL are given below.
Oracle syntax
SQL> CREATE TABLE sales
(
salesman_id INTEGER Primary Key,
salesman_name VARCHAR2(30),
sales_region VARCHAR2(30),
sales_date DATE,
sales_amount INTEGER
)
PARTITION BY RANGE (sales_date)
(
PARTITION p_q1 VALUES LESS THAN ('31-MAR-2020'),
PARTITION p_q2 VALUES LESS THAN ('30-JUN-2020'),
PARTITION p_q3 VALUES LESS THAN ('30-SEP-2020'),
PARTITION p_q4 VALUES LESS THAN (MAXVALUE)
);
Table created.
PostgreSQL syntax
postgres=# CREATE TABLE sales
postgres-# (
postgres(# salesman_id INTEGER,
postgres(# salesman_name VARCHAR(30),
postgres(# sales_region VARCHAR(30),
postgres(# sales_date DATE,
postgres(# sales_amount INTEGER
postgres(# )
postgres-# PARTITION BY RANGE (sales_date);
CREATE TABLE
postgres=# CREATE TABLE sales_p_q1 PARTITION OF sales FOR VALUES FROM (MINVALUE) TO ('31-MAR-2020');
CREATE TABLE
postgres=# CREATE TABLE sales_p_q2 PARTITION OF sales FOR VALUES FROM ('01-APR-2020') TO ('30-JUN-2020');
CREATE TABLE
postgres=# CREATE TABLE sales_p_q3 PARTITION OF sales FOR VALUES FROM ('01-JUL-2020') TO ('30-SEP-2020');
CREATE TABLE
postgres=# CREATE TABLE sales_p_q4 PARTITION OF sales FOR VALUES FROM ('01-OCT-2020') TO (MAXVALUE);
CREATE TABLE
Key Points
Before starting migration partition tables and data, consider these important points.
1. In range partitions users need to consider the limitation of UNIQUE constraints and BEFORE ROW triggers, the same as in list partitioning.
2. As shown in the syntax above, PostgreSQL range partitioning needs a starting range. This also allows users to create a range partition with range gaps between two partitions.
3. In Oracle, users can insert NULL values, which route to partitions with MAXVALUE range, whereas in PostgreSQL range partition NULL values are not allowed.
HASH PARTITION
Hash partitions are created by using a modulus and remainder for each partition, where rows are inserted by generating hash value using these modulus and remainders. However, while creating hash partitions in Oracle, the user does not need to provide a key value. We can understand this better by reviewing the hash partition syntax in Oracle and PostgreSQL below.
Oracle syntax
SQL> CREATE TABLE sales
(
salesman_id INTEGER Primary Key,
customer_id INTEGER,
sales_region VARCHAR2(30),
sales_date DATE,
sales_amount INTEGER
)
PARTITION BY HASH (customer_id)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);
Table created.
PostgreSQL syntax
postgres=# CREATE TABLE sales
postgres-# (
postgres(# salesman_id INTEGER,
postgres(# customer_id INTEGER,
postgres(# sales_region VARCHAR(30),
postgres(# sales_date DATE,
postgres(# sales_amount INTEGER
postgres(# )
postgres-# PARTITION BY HASH (customer_id);
CREATE TABLE
postgres=# CREATE TABLE sales_p1 PARTITION OF sales FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE
postgres=# CREATE TABLE sales_p2 PARTITION OF sales FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE
postgres=# CREATE TABLE sales_p3 PARTITION OF sales FOR VALUES WITH (MODULUS 3, REMAINDER 2);
CREATE TABLE
Key Points
Before starting migration partition tables and data, consider these important points.
1. In hash partitions, users need to consider the limitations of UNIQUE constraints and BEFORE ROW triggers, the same as in list and range partitioning.
2. Both Oracle and PostgreSQL hash partitions try to evenly distribute data across partitions based on hash value, but since both databases have different methods to calculate hash values, the data present in the sales_p1 partition in PostgreSQL is different than data present in the p1 partition in Oracle.
3. In PostgreSQL the user must create all modulus-remainder partition combinations to hold complete data migrated from Oracle partitioning. For example if Oracle has p1, p2, and p3 hash partitions, and PostgreSQL has only p1 (modulus 3 remainder 0) and p2 (modulus 3 remainder 1) partitions, then there may be some data which will not fit in any partitions. To solve this either create one more p3 (modulus 3 remainder 2) partition, or instead create PostgreSQL partition p1 (modulus 2 remainder 0) and p2 (modulus 2 remainder 1) partitions.
4. NULL values in PostgreSQL hash partitioned tables will always go to partition with remainder 0.
COMPOSITE PARTITION
Oracle supports multilevel partitions — or subpartitions or composite partitions — up to two levels, whereas in PostgreSQL multilevel partitions can be created up to n levels. Most Oracle composite partitions methods — LIST-LIST, LIST-RANGE, LIST-HASH, RANGE-RANGE, RANGE-LIST, RANGE-HASH, HASH-HASH, HASH-LIST, HASH-RANGE — can be created in PostgreSQL declarative partitioning. LWe can understand this better by reviewing the LIST-RANGE partition syntax in Oracle and PostgreSQL.
Oracle syntax
SQL> CREATE TABLE sales
(
salesman_id INTEGER Primary Key,
sales_region VARCHAR2(30),
sales_date DATE,
sales_amount INTEGER
)
PARTITION BY LIST (sales_region) SUBPARTITION BY RANGE (sales_date)
(
PARTITION p_america VALUES ('USA','CANADA') (
SUBPARTITION p1_q1 VALUES LESS THAN ('30-JUN-2020'),
SUBPARTITION p1_q2 VALUES LESS THAN (MAXVALUE)),
PARTITION p_rest VALUES ('FRANCE','INDIA') (
SUBPARTITION p2_q1 VALUES LESS THAN ('30-JUN-2020'),
SUBPARTITION p2_q2 VALUES LESS THAN (MAXVALUE))
);
Table created.
PostgreSQL syntax
postgres=# CREATE TABLE sales
postgres-# (
postgres(# salesman_id INTEGER,
postgres(# sales_region VARCHAR(30),
postgres(# sales_date DATE,
postgres(# sales_amount INTEGER
postgres(# )
postgres-# PARTITION BY LIST (sales_region);
CREATE TABLE
postgres=# CREATE TABLE sales_p_america PARTITION OF sales FOR VALUES IN ('USA','CANADA') PARTITION BY RANGE (sales_date);
CREATE TABLE
postgres=# CREATE TABLE sales_p_america_q1 PARTITION OF sales_p_america FOR VALUES FROM (MINVALUE) TO ('30-JUN-2020');
CREATE TABLE
postgres=# CREATE TABLE sales_p_america_q2 PARTITION OF sales_p_america FOR VALUES FROM ('01-JUL-2020') TO (MAXVALUE);
CREATE TABLE
postgres=# CREATE TABLE sales_p_rest PARTITION OF sales FOR VALUES IN ('FRANCE','INDIA') PARTITION BY RANGE (sales_date);
CREATE TABLE
postgres=# CREATE TABLE sales_p_rest_q1 PARTITION OF sales_p_rest FOR VALUES FROM (MINVALUE) TO ('30-JUN-2020');
CREATE TABLE
postgres=# CREATE TABLE sales_p_rest_q2 PARTITION OF sales_p_rest FOR VALUES FROM ('01-JUL-2020') TO (MAXVALUE);
CREATE TABLE
Key Points
Before starting migration partition tables and data, consider these important points.
1. In composite partitions (multilevel partitions), users need to consider the limitations of UNIQUE constraints and BEFORE ROW triggers, the same as in single-level partitioning.
2. Oracle creates a system-defined subpartition when not given explicitly, but in PostgreSQL if a subpartition is preset at least one partition should be preset to hold values.
3. In the case of HASH-LIST, HASH-RANGE, and HASH-HASH composite partitions users need to make sure all partitions are present at the subpartition level, as HASH can direct values at any partition based on hash value.
There are several other differences with indexes, tuple routing, and constraints between Oracle and PostgreSQL partitioning that also need to be considered before migration. The PostgreSQL documentation can help more:
https://www.postgresql.org/docs/current/ddl-partitioning.html
https://www.postgresql.org/docs/current/sql-createtable.html