How Xintai Life Insurance Replaced Oracle with PostgreSQL: A Three‑Step De‑Oracle Strategy

Background

For many years Chinese enterprises relied on the "IOE" stack—IBM mainframes, Oracle databases, and EMC storage. After the 2013 "Prism" disclosures, the Chinese government issued the "Document 39" policy to promote self‑controlled (自主可控) technologies and to curb the high licensing costs of Oracle as data volumes grew.

Motivation for De‑Oracle

Oracle license fees can reach millions of yuan per year, creating a strong financial incentive and a strategic need to avoid vendor lock‑in. Xintai Life Insurance therefore launched a multi‑year "去O" (de‑Oracle) program to replace Oracle with an open‑source alternative.

Migration Strategy

Database Selection – An independent evaluation was performed, comparing total cost of ownership, ecosystem maturity, and compatibility with existing applications. PostgreSQL was chosen for its open‑source licence, active community, and built‑in features that reduce Oracle‑specific syntax gaps.

Intensive Testing – Each target system underwent exhaustive functional and performance testing. Test cases covered 60‑80 % of the overall effort and simulated real‑world transaction loads, data‑migration scripts, and failure scenarios to verify parity with the Oracle baseline.

Phased Rollout with a Plan B – Migration started with peripheral, low‑risk systems to accumulate experience, then progressed to core insurance applications. A rollback procedure (Plan B) was prepared for each phase to guarantee service continuity.

Compatibility Layer on PostgreSQL

To minimise code changes, a compatibility layer was built on top of PostgreSQL. Key components included:

Emulation of Oracle DBLINK using foreign data wrappers.

Wrapper functions for SEQUENCE objects to enforce the same maximum values and caching behaviour.

Handling of empty strings versus NULL to preserve Oracle semantics.

SQL alias translation utilities that rewrite Oracle‑style column aliases to PostgreSQL‑compatible syntax.

PL/SQL to PL/pgSQL conversion scripts for stored procedures and triggers.

Testing Methodology

Test suites were organised into functional, performance, and regression groups. Automated scripts executed DDL/DML workloads, bulk data loads, and concurrent user simulations. Metrics such as query latency, transaction throughput, and error rates were recorded and compared against the Oracle baseline. Issues identified during testing were classified and addressed before moving to the next rollout phase.

Rollout and Rollback Procedures

Each migration batch followed a defined checklist:

Backup of the Oracle instance and snapshot of the target PostgreSQL database.

Deployment of the compatibility layer and configuration of connection strings.

Execution of a dry‑run migration in a staging environment.

Cut‑over to production after successful validation.

If validation failed, the Plan B process restored the Oracle instance from backup within the predefined SLA.

Post‑Migration Operations

PostgreSQL does not provide a native enterprise‑grade monitoring suite comparable to Oracle Enterprise Manager. Xintai Life therefore integrated Zabbix to collect metrics such as CPU, memory, disk I/O, connection count, replication lag, and query performance. Custom templates and alerts were created to detect abnormal behaviour and to trigger automated remediation scripts.

Key Migration Issues Encountered

During the de‑Oracle effort, roughly 50 concrete issues were documented and resolved, including:

Differences in DBLINK syntax and authentication.

Maximum value limits of SEQUENCE objects and cache size mismatches.

Oracle’s treatment of empty strings as NULL versus PostgreSQL’s distinct handling.

SQL alias placement rules that affect query parsing.

Data‑type mismatches (e.g., NUMBER precision, DATE vs TIMESTAMP).

PL/SQL package bodies requiring manual refactoring.

Optimizer behaviour differences leading to plan changes.

All issues were captured in a migration knowledge base to support future de‑Oracle projects.