PostgreSQL Foreign Data Wrappers: Cross-Database Queries Explained

2026-04-09

PostgreSQL Foreign Data Wrappers: Cross-Database Queries Explained

PostgreSQL foreign data wrappers (FDWs) let you query remote databases, files, and external services as if they were local tables. Using CREATE FOREIGN TABLE, you can JOIN data across PostgreSQL instances, Oracle databases, CSV files, and dozens of other sources directly from SQL.

The Problem

Applications often need data from multiple databases. A reporting system might need to join user data in one PostgreSQL instance with order data in another. A migration project might need to read from Oracle while writing to PostgreSQL. A data pipeline might need to query a remote analytics database without moving all the data locally.

The traditional approach is ETL: extract data from the source, transform it, and load it into the local database. This works but introduces latency (the local copy is always stale), complexity (you need a pipeline to manage), and storage costs (you are duplicating data). For many use cases, querying the remote data directly is simpler and more current.

PostgreSQL foreign data wrappers provide this direct access. The postgres_fdw extension -- the most commonly used FDW -- lets you define foreign tables that map to tables on a remote PostgreSQL instance. When you query a foreign table, PostgreSQL connects to the remote server, executes a query, and returns the results as if they came from a local table. The query planner is aware that the data is remote and pushes operations like WHERE clauses, JOINs, sorts, and aggregations to the remote server when possible, minimizing the amount of data transferred.

The problems start when teams treat foreign tables as if they were local. Remote queries have network latency. The planner's cost estimates for remote operations are often inaccurate because it does not have detailed statistics about the remote tables. Pushdown optimization does not always happen -- certain expressions, functions, and data types prevent the planner from sending operations to the remote side, causing it to fetch all rows locally and filter them in memory. Without monitoring, a foreign table query that worked well on a small dataset can become a performance disaster as the remote table grows.

How to Detect It

Check which foreign servers and foreign tables exist in your database, and verify their configuration:

-- List all foreign servers and their options
SELECT
    s.srvname AS server_name,
    f.fdwname AS wrapper_name,
    s.srvoptions AS server_options
FROM pg_foreign_server s
JOIN pg_foreign_data_wrapper f ON s.srvfdw = f.oid;

-- List all foreign tables and their remote mappings
SELECT
    ft.foreign_table_schema AS local_schema,
    ft.foreign_table_name AS local_table,
    ft.foreign_server_name AS server_name,
    fto.option_name,
    fto.option_value
FROM information_schema.foreign_tables ft
LEFT JOIN information_schema.foreign_table_options fto
    ON ft.foreign_table_catalog = fto.foreign_table_catalog
    AND ft.foreign_table_schema = fto.foreign_table_schema
    AND ft.foreign_table_name = fto.foreign_table_name
ORDER BY ft.foreign_table_name, fto.option_name;

Use EXPLAIN VERBOSE to check whether the planner is pushing operations to the remote server:

-- Check if WHERE clause and ORDER BY are pushed down to the remote server
EXPLAIN VERBOSE
SELECT order_id, customer_name, order_total_amount
FROM remote_orders
WHERE order_date >= '2025-01-01'
  AND status = 'completed'
ORDER BY order_total_amount DESC
LIMIT 100;

In the EXPLAIN output, look for Remote SQL in the Foreign Scan node. If you see your WHERE clause and ORDER BY in the remote SQL, pushdown is working. If the remote SQL is just SELECT * FROM orders with no filter, PostgreSQL is fetching all rows and filtering locally -- which is far slower.

Check for foreign tables with stale or missing statistics:

-- Foreign tables without recent ANALYZE
SELECT
    schemaname,
    relname AS foreign_table_name,
    last_analyze,
    last_autoanalyze,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
WHERE relname IN (
    SELECT foreign_table_name
    FROM information_schema.foreign_tables
)
ORDER BY last_analyze NULLS FIRST;

Foreign tables with last_analyze = NULL have no local statistics. The planner will assume a default row estimate (typically 1,000 rows), leading to poor plan choices if the remote table actually has millions of rows.

How myDBA.dev Shows It

myDBA.dev's Queries page captures all queries executed against your database, including those that involve foreign tables. Foreign table queries typically stand out in the query list because they have higher average execution times due to network latency -- they appear alongside local queries sorted by total execution time, making it easy to spot FDW queries that are consuming disproportionate resources.

Queries page showing foreign table queries with elevated execution times and call counts

Clicking on a foreign table query reveals its EXPLAIN plan, where you can verify whether pushdown optimization is working. If the plan shows a Foreign Scan with a simple remote SQL and then a local Filter or Sort node, that is a signal that pushdown failed and the query is fetching too much data from the remote server. The plan view makes this visible without having to run EXPLAIN manually.

The Health Check overview provides a complementary view, surfacing configuration issues that affect FDW performance -- such as missing statistics on foreign tables, excessive open connections to remote servers, or foreign tables that are queried frequently but never analyzed.

Health check overview showing scored assessment across all domains including query performance

How to Fix It

Setting up postgres_fdw

Install the extension and create a foreign server pointing to the remote PostgreSQL instance:

-- Install the postgres_fdw extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create a foreign server
CREATE SERVER remote_analytics
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'analytics-db.example.com',
        port '5432',
        dbname 'analytics',
        fetch_size '10000'    -- rows per fetch batch (default 100, too low)
    );

-- Create user mapping (credentials for the remote connection)
CREATE USER MAPPING FOR current_user
    SERVER remote_analytics
    OPTIONS (user 'readonly_user', password 'secure_password');

The fetch_size option is critical for performance. The default of 100 rows per batch means a query returning 1 million rows makes 10,000 round trips to the remote server. Setting it to 10,000 reduces this to 100 round trips, dramatically improving throughput.

Creating foreign tables

Define foreign tables that map to remote tables:

-- Create a foreign table mapping to the remote orders table
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');

-- Import all tables from a remote schema at once (PG9.5+)
IMPORT FOREIGN SCHEMA public
    LIMIT TO (customers, products, categories)
    FROM SERVER remote_analytics
    INTO foreign_data;

IMPORT FOREIGN SCHEMA saves time when you need many foreign tables -- it automatically creates the local foreign table definitions matching the remote schema.

Ensuring pushdown optimization

The planner pushes operations to the remote server when it can prove the operation is safe to execute remotely. Common reasons pushdown fails:

-- Pushdown works: simple operators and built-in functions
SELECT * FROM remote_orders WHERE order_date >= '2025-01-01';  -- pushed down

-- Pushdown fails: local function in WHERE clause
SELECT * FROM remote_orders WHERE my_custom_function(status) = true;  -- NOT pushed down

-- Fix: use the extension option to declare functions as shippable
-- Or rewrite the query to use expressions the remote server understands

-- Pushdown works for JOINs between foreign tables on the same server
SELECT o.order_id, c.customer_name
FROM remote_orders o
JOIN remote_customers c ON o.customer_id = c.customer_id;  -- JOIN pushed to remote

-- Pushdown fails: JOIN between foreign table and local table
-- The entire foreign table is fetched, then joined locally
SELECT o.order_id, l.label
FROM remote_orders o
JOIN local_labels l ON o.status = l.status_code;  -- remote fetched first, then joined

Updating statistics on foreign tables

Run ANALYZE on foreign tables so the planner makes accurate cost estimates:

-- Analyze a foreign table (connects to remote and samples rows)
ANALYZE remote_orders;

-- Verify statistics were collected
SELECT
    relname,
    reltuples AS estimated_rows,
    relpages AS estimated_pages
FROM pg_class
WHERE relname = 'remote_orders';

Without ANALYZE, the planner uses default estimates and may choose a nested loop join when a hash join would be 100x faster, or vice versa.

Other FDW options

PostgreSQL supports FDWs for many data sources beyond PostgreSQL:

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

-- File FDW for 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');

How to Prevent It

Run ANALYZE on foreign tables regularly

Foreign table statistics are not updated by autovacuum. Schedule regular ANALYZE on all foreign tables, especially before queries that involve joins between foreign and local tables. Without current statistics, the planner makes poor join strategy decisions that can turn a 100ms query into a 10-minute table scan.

Set appropriate fetch_size

The default fetch_size of 100 is appropriate for small result sets but terrible for analytical queries. Set it at the server level for your typical workload, and override it per-table if needed:

-- Set fetch_size at the server level
ALTER SERVER remote_analytics OPTIONS (SET fetch_size '10000');

-- Override per-table for a large fact table
ALTER FOREIGN TABLE remote_events OPTIONS (SET fetch_size '50000');

Monitor query performance over time

Foreign table query performance depends on network latency, remote server load, and remote table sizes -- all of which change over time. A query that took 200ms when the remote table had 100K rows might take 20 seconds when it grows to 10M rows, especially if pushdown is not working. myDBA.dev tracks query execution times continuously, surfacing performance degradations as they happen rather than after users start complaining.

Consider materialized views for frequently accessed remote data

If you query the same remote data repeatedly and can tolerate some staleness, a materialized view eliminates the 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 on a schedule
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_recent_orders;

This gives you local-speed queries while keeping the foreign table available for ad-hoc access to the full remote dataset.

Secure your FDW credentials

User mappings store passwords in plaintext in pg_user_mappings (visible to superusers). Use a dedicated read-only role on the remote server with minimal privileges. Rotate credentials periodically and audit which local users have mappings to which remote servers.

FAQ

What is a PostgreSQL foreign data wrapper?

A foreign data wrapper (FDW) is a PostgreSQL extension that provides access to external data sources through the standard SQL interface. You define foreign tables that map to remote data, then query them with regular SELECT, JOIN, and WHERE clauses. PostgreSQL handles the connection, data retrieval, and where possible, pushes filtering and aggregation to the remote source. The built-in postgres_fdw connects to other PostgreSQL instances, while community extensions provide access to Oracle, MySQL, MongoDB, CSV files, and dozens of other sources.

What is a foreign table in PostgreSQL?

A foreign table is a table definition in your local database that represents data stored elsewhere -- on a remote database server, in a file, or in an external service. It has columns and data types like a regular table, but no local storage. When you query a foreign table, PostgreSQL connects to the data source, retrieves the data, and returns it as if it were local. Foreign tables support SELECT and, with postgres_fdw, also INSERT, UPDATE, and DELETE on the remote data.

How do I use postgres_fdw for cross-database queries?

Install the postgres_fdw extension, create a foreign server with the remote connection details, create a user mapping with credentials, and then either create individual foreign tables or use IMPORT FOREIGN SCHEMA to map multiple tables at once. After setup, query the foreign tables with standard SQL. Run ANALYZE on foreign tables so the planner has accurate statistics, and set fetch_size to at least 1,000 (10,000 for analytical workloads) to avoid excessive round trips.

How do I connect PostgreSQL to Oracle using a foreign data wrapper?

Install the oracle_fdw extension (requires Oracle client libraries on the PostgreSQL server). Create a foreign server with the Oracle connection string, create user mappings with Oracle credentials, and define foreign tables matching the Oracle table schemas. The Oracle FDW supports pushdown of WHERE clauses, ORDER BY, and aggregations. Run ANALYZE on foreign tables to give the PostgreSQL planner accurate row estimates for the Oracle data.