PostgreSQL Foreign Data Wrappers: Cross-Database Queries Explained

Every few months I see a team build an elaborate ETL pipeline to sync data between two PostgreSQL instances -- scheduled jobs, temporary staging tables, conflict resolution logic, monitoring for sync failures. Then someone mentions that PostgreSQL can query remote databases directly with a single SQL statement, and the room goes quiet.

Foreign data wrappers (FDWs) have been in PostgreSQL since version 9.1, but they remain one of its most underutilized features. Let's fix that.

What FDWs Actually Do

A foreign data wrapper lets you define tables in your local database that represent data stored somewhere else -- a remote PostgreSQL instance, an Oracle database, a CSV file, or dozens of other sources. When you query a foreign table, PostgreSQL connects to the remote source, retrieves the data, and returns it as if it were local. You can use standard SELECT, JOIN, WHERE, and (with postgres_fdw) even INSERT, UPDATE, and DELETE.

The query planner is aware that the data is remote and will push operations to the remote server when possible -- WHERE clauses, JOINs between foreign tables, sorts, and aggregations. This means a well-configured FDW query sends filters to the remote side and only transfers the matching rows, not the entire table.

Full Setup Walkthrough

1. Install the extension and create a server

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER remote_analytics
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'analytics-db.example.com',
        port '5432',
        dbname 'analytics',
        fetch_size '10000'
    );

That fetch_size is critical. The default is 100 rows per batch. A query returning 1M rows at fetch_size=100 makes 10,000 round trips. At 10,000, it makes 100 round trips. This is often the single biggest performance lever for FDW queries.

2. Create user mapping

CREATE USER MAPPING FOR current_user
    SERVER remote_analytics
    OPTIONS (user 'readonly_user', password 'secure_password');

3. Create foreign tables

Individually:

CREATE FOREIGN TABLE remote_orders (
    order_id BIGINT,
    customer_id BIGINT,
    customer_name TEXT,
    order_date DATE,
    order_total_amount NUMERIC(12, 2),
    status TEXT
)
SERVER remote_analytics
OPTIONS (schema_name 'public', table_name 'orders');

Or import an entire schema at once:

IMPORT FOREIGN SCHEMA public
    LIMIT TO (customers, products, categories)
    FROM SERVER remote_analytics
    INTO foreign_data;

Why Your FDW Queries Are Slow

Pushdown failures

The planner pushes operations to the remote server when it can prove they're safe to execute remotely. When pushdown fails, PostgreSQL silently fetches the entire table and filters locally. Common causes:

Custom functions in WHERE clauses:

-- This gets pushed down (built-in operator)
SELECT * FROM remote_orders WHERE order_date >= '2025-01-01';

-- This does NOT (custom function)
SELECT * FROM remote_orders WHERE my_local_function(status) = true;

JOINs between foreign and local tables:

-- Pushed down (both tables on same foreign server)
SELECT o.*, c.customer_name
FROM remote_orders o
JOIN remote_customers c ON o.customer_id = c.customer_id;

-- NOT pushed down (foreign + local)
SELECT o.*, l.label
FROM remote_orders o
JOIN local_labels l ON o.status = l.status_code;

Always verify with EXPLAIN VERBOSE and look for Remote SQL in the Foreign Scan node. If the remote SQL is just SELECT * FROM table with no filters, pushdown failed.

Missing statistics

Autovacuum does not analyze foreign tables. Without statistics, the planner assumes ~1,000 rows for every foreign table. If the remote table has 10 million rows, the planner chooses a nested loop join when a hash join would be 100x faster.

-- You must run this manually and on a schedule
ANALYZE remote_orders;

-- Verify
SELECT relname, reltuples AS estimated_rows
FROM pg_class WHERE relname = 'remote_orders';

Auditing Your FDW Configuration

-- All foreign servers and their settings
SELECT s.srvname, f.fdwname, s.srvoptions
FROM pg_foreign_server s
JOIN pg_foreign_data_wrapper f ON s.srvfdw = f.oid;

-- All foreign tables with server mappings
SELECT
    ft.foreign_table_schema,
    ft.foreign_table_name,
    ft.foreign_server_name
FROM information_schema.foreign_tables ft;

-- Foreign tables missing ANALYZE
SELECT relname, last_analyze, n_live_tup
FROM pg_stat_user_tables
WHERE relname IN (
    SELECT foreign_table_name FROM information_schema.foreign_tables
)
ORDER BY last_analyze NULLS FIRST;

Beyond PostgreSQL

The FDW framework supports many data sources:

-- Oracle
CREATE SERVER oracle_prod
    FOREIGN DATA WRAPPER oracle_fdw
    OPTIONS (dbserver '//oracle-host:1521/ORCL');

-- CSV files
CREATE EXTENSION file_fdw;
CREATE SERVER csv_files FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE import_data (
    id INTEGER, name TEXT, value NUMERIC
) SERVER csv_files
OPTIONS (filename '/data/import.csv', format 'csv', header 'true');

When to Use Materialized Views Instead

If you query the same remote data repeatedly, a materialized view eliminates per-query network overhead:

CREATE MATERIALIZED VIEW mv_recent_orders AS
SELECT order_id, customer_name, order_total_amount, order_date
FROM remote_orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days';

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_recent_orders;

Local-speed reads with periodic refreshes. The foreign table stays available for ad-hoc access to the complete remote dataset.

Optimization Checklist

1. Set fetch_size to 10,000+ -- the default of 100 is almost never appropriate
2. Run ANALYZE on all foreign tables -- schedule it, autovacuum won't do it
3. Verify pushdown with EXPLAIN VERBOSE -- check Remote SQL for your filters
4. Use materialized views for frequently accessed remote data
5. Tune per-table fetch_size for large fact tables (50,000+)
6. Secure credentials -- use read-only roles, rotate passwords, audit user mappings

FDW vs ETL: The Decision

FDWs are better when you need real-time access, want to avoid pipeline maintenance, and the remote dataset is manageable in size. ETL is better when you need sub-millisecond latency, query the same data thousands of times daily, or need data transformation. They're not mutually exclusive -- use foreign tables for development, materialized views or ETL for production hot paths.