Oracle Range-Interval Partitioning to Postgres: The Automation Gap

Oracle INTERVAL Partitioning: What It Does Automatically

Oracle introduced INTERVAL partitioning in 11g as an extension of range partitioning that removes the manual partition management burden. A table defined with INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) will automatically create a new monthly partition the first time a row is inserted with a partition key value that falls outside all existing partition ranges.

-- Oracle: INTERVAL partitioned table
CREATE TABLE transactions (
  txn_id      NUMBER,
  txn_date    DATE,
  amount      NUMBER(18,4),
  account_id  NUMBER
)
PARTITION BY RANGE (txn_date)
INTERVAL (NUMTOYMINTERVAL(1, 'MONTH'))
(
  -- At least one anchor partition required
  PARTITION p_before_2020 VALUES LESS THAN (DATE '2020-01-01')
);

-- Oracle automatically creates p_2024_10 when the first October 2024 row arrives
-- No DBA action required. No job needed. No failure mode.
INSERT INTO transactions VALUES (1, DATE '2024-10-01', 500.00, 42);
-- Partition SYS_P_nnn created automatically by Oracle kernel

This behavior is deeply embedded in how Oracle DBAs think about partitioned tables. On a system running for several years, there may be 50+ auto-generated partitions with system-assigned names. The DBA's job is occasional maintenance (statistics, archival, compression) — not creation.

Postgres Declarative Partitioning: What It Does Not Do

PostgreSQL 10+ declarative partitioning is a mature feature. It supports range, list, and hash partitioning with clean syntax and good optimizer support for partition pruning. What it explicitly does not do: auto-create partitions.

-- Postgres: range partitioned table
CREATE TABLE transactions (
  txn_id      BIGINT,
  txn_date    DATE,
  amount      NUMERIC(18,4),
  account_id  BIGINT
) PARTITION BY RANGE (txn_date);

-- Each partition must be created manually
CREATE TABLE transactions_2024_10
  PARTITION OF transactions
  FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');

CREATE TABLE transactions_2024_11
  PARTITION OF transactions
  FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');

-- What happens when November data arrives and there is no November partition?
INSERT INTO transactions VALUES (1, '2024-11-01', 500.00, 42);
-- ERROR: no partition of relation "transactions" found for row
-- DETAIL: Partition key of the failing row contains (txn_date) = (2024-11-01).

That error in production — on the first day of a month, during batch processing, at 2am — is the failure mode that catches migrated systems that didn't address the automation gap.

The Default Partition Safety Net

Before setting up pg_partman, add a DEFAULT partition to catch rows that don't match any existing partition:

CREATE TABLE transactions_default
  PARTITION OF transactions DEFAULT;

The default partition prevents INSERT failures when a target partition doesn't exist. But it creates a different problem: data accumulates in the default partition silently, queries against the default partition don't benefit from partition pruning, and moving data out of the default partition into the correct one requires DETACH PARTITION plus data movement — a heavier operation than simply having created the partition in advance.

The default partition is a last-resort catch, not a substitute for pre-creating partitions. Use it as a monitoring trigger: if rows appear in the default partition, your pre-creation job missed a window.

-- Monitor the default partition for unexpected data
SELECT COUNT(*) FROM transactions_default;
-- Should always be zero on a healthy system

pg_partman: The Standard Solution

pg_partman is a PostgreSQL extension that manages partition creation and maintenance automatically. It supports time-based and integer-based range partitioning, runs as a background worker or via a scheduled function call, and handles pre-creation of future partitions.

Installing pg_partman on Aurora PostgreSQL

-- On Aurora PostgreSQL, pg_partman is available as a managed extension
CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

On RDS PostgreSQL or self-managed EC2, install the extension package first:

# Amazon Linux 2 / RHEL
sudo yum install pg_partman_14  # replace 14 with your PG major version

# Ubuntu
sudo apt-get install postgresql-14-partman

Registering a Partitioned Table with pg_partman

-- Register the transactions table for monthly partitioning
SELECT partman.create_parent(
  p_parent_table   => 'public.transactions',
  p_control        => 'txn_date',
  p_type           => 'native',
  p_interval       => 'monthly',
  p_premake        => 4,         -- pre-create 4 future partitions
  p_start_partition => '2020-01-01'
);

The p_premake parameter controls how many future partitions pg_partman creates ahead of the current date. Setting this to 4 means at any point in time, the next 4 months of partitions already exist. When the maintenance function runs, it creates additional future partitions to keep the pre-made count at 4.

Running the Maintenance Function

pg_partman's maintenance function must be called on a schedule. On Aurora and RDS, use pg_cron:

-- Install pg_cron (requires rds.enable_pgcron = 1 in parameter group)
CREATE EXTENSION pg_cron;

-- Schedule pg_partman maintenance daily at 2am UTC
SELECT cron.schedule(
  'partman-maintenance',
  '0 2 * * *',
  $$SELECT partman.run_maintenance(p_analyze := false)$$
);

On self-managed EC2, use a cron job calling psql:

# /etc/cron.d/pg_partman_maintenance
0 2 * * * postgres psql -d yourdb -c "SELECT partman.run_maintenance(p_analyze := false);" >> /var/log/pg_partman.log 2>&1

The p_analyze := false parameter skips automatic ANALYZE during maintenance. Run ANALYZE separately on a schedule that fits your workload — running it during partition creation adds unnecessary I/O to the maintenance window.

Migrating Existing Oracle Partitions

When migrating a table with 50 existing Oracle partitions, you need to create the corresponding Postgres partitions before loading data. ora2pg does not handle this automatically for INTERVAL-partitioned tables — it generates the parent table DDL but may not enumerate all existing partitions if they were system-named.

Query the Oracle source to get the full partition list:

-- On Oracle source: list all partitions with their bounds
SELECT
  partition_name,
  high_value,
  partition_position,
  num_rows,
  blocks
FROM dba_tab_partitions
WHERE table_owner = 'SCHEMA_NAME'
  AND table_name  = 'TRANSACTIONS'
ORDER BY partition_position;

Convert the high_value to Postgres partition bounds in a script. The high_value column in Oracle is a LONG type storing the literal partition bound expression — for date partitions it looks like TO_DATE(' 2024-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'). Extract the date value and generate CREATE TABLE statements:

import cx_Oracle
import datetime

# Connect to Oracle and extract partition bounds
conn = cx_Oracle.connect(dsn='...')
cursor = conn.cursor()
cursor.execute("""
    SELECT partition_name, high_value
    FROM dba_tab_partitions
    WHERE table_owner = 'SCHEMA_NAME'
      AND table_name = 'TRANSACTIONS'
    ORDER BY partition_position
""")

partitions = []
for name, high_val in cursor:
    # high_val is a cx_Oracle.LOB for LONG columns
    hv_str = high_val.read() if hasattr(high_val, 'read') else str(high_val)
    # Parse date from Oracle expression
    # TO_DATE(' 2024-11-01 00:00:00', ...)
    import re
    match = re.search(r"'(\d{4}-\d{2}-\d{2})", hv_str)
    if match:
        upper_bound = match.group(1)
        partitions.append((name, upper_bound))

# Generate Postgres DDL
for i, (name, upper) in enumerate(partitions):
    if i == 0:
        lower = '2000-01-01'  # or your actual minimum
    else:
        lower = partitions[i-1][1]
    pg_name = f"transactions_{upper.replace('-', '_')[:7].replace('-', '_')}"
    print(f"CREATE TABLE {pg_name} PARTITION OF transactions")
    print(f"  FOR VALUES FROM ('{lower}') TO ('{upper}');")

Partition Pruning Verification After Migration

After migration, verify that the Postgres query planner is actually pruning partitions for date-range queries:

-- Should show Seq Scan on specific partition only, not all partitions
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*) FROM transactions
WHERE txn_date >= '2024-10-01' AND txn_date < '2024-11-01';

-- Look for: Append -> Seq Scan on transactions_2024_10
-- Bad sign: Append with scans on ALL child partitions

If the plan shows scans on all partitions, check that the query predicate exactly matches the partition key column type. A predicate against a DATE column using a TIMESTAMP parameter can defeat pruning due to implicit casting. Oracle was more permissive about this; Postgres requires exact type matches on partition keys for pruning to engage.

Operational Checklist Post-Migration

• pg_partman installed and registered for all formerly INTERVAL-partitioned tables
• Maintenance job scheduled and monitored (check logs for errors after each run)
• Default partition row count alert configured (alert if non-zero)
• At least 4 future partitions pre-created for each partitioned table
• EXPLAIN output confirms partition pruning is working for primary query patterns
• Retention/archival job defined for old partitions (Oracle INTERVAL tables often relied on DBA discretion; Postgres requires explicit DROP or DETACH)