Migrating Oracle Partitioned Tables to PostgreSQL

Partitioning Both Databases Have — Shaped Differently

Partitioned tables are where a lot of Oracle migrations slow down, not because PostgreSQL lacks partitioning — it has mature declarative partitioning — but because the two implementations differ in a few places that quietly break assumptions. Map the concepts correctly up front and the migration is smooth. Here’s how Oracle’s partitioning translates to PostgreSQL, and the gotchas that catch Oracle DBAs.

Mapping the Partition Types

• RANGE → PARTITION BY RANGE — a direct match (dates, IDs).
• LIST → PARTITION BY LIST — a direct match (regions, statuses).
• HASH → PARTITION BY HASH — uses MODULUS/REMAINDER instead of a partition count.
• Composite (RANGE-LIST, etc.) → sub-partitioning — a PostgreSQL partition can itself be partitioned, so you nest the two levels.
• Interval (auto-create on insert) → no native equivalent; use a DEFAULT partition plus automation (a scheduled job or pg_partman) to pre-create partitions.
• Reference → no direct equivalent; carry the parent’s partition key onto the child and partition it the same way.

What the DDL Looks Like

PostgreSQL splits the declaration in two — the parent declares the strategy, and each partition declares its bounds:

CREATE TABLE orders (
  id       bigint,
  order_dt date NOT NULL,
  region   text
) PARTITION BY RANGE (order_dt);

CREATE TABLE orders_2026 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');

-- Catch-all so an out-of-range insert doesn't fail (Oracle interval's safety net)
CREATE TABLE orders_default PARTITION OF orders DEFAULT;

The Gotchas That Bite

• No global indexes. This is the big one. PostgreSQL indexes on a partitioned table are local (per-partition). A consequence: a primary key or unique constraint must include the partition key. An Oracle global unique index on a column that isn’t the partition key has no direct PostgreSQL equivalent — plan the key design around it.
• DEFAULT partition. Oracle interval partitioning auto-creates a partition for a new value; PostgreSQL routes unmatched rows to the DEFAULT partition, or errors if there isn’t one. Always add a default, or automate partition creation.
• Partition-wise joins are off by default. For large joins between two similarly-partitioned tables, turn on enable_partitionwise_join (and enable_partitionwise_aggregate) — a real speedup Oracle did implicitly.
• ATTACH / DETACH. Load into a standalone table then ATTACH PARTITION, and archive with DETACH PARTITION CONCURRENTLY — the PostgreSQL equivalent of Oracle’s partition exchange.

It Keeps Getting Better

PostgreSQL 19 (in beta) adds in-place ALTER TABLE … MERGE PARTITIONS and … SPLIT PARTITIONS, closing one of the last ergonomic gaps against Oracle’s partition maintenance — see our PostgreSQL 19 features guide. For how partitioning compares to PostgreSQL’s older inheritance mechanism, see inheritance vs partitioning.