Oracle to PostgreSQL Migration: What No One Tells You

Why this migration is different

Oracle to PostgreSQL is the most requested — and most underestimated — enterprise database migration. The databases share SQL syntax at the surface level, which creates a dangerous illusion of compatibility. The real challenges are deeper: PL/SQL conversion, Oracle-specific data types, partitioning strategies, and optimizer behaviour differences.

Data type mapping: the non-obvious cases

The straightforward mappings (NUMBER → NUMERIC, VARCHAR2 → VARCHAR) are well documented. These are the ones that cause production issues:

• DATE in Oracle includes time; in PostgreSQL, DATE is date-only. Use TIMESTAMP instead.
• NUMBER without precision in Oracle is a floating-point number. Map to NUMERIC or DOUBLE PRECISION depending on usage.
• CLOB/BLOB → TEXT/BYTEA, but PostgreSQL has a 1GB limit on TEXT fields. If your CLOBs exceed this, you need an external storage strategy.
• RAW → BYTEA, but the hex encoding is different. Conversion must handle this at the application layer.
• INTERVAL types have different syntax between Oracle and PostgreSQL.

PL/SQL to PL/pgSQL: the hard part

Oracle PL/SQL and PostgreSQL PL/pgSQL look similar but differ in critical ways:

• Package support: Oracle packages don't exist in PostgreSQL. Each procedure and function must be standalone, and package-level variables must be replaced with session variables or schema-level functions.
• Exception handling: Oracle's WHEN OTHERS THEN maps to PostgreSQL's EXCEPTION WHEN OTHERS THEN, but the error codes are completely different.
• Autonomous transactions: Oracle supports PRAGMA AUTONOMOUS_TRANSACTION. PostgreSQL requires dblink or a separate connection — a significant architectural change.
• Cursors: Oracle's implicit cursors don't exist in PostgreSQL. Every cursor must be explicitly declared.
• CONNECT BY: Oracle's hierarchical query syntax must be rewritten using PostgreSQL's recursive CTEs (WITH RECURSIVE).

Partitioning strategy differences

Oracle's partitioning is more mature and flexible. When migrating:

• Oracle range-range composite partitioning → PostgreSQL supports this natively since v12, but the syntax is different.
• Oracle hash partitioning → PostgreSQL hash partitioning works differently. Partition count must be a power of 2 for optimal distribution.
• Oracle interval partitioning (auto-create partitions) → PostgreSQL requires pg_partman extension or manual partition management.

Performance tuning post-migration

The Oracle optimizer and PostgreSQL planner make different decisions. After migration:

• Run ANALYZE on all tables to populate PostgreSQL statistics.
• Review execution plans for your top 20 queries. Oracle hint-based tuning doesn't translate — PostgreSQL uses cost-based optimization exclusively.
• Adjust work_mem, shared_buffers, and effective_cache_size based on your workload profile.
• Consider pg_stat_statements for ongoing query performance monitoring.