Estimating Effort for Migrating Oracle to PostgreSQL Before You Start

Why estimate effort before starting

Data transfer is usually faster than moving logic. Tables can be exported and imported, then verified with reconciliations and checksums. Business rules in Oracle are often spread across packages, triggers, jobs, user types and small “conveniences” people have relied on for years. Those are what most often consume the schedule.

If you start without an initial assessment, surprises are almost inevitable. A package that looks like “a couple of procedures” can pull dozens of dependencies, global variables and nontrivial exception handling. Triggers silently modify data, block operations, or perform auditing. Jobs and schedules are tied to infrastructure and privileges. Oracle-specific types and behaviors (for example, collections, date and string quirks) sometimes require schema and code redesign.

Before you set timelines and assemble a team, it helps to answer three questions: how much logic actually needs to be rewritten, what can be migrated almost directly, and where the risks are highest. With those answers, the estimate becomes manageable planning rather than a guess.

A good initial assessment produces concrete artifacts: a registry of key objects and their dependencies, a rough complexity classification, a list of risk areas (what might affect accounting, payments, reporting, audit), an understanding of required team roles and a draft work order.

A simple example: in a “billing” module, data migration may take a day, but the calculations rely on 15 triggers and one large package with shared state. Without assessment, this only appears during development, when it becomes hard to rework schedules and budgets.

What to evaluate and where to set boundaries

An initial assessment does not start by counting objects, but by setting boundaries. If those are not fixed, any “nice number” will fall apart the first week when previously uncounted schemas, jobs or integrations surface.

Define the perimeter: which schemas and modules are in scope, which environments are considered (dev, test, prod), and what surrounds the database. If part of the logic lives in the DB and part in the application, without agreed boundaries the estimate will keep growing due to arguments about what is being migrated.

Agree on what you mean by migration. In practice this is usually one of the levels:

• structure and data (DDL + data);
• all server-side logic (packages, procedures, functions, triggers);
• plus operations (schedules, monitoring, backups, privileges, roles);
• plus application changes (driver, SQL dialect, transactions, error handling).

Next, gather the minimum artifacts without which estimates are almost always underestimated: schema DDL, PL/SQL sources (including package specs and bodies), trigger definitions, scheduler configs (DBMS_SCHEDULER/DBMS_JOB), privileges and roles, and a list of external dependencies (queues, external tables, dblink, file operations).

Also record assumptions: Oracle version (and modes in use), target PostgreSQL version, compatibility requirements and acceptance criteria. It is useful to note upfront what is excluded from the estimate unless agreed: data model redesign, query optimization, business-logic refactoring.

When boundaries are clear, the inventory becomes comparable across modules and timeline discussions are substantive.

Inventory of objects: the minimal set to avoid missing things

Estimates most often fail because people count the “schema” while migration involves rules, automation and access. The inventory should be short but cover all classes of objects.

For each object record at least: name, owner (schema), purpose in one sentence, code size (if it is code), and — most importantly — whether it depends on Oracle-specific features. That is enough to categorize objects as “easy/medium/hard.”

The registry usually includes:

• data and structure: tables, partitioning, indexes, views (including materialized), sequences;
• code and business logic: procedures, functions, packages (specs and bodies), triggers;
• scheduler and integrations: DBMS_SCHEDULER/DBMS_JOB, external calls (shell, HTTP, queues), file dependencies;
• security and bindings: synonyms, roles, grants, access policies, audit;
• storage and files: LOB (BLOB/CLOB), external tables, directory operations.

You don’t need to dive into details yet. It is important to see where the logic is hidden. A “Accounts” module may look simple: 20 tables and 30 indexes. But the registry might reveal an 8,000-line package, triggers that recompute limits, and nightly jobs that close periods. That defines the real effort.

A practical rule: if an object affects data without the application’s involvement (triggers, jobs, batch procedures), it must be listed even if it seems “auxiliary.”

Packages and PL/SQL: quickly estimating rewrite effort

Oracle packages often hold the main business logic and therefore most influence timelines. For an initial estimate you don’t need to read every line. It is important to understand size and shape: how many logical units, how tightly they are coupled, and which parts won’t map directly.

Start with a rough count: how many packages, number of procedures and functions inside, and approximate code size (for example: small up to 300–500 lines, medium up to 2,000, large above). Check both spec and body: the interface can be small while the implementation is heavy with branching and side effects.

What inflates schedules are often exception handling with business nuances, cursors (especially stateful or multi-pass), and dynamic SQL where queries are built from strings and depend on parameters. These often require redesign rather than direct translation.

Note a package’s dependencies on tables, views, triggers and jobs. If a package calls other packages or uses shared types and constants, rewriting becomes a chain.

Signs of high complexity to mark immediately: deep nesting of CASE/IF, many operation modes and parameters, active dynamic SQL, COMMIT/ROLLBACK inside, and strong reliance on Oracle specifics.

Triggers: where hidden rules live

Triggers often appear like “small rules on a table,” but in practice they hide business logic, audit and side effects. If you don’t account for them at the start, the estimate will almost always be understated.

First, determine the scale: how many triggers and on which tables, which events they cover (INSERT, UPDATE, DELETE). A single key table may have 5–10 triggers that together define module behavior.

Quickly separate triggers by purpose

It helps to separate data control from integrations. Validation often maps to the model: constraints, defaults, computed fields and integrity checks. Integrations typically require rewriting into the application or a separate service.

In practice triggers usually fall into types: validation and prohibition, autofill, cascading changes, logging and auditing, integrations (queues, external calls, notifications).

Side effects and signs of high complexity

The danger with triggers is hidden consequences: one update leads to a series of additional inserts or updates. For assessment, mark “red flags”: references to many tables, row-by-row loops, complex status handling, time-dependent logic, writes to logs or queues.

Check triggers that are better replaced with non-trigger solutions. For example, creation timestamps are usually simpler as DEFAULT values, and some checks can be moved to CHECK constraints or foreign keys. This reduces rewrite effort and simplifies testing.

A minimal technical inventory step can be done with one Oracle query:

SELECT table_name, trigger_name, triggering_event, status
FROM user_triggers
ORDER BY table_name, trigger_name;

For each trigger a short card is enough: table, event, what it changes, which tables it touches, and whether there are external effects.

Jobs and schedules: assessing portability and risk

Jobs often reveal the real complexity: it’s not just SQL. A schedule may run a chain of procedures, update materialized views, generate reports, export files and launch external scripts. So it’s important to understand not “how many jobs,” but “what they do” and where their boundaries are.

Criticality of schedules is determined by the cost of failure. Note processes with nightly windows, hourly calculations and near-real-time tasks. The smaller the allowable window, the higher the requirements for monitoring, queuing and retries.

Quick portability filter

For each job collect 4–5 characteristics: what it launches (SQL, procedure, package, external script), where dependencies are (files, SFTP, mail, API), what privileges are needed (directories, accounts, keys), whether it has state (staging tables, queues, checkpoints), and how restarts are handled.

Risks of downtime and recovery

Check what happens if a job fails mid-run. In Oracle this might be covered by transactions and status flags. In PostgreSQL the logic often needs to be made explicit: idempotency (repeated runs without duplicates), step logging, safe cleanup of temporary data. If these are missing, the estimate must include not just schedule migration but also enhancements to recovery scenarios.

Oracle-specific types and features: a quick complexity filter

Timelines most often “explode” not on tables and simple queries, but where Oracle provides special types and service packages. A quick filter helps see where migration will be direct and where it will require redesign.

First, mark objects that almost always need a bespoke solution: XMLTYPE, user-defined types (UDT), VARRAY and nested tables. PostgreSQL can replace many of these, but the cost varies: sometimes jsonb and regular tables suffice, other times the data contract and code must change.

Also check Oracle service packages used. DBMS_SCHEDULER, UTL_FILE, UTL_HTTP and similar utilities are tied to environment, privileges, filesystem and network rules. In PostgreSQL these are usually moved to external services, background workers, extensions or application code.

Separately note infrastructure features affecting behavior and performance: partitioning, materialized views, special indexes (for example, bitmap), compression, hints.

A short complexity filter:

• is there a direct equivalent in PostgreSQL or an extension;
• is the function used in a critical business flow;
• does it depend on OS, files or network;
• is the same Oracle-level performance required;
• can the approach be changed without losing requirements.

For each item record a decision: “equivalent exists”, “needs redesign” or “change approach”. For example, XMLTYPE in a reporting module often moves to jsonb with transformation on input, while UTL_FILE usually requires rethinking where files are written, who has access and how to monitor it.

Point-based method: turning inventory into effort estimates

To avoid turning the initial estimate into guessing, convert the inventory into points. The idea is simple: assign base points to each object, multiply by object-type weight and risk coefficients. The result is not a precise quote but a clear range and a list of items dragging the project down.

1. Complexity: three levels with clear signs

The scale should be equally understandable across the team. For example: simple (1), medium (3), complex (7).

Keep short criteria: simple — few conditions and no dynamic SQL; medium — several logic branches, cursors, error handling; complex — dynamic SQL, strong dependence on Oracle features, many exceptions, nontrivial transactionality or strict performance requirements.

2. Weight by object type and risk coefficients

Multiply base points by object-type weight so that the same “complexity 3” for a trigger and a package doesn’t look identical. For a start these groups are enough:

• packages/procedures/functions: x3;
• triggers: x2;
• jobs/schedules: x2;
• Oracle-specific features (types, built-in packages, SQL quirks): x4;
• simple objects (tables, indexes without exotic features): x1.

Add risk coefficients (usually 1.1–1.5) where appropriate: poor documentation, high business criticality, many integrations/external calls, strict latency requirements.

A practical result: total points per module and a top-10 list of objects by contribution. Plan a short spike for these top items before the final plan: often 10% of objects cause 60–80% of the effort.

Common mistakes in initial estimates

The most expensive mistake is counting only objects and inferring timelines from that. Real complexity lives in object relationships and in implicit assumptions embedded in code for years.

First trap — dependencies. One package may touch dozens of tables, views, triggers, synonyms and other packages. If you estimate only the package text and not its “tail,” schedules will almost certainly be understated. Typically this looks like: you migrate a module and later chase missing functions and types that surface only during build and tests.

Second mistake — forgetting privileges, roles and schemas. Oracle often relies on roles, grants “through roles” and definer/invoker behavior. PostgreSQL behaves differently, and issues appear not during data transfer but when users start working.

Third — underestimating types, dates and strings. Differences between an empty string and NULL, implicit conversions, character set and NLS settings, time zones, DATE vs TIMESTAMP cause silent errors: a query “works” but results differ slightly.

What to check at the start:

• record dependencies: what calls a package/trigger and what they call;
• build an access matrix: roles, grants, schemas, service accounts;
• mark risky areas by type: dates, strings, rounding of numbers;
• do not rely on auto-conversion without spot checks;
• plan tests: result comparisons, regression and basic performance checks.

Fourth mistake — believing a converter will “translate everything.” Automation helps, but there is almost always a manual layer: exceptions, cursors, batch architecture and transaction specifics.

Fifth — lack of a test plan. Without reference datasets and comparison scenarios you will learn about discrepancies too late, when fixes start breaking already migrated parts.

Short checklist: are you ready to start?

Before setting timelines and budget, verify basic things. This checklist helps understand how realistic your estimate is and where you risk getting bogged down in rewriting logic.

Start with inventory, but don’t stop at exporting a list of objects. Record owners and responsible parties: who confirms that a package, trigger or job is still needed, and who can explain its business intent.

Next, mark critical processes. Often the issue is not code volume but timing: nightly windows, month-end closes, integration chains. If schedules and load peaks are not identified, you cannot fairly assess risks.

Run a quick complexity filter: which objects use nonstandard types, external calls, dependency chains, privileges, or session context. One “heavy” object can cost as much as dozens of simple ones.

Practical minimum that should be ready:

• an object registry with owners and status (used/questionable/removable);
• a map of critical scenarios and job windows, including “hard dates” (month close, reports);
• a list of high-complexity objects with reasons (dependencies, external calls, Oracle specifics);
• agreed assumptions and acceptance criteria: what “works the same” means, performance and resilience requirements;
• a pilot plan for 1–2 representative modules with success metrics.

Example: if the pilot module contains 2 packages, 10 triggers and 3 jobs, but one package drags an integration and many exceptions, isolate it. The pilot then shows real rewrite and testing speed rather than the database-wide average.

Example assessment scenario for a small module

Imagine an accounting module: users enter transactions during the day and nightly calculations run (accruals, reconciliations, building reporting marts). Data quality checks are partially implemented in triggers: limit checks, status handling, delete restrictions, autofill.

The initial inventory found: 3 PL/SQL packages (medium-sized), 20 triggers (complex), 5 jobs (medium). What matters is not only counts but logic character. A “medium” package that only reads and writes tables is usually migrated faster than a trigger that touches other objects and relies on Oracle specifics.

A quick calculation can be built like this: give packages a base complexity, increase triggers’ weight due to hidden rules and side effects, and evaluate job portability and dependencies. Then ask the business a few questions to avoid spending time on avoidable work:

• which 2–3 reports and calculations are critical in the first weeks after launch;
• are there rules that can be moved from triggers to the application or explicit procedures;
• is it acceptable to change the nightly window or execution order;
• which errors are currently “caught” by triggers and how users check them.

After clarifying, convert the estimate to a mini-plan showing quick wins and risk zones. For example, start with a pilot on one nightly calculation and a couple of tables while preparing data migration in parallel.

• pilot: migrate 1 package + 2–3 key triggers and run control scenarios;
• data migration: load rules, result reconciliation, rollback plan;
• logic rewrite: remaining packages, triggers, job and schedule migration;
• tests: regression for critical reports, nightly window load, business acceptance.

This makes it clear that 20 complex triggers will likely consume more time than 3 “medium” packages and helps you validate requirements before mass rewriting.

Next steps: from initial assessment to project plan

After the initial assessment, quickly turn numbers and notes into a working plan. The goal is to record results so both developers and the business can use them.

A convenient format is a single inventory registry. For each object (package, trigger, job, type, view) record: module, short purpose, complexity estimate (S/M/L), key risks (dependencies, external utilities, Oracle specifics), logic owner and readiness criterion (tests, reports, SLA).

Then choose a migration strategy that reduces unknowns: start with what gives the most insight into your codebase. Usually this is a mix of business criticality and technical dependencies.

To convert assessment into a plan, do a few things: break work into modules and dependency groups, mark blocking objects, assign roles (developers, DBAs, testers, process owners), agree acceptance criteria and schedule a pilot on one representative module with time to revise estimates.

If you need a partner for infrastructure and support at the planning stage, this can be discussed with GSE.kz (gse.kz): as a system integrator the company helps design a PostgreSQL environment and organize support, including 24/7 service for critical systems.