Zero-Downtime PostgreSQL Migration from Oracle
Migrated 15TB Oracle database to PostgreSQL with zero downtime using change data capture and gradual traffic cutover. Saved $2.1M annually in licensing.
[Overview] System Overview
A large insurance company operated its policy management system on Oracle Database for 18 years. The database contained 15TB of data spanning policy details, claims history, customer records, and actuarial calculations. Transaction volume reached 50,000 per second during enrollment periods.
Oracle licensing costs had grown to $2.4M annually, consuming a significant portion of IT budget. The company evaluated migrating to PostgreSQL, which offered comparable functionality at dramatically lower cost. However, migration complexity was substantial-the database included 500+ stored procedures, complex triggers, and Oracle-specific features.
We designed and executed a zero-downtime migration using change data capture to keep PostgreSQL synchronized with Oracle during an extended parallel operation period. The migration preserved all business logic while eliminating the licensing burden.
[Context] Situation before
Oracle had been reliable but expensive. License audits were stressful, with the vendor frequently finding "non-compliance" requiring additional licensing. The relationship felt adversarial rather than partnership-oriented.
##### Technical Lock-in
Applications used Oracle-specific PL/SQL extensively. Stored procedures implemented core business logic. Oracle-specific data types were used throughout. Migration seemed impossible without rewriting applications.
##### Failed Previous Attempts
Two previous migration attempts had failed. The first attempted big-bang cutover and was abandoned after testing revealed data issues. The second started rewriting stored procedures but stalled when complexity exceeded estimates.
[Diagnosis] The real problem
Previous attempts failed because they treated migration as a single project with a fixed deadline. The scope was too large to succeed in one effort. Risk accumulated until it overwhelmed the project.
The stored procedure challenge was particularly severe. Many procedures had undocumented business logic-the procedures themselves were the only documentation. Oracle-specific syntax permeated every procedure.
The real solution required incremental migration with continuous validation-never betting the business on a single cutover.
Understanding the root cause changed everything.
[Boundaries] Working constraints
Insurance system migration operates under strict regulatory and operational constraints.
Constraints are features, not obstacles. They guide design decisions.
[Decisions] Key tradeoffs
We evaluated migration approaches that could work within constraints.
status="rejected" title="Big-Bang Cutover" description="Complete migration during maintenance window" reasoning="Previous attempt showed this is too risky. No maintenance window long enough exists." /> status="chosen" title="Strangler Pattern" description="Gradually move functionality to new database" reasoning="Incremental migration limits risk. Each step is independently validated. Rollback is always possible." /> status="rejected" title="Hybrid Long-term" description="Keep some data in Oracle, some in PostgreSQL" reasoning="Hybrid creates distributed transaction complexity. Licensing savings only achieved with complete migration." />
[Design] Architecture design
The architecture uses Debezium for change data capture from Oracle, streaming changes to Kafka, then applying to PostgreSQL in real-time. The PostgreSQL replica stays synchronized within seconds of Oracle.
Application routing layer directs read queries to PostgreSQL progressively while writes continue to Oracle. Once PostgreSQL is validated, writes migrate too, with reverse synchronization keeping Oracle updated during parallel operation.
"type": "architecture", "title": "Zero-Downtime Migration Architecture", "description": "CDC-based migration with dual-write capability", "components": [ "name": "Application", "type": "service" }, "name": "Router", "type": "service", "description": "Read/write routing" }, "name": "Oracle DB", "type": "database", "description": "Source database" }, "name": "Debezium", "type": "service", "description": "CDC capture" }, "name": "Kafka", "type": "queue", "description": "Change stream" }, "name": "Sync Service", "type": "service", "description": "Apply changes" }, "name": "PostgreSQL", "type": "database", "description": "Target database" } ], "connections": [ "from": "Application", "to": "Router", "label": "Queries" }, "from": "Router", "to": "Oracle DB", "label": "Writes (initially)" }, "from": "Router", "to": "PostgreSQL", "label": "Reads (progressive)" }, "from": "Oracle DB", "to": "Debezium", "label": "Log mining" }, "from": "Debezium", "to": "Kafka", "label": "Changes" }, "from": "Kafka", "to": "Sync Service", "label": "Consume" }, "from": "Sync Service", "to": "PostgreSQL", "label": "Apply" } ], "boundaries": [ "name": "Migration Layer", "contains": [ "Debezium", "Kafka", "Sync Service", "Router" ] } ] }
{/* Diagram: sequence - Gradual Cutover Sequence */}
{/* ```json
{
"type": "sequence",
"title": "Gradual Cutover Sequence",
"description": "Progressive traffic migration with validation",
"components": [
{
"name": "Query",
"type": "user"
},
{
"name": "Router",
"type": "service"
},
{
"name": "Oracle",
"type": "database"
},
{
"name": "PostgreSQL",
"type": "database"
},
{
"name": "Validator",
"type": "service"
}
],
"connections": [
{
"from": "Query",
"to": "Router",
"label": "1. Read request"
},
{
"from": "Router",
"to": "PostgreSQL",
"label": "2. Route to PG (increasing %)"
},
{
"from": "Router",
"to": "Oracle",
"label": "3. Shadow read"
},
{
"from": "Validator",
"to": "Oracle",
"label": "4. Compare results"
},
{
"from": "Validator",
"to": "PostgreSQL",
"label": "5. Validate match"
}
]
}[Approach] Execution model
Migration proceeded in waves over 12 months. Wave 1 migrated read-only reference data (2 months). Wave 2 migrated read-heavy transactional data (3 months). Wave 3 migrated write-heavy core tables (4 months). Wave 4 migrated remaining tables and cut over writes (3 months).
Each wave followed the same pattern: schema migration, data synchronization, gradual read traffic shift, validation, write migration, Oracle decommission for those tables.
[Results] Measurable outcomes
Migration completed with zero downtime and zero data loss. Not a single policy servicing request was delayed due to migration activities. Regulatory auditors reviewed the migration approach and confirmed compliance throughout.
Annual licensing savings of $2.1M were achieved immediately upon Oracle decommissioning. PostgreSQL runs on commodity hardware at $180K annually, a 91% cost reduction.
Performance improved unexpectedly. PostgreSQL on modern hardware with SSD storage outperformed the aging Oracle installation. Query response times improved 35% on average.
title="Key improvements achieved:" items={[ "System handles 10x burst load without degradation", "Delivery latency reduced from seconds to milliseconds", "Zero message loss during traffic spikes", "Operational complexity reduced by 60%" ]} />
[Anti-patterns] What could have gone wrong
Without migration, Oracle costs would have continued growing. The vendor was pressuring for additional licensing based on virtualization changes. Projected 5-year Oracle costs exceeded $15M.
The existing Oracle hardware was aging. Replacement would require substantial capital expenditure plus ongoing licensing. The migration made economic sense even considering the project investment.
variant="yellow" title="" items={[ "Over-engineering the initial solution", "Ignoring operational constraints during design", "Premature optimization without load testing", "Skipping failure mode analysis" ]} />
[Applicability] Where this pattern applies
CDC-based migration applies to any database migration with zero-downtime requirements. The pattern works for database version upgrades, cloud migrations, and vendor changes.
The key enabler is real-time synchronization. Modern CDC tools make this practical even for large databases with complex schemas.
Every system is different. Context always matters.