| Most Oracle to PostgreSQL defects come from behaviour that differs rather than features that are missing, which is why they produce wrong answers instead of error messages. |
| Four behaviours account for the bulk of them: empty string as NULL, DATE carrying a time component, identifier case folding, and a failed statement aborting the whole transaction. |
| A query that runs slower after migration is usually missing statistics or a saturated connection pool rather than a weaker planner. |
| Row counts prove a load finished and nothing else. Only result comparison against Oracle catches a behaviour difference. |
Oracle to PostgreSQL migrations fail on behaviour, not on missing features. PostgreSQL has an answer for almost everything Oracle does; what it does not have is Oracle’s exact semantics, and the gap surfaces as wrong results rather than errors. That is what makes these defects expensive: they pass every build, every row count and most test suites, then change a number on a report.
The challenges that cause most defects
Eight differences produce the bulk of post-migration defects. Three are type and identifier semantics, two are transaction and session behaviour, one is structural, and two appear only under load. None of them raises an error during conversion, which is why each has to be looked for deliberately rather than waited for.
Empty strings are NULL in Oracle, but not in PostgreSQL
What breaks. Oracle treats an empty string as NULL. PostgreSQL treats it as a zero-length string that is not NULL. Any code that assigned '' to mean “no value” changes meaning the moment it runs on PostgreSQL.
-- Oracle
SELECT 1 FROM dual WHERE '' IS NULL; -- returns a row
-- PostgreSQL
SELECT 1 WHERE '' IS NULL; -- returns nothing
SELECT 1 WHERE '' = ''; -- returns a row
How you notice. You do not, from the database. Row counts match, no error is raised, and the application starts fine. What changes is totals on reports, records that stop appearing in a filtered list, and validation that now accepts a blank where it used to reject one.
The fix. Decide per column, not globally. Either normalise '' to NULL during the load and keep the predicates as they are, or leave the data and rewrite the predicates to test for both. Find the candidates by searching the Oracle code for '' assignments and for IS NULL tests on columns that accept them.
Oracle DATE carries a time component
What breaks. An Oracle DATE stores date and time to the second. Mapped to a PostgreSQL date, the time of day is discarded silently. The column still loads, still matches on row count, and still groups correctly by day.
How you notice. Reports grouped by day keep agreeing with the old system, which is what makes this one dangerous. Anything that orders within a day, compares two same-day values, or calculates an elapsed duration starts returning different answers.
The fix. Map Oracle DATE to timestamp(0) rather than date. Then review every TRUNC(date_col) in the converted code, because its PostgreSQL equivalent depends on whether the intent was to drop the time or to round it.
Identifier case folding runs in opposite directions
What breaks. Oracle folds unquoted identifiers to upper case. PostgreSQL folds them to lower case. A converter that quotes every identifier preserves Oracle’s upper case permanently, and from that point every query in every application has to quote them too.
How you notice. Immediately, as a wall of “column does not exist” errors, or worse, not at all until someone adds an unquoted query months later and it fails against a quoted schema.
The fix. Set the rule before generating a single object, usually unquoted lower case, and apply it to the schema and the applications in the same change. Mixed quoting is the outcome to avoid: it works until it does not, and unpicking it later touches every query.
A failed statement aborts the whole transaction
What breaks. In Oracle, a statement that fails inside a transaction leaves the transaction usable, so code can catch the error and carry on. In PostgreSQL the transaction enters an aborted state and rejects every subsequent statement until it is rolled back.
How you notice. Batch jobs that processed a thousand records and skipped the handful that failed now stop at the first failure, usually with current transaction is aborted, commands ignored until end of transaction block.
The fix. Wrap the statements expected to fail in a savepoint, or restructure the loop so each iteration runs in its own transaction. A BEGIN ... EXCEPTION block in PL/pgSQL creates an implicit savepoint, which makes it the cheaper option where the loop body is already a block.
Packages and package state have no equivalent
What breaks. A package maps cleanly to a schema of functions. Package-level variables do not: in Oracle they persist for the session, and PostgreSQL has nowhere to put them by default.
How you notice. During conversion rather than after, which is the good news. The bad news is volume. A package of twenty procedures is twenty functions plus a decision about state, and that decision reaches every caller.
The fix. Decide once, as a pattern, then apply it to every package: session configuration parameters for small scalar state, a temporary table for row sets, or an explicit argument where the state was only ever passed through.
now() returns transaction start time, not wall-clock time
What breaks. PostgreSQL’s now() and CURRENT_TIMESTAMP return the moment the current transaction began and hold that value for its duration. Oracle’s SYSDATE advances as the statement runs.
How you notice. Long-running batches stamp every row with an identical timestamp where the old system recorded a spread. Anything measuring elapsed time inside a transaction reports zero.
The fix. Use clock_timestamp() wherever real elapsed time is wanted, and keep now() where a single consistent transaction timestamp is correct, which is more often than teams expect. Audit columns usually want now(); duration measurement always wants clock_timestamp().
LOB columns dominate transfer time and memory
What breaks. Nothing semantically. CLOB becomes text and BLOB becomes bytea without drama. What breaks is the load itself.
How you notice. A migration sized on total gigabytes runs for several times its estimate, or the tool moving the data exhausts memory on a table that looked unremarkable in the row counts.
The fix. Treat LOB columns as a separate load pass with their own batch sizing, rather than as columns inside a normal table export. Size the estimate on LOB volume and row width, not on the row count, and test the batch size against the largest values rather than the average.
PostgreSQL runs one process per connection
What breaks. Oracle’s shared-server model lets a large pool sit against a modest server. PostgreSQL forks a backend process per connection, so connection count becomes a memory decision rather than a configuration detail.
How you notice. Under load, not in testing. Connection refusals and memory pressure arrive while CPU is still comfortable, which sends people tuning the wrong thing.
The fix. Put PgBouncer or an equivalent pooler in front before the first load test, and size the application pool deliberately rather than carrying the Oracle number across. This is a recurring reason cloud migrations fail to deliver the performance that was modelled before they started.
Want these checked against your own database before you start?
Send us the object counts and we will run an assessment against your actual code: an object inventory, a conversion-complexity grading, and the Oracle-only features and behaviours in use. Findings from your codebase rather than from an average one.
Why performance drops after migration
A query that was fast on Oracle and is slow on PostgreSQL is rarely evidence of a weaker planner. In order of how often it turns out to be the cause: the planner has no statistics, the query carried an Oracle hint that is now absent, the connection pool is saturated, or dead rows have accumulated faster than autovacuum is clearing them.
Run ANALYZE before measuring anything. A freshly loaded database has no statistics, so the planner works from defaults and produces plans that will send you investigating the wrong query. A large share of “PostgreSQL is slower” findings disappear here.
PostgreSQL has no native optimizer hints. Queries carrying Oracle hints lose them in conversion. The first answer is better statistics, which often means raising the statistics target on the columns the planner is misjudging, or rewriting the query so the planner can see what the hint was asserting. The pg_hint_plan extension exists and works, and it is worth treating as the last option rather than the first, because a hint fixed in place is a plan that cannot adapt as the data changes.
Understand what VACUUM is for. PostgreSQL keeps old row versions in the table rather than in a separate undo area, so an update writes a new version and leaves the old one to be reclaimed. Tables under heavy update churn bloat if autovacuum cannot keep pace, and a bloated table is slower to scan in proportion to the dead space it carries. Watch it on the tables your batch jobs rewrite, which are rarely the tables anyone thought to monitor.
Size the pool, then test it. As above, PostgreSQL’s process-per-connection model makes an oversized pool actively harmful. Put a pooler in front and load test through it, because a test that bypasses the pooler measures a system you are not going to run.
How to catch these before cutover
Four stages, in order, each catching a class of defect the one before it cannot see. Running them in the wrong order wastes effort: there is no point comparing query results against a schema that is still missing objects.
Object parity. Count every object type on both sides and diff the lists, not just the totals. This catches the objects a data migration tool was never going to move, which is most of them: secondary indexes, sequences, default values, procedures, triggers, views. It is a cheap check that prevents an expensive misunderstanding about what the tooling covered.
Result comparison. Run the reports and the high-value queries against both databases and diff the output. This is the only stage that catches the behaviour differences above, because every one of them produces a correct-looking result rather than an error. It is also the stage teams drop when the schedule tightens, which is precisely why those defects reach production.
Load test with pooling. Test through the pooler, at a realistic concurrency, after ANALYZE has run. This catches the connection and plan problems, and it is the stage that tells you whether the instance you sized is the instance you need.
Cutover rehearsal. Run the cutover end to end against a copy, including the rollback. A rollback plan that has never been executed is a document, and the cutover window is the wrong time to discover that the reverse replication task was never built.
Planning an Oracle to PostgreSQL migration?
Tell us the Oracle version, roughly how large the database is and where you want to land. You will talk to a database architect, not an account manager.