Logo-Icon
HOMEABOUTSERVICESHOW WE WORKBLOGInstagram-Icon Talk To an Expert
RadixTrie-image
Oracle to PostgreSQL migration
Architecture

Oracle to PostgreSQL Migration: What Actually Takes the Time

Moving from Oracle to PostgreSQL is rarely blocked by the database engine. It is the code, the assumptions and the testing that take the time. A realistic view of the work, in the order it usually happens.
By RadixTrie3 September 20262 min read
Organisations consider moving from Oracle to PostgreSQL for familiar reasons: licence cost, vendor independence, cloud portability and the maturity PostgreSQL has reached. The engine is rarely the obstacle. PostgreSQL handles the workloads. What consumes the time is everything around the engine: procedural code, application assumptions, operational habits and the testing needed to prove that nothing important changed. This article walks through where the effort really goes.

Start with an honest assessment

Before any migration plan, inventory what you have: schemas, object counts, lines of PL/SQL in packages, procedures, functions and triggers, use of Oracle-specific features such as materialised views, advanced queuing, partitioning options, external tables and proprietary data types, and how the applications talk to the database. Open-source assessment tooling such as ora2pg can produce a first estimate of conversion effort per object type. Treat it as a map of where the work is, not as a schedule.

Where the time goes, in order

  1. Schema and data types. Most tables convert cleanly. The care goes into numeric precision, date and timestamp semantics, character sets, sequences and identity behaviour, and constraints that were enforced in triggers rather than declared.
  2. Procedural code. PL/SQL to PL/pgSQL is the largest single effort in most migrations. Packages have no direct equivalent and need restructuring; exception handling, cursors, bulk operations, autonomous transactions and Oracle-specific built-in functions all need deliberate translation and testing.
  3. The application layer. SQL embedded in applications carries Oracle idioms: outer-join syntax, ROWNUM, DUAL, NVL and DECODE, date arithmetic, hints, and driver-specific behaviour. Object-relational mappers help; hand-written SQL needs review.
  4. Data migration and validation. Moving the data is straightforward; proving it arrived intact is the work. Row counts, checksums, referential integrity and business-level reconciliation reports, repeated for every rehearsal.
  5. Performance validation. PostgreSQL's planner and indexing model differ from Oracle's. Expect a set of queries that need new indexes, rewritten predicates or configuration changes, and plan the time to find them with production-like data.
  6. Operations. Backups, point-in-time recovery, replication, monitoring, vacuum management and security all work differently. The team that will run PostgreSQL needs to be ready before cutover, not after.
The single best predictor of a smooth migration is how many full rehearsals you run against production-like data before the real cutover. Two is a minimum for anything that matters.

Cutover patterns

Small systems can move in a single maintenance window: freeze, migrate, validate, switch. Larger or busier systems usually need a period of change-data-capture or dual running, where Oracle stays the system of record while PostgreSQL is fed and validated in parallel, followed by a short final switch. The right pattern depends on how long the business can pause writes and how quickly you need to be able to roll back.

People, not just code

Experienced Oracle DBAs are a strength in a migration, and they also carry habits that do not transfer directly. Our note on <a href="/blog/reskilling-to-postgresql-how-to-navigate-the-challenges">reskilling to PostgreSQL</a> covers what changes and how to approach it. Plan for that transition alongside the technical work.

How RadixTrie approaches it

RadixTrie supports both Oracle and PostgreSQL in production for South African organisations, so our advice is about the workload rather than the licence: some systems should move, some should stay, and the assessment says which. When a migration goes ahead, senior engineers run the assessment, conversion, rehearsals and cutover, and then <a href="/blog/postgresql-support-south-africa">support the PostgreSQL estate</a> afterwards under our <a href="/services">managed DBA services</a>.
<strong>Related reading:</strong> <a href="/blog/oracle-database-support-south-africa">Oracle Database Support in South Africa: Keeping Licensed Estates Healthy and Compliant</a> &middot; <a href="/blog/postgresql-support-south-africa">PostgreSQL Support in South Africa: Running Postgres in Production Without the Guesswork</a> &middot; <a href="/blog/reskilling-to-postgresql-how-to-navigate-the-challenges">Reskilling to PostgreSQL: How to Navigate the Challenges</a>
Weighing up an Oracle to PostgreSQL move? Start with an assessment, not a decision.
TAGSOraclePostgreSQLMigrationArchitectureSouth Africa
RadixTrie-image
Oracle to PostgreSQL migration
Architecture

Oracle to PostgreSQL Migration: What Actually Takes the Time

Moving from Oracle to PostgreSQL is rarely blocked by the database engine. It is the code, the assumptions and the testing that take the time. A realistic view of the work, in the order it usually happens.
By RadixTrie3 September 20262 min read
Organisations consider moving from Oracle to PostgreSQL for familiar reasons: licence cost, vendor independence, cloud portability and the maturity PostgreSQL has reached. The engine is rarely the obstacle. PostgreSQL handles the workloads. What consumes the time is everything around the engine: procedural code, application assumptions, operational habits and the testing needed to prove that nothing important changed. This article walks through where the effort really goes.

Start with an honest assessment

Before any migration plan, inventory what you have: schemas, object counts, lines of PL/SQL in packages, procedures, functions and triggers, use of Oracle-specific features such as materialised views, advanced queuing, partitioning options, external tables and proprietary data types, and how the applications talk to the database. Open-source assessment tooling such as ora2pg can produce a first estimate of conversion effort per object type. Treat it as a map of where the work is, not as a schedule.

Where the time goes, in order

  1. Schema and data types. Most tables convert cleanly. The care goes into numeric precision, date and timestamp semantics, character sets, sequences and identity behaviour, and constraints that were enforced in triggers rather than declared.
  2. Procedural code. PL/SQL to PL/pgSQL is the largest single effort in most migrations. Packages have no direct equivalent and need restructuring; exception handling, cursors, bulk operations, autonomous transactions and Oracle-specific built-in functions all need deliberate translation and testing.
  3. The application layer. SQL embedded in applications carries Oracle idioms: outer-join syntax, ROWNUM, DUAL, NVL and DECODE, date arithmetic, hints, and driver-specific behaviour. Object-relational mappers help; hand-written SQL needs review.
  4. Data migration and validation. Moving the data is straightforward; proving it arrived intact is the work. Row counts, checksums, referential integrity and business-level reconciliation reports, repeated for every rehearsal.
  5. Performance validation. PostgreSQL's planner and indexing model differ from Oracle's. Expect a set of queries that need new indexes, rewritten predicates or configuration changes, and plan the time to find them with production-like data.
  6. Operations. Backups, point-in-time recovery, replication, monitoring, vacuum management and security all work differently. The team that will run PostgreSQL needs to be ready before cutover, not after.
The single best predictor of a smooth migration is how many full rehearsals you run against production-like data before the real cutover. Two is a minimum for anything that matters.

Cutover patterns

Small systems can move in a single maintenance window: freeze, migrate, validate, switch. Larger or busier systems usually need a period of change-data-capture or dual running, where Oracle stays the system of record while PostgreSQL is fed and validated in parallel, followed by a short final switch. The right pattern depends on how long the business can pause writes and how quickly you need to be able to roll back.

People, not just code

Experienced Oracle DBAs are a strength in a migration, and they also carry habits that do not transfer directly. Our note on <a href="/blog/reskilling-to-postgresql-how-to-navigate-the-challenges">reskilling to PostgreSQL</a> covers what changes and how to approach it. Plan for that transition alongside the technical work.

How RadixTrie approaches it

RadixTrie supports both Oracle and PostgreSQL in production for South African organisations, so our advice is about the workload rather than the licence: some systems should move, some should stay, and the assessment says which. When a migration goes ahead, senior engineers run the assessment, conversion, rehearsals and cutover, and then <a href="/blog/postgresql-support-south-africa">support the PostgreSQL estate</a> afterwards under our <a href="/services">managed DBA services</a>.
<strong>Related reading:</strong> <a href="/blog/oracle-database-support-south-africa">Oracle Database Support in South Africa: Keeping Licensed Estates Healthy and Compliant</a> &middot; <a href="/blog/postgresql-support-south-africa">PostgreSQL Support in South Africa: Running Postgres in Production Without the Guesswork</a> &middot; <a href="/blog/reskilling-to-postgresql-how-to-navigate-the-challenges">Reskilling to PostgreSQL: How to Navigate the Challenges</a>
Weighing up an Oracle to PostgreSQL move? Start with an assessment, not a decision.
TAGSOraclePostgreSQLMigrationArchitectureSouth Africa