Replacing Oracle Database Links with Postgres Foreign Data Wrappers

How Oracle Database Links Work

An Oracle database link is a named connection definition stored in the data dictionary. When SQL references a remote object using table_name@dblink_name, Oracle's distributed query executor opens a connection to the remote database, sends the relevant portion of the query, and merges the results locally. The link definition includes the remote database's service name (from tnsnames.ora or LDAP), the authentication credentials, and optionally a fixed connection type (shared or dedicated).

-- Oracle: create a database link
CREATE DATABASE LINK remote_db
  CONNECT TO app_user IDENTIFIED BY "password"
  USING 'remote_service_name';

-- Oracle: query through a database link
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers@remote_db c ON c.customer_id = o.customer_id
WHERE o.order_date > SYSDATE - 30;

-- Oracle: write through a database link
INSERT INTO audit_log@remote_db (event_type, event_time)
VALUES ('ORDER_CREATED', SYSDATE);

Oracle's distributed query optimizer can push predicates and joins to the remote site, which is important for performance — if a join filter eliminates 99% of rows before network transfer, the optimizer can push that filter to the remote database and return only the relevant rows across the link.

How Postgres FDW Works

Postgres Foreign Data Wrappers implement the SQL/MED standard. The architecture has three layers: a foreign data wrapper (the driver, e.g., postgres_fdw), a foreign server (the remote database connection definition), and foreign tables (local objects that map to remote tables). Queries against foreign tables are transparently forwarded to the remote server.

-- Step 1: Install the foreign data wrapper extension
CREATE EXTENSION postgres_fdw;

-- Step 2: Create a foreign server pointing to the remote Postgres database
CREATE SERVER remote_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote-host.internal', port '5432', dbname 'appdb');

-- Step 3: Create a user mapping for authentication
CREATE USER MAPPING FOR current_user
  SERVER remote_db
  OPTIONS (user 'app_user', password 'password');

-- Step 4: Create foreign tables for each remote table you need
CREATE FOREIGN TABLE customers_remote (
  customer_id   BIGINT NOT NULL,
  customer_name TEXT,
  email         TEXT
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'customers');

-- Now query like a local table
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers_remote c ON c.customer_id = o.customer_id
WHERE o.order_date > NOW() - INTERVAL '30 days';

The key architectural difference: Postgres FDW requires explicit foreign table definitions. Oracle database links let you reference any remote table with any_table@dblink without pre-declaring the schema. In Postgres, you need a foreign table definition for each remote table you want to access.

Automating Foreign Table Creation with IMPORT FOREIGN SCHEMA

For migrations with many remote tables, creating foreign tables one by one is impractical. Postgres provides IMPORT FOREIGN SCHEMA to bulk-import table definitions from a remote schema:

-- Create a local schema to hold the foreign table definitions
CREATE SCHEMA remote_schema;

-- Import all tables from the remote 'public' schema
IMPORT FOREIGN SCHEMA public
  FROM SERVER remote_db
  INTO remote_schema;

-- Import only specific tables
IMPORT FOREIGN SCHEMA public
  LIMIT TO (customers, products, product_categories)
  FROM SERVER remote_db
  INTO remote_schema;

-- Verify what was imported
SELECT foreign_table_name, foreign_server_name
FROM information_schema.foreign_tables
WHERE foreign_table_schema = 'remote_schema'
ORDER BY foreign_table_name;

IMPORT FOREIGN SCHEMA introspects the remote catalog and generates the local foreign table DDL automatically. This works well for Postgres-to-Postgres FDW setups. For Oracle-to-Postgres FDW using oracle_fdw, the import behavior is similar but requires the oracle_fdw extension and Oracle client libraries.

Join Pushdown: Where the Performance Gap Lives

Oracle's distributed query optimizer is sophisticated about pushing predicates to remote sites. Postgres FDW pushdown is more limited and depends on the FDW implementation. postgres_fdw supports pushdown of WHERE clauses, ORDER BY, LIMIT, and in Postgres 10+, join pushdown — but only for joins between tables on the same foreign server.

-- Check whether a query uses FDW pushdown
EXPLAIN (VERBOSE, ANALYZE)
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers_remote c ON c.customer_id = o.customer_id
WHERE o.order_date > NOW() - INTERVAL '30 days';

-- Look for "Foreign Scan" nodes with pushed conditions in the output
-- A good pushdown plan shows the WHERE on the foreign table being evaluated remotely
-- A bad plan shows the entire foreign table scanned and joined locally

The worst case for FDW performance is a query that joins a large local table with a large foreign table without a pushed predicate. Postgres fetches the entire foreign table across the network and performs the join locally. Oracle's optimizer is better at recognizing and avoiding this pattern.

For migrations that rely heavily on database link joins across large tables, validate each cross-database query's execution plan after the FDW replacement. Some queries may need to be rewritten as application-layer two-step fetches with explicit filtering.

Write Operations Through FDW

Postgres FDW supports DML (INSERT, UPDATE, DELETE) against foreign tables when the FDW implementation supports it. postgres_fdw does. Enable it explicitly:

-- Allow writes through this foreign table
ALTER FOREIGN TABLE customers_remote OPTIONS (ADD updatable 'true');

-- Or set it at the server level
ALTER SERVER remote_db OPTIONS (ADD updatable 'true');

-- Test a write
UPDATE customers_remote
SET email = 'new@example.com'
WHERE customer_id = 12345;

-- Verify the write was applied remotely
SELECT email FROM customers_remote WHERE customer_id = 12345;

Write operations through FDW are subject to a critical limitation: they are not part of a distributed transaction. If your local transaction commits but the FDW write fails, you have a partial commit. Oracle's distributed transactions use a two-phase commit protocol that prevents this scenario. Postgres FDW has no two-phase commit support.

Distributed Transaction Semantics: The Critical Difference

Oracle database links participate in Oracle's distributed transaction framework. A single transaction can write to both local and remote tables, and Oracle coordinates a two-phase commit across all participants. If any participant fails during commit, Oracle rolls back all participants.

Postgres FDW has no equivalent. Each FDW write is sent to the remote server as an independent operation. If the local transaction rolls back after a committed FDW write, the remote write is not rolled back. This is documented behavior, not a bug.

The practical implication for Oracle database link migrations: any code pattern that writes to both local and remote tables within a single transaction and relies on atomic rollback behavior needs architectural redesign. Common patterns that require this:

• Audit logging via database link (write audit record to remote audit database as part of main transaction)
• Cross-database financial transfers (debit local account, credit remote account in one transaction)
• Two-system data synchronization written as single transactions

The replacement patterns are application-level sagas with compensating transactions, message queue-based eventual consistency, or schema consolidation that eliminates the cross-database writes entirely.

Using oracle_fdw for Hybrid Oracle-Postgres Environments

During staged migrations where some tables are still on Oracle and some have been migrated to Postgres, oracle_fdw lets Postgres query the Oracle source directly:

-- Install oracle_fdw (requires Oracle Instant Client on the Postgres server)
CREATE EXTENSION oracle_fdw;

-- Create a foreign server pointing to Oracle
CREATE SERVER oracle_source
  FOREIGN DATA WRAPPER oracle_fdw
  OPTIONS (dbserver '//oracle-host:1521/ORCLPDB1');

-- Create user mapping
CREATE USER MAPPING FOR app_user
  SERVER oracle_source
  OPTIONS (user 'oracle_app_user', password 'oracle_password');

-- Import the Oracle schema into a local foreign schema
IMPORT FOREIGN SCHEMA "APPSCHEMA"
  FROM SERVER oracle_source
  INTO oracle_tables;

-- Query Oracle data from Postgres during migration
SELECT pg_orders.*, oracle_tables.customers.customer_name
FROM orders pg_orders
JOIN oracle_tables.customers ON oracle_tables.customers.customer_id = pg_orders.customer_id;

This pattern is useful for the cutover window, where you need application code pointing at Postgres to still be able to read data that hasn't been migrated yet. Once migration is complete, drop the foreign schema and foreign server.

Connection Pooling and FDW

Oracle database links maintain persistent connections to remote databases at the session level. Under load, a 200-connection Oracle application might hold 200 simultaneous connections to the remote database. Postgres FDW has the same behavior — each local connection that uses a foreign table opens a connection to the remote server.

Manage this by configuring connection pool limits in the foreign server options:

-- Limit the number of connections FDW opens to the remote server
-- (Postgres 14+)
ALTER SERVER remote_db OPTIONS (ADD keep_connections 'on');

-- For Postgres 16+, use connection_lifetime to expire idle FDW connections
ALTER SERVER remote_db OPTIONS (ADD connect_timeout '10');

On the remote server, ensure pg_hba.conf and max_connections account for FDW connections from all source instances, not just direct application connections.