Database field guide · 11
Oracle to PostgreSQL
Translate behavior, not syntax
A migration field guide to the architectural differences behind plans, MVCC, indexes, datatypes, transactions, and operations.
Franck Pachot9 chaptersOracle · PostgreSQL · migration
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
Compatibility is behavioral
A converter can rewrite syntax while preserving the wrong assumptions about empty strings, implicit casts, isolation, locking, or object names. Inventory the application behaviors that matter before translating code, then test those behaviors on both systems.
- Classify differences as syntax, semantics, performance, or operations.
- Create paired tests for critical queries and transactions.
- Do not use one product as an imperfect emulator of the other.
02
Plans use different vocabularies
Oracle and PostgreSQL expose similar physical ideas through different nodes, cost models, and instrumentation. A FULL scan resembles a sequential scan; index range access resembles an index scan, but visibility checks, bitmap mechanics, and row-location designs change the real work.
- Compare rows, buffers, and time rather than cost numbers across products.
- Translate the purpose of a hint before seeking an equivalent.
- Refresh statistics and verify estimates after loading migrated data.
03
MVCC moves the maintenance burden
Oracle reconstructs older images largely from undo while PostgreSQL stores tuple versions in the heap and later vacuums them. This changes index access, update amplification, space reuse, long-running transaction impact, and the operational signals used to detect trouble.
- Size and tune autovacuum as a core workload service.
- Review update-heavy tables for HOT opportunities.
- Replace undo-retention assumptions with snapshot and bloat monitoring.
04
Row identity is not portable
Oracle ROWID and PostgreSQL CTID both identify physical locations, but updates and table maintenance can change them. Neither should replace a declared business or surrogate key. PostgreSQL updates often create a new CTID as a normal consequence of MVCC.
- Add explicit primary keys before replication or synchronization.
- Remove persisted ROWID dependencies from application contracts.
- Use physical identifiers only for controlled, immediate diagnostics.
05
Index features solve different gaps
PostgreSQL partial indexes, expression indexes, included columns, and visibility-map-dependent index-only scans do not map one-to-one to Oracle function-based, bitmap, or covering strategies. Rebuild the index set from migrated query shapes and write costs.
- Preserve constraints before preserving incidental indexes.
- Retest null and uniqueness behavior.
- Consolidate overlapping indexes after representative workload capture.
06
Transactions need semantic tests
Oracle Read Committed consistency, Oracle's Serializable implementation, and PostgreSQL isolation levels differ in snapshot boundaries and conflict handling. Lock modes and foreign-key behavior also differ, so successful single-session tests prove little.
- Run two-session anomaly and blocking tests.
- Implement retries for deadlocks and serialization failures.
- Verify autonomous and external side effects during transaction rewrites.
07
Datatypes carry application policy
Oracle treats an empty string as null; PostgreSQL distinguishes them. Numeric precision, timestamp zones, character padding, generated values, and implicit conversions can all change results or index use after a syntactically successful migration.
- Profile actual values, not only declared source types.
- Make casts and timezone policy explicit at boundaries.
- Compare ordering, null handling, and round trips with production samples.
08
Operations complete the migration
AWR, ASH, SQL Plan Management, SQL*Plus, Data Guard, and RMAN responsibilities must be mapped to PostgreSQL statistics, logs, extensions, psql, replication, backup, and recovery practices. Feature names matter less than preserving the operating capability.
- Define performance baselines before cutover.
- Practice restore and failover under the target topology.
- Train incident response on PostgreSQL wait and lock evidence.
09
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
Test null and empty-string behavior first
Oracle folds a zero-length character string to NULL; PostgreSQL stores it as a distinct value. This changes NOT NULL checks, unique constraints, concatenation, count(column), and application round trips. Do not hide the difference in scattered compatibility functions. Choose a target policy, clean source values, and test every boundary that treats blank as missing.
-- PostgreSQL returns two different predicates
SELECT '' IS NULL AS empty_is_null,
NULL IS NULL AS null_is_null,
length('') AS empty_length;
-- Normalize explicitly only where the business model requires it:
NULLIF(btrim(input_value), '')
Translate plans through physical work
Oracle cost and PostgreSQL cost are unrelated units. Map FULL TABLE SCAN to Seq Scan conceptually, but compare actual rows, buffers, temp spills, and elapsed time. PostgreSQL Bitmap Heap Scan has no direct Oracle bitmap-index requirement, and Index Only Scan depends on the visibility map. Replace hints by understanding which estimate or physical structure made the desired path unavailable.
EXPLAIN (ANALYZE, BUFFERS, WAL, SETTINGS) SELECT ...;
-- Oracle counterpart for runtime row-source statistics:
SELECT * FROM TABLE(dbms_xplan.display_cursor(
sql_id => :sql_id, format => 'ALLSTATS LAST +PEEKED_BINDS'));
Replace undo assumptions with vacuum operations
Oracle retains before-images in undo and can raise snapshot-too-old when required undo is overwritten. PostgreSQL leaves old tuple versions in heap pages until no snapshot can see them, then vacuum marks space reusable. Long PostgreSQL transactions delay cleanup across every table they might see. Migration capacity plans must include autovacuum throughput, WAL volume, fillfactor, and index bloat.
SELECT pid, now() - xact_start AS age, backend_xmin, query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;
SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
Map generated values and numeric types explicitly
Oracle NUMBER without precision can hold a wider range than many automatic mappings. PostgreSQL numeric is exact but slower and larger than integer types; choose bigint only after proving the range. Oracle sequences are independent objects, while PostgreSQL identity columns own sequence behavior more clearly. Neither guarantees gap-free committed numbering.
CREATE TABLE invoice (
invoice_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
amount numeric(19,4) NOT NULL,
issued_at timestamptz NOT NULL
);
Build a capability-based operations map
Map responsibilities rather than product names: statement aggregation, session sampling, plan capture, backup, point-in-time recovery, physical replication, failover control, and patching. pg_stat_statements is not AWR, streaming replication is not Data Guard Broker, and a filesystem copy is not a tested backup. Define retention, overhead, recovery objectives, and ownership for each target capability.
CREATE EXTENSION pg_stat_statements;
SELECT queryid, calls, total_exec_time, rows,
shared_blks_hit, shared_blks_read, temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;
10
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
