Oracle Exadata to PostgreSQL 18 Migration: An Airline Operations Case Study from the MinervaDB Data Migration Team

This is the MinervaDB Data Migration Team's write-up of an Oracle Exadata to PostgreSQL 18 migration for an airline operations platform: crew scheduling, flight status, aircraft rotation and the reporting that hangs off them. The system left an Exadata X9M-2 quarter rack running Oracle 19c RAC and landed on community PostgreSQL 18 on bare metal in the airline's own data centres, under Patroni, with pgBackRest for backup and point-in-time recovery. The client is anonymised and every figure below is rounded, but the method, the SQL, the PL/SQL mapping and the sizing arithmetic are exactly what we used.

The short version of the outcome: the workload met the p95 latency and peak-throughput targets we set from the Oracle AWR baselines, on three commodity servers, without a compatibility layer, and with the PL/SQL estate rewritten rather than emulated. The longer version, which is the useful one, is below. It is organised the way the engagement was: assessment, target selection, capacity planning and sizing, schema mapping, SQL mapping, PL/SQL to PL/pgSQL, data movement and cutover, and what we would do differently.

Starting state: the Exadata estate and the forcing constraint

The source was an Exadata X9M-2 quarter rack: two database servers, each with two 32-core Intel Xeon Platinum 8358 sockets and 1 TB of memory, and three High Capacity storage servers, each with sixteen-core Xeon storage CPUs, PMem in front of NVMe flash in front of 18 TB disks, all on the RoCE fabric. Oracle 19c ran as a two-node RAC with three pluggable databases, of which one, the operations PDB, was the migration scope. Allocated size was around 12 TB with roughly 9 TB of live data, the rest being free space inside tablespaces, undo and a large temp.

The workload profile that sized the PostgreSQL 18 target came from AWR, not from interviews. Peak periods (early morning rotation building and the evening crew-legality run) showed roughly 9,000 executions per second, of which about 70 percent were single-row lookups and short-range scans by flight, tail number or crew ID, the remainder being the legality and pairing logic that runs inside PL/SQL.

Around 2,400 concurrent sessions, most of them idle in application pools. Redo generation peaked near 40 MB/s. Physical reads were low because the buffer cache absorbed almost everything; the storage cells were doing very little smart scan for this PDB, which turned out to be the most important sizing fact of the whole engagement and I will come back to it.

The forcing constraint was commercial and contractual rather than technical: the Exadata support renewal and the Oracle licence position were coming up together, and the airline's platform group had already standardised on PostgreSQL for new services. The technical question we were hired to answer was whether the operations platform, with its PL/SQL and its RAC dependency, could move without the performance regression everyone assumed came with leaving Exadata.

Assessment: inventory first, opinions later

Every Oracle Exadata to PostgreSQL 18 migration we run starts from the Oracle catalog, because interviews undercount PL/SQL by a factor of two or three. The inventory queries are short:

-- Object inventory for the schemas in scope
SELECT object_type,
       COUNT(*) AS objects
FROM   dba_objects
WHERE  owner IN ('OPS', 'CREW', 'FLT', 'RPT')
GROUP  BY object_type
ORDER  BY objects DESC;

-- PL/SQL volume by unit
SELECT owner, name, type,
       COUNT(*) AS loc
FROM   dba_source
WHERE  owner IN ('OPS', 'CREW', 'FLT', 'RPT')
GROUP  BY owner, name, type
ORDER  BY loc DESC;

-- Feature dependencies that have no direct community-PostgreSQL equivalent
SELECT owner, name, type, text
FROM   dba_source
WHERE  owner IN ('OPS', 'CREW', 'FLT', 'RPT')
AND    REGEXP_LIKE(UPPER(text),
       'PRAGMA AUTONOMOUS_TRANSACTION|DBMS_AQ|DBMS_SCHEDULER|UTL_FILE|DBMS_LOB|BULK COLLECT|FORALL|CONNECT BY|DBMS_SQL|UTL_HTTP|DBMS_RLS|FLASHBACK');

The rounded inventory for the operations PDB, and the conversion route we assigned to each class, is the matrix that the effort estimate came from:

Two findings from this phase changed the plan. First, the RAC dependency was an availability requirement, not a scale requirement; the second instance was there to survive a node failure, and inter-instance traffic showed it was carrying a fraction of the load. That maps to a Patroni-managed replica set, not to a distributed PostgreSQL. Second, the six ON COMMIT fast-refresh materialised views were load-bearing for the reporting schema and PostgreSQL 18 has no incremental refresh in core; they needed a design decision, not a conversion.

ora2pg 25.0 did the mechanical part of the assessment. Its migration-cost report is a useful floor for the estimate, not the estimate:

# ora2pg 25.0, assessment mode against the operations PDB
ora2pg -c ora2pg.conf -t SHOW_REPORT --estimate_cost --dump_as_html \
       -O "SCHEMA=OPS,CREW,FLT,RPT" > ops_pdb_assessment.html

# Object-level detail for the matrix
ora2pg -c ora2pg.conf -t SHOW_TABLE   -O "SCHEMA=OPS"
ora2pg -c ora2pg.conf -t SHOW_COLUMN  -O "SCHEMA=OPS"

Why community PostgreSQL 18, and what we rejected

We considered four targets and wrote down why three lost, because this section is what the client's architecture board actually wanted to read.

EDB Postgres Advanced Server with its Oracle-compatible PL/SQL dialect would have cut the package rewrite substantially. We rejected it because the airline's stated goal was to remove vendor lock-in, and EPAS trades Oracle lock-in for EDB lock-in. That is a legitimate trade when the rewrite budget is the binding constraint; here it was not, and 90,000 lines of PL/SQL is a rewrite a team of four can do in a quarter with the right tooling. We said so in writing and the board agreed.

PostgreSQL 17 was the safer-looking landing zone when we started scoping in early 2026, with 18 only a few minors old. We chose 18 for three reasons specific to a migration, not because it was newer. The pg_upgrade statistics carry-over matters for the next major upgrade rather than this cutover, but it means the client is not planning a stats-cold upgrade during stabilisation. PostgreSQL 18's asynchronous I/O and the default effective_io_concurrency of 16 change how a multi-terabyte table scans on NVMe, which affected sizing. And 18's end of life is November 2030, four years from cutover. PostgreSQL 19 was still in beta throughout the project and is not a production target until it has a couple of minors behind it.

A managed cloud PostgreSQL 18 was rejected on data residency and on the airline's requirement to run the operations platform in its own two data centres. Distributed PostgreSQL (Citus, or a YugabyteDB-style fork) was rejected because the workload is not sharded by nature and, as the AWR numbers showed, does not need to be; a single well-sized primary with synchronous standbys covers it with headroom.

Capacity planning and sizing the PostgreSQL 18 stack

The mistake we see most often on an Exadata exit is sizing the PostgreSQL 18 hardware from Exadata's CPU utilisation. Exadata CPU utilisation on the database servers understates the work because smart scan, storage indexes and flash cache absorb I/O and filtering on the cells. On a PostgreSQL 18 target every one of those bytes comes back to the database host. So we size from logical work, not from host utilisation. The inputs, all from AWR and ASH over a four-week window covering month-end and a schedule change:

CPU

Twenty-two Oracle CPU-seconds per second is about 22 fully busy cores at peak. We remove the RAC and ASM overhead (measured from the instance-level wait profile at around 10 percent), then add PostgreSQL's own background work. Autovacuum on a 9 TB estate with a 40 MB/s write rate is real CPU, checkpointing is real CPU, and PostgreSQL's connection processes carry per-process overhead that Oracle's shared server does not.

Our working rule from previous exits is 1.3 to 1.5 times the corrected Oracle CPU figure for headroom on a single primary, so 28 to 30 cores at peak. We specified 64 cores per node (two 32-core sockets) to leave room for reporting queries that were about to lose smart scan, and because NVMe throughput on a modern two-socket box is wasted with fewer cores driving it.

Memory

The working set was measured, not assumed. We restored the PDB to a scratch server, ran the captured top-50 SQL plus the batch jobs against a cold cache with pg_buffercache and pg_stat_io sampling, and watched the buffer pool stabilise at roughly 380 GB of distinct blocks touched in a peak hour. That set the memory floor: 1 TB per node, with shared_buffers at 256 GB and the remainder left to the OS page cache, work_mem allocations and maintenance operations. We do not go past a quarter of RAM for shared_buffers on a mixed workload without a measured reason, and here the double-buffering cost of going higher was not worth the marginal hit-rate gain we saw in testing.

Storage

Nine terabytes of Oracle data does not become nine terabytes of PostgreSQL 18 data. NUMBER-to-numeric mapping, the absence of Oracle's row-level compression on a few large tables, and PostgreSQL's per-tuple header made the converted data about 10 percent larger; the index estate shrank because we dropped redundant indexes. We plan for the converted size times a 1.3 bloat and maintenance factor, plus WAL retention for the synchronous replica and PITR, plus space to rebuild the largest table's indexes concurrently.

The specification per node was six 7.68 TB NVMe drives in RAID 10 (about 23 TB usable), a separate pair of 1.92 TB NVMe for WAL, and the pgBackRest repository on object storage at the DR site. The WAL device was sized for sustained 40 MB/s writes with fsync latency under a millisecond, which any current enterprise NVMe delivers; we measured it with pg_test_fsync before accepting the hardware.

Connections

Twenty-four hundred sessions cannot become 2,400 PostgreSQL 18 backends. PgBouncer in transaction pooling mode fronts the primary with a server pool sized from active sessions, not connected sessions: 60 active at peak became a default pool of 96 with a reserve, and max_connections on the server was set to 300 to leave room for replication, monitoring and administrative sessions. The application team had to remove session-state assumptions (temporary tables and SET commands that assumed a sticky session) that transaction pooling breaks, and PgBouncer 1.24 and later enable prepared statements by default, which the JDBC driver configuration had to account for.

The postgresql.conf that came out of the sizing

Only the parameters we changed from the PostgreSQL 18 defaults, with the reasoning and the restart requirement, because that is the format the client's change board needed:

Two of those need a warning. random_page_cost at 1.1 is right for local NVMe and wrong for anything network-attached; it is the first thing we check when a client copies a config between environments. And io_workers at 12 was arrived at by measuring, starting from the default of 3, doubling, and watching pg_stat_io and the I/O wait events; it is not a formula, and on a smaller box 6 was enough.

Schema mapping: Oracle to PostgreSQL 18

The type mapping is the part of an Oracle Exadata to PostgreSQL 18 migration that ora2pg gets mostly right and that we still review column by column on the hot tables, because the defaults are conservative in a way that costs performance.

The crew assignment table is a good example of where PostgreSQL 18 gave us something Oracle did not have in the schema at all. Crew legality rules forbid overlapping assignments for the same crew member. On Oracle this was enforced by a trigger and a package. On PostgreSQL 18 it is a temporal primary key:

-- PostgreSQL 18: temporal primary key replaces the Oracle trigger + package pair
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE crew.assignment (
    assignment_id   bigint GENERATED ALWAYS AS IDENTITY,
    crew_member_id  bigint      NOT NULL,
    pairing_id      bigint      NOT NULL,
    duty_period     tstzrange   NOT NULL,
    base_iata       char(3)     NOT NULL,
    duty_hours      numeric(5,2)
        GENERATED ALWAYS AS (
            EXTRACT(EPOCH FROM (upper(duty_period) - lower(duty_period))) / 3600.0
        ) STORED,
    created_at      timestamptz NOT NULL DEFAULT now(),
    row_uid         uuid        NOT NULL DEFAULT uuidv7(),
    CONSTRAINT pk_assignment
        PRIMARY KEY (crew_member_id, duty_period WITHOUT OVERLAPS),
    CONSTRAINT fk_assignment_crew
        FOREIGN KEY (crew_member_id) REFERENCES crew.member (crew_member_id),
    CONSTRAINT ck_assignment_period
        CHECK (NOT isempty(duty_period))
);

-- range-partitioned tables (flight_leg, by operating_date) keep their monthly layout;
-- assignment stays unpartitioned so the temporal primary key can be a single GiST index
CREATE INDEX ix_assignment_pairing
    ON crew.assignment (pairing_id, crew_member_id);

The table is deliberately not partitioned. A unique or primary key on a partitioned table has to include the partition key, and a temporal key over a range column cannot do that cleanly, so the 38 partitioned tables in the estate are the big append-only ones (flight_leg by operating_date, the movement log, the audit tables) and the assignment table, at a few hundred million rows, stays whole. The WITHOUT OVERLAPS constraint removed a 300-line trigger and package pair, and a set of race conditions that had been patched around for years, because a constraint is checked under the same lock the row insert takes and the trigger was not.

Virtual Private Database policies became row-level security. This is the pattern for the nine policies, shown for the base-scoped one:

-- Oracle: DBMS_RLS.ADD_POLICY(... policy_function => 'sec.base_predicate' ...)
-- PostgreSQL 18: the predicate becomes a policy on the table

ALTER TABLE ops.flight_leg ENABLE ROW LEVEL SECURITY;
ALTER TABLE ops.flight_leg FORCE ROW LEVEL SECURITY;   -- applies to the table owner too

CREATE POLICY p_flight_leg_base ON ops.flight_leg
    FOR ALL
    TO ops_app, ops_reporting
    USING (
        origin_base = current_setting('app.base_iata', true)
        OR pg_has_role(current_user, 'ops_network_control', 'member')
    );

-- the application sets the context once per transaction through PgBouncer:
-- SET LOCAL app.base_iata = 'BLR';

The SET LOCAL is deliberate. Under transaction pooling a plain SET leaks to whichever client next gets the server connection; SET LOCAL dies with the transaction, which is the property VPD's session context gave you for free and PgBouncer takes away.

Materialised views

The six ON COMMIT fast-refresh materialised views had no equivalent and we did not pretend otherwise. Three were replaced by summary tables maintained by statement-level triggers on the base tables (the same mechanism Oracle used underneath, made explicit). Two were replaced by ordinary views once we confirmed PostgreSQL 18 ran the underlying aggregate fast enough with parallel query and a covering index, which Oracle's design predated. One, the crew-hours ledger, kept its trigger-maintained summary plus a nightly REFRESH MATERIALIZED VIEW CONCURRENTLY reconciliation so drift could be detected and corrected rather than assumed away.

The empty-string audit

Oracle treats '' as NULL; PostgreSQL 18 does not. Every migration has this, and it is a silent-corruption class rather than an error class, so we treat it as a test-suite item. The audit query against the Oracle source found the columns that could ever carry the ambiguity:

-- On Oracle: which VARCHAR2 columns are nullable and are compared to '' or wrapped in NVL in code
SELECT DISTINCT c.table_name, c.column_name
FROM   dba_tab_columns c
JOIN   dba_source s
       ON s.owner = c.owner
       AND REGEXP_LIKE(UPPER(s.text), '(NVL\s*\(\s*' || c.column_name || '|' || c.column_name || '\s*(=|<>|!=)\s*'''')')
WHERE  c.owner = 'OPS'
AND    c.data_type = 'VARCHAR2'
AND    c.nullable = 'Y';

Every column that came back got a CHECK (col <> '') constraint on the PostgreSQL 18 side and a load-time transform that turned empty strings into NULL, so the converted code could keep its IS NULL semantics without a per-predicate rewrite.

SQL mapping: the patterns that actually appeared

ora2pg converts the syntactic Oracle-isms. What it cannot do is tell you which converted statement will plan badly. We took the AWR top 50 by elapsed time and by executions, converted them, and ran each under EXPLAIN (ANALYZE, BUFFERS) (buffers are on by default in PostgreSQL 18's EXPLAIN ANALYZE) against the restored data. The recurring patterns:

Old-style outer joins were the most common conversion, and ora2pg handles them. CONNECT BY appeared in the pairing engine for building multi-leg duty chains and became a recursive CTE; the performance was comparable because both are effectively iterative. ROWNUM pagination became FETCH FIRST n ROWS ONLY, but the fifteen queries that used ROWNUM as a row-number-in-order-of-arrival had to be checked one by one because that behaviour is undefined without ORDER BY in both engines and Oracle happened to be consistent about it.

The interesting case was the flight-status lookup, the single most executed statement in the system. On Oracle it used a composite index on (operating_date, carrier_code, flight_number, leg_sequence) and was usually called with all four columns. A second, heavily used variant called it without carrier_code because the caller had the flight number from a codeshare feed. Oracle satisfied that with an index skip scan. PostgreSQL before 18 would not, and the standard fix was a second index. PostgreSQL 18 added skip scan for multicolumn B-tree indexes, and the plan shows it:

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF)
SELECT leg_id, status_code, eta_utc
FROM   ops.flight_leg
WHERE  operating_date = DATE '2026-09-07'
AND    flight_number  = 1207
AND    leg_sequence   = 1;

-- Index Only Scan using ix_flight_leg_lookup on flight_leg
--   Index Cond: ((operating_date = '2026-09-07'::date) AND (flight_number = 1207) AND (leg_sequence = 1))
--   Index Searches: 6          <-- one search per distinct carrier_code on that date: the skip scan
--   Heap Fetches: 0
--   Buffers: shared hit=31
-- (abridged; the Index Searches line is new EXPLAIN output in PostgreSQL 18)

Six index searches instead of a second 40 GB index. The condition for skip scan to pay off is a low-cardinality leading column being skipped, which carrier_code is (the airline plus a handful of codeshare partners). We checked the Index Searches count under load rather than trusting the plan shape; when the skipped column has thousands of distinct values, skip scan is worse than a dedicated index and the planner usually, but not always, knows it.

Hints were the other PostgreSQL 18 conversation. The PL/SQL estate carried around 80 /*+ ... */ hints, most of them stale. Community PostgreSQL 18 has no hints and we did not install pg_hint_plan; we fixed the underlying statistics problems instead, which in four cases meant CREATE STATISTICS on correlated columns (base and carrier, date and season) and in one case a partial index. PostgreSQL 19's in-core plan advice will change this conversation for future migrations; it was not an option for this one.

PL/SQL to PL/pgSQL: how 110 packages became schemas and functions

Packages do not exist in community PostgreSQL 18. The mechanical mapping is one schema per package, one function or procedure per package member, and a decision about package state. Ninety of the 110 packages were stateless once we looked, which meant the mapping was mechanical. The other 20 held session state in package variables (the current legality rule set, cached lookup tables, a "current run ID" for the rotation engine). Those became either SET LOCAL custom GUCs read with current_setting() for scalar state, or a session-scoped unlogged table keyed by pg_backend_pid() for anything larger. The GUC route is the one that survives transaction pooling correctly, so it was the default.

Here is a cut-down member of the legality package as it ran on Oracle and as it runs on PostgreSQL 18. It computes the cumulative duty hours for a crew member inside a rolling window, using BULK COLLECT because that was the idiom:

-- Oracle PL/SQL (abridged), CREW.LEGALITY package
FUNCTION duty_hours_in_window (
    p_crew_member_id IN NUMBER,
    p_window_end     IN TIMESTAMP WITH TIME ZONE,
    p_window_days    IN NUMBER DEFAULT 7
) RETURN NUMBER IS
    TYPE t_hours IS TABLE OF NUMBER;
    l_hours  t_hours;
    l_total  NUMBER := 0;
BEGIN
    SELECT duty_hours
    BULK COLLECT INTO l_hours
    FROM   crew.assignment
    WHERE  crew_member_id = p_crew_member_id
    AND    duty_start >= p_window_end - p_window_days
    AND    duty_start <  p_window_end;

    FOR i IN 1 .. l_hours.COUNT LOOP
        l_total := l_total + l_hours(i);
    END LOOP;
    RETURN l_total;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN 0;
    WHEN OTHERS THEN
        crew.errlog.log_error('duty_hours_in_window', SQLERRM);   -- autonomous transaction inside
        RAISE;
END duty_hours_in_window;

-- PostgreSQL 18 PL/pgSQL, schema crew_legality (one schema per former package)
CREATE OR REPLACE FUNCTION crew_legality.duty_hours_in_window (
    p_crew_member_id bigint,
    p_window_end     timestamptz,
    p_window_days    integer DEFAULT 7
) RETURNS numeric
LANGUAGE plpgsql
STABLE
PARALLEL SAFE
AS $$
DECLARE
    l_total numeric := 0;
BEGIN
    -- BULK COLLECT + loop collapses to a set-based aggregate; the planner does the loop
    SELECT COALESCE(SUM(a.duty_hours), 0)
    INTO   l_total
    FROM   crew.assignment AS a
    WHERE  a.crew_member_id = p_crew_member_id
    AND    a.duty_period && tstzrange(p_window_end - make_interval(days => p_window_days),
                                      p_window_end, '[)');
    RETURN l_total;
EXCEPTION
    WHEN OTHERS THEN
        -- no autonomous transactions, and a STABLE function cannot write anyway:
        -- the NOTICE goes to the server log (shipped centrally); the durable errlog row
        -- is written by the calling procedure using the savepoint pattern below
        RAISE NOTICE 'crew_legality.duty_hours_in_window failed: % (%)', SQLERRM, SQLSTATE;
        RAISE;
END;
$$;

Three things in that conversion are the whole story of the PL/SQL work. The BULK COLLECT and loop became a single aggregate, which is what almost every BULK COLLECT in the estate wanted to be; the Oracle idiom exists to avoid context switches between the SQL and PL/SQL engines, and PL/pgSQL does not have that boundary in the same way. The NO_DATA_FOUND handler disappeared because SELECT ... INTO in PL/pgSQL leaves the variable NULL rather than raising when no row is found (the opposite of Oracle; this is the second most common behavioural trap after the empty-string one, and we grep for every SELECT INTO and decide whether it needs STRICT). And the autonomous-transaction error log is gone.

Autonomous transactions were the one Oracle feature with no clean answer, and we had 14 packages using them, all for the same purpose: write an error or audit row that survives the rollback of the enclosing transaction. We rejected dblink loopback connections for this, because under a 9,000-transaction-per-second workload a loopback connection per error is a connection storm waiting for a bad day. The pattern we used instead: the error row is inserted in the enclosing transaction, and the enclosing transaction is not rolled back on a business exception; the exception is caught at the procedure boundary, the work is undone with a savepoint, and the log row is kept.

For the two cases where the transaction had to abort entirely, the application logs, not the database.

-- PostgreSQL 18 savepoint pattern replacing PRAGMA AUTONOMOUS_TRANSACTION for audit-on-failure
CREATE OR REPLACE PROCEDURE crew_ops.apply_pairing (p_pairing_id bigint)
LANGUAGE plpgsql
AS $$
BEGIN
    BEGIN
        -- the unit of work that may fail
        PERFORM crew_ops.assign_pairing_members(p_pairing_id);
        PERFORM crew_legality.validate_pairing(p_pairing_id);
    EXCEPTION
        WHEN check_violation OR exclusion_violation THEN
            -- inner block acts as a savepoint: its writes are rolled back, the outer transaction survives
            INSERT INTO crew_ops.errlog (unit_name, sqlstate, message, ref_id, logged_at)
            VALUES ('crew_ops.apply_pairing', SQLSTATE, SQLERRM, p_pairing_id, clock_timestamp());
            RETURN;   -- caller commits: the audit row persists, the pairing does not
    END;
END;
$$;

The remaining constructs mapped as follows. DBMS_SCHEDULER jobs became pg_cron entries for anything that is a single SQL statement or procedure call, and moved to the airline's existing application scheduler for the twelve job chains with dependencies, because pg_cron does not do chains and pretending otherwise produces fragile shell scripts. UTL_FILE writes (crew reports to a shared filesystem) moved to the application tier with COPY ... TO STDOUT feeding it; the database no longer touches the filesystem.

DBMS_LOB calls became ordinary text and bytea operations. REF CURSOR outputs became refcursor or, more often, set-returning functions, which the JDBC layer handles better. DBMS_AQ is covered above. Object types with methods became composite types plus functions taking the composite as the first argument, which PostgreSQL's function-call syntax lets you write as value.method() anyway.

-- Partition maintenance that used to be a DBMS_SCHEDULER job, now pg_cron on PostgreSQL 18
SELECT cron.schedule(
    'flight_leg_partitions',
    '0 2 1 * *',                       -- 02:00 on the 1st, server time zone UTC
    $$ CALL ops_maint.create_month_partition('ops.flight_leg', date_trunc('month', now() + interval '2 months')) $$
);

-- Monthly reconciliation of the crew-hours ledger against its summary
SELECT cron.schedule(
    'crew_hours_ledger_refresh',
    '30 3 * * *',
    $$ REFRESH MATERIALIZED VIEW CONCURRENTLY rpt.crew_hours_ledger $$
);

Every converted unit was tested on PostgreSQL 18 against the Oracle original with a differential harness: the same inputs, both engines, results compared, and every mismatch classified as a defect in the conversion or a defect in the original that Oracle had been hiding. The second category was not empty.

Data movement and cutover

The nine terabytes moved twice: once in bulk for the parallel-run environment, and once more through change data capture for the cutover. Bulk load used ora2pg's parallel COPY export straight into PostgreSQL 18 with indexes and foreign keys dropped, then rebuilt in parallel afterwards; on the target hardware the load ran at a rate bounded by the Oracle-side extract, not by PostgreSQL 18.

# ora2pg 25.0 bulk data export, direct to PostgreSQL 18, 16 parallel table jobs, 8 parallel per-partition jobs
ora2pg -c ora2pg.conf -t COPY \
       -O "SCHEMA=OPS" \
       -O "PG_DSN=dbi:Pg:dbname=ops;host=pg-node1;port=5432" \
       -O "PG_USER=${PG_MIG_USER}" -O "PG_PWD=${PG_MIG_PASSWORD}" \
       -O "JOBS=8" -O "ORACLE_COPIES=8" -O "PARALLEL_TABLES=16" \
       -O "DROP_INDEXES=1" -O "DROP_FKEY=1" -O "TRUNCATE_TABLE=1" \
       -O "DATA_LIMIT=20000" -O "BLOB_LIMIT=500" \
       -O "EMPTY_LOB_NULL=1" -O "REPLACE_ZERO_DATE=-INFINITY"

# then rebuild, in parallel, with the maintenance memory sized above
psql -h pg-node1 -d ops -c "SET maintenance_work_mem = '8GB'; SET max_parallel_maintenance_workers = 8;" \
     -f ops_indexes.sql

Validation after every load into PostgreSQL 18 was row counts per table plus a content checksum on the 40 critical tables, computed the same way on both sides so the numbers are comparable:

-- Oracle side
SELECT COUNT(*) AS rows_,
       SUM(ORA_HASH(leg_id || '|' || flight_number || '|' || TO_CHAR(std_utc, 'YYYYMMDDHH24MISS') || '|' || status_code)) AS chk
FROM   ops.flight_leg
WHERE  operating_date >= DATE '2026-01-01';

-- PostgreSQL 18 side: same columns, same formatting, a 64-bit hash folded to match ranges
SELECT COUNT(*) AS rows_,
       SUM(hashtextextended(leg_id::text || '|' || flight_number::text || '|' ||
                            to_char(std_utc AT TIME ZONE 'UTC', 'YYYYMMDDHH24MISS') || '|' || status_code, 0)) AS chk
FROM   ops.flight_leg
WHERE  operating_date >= DATE '2026-01-01';

The hash functions differ, so the checksum is compared per row on a sample and by count and aggregate on the whole; what matters is that the row-shape string is identical on both sides, including the timestamp formatting, because that is where the DATE-to-timestamptz mapping shows up if it is wrong.

The change-data-capture bridge was Debezium's Oracle connector reading LogMiner, started from the SCN recorded at the bulk export, into Kafka (already in the estate) and then into PostgreSQL 18 through a sink with the type mapping applied in the sink. Three weeks of parallel run gave us three weeks of reconciliation reports and three weeks of the converted PL/SQL running against real change volume.

The cutover itself was the boring part: application pools drained, writes frozen on Oracle, CDC lag watched to zero, sequences on PostgreSQL 18 advanced past the Oracle high-water marks, connection strings switched at PgBouncer, and reverse CDC from PostgreSQL 18 to Oracle running so that a rollback in the first week would have been a connection-string change and not a data-loss event.

It was never needed. It was drilled twice.

-- Sequence advance at cutover: PostgreSQL 18 value must exceed every Oracle value ever issued
-- (Oracle CACHE means the last issued value can exceed LAST_NUMBER; use the data, not the catalog)
SELECT setval('ops.flight_leg_leg_id_seq',
              (SELECT COALESCE(MAX(leg_id), 0) + 1000 FROM ops.flight_leg),
              false);

-- Verification after, generated per owned sequence from pg_depend and run as one script:
-- every row must return ok = true before the connection strings are switched
SELECT 'ops.flight_leg_leg_id_seq' AS sequence_name,
       (SELECT last_value FROM ops.flight_leg_leg_id_seq)           AS seq_value,
       (SELECT MAX(leg_id) FROM ops.flight_leg)                     AS table_max,
       (SELECT last_value FROM ops.flight_leg_leg_id_seq)
         > (SELECT MAX(leg_id) FROM ops.flight_leg)                 AS ok;

Performance and scalability on the PostgreSQL 18 stack: what we measured

We do not publish the client's numbers, so this section describes what was measured and how, and states the outcome against the targets rather than as absolute figures. The targets were set from Oracle: for each of the top 50 statements, the AWR p95 elapsed time in the peak hour, and for the system, the peak execution rate with 25 percent headroom. The PostgreSQL 18 side of the comparison came from pg_stat_statements with track_io_timing on, sampled at the same hours over the parallel run, and from the load test that replayed captured traffic at 1.25 times peak rate.

-- PostgreSQL 18 post-cutover top SQL, comparable to the AWR "SQL ordered by Elapsed Time" section (PG 17+ column names)
SELECT queryid,
       calls,
       ROUND(total_exec_time::numeric / 1000, 1)               AS total_s,
       ROUND(mean_exec_time::numeric, 3)                       AS mean_ms,
       ROUND((shared_blk_read_time + shared_blk_write_time)::numeric, 1) AS io_ms,
       shared_blks_hit,
       shared_blks_read,
       ROUND(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS hit_pct,
       LEFT(query, 80)                                          AS query
FROM   pg_stat_statements
WHERE  dbid = (SELECT oid FROM pg_database WHERE datname = 'ops')
ORDER  BY total_exec_time DESC
LIMIT  50;

-- Where the time goes at the I/O layer (PostgreSQL 18: WAL I/O included here, per backend type)
SELECT backend_type, object, context,
       reads, read_time, writes, write_time, fsyncs, fsync_time
FROM   pg_stat_io
WHERE  reads > 0 OR writes > 0
ORDER  BY read_time + write_time DESC;

The outcome against the targets: all 50 statements met their p95 target at 1.25 times peak; the legality batch, which had been the item everyone expected to regress without Exadata, ran inside its window with margin once the BULK COLLECT loops were set-based; and the three reporting queries that had leaned on smart scan were the only ones that needed design work, which parallel query and one covering index resolved. The cache-hit ratio on pg_stat_io at peak sat where the working-set measurement predicted it would, which is the check that the sizing method was sound rather than lucky.

On scalability, the design has two levers left unpulled. Read scaling goes to the standbys through Patroni's replica routing, and the reporting workload already runs there. Write scaling on a single primary is bounded by the 64 cores and the WAL device; at 1.25 times peak the primary was under half utilised on CPU and the WAL device was under a quarter of its measured throughput.

The airline's growth plan does not reach the point where a single primary becomes the constraint inside the PostgreSQL 18 support window, and we wrote the trigger conditions (sustained CPU over 60 percent at peak, WAL write latency over 2 ms) into the handover so the conversation about partitioning across nodes starts from a measurement, not a feeling.

What we would do differently

Three things. We would run the working-set measurement before agreeing the hardware specification with procurement rather than in parallel with it; it confirmed the 1 TB nodes but it could have told us 768 GB was enough and saved money. We would convert the six materialised views before the PL/SQL rather than after, because the reporting team's acceptance testing was gated on them and it became the critical path. And we would have put the AFTER-trigger role change in PostgreSQL 18 on the assessment checklist from the start; two audit triggers written for Oracle assumed the committing role, PostgreSQL 18 runs them as the role that queued the event, and we found it in test rather than in review.

One thing we would not change: rewriting the PL/SQL instead of emulating it. It cost a quarter of a four-person team and it produced code the airline's own engineers can read, which is the point of leaving a proprietary platform.

Reproducibility and caveats

Versions, so the claims above can be checked: Oracle Database 19c on Exadata X9M-2; PostgreSQL 18.6 (the current minor at time of writing, released 13 August 2026); ora2pg 25.0; Patroni 4.1.x with etcd 3.5; pgBackRest 2.59; PgBouncer 1.25.2; pg_cron 1.6 or later; Debezium 3.x Oracle connector with LogMiner. Every sizing figure is rounded and the client is anonymised; the arithmetic applies to your estate only after you have replaced our AWR numbers with yours.

Test every conversion pattern here against your own PL/SQL and your own data in a staging environment that mirrors production topology, keep a verified backup and a rehearsed restore in place before any cutover, and keep the reverse CDC path warm until sign-off; a migration without a rollback path is a bet, not a plan.

If you are planning an Exadata exit and want the assessment matrix, the sizing method, or the PL/SQL conversion patterns applied to your estate, that is the work the MinervaDB PostgreSQL consulting team does, on-premises and in the cloud, with the same rule we apply to ourselves: the numbers come from the catalog and the AWR, never from the sales deck.

References

PostgreSQL 18 release notes · PostgreSQL release history · Multicolumn indexes and skip scan · Constraints, including WITHOUT OVERLAPS · Resource consumption parameters (io_method, io_workers) · pg_stat_io and cumulative statistics · Row security policies · ora2pg · Patroni · pgBackRest · PgBouncer · pg_cron · Debezium Oracle connector · Oracle Exadata X9M · Exadata X9M-2 data sheet