Home › Insights › Blogs › App Modernization

Oracle to PostgreSQL Migration Challenges (and How Teams Fix Them)

Mohit Agarwal Mohit Agarwal
Last updated: 19 Nov 2026
Get an AI summary of this post on Perplexity ChatGPT Gemini
Key takeaways
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.

Get a Free Oracle Migration Assessment

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.

Four testing stages that catch migration defects before cutover A narrowing pipeline of four stages: object parity, result comparison, load test with pooling, and cutover rehearsal. Each stage is marked in red to show that defects are caught and removed there, so fewer reach the next stage and fewer still reach production. 1 Object parity 2 Result comparison 3 Load test with pooling 4 Cutover rehearsal Defects caught and removed at each stage Four testing stages that catch migration defects before cutover A narrowing pipeline of four stages: object parity, result comparison, load test with pooling, and cutover rehearsal. Each stage is marked in red to show that defects are caught and removed there, so fewer reach the next stage and fewer still reach production. 1 Object parity 2 Result comparison 3 Load test with pooling 4 Cutover rehearsal Defects caught and removed at each stage
Each stage catches a class of defect the one before it cannot see, which is why dropping a stage moves its defects into production rather than removing them.

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.

Talk to a Database Architect
#oracle to postgres migration challenges #oracle to postgresql migration issues #oracle to postgresql migration problems #oracle to postgresql migration risks #performance issues after migration #oracle to postgresql common mistakes
Share

Frequently asked questions

Why does converted code treat empty strings differently?
Because Oracle treats an empty string as NULL and PostgreSQL does not. In Oracle, '' IS NULL is true. In PostgreSQL it is false, and '' is a zero-length string that fails an IS NULL test and passes an equality test against ''. Any Oracle code that assigned '' to mean no value changes meaning on migration, and the change is invisible to a row-count check.
Why is my query slower in PostgreSQL than in Oracle?
Most often because the planner has no statistics to work from. A freshly loaded database has none until ANALYZE runs, so plans are built on guesses. After that, check whether the query carried Oracle optimizer hints, which PostgreSQL has no native equivalent for, and whether the connection pool is saturated, since PostgreSQL runs one backend process per connection.
Which Oracle features have no direct PostgreSQL equivalent?
Packages, package-level variables, autonomous transactions, optimizer hints, synonyms, global temporary tables with Oracle's session semantics, and Real Application Clusters. Each has a workaround rather than a replacement: schemas for packages, dblink or pg_background for autonomous transactions, pg_hint_plan for hints, and views or search_path for synonyms.
Mohit Agarwal
Lead Architect, Kansoft

Lead Architect at Kansoft with 15+ years designing scalable, maintainable application and system architectures for enterprise clients. He writes about software architecture, application development, and the engineering decisions that keep systems fast and easy to evolve.

Need help with your next project?

Our engineering experts can help you build something exceptional.

Book a Free Call