oracle_fdw

What oracle_fdw does

oracle_fdw establishes a live connection from PostgreSQL to an Oracle database via Oracle Call Interface (OCI) and exposes Oracle tables as foreign tables in your PostgreSQL database. Queries against these foreign tables are translated into Oracle SQL, executed on the Oracle server, and the results are returned to PostgreSQL over the network.

The extension handles the type mapping between Oracle and PostgreSQL automatically. Oracle VARCHAR2 maps to PostgreSQL varchar or text, Oracle NUMBER maps to numeric or integer types, Oracle DATE (which includes time) maps to PostgreSQL timestamp, and so on. This mapping covers the full range of Oracle data types including CLOB, BLOB, RAW, and XMLTYPE.

Performance-critical operations like WHERE clause filtering are pushed down to Oracle — the extension sends the filter conditions to the Oracle server rather than fetching all rows and filtering locally. JOIN pushdown is also supported for joins between two foreign tables on the same Oracle server, reducing network traffic.

When to use oracle_fdw

Common scenarios where oracle_fdw is the right tool:

• Oracle-to-PostgreSQL migration — run both databases simultaneously during a gradual migration, with PostgreSQL reading from Oracle via foreign tables for not-yet-migrated data
• Table-by-table migration — migrate one table at a time from Oracle to PostgreSQL while keeping the application running, using foreign tables to bridge the gap for tables still in Oracle
• Cross-database reporting — join Oracle data with PostgreSQL data in a single query without building ETL pipelines or data warehouses
• Data validation during migration — compare Oracle source data with migrated PostgreSQL data by querying both from the same PostgreSQL session
• Legacy system integration — access Oracle-based systems from PostgreSQL applications without modifying the Oracle side

Installation and setup

oracle_fdw is a third-party extension, not a contrib module. It requires Oracle Instant Client libraries to be installed on the PostgreSQL server — the basic and devel (SDK) packages at minimum. The Instant Client provides the OCI layer that oracle_fdw uses to communicate with Oracle.

-- Prerequisites: Oracle Instant Client (basic + devel) must be installed
-- on the PostgreSQL server, and ORACLE_HOME / LD_LIBRARY_PATH must be set.

-- Install the extension (requires superuser)
CREATE EXTENSION oracle_fdw;

Step 1: Create a foreign server

The dbserver option accepts an Oracle Easy Connect string or a TNS name. If the Oracle server is on the same machine, you can omit dbserver and oracle_fdw will use a local bequeath connection.

-- Create a foreign server pointing to an Oracle database
-- The dbserver value is an Oracle connect string (TNS or Easy Connect)
CREATE SERVER oracle_server
  FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//oracle-host.example.com:1521/ORCL');

Step 2: Create a user mapping

A user mapping associates a local PostgreSQL user with Oracle credentials. The Oracle user must have SELECT (and optionally INSERT, UPDATE, DELETE) privileges on the target tables.

-- Map a local PostgreSQL user to Oracle credentials
CREATE USER MAPPING FOR current_user
  SERVER oracle_server
  OPTIONS (user 'oracle_user', password 'oracle_password');

Step 3: Create foreign tables

You can define foreign tables individually or import an entire Oracle schema at once. Schema and table names in Oracle are uppercase by default — use double quotes in the IMPORT FOREIGN SCHEMA command to preserve the case.

-- Create a foreign table mapping to an Oracle table
-- Column names must match the Oracle column names (case-insensitive)
CREATE FOREIGN TABLE oracle_orders (
  order_id   numeric,
  customer_id numeric,
  order_date timestamp,
  total      numeric(10,2),
  status     varchar(20)
)
SERVER oracle_server
OPTIONS (schema 'SALES', table 'ORDERS');

-- Import all tables from an Oracle schema at once
IMPORT FOREIGN SCHEMA "SALES"
  FROM SERVER oracle_server
  INTO local_schema;

-- Import only specific tables
IMPORT FOREIGN SCHEMA "SALES"
  LIMIT TO ("ORDERS", "CUSTOMERS", "PRODUCTS")
  FROM SERVER oracle_server
  INTO local_schema;

IMPORT FOREIGN SCHEMA is the preferred approach when migrating large Oracle schemas. It reads the Oracle data dictionary and creates matching foreign tables in PostgreSQL with appropriate type mappings automatically.

Querying and writing

Once foreign tables are in place, you use them like any local table. SELECT, INSERT, UPDATE, and DELETE all work transparently.

-- Query an Oracle table like any local table
SELECT customer_id, sum(total)
FROM oracle_orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id
ORDER BY sum(total) DESC;

-- Join a local PostgreSQL table with an Oracle foreign table
SELECT c.name, sum(o.total) AS lifetime_value
FROM customers c
JOIN oracle_orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY lifetime_value DESC;

-- Write to an Oracle table through the foreign table
INSERT INTO oracle_orders (order_id, customer_id, order_date, total, status)
VALUES (50001, 42, now(), 99.95, 'PENDING');

UPDATE oracle_orders SET status = 'SHIPPED' WHERE order_id = 50001;
DELETE FROM oracle_orders WHERE order_id = 50001;

Joins between a local PostgreSQL table and an Oracle foreign table work, but the join executes locally — PostgreSQL fetches the Oracle data first, then joins it with local data. For large Oracle tables, this is the equivalent of asking the kitchen to prepare dinner using ingredients stored in a neighbouring building. Add WHERE clauses to limit what crosses the network, or cache frequently joined data in a materialized view.

Type mapping

oracle_fdw maps Oracle data types to PostgreSQL types when creating foreign tables. The most important mapping to remember — and the one that has silently caused more migration grief than I care to recount — is that Oracle DATE includes a time component and should be mapped to PostgreSQL timestamp, not date.

-- Common Oracle-to-PostgreSQL type mappings used by oracle_fdw
--
-- Oracle type          PostgreSQL type
-- -------------------  ---------------------------
-- VARCHAR2(n)          varchar(n), text
-- CHAR(n)              char(n), varchar, text
-- NUMBER               numeric
-- NUMBER(n)            numeric, int2, int4, int8
-- NUMBER(n,m)          numeric(n,m), float4, float8
-- DATE                 timestamp (Oracle DATE includes time)
-- TIMESTAMP            timestamp, timestamptz
-- CLOB                 text, json
-- BLOB                 bytea
-- RAW(n)               bytea, uuid (for RAW(16))
-- XMLTYPE              xml

When using IMPORT FOREIGN SCHEMA, oracle_fdw chooses appropriate PostgreSQL types automatically. When defining foreign tables manually, you can choose which PostgreSQL type to use — for example, mapping Oracle NUMBER(1) to PostgreSQL boolean, or Oracle RAW(16) to PostgreSQL uuid.

Performance and caching

Every oracle_fdw query crosses the network to the Oracle server, so performance depends heavily on network latency and the volume of data transferred. Key strategies for improving performance:

• WHERE pushdown — oracle_fdw automatically pushes WHERE conditions to Oracle. Use EXPLAIN VERBOSE to verify that your filters are being pushed down.
• ANALYZE foreign tables — run ANALYZE on foreign tables so the PostgreSQL planner has accurate row estimates for choosing join strategies.
• Materialized views — for data that does not need to be real-time, cache Oracle query results in a local materialized view with local indexes.
• Limit columns — only include columns you actually need in the foreign table definition. oracle_fdw only fetches the columns referenced in the query, but having fewer columns simplifies maintenance.

-- Cache frequently queried Oracle data in a local materialized view
CREATE MATERIALIZED VIEW recent_oracle_orders AS
SELECT order_id, customer_id, order_date, total, status
FROM oracle_orders
WHERE order_date >= now() - interval '30 days';

CREATE INDEX ON recent_oracle_orders (customer_id);
CREATE INDEX ON recent_oracle_orders (order_date);

-- Refresh periodically (or use pg_cron to automate)
REFRESH MATERIALIZED VIEW CONCURRENTLY recent_oracle_orders;

Cloud availability

Cloud availability for oracle_fdw is more limited than most extensions because it requires Oracle Instant Client libraries on the server. The major cloud providers (AWS, GCP, Azure) bundle these libraries in their managed PostgreSQL offerings. Serverless and developer-focused platforms generally do not. Network connectivity between the cloud PostgreSQL instance and the Oracle server must also be configured (VPC peering, private endpoints, or public access with firewall rules).

How Gold Lapel relates

A migration from Oracle to PostgreSQL is a period when patience is a virtue and latency is an adversary. Every oracle_fdw query crosses the network — and during the months when half your tables are here and half are still over there, those round trips can quietly dominate your response times.

This is precisely the sort of situation I was built for. Gold Lapel watches your oracle_fdw query patterns and identifies the ones that run repeatedly against stable data. When the same query fetches the same Oracle results for the hundredth time, I can recommend — or create outright — a materialized view that caches that data locally with proper indexes. The next hundred queries never leave PostgreSQL.

If I may be direct: migrations done well take time. Table by table, carefully, with validation at each step. Gold Lapel's caching removes the performance pressure that tempts teams into rushing. You migrate at the pace the work deserves, not at the pace the latency demands.