🔄 Oracle to PostgreSQL Transfer: pg_fasttransfer vs oracle_fdw

Migrating large tables from Oracle to PostgreSQL can be challenging when performance and reliability matter. In this article, we compare two tools that let you move data from Oracle to PostgreSQL:

• pg_fasttransfer: A high-performance, parallel transfer tool
• oracle_fdw: A Foreign Data Wrapper that lets PostgreSQL query Oracle tables

We benchmarked both tools using the same data set and report on performance, setup complexity, and features.

---

📦 Test Scenario

We transferred the ORDERS table from Oracle (FREEPDB1, schema TPCH10, \~15 million rows, 9 columns) into a PostgreSQL table named orders_oracle.

---

⚡ 1. Transfer Using pg_fasttransfer

-- Enable the extension
CREATE EXTENSION IF NOT EXISTS pg_fasttransfer;

-- Create the target table
CREATE TABLE Target_Table;

-- Launch the high-performance transfer
SELECT * FROM xp_RunFastTransfer_secure(
  sourceconnectiontype := 'oraodp',
  sourceserver := 'localhost:1521/FREEPDB1',
  sourceuser := 'system',
  sourcepassword := '...encrypted...',
  sourcedatabase := 'FREEPDB1',
  sourceschema := 'TPCH10',
  sourcetable := 'ORDERS',
  targetconnectiontype := 'pgcopy',
  targetserver := 'localhost:15433',
  targetuser := 'postgres',
  targetpassword := '...encrypted...',
  targetdatabase := 'postgres',
  targetschema := 'public',
  targettable := 'orders_oracle',
  degree := 8,
  method := 'Rowid',
  loadmode := 'Truncate',
  batchsize := 1048576,
  mapmethod := 'Position',
  fasttransfer_path := 'D:\pacollet\FastTransfer'
);

⏱ Time Taken: 22.8 seconds

🔐 Security: Encrypted passwords (PGP-compatible)

🚅 Transfer Mode: Multi-threaded fetch via ROWID + binary COPY into PostgreSQL

---

🛠 2. Transfer Using oracle_fdw

CREATE SERVER oracle_svr
  FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//localhost:1521/FREEPDB1');

CREATE USER MAPPING FOR postgres
  SERVER oracle_svr
  OPTIONS (user 'SYSTEM', password 'system');

CREATE FOREIGN TABLE oracle_orders_foreign (
    o_orderdate      DATE,
    o_orderkey       BIGINT,
    o_custkey        BIGINT,
    o_orderpriority  CHAR(15),
    o_shippriority   INTEGER,
    o_clerk          CHAR(15),
    o_orderstatus    CHAR(1),
    o_totalprice     NUMERIC(19,4),
    o_comment        VARCHAR(79)
)
SERVER oracle_svr
OPTIONS (schema 'TPCH10', table 'ORDERS');

-- Transfer the data into a local PostgreSQL table
INSERT INTO public.orders_oracle
SELECT * FROM oracle_orders_foreign;

⏱ Time Taken: 354 seconds (\~6 minutes)

🔒 Security: Plain text credentials

📤 Transfer Mode: Row-by-row fetch via Oracle client

---

📊 Performance Summary

Tool	Time Taken	Rows	Transfer Method	Notes
pg_fasttransfer	22.8 s	\~15 million	Parallel + COPY	Very fast, secure, batch-based
oracle_fdw	354 s	\~15 million	Sequential row fetch + INSERT	Simple setup, but slow for large sets

---

✅ Feature Comparison

Feature	pg_fasttransfer	oracle_fdw
Oracle to PostgreSQL transfer	✅ Yes	✅ Yes
Secure password encryption	✅ Yes (PGP)	❌ No
Parallelism	✅ Yes (configurable threads)	❌ No
Batched transfer	✅ Yes	❌ No
Federated query access	❌ No	✅ Yes
Ideal for large data volumes	✅ Absolutely	❌ Not optimized
Cross-platform support	✅ Windows & Linux	✅ Windows & Linux (Oracle client needed)
Requires external binary	✅ Yes	❌ No

---

🏁 Conclusion

✅ Choose pg_fasttransfer for large Oracle → PostgreSQL migrations

If you’re dealing with millions of rows and need a fast, secure, and automated solution, pg_fasttransfer is clearly the better option:

• Over 15x faster than oracle_fdw in our test
• Supports multi-threaded parallel execution
• Designed for ETL workflows and high-throughput transfers

🚀 Use it when: migrating data warehouses, syncing large Oracle datasets, or automating data ingestion pipelines.

---

🔗 Choose oracle_fdw for live Oracle access

If your need is real-time querying or ad-hoc access to Oracle tables from PostgreSQL, oracle_fdw remains a solid choice:

• Simple to set up
• Works well for low to medium volume
• Enables federated queries with PostgreSQL

🧩 Use it when: integrating Oracle into reporting dashboards, joining data from multiple sources, or building prototypes.

---

📎 Resources

• 🔗 pg_fasttransfer GitHub
• 🔗 FastTransfer CLI Docs
• 🔗 FastTransfer CLI Home
• 🔗 oracle_fdw GitHub