Migration & Modernization

Migrating and Modernizing Oracle Databases to PostgreSQL on AWS – Part 1: Discovery

Introduction

In this blog post, we explore the discovery phase of migrating Oracle databases to PostgreSQL on Amazon Web Services (AWS), the foundational step that determines the success of every migration that follows. This is the first installment of a four-part series covering the Oracle-to-PostgreSQL migration journey, from initial discovery through post-migration optimization.

Evolving licensing models, architectural requirements, and operational priorities are leading many organizations to migrate from Oracle to Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition. This transition is not a simple plug-and-play swap; organizations must navigate complex technical and structural mismatches before they can successfully cut the cord.

While every migration involves modernizing your setup (such as adding connection pooling or moving to cloud-native AWS patterns), this series highlights a key difference: migration moves your database, modernization transforms your architecture.

You cannot simply copy Oracle schemas into PostgreSQL and expect identical behavior. The scope of your architectural modernization depends entirely on what your discovery phase reveals. We use Zulon City Insurance (ZCINS), a fictional composite case study modeled on real migration patterns, to illustrate discovery-phase techniques and findings.

Understanding modernization drivers

ZCINS operated a 42-database Oracle estate spanning on-premises infrastructure and Oracle Cloud Infrastructure (OCI), supporting core insurance operations: policy administration, claims processing, billing, and fraud detection. Three converging pressures made migration urgent: cost escalation, operational rigidity, and technical debt. ZCINS’s Oracle environment consumed approximately 15–20% of their total IT budget, a figure growing 18% annually due to per-CPU licensing, mandatory support contracts, and audit-driven compliance overhead.

Business drivers

Transitioning to cloud-native PostgreSQL services reclaims IT budget and breaks vendor lock-in cycles. Your strategic drivers include:

  • Cost optimization: Oracle’s per-CPU and Named User Plus licensing create unpredictable cost curves. ZCINS faced a heavy annual spend projected to climb, illustrating how migration delays add carrying costs while technical debt compounds.
  • Competitive agility: Oracle’s rigid licensing restricted horizontal scaling, requiring hefty supplemental fees and months of procurement lead time. Cloud-native architecture removes these constraints, enabling on-demand elasticity.
  • Vendor independence: Moving to open-source PostgreSQL reduces concentration risk, eliminates audit exposure, and provides architectural flexibility.

For ZCINS, modernization was a business necessity. Every new insurance product previously required 6 to 9 months to reach market. The cloud-native database removed these bottlenecks, allowing launches in weeks instead of months.

Technical drivers

Cloud-native modernization changes not just where your data lives, but how it works. The technical case rests on three pillars:

When ZCINS needed real-time fraud scoring, Oracle AQ’s polling model produced 15–45 seconds of latency and required a dedicated Amazon EC2 fleet. Modernizing the database layer enabled an event-driven architecture that reduced latency to under 1 second.

Operational drivers

The shift to AWS managed PostgreSQL services transforms day-to-day operations, enabling a transition from routine maintenance toward innovation.

  • Managed service benefits: Eliminate patching, backup management, and storage provisioning overhead.
  • Cloud-native observability: Amazon CloudWatch, AWS CloudTrail, and CloudWatch Database Insights provide unified monitoring across database and application layers, unavailable in on-premises Oracle.
  • Disaster recovery simplification: Amazon Aurora Global Database provides cross-region DR with sub-1-second replication lag, typically under one minute for managed planned failover, and sub-30-second local AZ failover, a managed alternative to self-administered DR such as Oracle Data Guard.
  • Scaling readiness: To prepare for 40% growth, ZCINS previously faced extensive Oracle Automatic Workload Repository (AWR) analysis cycles and specialized consulting. Managed services replaced this with automated scaling workflows (auto-scaling).

Key differences between Oracle and PostgreSQL

These differences span process models, concurrency, procedural languages, data types, and security, each requiring specific approaches. The following sections compare Oracle with AWS managed PostgreSQL; Aurora-specific capabilities are noted where they provide distinct advantages.

Architectural model and concurrency

The transition requires a fundamental shift in how database resources are managed. Oracle’s multi-threaded process model is generally more forgiving of high connection counts. PostgreSQL uses a multi-process architecture (one process per connection), making robust connection pooling essential when connection counts exceed a few hundred.

Their Multiversion concurrency control (MVCC) approaches also differ. Oracle implements undo-based MVCC; PostgreSQL uses tuple-based MVCC, writing new row versions directly into the table and necessitating active VACUUM tuning to prevent bloat.

Table 1: Core architectural differences
Aspect Oracle AWS managed PostgreSQL Impact
Process Model Multi-threaded: single-server process with background threads sharing memory (SGA) Multi-process: each connection spawns a separate OS process; no shared global memory pool Connection pooling becomes important as connection counts grow (when connection approaches 200-400, depending on instance memory, workload type). Options include PgBouncer and Amazon RDS Proxy
Concurrency (MVCC) Undo-based: old row versions in UNDO tablespace; readers never block writers Tuple-based (heap): old versions stored inline; autovacuum reclaims dead tuples Dead tuple bloat can degrade performance without proper vacuum configuration
Scalability Oracle RAC: shared-everything with dedicated cluster interconnect; horizontal write + read scaling Aurora Read Replicas share distributed storage (<100ms lag), reducing cost. Aurora Limitless addresses write scaling Aurora Read Replicas are not a direct RAC equivalent. Map RAC write-scaling to Aurora Limitless or application-level sharding; Read Replicas cover read-scaling
Connection Handling Dedicated or MTS shared server; supports 60,000+ connections with minimal overhead One process per connection; each consumes 10–30 MB High-concurrency workloads require connection pooling

ZCINS chose PgBouncer over Amazon RDS Proxy for its transaction-level pooling, which gave finer-grained control over connection multiplexing for their high-frequency OLTP workloads. In production, scaled to 600 application connections multiplexed to 50 backend connections (see Part 3).

Language and procedural logic

Migrating Oracle PL/SQL packages requires strategic refactoring, not just code conversion. Because PostgreSQL has no direct equivalent for packages, teams must decide whether to group functions into schemas or move logic into the application layer.

Table 2: Logic and syntax disparities
Aspect Oracle AWS managed PostgreSQL Impact
Procedural Language PL/SQL: packages, procedures, functions, triggers PL/pgSQL: similar structure, different syntax, exception handling, built-in functions AWS DMS Schema Conversion (DMS SC) automates most PL/SQL conversion; the remainder needs manual remediation. ZCINS: 78,000 lines across 42 packages
Sequences Standalone SEQUENCE objects; NEXTVAL/CURRVAL PostgreSQL sequences; SERIAL/BIGSERIAL; GENERATED ALWAYS AS IDENTITY Map to PostgreSQL sequences or IDENTITY columns; update INSERT statements
DUAL Table Single-row utility table for expressions No DUAL needed DMS SC auto-converts most DUAL references
Date/Time Handling DATE stores date + time (seconds). TIMESTAMP adds fractional seconds DATE stores date only. TIMESTAMP[TZ] includes time with optional timezone Replace Oracle DATE with TIMESTAMP for date+time columns. Audit date arithmetic and TO_DATE/TO_CHAR formats
OUTER JOIN Syntax Vendor-specific (+) notation in legacy code ANSI JOIN (LEFT/RIGHT/FULL OUTER JOIN) DMS SC converts (+) to ANSI. Review complex multi-table joins manually
Case Sensitivity Uppercase default (insensitive) Lowercase default (sensitive) Audit SQL and ORMs to avoid identifier conflicts. ZCINS standardized on snake_case
Temp Tables Persistent definitions Temporary definitions Session-based logic needs refactoring for definition lifecycles

Data types and security

Data mapping is where migrations often collide with the application layer. Subtle differences in how Oracle and PostgreSQL handle time, identity, and complex objects can trigger runtime failures if not addressed during schema conversion.

Table 3: Data type mapping and identity logic
Aspect Oracle AWS managed PostgreSQL Impact
Date and Time DATE stores date + time to seconds (no timezone) DATE: date-only. TIMESTAMP: date + time. TIMESTAMPTZ: stored as UTC, displayed in session time zone Critical: PostgreSQL DATE stores date only. Map Oracle DATE to TIMESTAMP(0) to preserve time. Use TIMESTAMP(0) for single-timezone systems. For multi-timezone or global systems, use TIMESTAMPTZ to preserve timezone context and avoid silent errors during DST transitions.
VARCHAR2(n) Variable-length string; n = bytes or chars VARCHAR(n) or TEXT Verify character set (AL32UTF8 → UTF-8). Bytes vs chars can cause truncation
NUMBER(p,s) Variable-precision decimal, up to 38 digits NUMERIC(p,s) or DECIMAL For integer columns (especially PKs), use BIGINT/INT. NUMERIC’s variable-length storage and software arithmetic make joins and index lookups measurably slower. Floating-point logic requires NUMERIC for precision
CLOB/NCLOB Character Large Object; inline up to 4 GB TEXT (~1GB) or large object (lo) TOAST handles large values transparently; TEXT simplifies character-heavy migration. Verify LOB API compatibility
BLOB Binary Large Object; up to 4 GB BYTEA or large object (lo) BYTEA limited to ~1GB. Migrate large BLOBs to Amazon S3 with a metadata pointer to improve performance and reduce cost
XMLTYPE Native XML with XQuery/XPath XML type or JSONB Evaluate converting XML to JSONB (better indexing); retain XML type if schema validation is required
Row Identity ROWID (stable address) CTID (volatile) Redesign deduplication using unique constraints or window functions
External Files BFILE (external files) BYTEA Store files in the DB or use external storage patterns
Object Types Complex types & methods Not supported natively Flatten nested objects into relational tables and joins

ZCINS initially mapped Oracle DATE to PostgreSQL DATE, causing batch failures; they corrected this by mapping to TIMESTAMP. Oracle’s stable ROWID required redesign because PostgreSQL’s CTID is volatile. ZCINS used DMS SC for simple types and manually flattened complex nested definitions into relational tables.

Security model differences

The migration requires overhauling how access is governed. Oracle tightly couples users and schemas; PostgreSQL provides a flexible, unified role system where schemas and users are decoupled.

Table 4: Security and compliance mapping
Aspect Oracle AWS managed PostgreSQL Impact
Row-Level Security Virtual Private Database (VPD) Row-Level Security (RLS) Policy logic must be rewritten; RLS is declarative and easier to audit
Auditing Fine-Grained Auditing (FGA) pgAudit extension Stream pgaudit to Amazon CloudWatch for unified logging; AWS CloudTrail for API-level tracking
Encryption Transparent Data Encryption (TDE) AWS KMS & TLS Eliminates Oracle Advanced Security licensing; supports automatic key rotation
Authentication SYSDBA / Global Roles IAM Database Auth Eliminates long-lived passwords; uses AWS Secrets Manager for application credentials

ZCINS replaced Oracle’s integrated user-schema model with PostgreSQL’s separate roles and schemas, requiring custom security architecture to manage ownership, access controls, and privilege cascading. To replicate Oracle VPD policies for regional data restriction, ZCINS implemented PostgreSQL RLS (Row level security). Replacing FGA with pgaudit, they streamed logs to CloudWatch, unifying application and database logs for cross-system correlation and richer audit reporting.

Other differences

PostgreSQL offers unique indexing and data-handling techniques that simplify legacy Oracle code. Transitioning these features often delivers the most immediate impact on developer productivity and licensing costs.

Table 5: Search, syntax, and storage comparisons
Aspect Oracle AWS managed PostgreSQL Impact
Indexing B-tree, Bitmap, Function-based, IOT B-tree, GIN, GiST, BRIN, Partial, Expression PostgreSQL lacks Bitmap indexes, but BRIN provides similar efficiency for time-series data with less overhead. Partial indexes index only a data subset, reducing I/O
Full-Text Search Oracle Text: CTX_DDL, CONTAINS/SCORE tsvector/tsquery; GIN indexes; pg_trgm Migrate Oracle Text to native GIN indexes; offload advanced needs to Amazon OpenSearch Service
Geospatial Oracle Spatial: SDO_GEOMETRY, SDO_ functions PostGIS: geometry/geography, ST_* functions PostGIS is the spatial gold standard. Use DMS SC to automate SDO_GEOMETRY migration
Vector Search Oracle AI Vector Search (23ai+) pgvector: vector type, HNSW/IVFFlat indexes Aurora/RDS pgvector support enables similarity search for ML embeddings and RAG, included in PostgreSQL
Large Objects CLOB, BLOB in separate LOB segments TOAST (Oversized Attribute Storage Technique) TOAST automatically compresses and moves large data out-of-line, transparently simplifying large text/binary management
SQL Syntax & Nulls SELECT FROM DUAL; ” treated as NULL No DUAL; ” is a distinct value, not NULL Audit logic where ” is expected to trigger NULL behavior

ZCINS used PostgreSQL GIN indexes for faster policy document searches and migrated from SDO_GEOMETRY to PostGIS. They also updated over 200 queries where the application expected empty strings to behave like NULL.

The modernization lifecycle

Moving to cloud-native PostgreSQL requires a disciplined lifecycle balancing speed with accuracy. Your migration follows seven phases depicted in Figure 1:

Figure 1: Modernization lifecycle

Figure 1: Modernization lifecycle

  1. Discovery inventories current architecture and dependencies.
  2. Assessment evaluates schema conversion complexity and calculates total cost of ownership (TCO).
  3. Planning designs target architecture, selecting instance sizes and rollback strategies.
  4. Implementation executes schema conversion, application remediation, and data migration.
  5. Testing validates that every row migrated accurately and performance meets baselines.
  6. Cutover freezes writes to the source and re-routes traffic.
  7. Post-Cutover optimization monitors real-world traffic to right-size resources and decommission the source.

The Assessment-to-Planning transition is the critical pivot from theoretical analysis to technical design. The quality of Discovery and Assessment determines how many surprises emerge during Testing and Cutover, the highest-risk stages. Though the lifecycle appears linear, Planning and Implementation often iterate as you refine target architecture based on testing results.

Discovery phase

Discovery is the foundation of your migration and inadequate discovery is the single most common cause of delays, budget overruns, and post-cutover incidents. This phase exposes the “as-is” architecture rather than the idealized version in outdated documentation.

You will likely uncover a reality more complex than diagrams suggest. Common hidden blockers include:

  • Shadow integrations: Undocumented database links and legacy API connections.
  • Obsolete logic: Dead code paths that still execute in production.
  • Recursive dependencies: Interconnected stored procedures cross-linking multiple business units.

By investing six weeks in deep discovery, ZCINS identified 37 critical dependencies missing from their architecture maps. Identifying these early prevented cascading implementation failures that would have required months of emergency rework and significant unplanned costs.

Set up objectives, principles, and constraints

Before technical analysis, formalize your guiding framework:

Objectives: Measurable goals such as “Eliminate Oracle licensing costs within 18 months” or “Achieve sub-second failover for customer-facing systems.”

Principles: Non-negotiable rules such as “Zero data loss during cutover” or “Near-zero production downtime.” For systems that cannot tolerate a maintenance window, plan a near-zero-downtime cutover using AWS DMS change data capture (CDC) for continuous replication and phased, application-level read/write redirection.

Constraints: Compliance requirements, budget boundaries, and team skill gaps.

This framework helps you make consistent decisions as you encounter trade-offs throughout assessment, planning, and execution.

Identify and engage stakeholders

Successful migration requires alignment across functions:

  • Business owners define downtime windows, validate success criteria, and approve go/no-go decisions.
  • Application teams provide code-level knowledge of Oracle dependencies and participate in testing.
  • Database administrators contribute Oracle expertise and validate conversion accuracy.
  • Operations teams define monitoring requirements and establish runbooks.
  • Security and compliance validate that the target architecture meets regulatory requirements.
  • Executive sponsors remove organizational blockers and maintain strategic alignment.

Early, continuous engagement ensures complete requirements and sustained support throughout the lifecycle.

Identify pain points and gather requirements

Document current-state challenges alongside future-state requirements. Each pain point becomes a migration success criterion whose resolution validates business value.

  1. Strategic pain points & success metrics: Define the target state, not just the challenge.
  2. Cost & compliance: “Oracle licensing consumes ~15% of IT budget; target <8% post-migration including Aurora infrastructure.”
  3. Performance & scalability: Use AWR/Statspack data, “Claims batch processing takes 6 hours; target ≤3 hours via parallel query optimization.”
  4. Operational efficiency: “DBA team spends 35% of capacity on patching/storage; target <10% using managed Aurora/RDS.”
  5. Comprehensive requirements analysis: Produces the Requirements Document covering three pillars, Functional (database objects, stored procedures, triggers, custom functions to preserve or enhance), Non-Functional (high availability RTO/RPO, DR objectives, elastic scaling), and Security & Compliance (mapping Oracle security policies, encryption, and audit patterns to AWS equivalents).
  6. Pain-point and risk register: For every challenge, document severity (1–5), frequency, affected stakeholders, and a preliminary risk score. This living document evolves as Assessment reveals deeper complexity.

Analyze applications and databases

Perform comprehensive analysis of applications interacting with Oracle:

  • Technology stack inventory: Languages, frameworks, ORM tools (Hibernate, Entity Framework), connection pooling, and middleware.
  • Application architecture analysis: Modules, service dependencies, integration patterns, and applications needing code changes due to database-specific functionality.
  • Database analysis: Schemas, tables, views, indexes, sequences, triggers, stored procedures, functions, packages, and user-defined types.
  • Data volume and growth analysis: Current volumes, growth patterns, and storage utilization to inform sizing and migration estimates.
  • Workload pattern analysis: Usage patterns, peak load, and transaction profiles.

This produces your asset inventory and workload pattern baseline, critical inputs for assessment.

Map dependencies

Dependency mapping transforms a chaotic asset list into a structured roadmap. It is critical for:

  • Reducing migration scope: Identifying obsolete code, orphaned procedures, and unused tables so you migrate only high-value assets.
  • Protecting integrations: Documenting cross-application touchpoints to prevent downstream failures.
  • Optimizing sequencing: Revealing dependencies so you group components into logical waves and avoid circular dependencies stalling deployment.
  • Accurate impact analysis: Identifying exactly which modules require testing or refactoring when you modify specific objects.

The output is your dependency graph, perhaps the most valuable discovery deliverable.

ZCINS’s discovery experience

ZCINS’s configuration management database documented 100 applications supported by 42 Oracle databases. Architecture diagrams showed clean boundaries, but incidents told a different story: performance issues cascaded across systems, database links behaved unpredictably, and unexplained Oracle AQ queues existed.

Discovery required automated analysis, manual investigation, and stakeholder interviews. ADMS SC mapped dependencies, AWR reports revealed workload patterns, network analysis uncovered hidden integrations, and code scanning exposed undocumented references. The resulting dependency graph revealed three coupling categories and the shared services as shown in Figure 2:

Figure 2: ZCINS Dependency graph

Figure 2: ZCINS Dependency graph

  • Loose coupling: independent migration: Billing Engine, Customer Portal, and Commission Calc had minimal interdependencies and could migrate independently.
  • Moderate coupling: coordinated migration: Policy Administration and Underwriting coupled through database links, API calls, and batch extracts, requiring coordinated planning but not simultaneous cutover.
  • Tightly coupled: migrate together: Claims Submission, Claims Adjudication, and Fraud Detection shared schemas and communicated via PL/SQL calls and Oracle AQ, requiring migration as a single unit.
  • Shared services: Reference Data and Reporting Database served multiple systems, requiring careful sequencing.

The graph revealed more dependencies than the diagrams showed. When you encounter undocumented dependencies, and you will, allocate additional discovery time. This investment prevents costly implementation surprises.

Discovery deliverables

At the conclusion of discovery, you should have produced:

  • Asset inventory: Catalog of databases, schemas, objects, applications, and integration points.
  • Dependency graph: Map of inter-system dependencies, coupling levels, and migration groupings.
  • Requirements document: Functional, non-functional, security, and compliance requirements.
  • Stakeholder map: Stakeholders with roles, responsibilities, and engagement cadence.
  • Pain-point and risk register: Current-state challenges, risks, and preliminary mitigations.
  • Platform differences reference: Understanding of Oracle/PostgreSQL differences informing assessment and planning.
  • Workload pattern baseline: AWR/Statspack data capturing current performance.

These deliverables become the foundation for Assessment, where you’ll analyze complexity and define target architecture.

Cleanup

No cleanup is required for this post as no AWS resources are provisioned.

Conclusion

Modernizing an Oracle workload is strategic re-engineering requiring respect for the profound differences between Oracle and PostgreSQL. While the transition yields business agility, technical scalability, and operational simplicity, success depends on comprehensive discovery.

Part 2 covers Assessment, where you’ll evaluate schema conversion complexity, calculate total cost of ownership, and produce data-driven recommendations. You’ll see how ZCINS used DMS SC assessment reports to prioritize migration waves and build executive support for the 18-month modernization program.

Continue your Oracle-to-PostgreSQL migration journey with Part 2: Assessment, where we walk through schema complexity scoring, TCO modeling, and target architecture selection for the ZCINS case study.