Migrating from Oracle to PostgreSQL — why and how

Oracle Database has been with us since 2003. Reliable, performant, proven. But also expensive. When we calculated the licensing costs over the past ten years, the number frightened us. We started looking for an alternative.

Why PostgreSQL¶

There were several candidates: MySQL, MariaDB, PostgreSQL. We ruled out MySQL due to uncertainty around Oracle’s ownership. PostgreSQL won us over with its enterprise-grade feature set — window functions, CTEs, partial indexes, JSONB support. PostgreSQL 9.3 brought materialised views and improved JSON support. The PostgreSQL community is active and professional.

What we migrated¶

We chose an internal project management system — approximately 120 tables, 50 stored procedures, and dozens of views. Not our largest system, but complex enough to verify feasibility.

Tools and data type conversion¶

We used Ora2Pg to export the schema and data. Manual fixes: NUMBER → NUMERIC, VARCHAR2 → VARCHAR, trigger-based auto-increment → SERIAL.

-- Oracle
CREATE TABLE projects (
    id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name VARCHAR2(200) NOT NULL,
    created_date DATE DEFAULT SYSDATE
);

-- PostgreSQL
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

PL/SQL → PL/pgSQL¶

The most labour-intensive part. Oracle-specific functions (NVL, DECODE, ROWNUM) were rewritten using COALESCE, CASE, and ROW_NUMBER. Packages were replaced by schemas. Some procedures were simplified during the rewrite — Oracle workarounds were no longer needed.

Performance and monitoring¶

On most queries PostgreSQL is comparable with Oracle. EXPLAIN ANALYZE is a fantastic tool — it provides more information than Oracle’s EXPLAIN PLAN. We miss Oracle Enterprise Manager and RAC clustering. pgAdmin is solid, but does not reach the level of OEM.

What is better in PostgreSQL¶

Cost: Zero licensing fees. Simplicity: Installation in minutes, configuration in one file. JSONB: Storing and querying JSON documents directly within a relational database.

The migration paid off¶

After three months of work, our project management system runs on PostgreSQL. Performance is comparable, operational costs significantly lower. Oracle will remain for our most critical systems, but PostgreSQL is a fully-fledged enterprise database.