Foreign Data Wrappers

Foreign Data Wrappers (FDWs) implement the SQL/MED standard, letting PostgreSQL query data that lives outside the current database — a different PostgreSQL server, a different database engine entirely, or even a flat file — as though it were an ordinary local table.

postgres_fdw and Others

postgres_fdw is the most commonly used FDW, letting one PostgreSQL server query tables on another PostgreSQL server. It's maintained as part of core PostgreSQL. Beyond it, the broader FDW ecosystem covers a wide range of sources — MySQL, MongoDB, CSV and other flat files, and many more, each implemented as its own extension.

Basic Setup

postgres-fdw-setup.sql

SQL

-- 1. Enable the extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- 2. Describe the remote server to connect to
CREATE SERVER remote_analytics
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'analytics-db.internal', port '5432', dbname 'analytics');

-- 3. Map local credentials to remote credentials
CREATE USER MAPPING FOR CURRENT_USER
  SERVER remote_analytics
  OPTIONS (user 'reporting_user', password 'secret');

-- 4. Declare a foreign table that mirrors a table on the remote server
CREATE FOREIGN TABLE remote_events (
  id BIGINT,
  event_type TEXT,
  occurred_at TIMESTAMPTZ
)
  SERVER remote_analytics
  OPTIONS (schema_name 'public', table_name 'events');

-- Now query it exactly like a local table
SELECT event_type, count(*)
FROM remote_events
WHERE occurred_at > now() - interval '7 days'
GROUP BY event_type;

Use Cases

Federating data across multiple databases — joining local tables against another team's database without building and maintaining a separate ETL pipeline just to copy the data over.
Gradually migrating off a legacy database — stand up the new PostgreSQL database, use an FDW to reach back into the legacy system for data that hasn't been migrated yet, and cut over table by table.
Reporting/analytics queries that need to combine operational data with data that intentionally lives on a separate, isolated server.

Performance depends on pushdown

How fast an FDW query runs depends heavily on how much work PostgreSQL can "push down" to the remote source — filtering and even aggregation can sometimes be executed remotely so only the relevant rows cross the network, but not every operation supports this, and unsupported ones fall back to pulling all the raw rows over before processing them locally. FDWs are genuinely useful for the integration scenarios above, but they are not a wholesale substitute for proper data integration or a data warehouse when you need heavy, repeated cross-source joins at scale.