SQL 124 🛢️ Foreign Data Wrappers (FDW) and Federated Queries
A Foreign Data Wrapper is PostgreSQL’s implementation of the SQL/MED standard: a library that lets the database treat data residing outside PostgreSQL as if it were a local table . The wrapper handles the protocol for connecting to the external source, fetching rows, and returning them to the PostgreSQL executor. The postgres_fdw extension is the bundled wrapper for connecting to other PostgreSQL servers, and it is the one most commonly used for federated queries—SQL that joins tables from multiple databases in a single statement .
This chapter covers the architecture of FDW (foreign data wrapper, foreign server, user mapping, foreign table), the setup of postgres_fdw for cross-database queries, the push-down mechanism that determines whether work happens on the remote server or locally, and the limitations that make federated queries slower than local queries when push-down is incomplete.
Key point: A foreign table has no storage in the local database. Every query against it is translated into a remote SQL statement, sent to the foreign server, and the results are fetched back. The performance of a federated query depends almost entirely on how much of the query the wrapper can push down to the remote server, and on how much data crosses the network.
Why FDWs exist
The data silo problem. An organization accumulates databases over time. The billing system has its own PostgreSQL instance, the analytics team has another, and the legacy ERP runs on a third. Joining data across these systems traditionally requires ETL: extract from each source, load into a warehouse, and query the result. FDWs offer an alternative for cases where the join is needed occasionally or the data is too volatile for a batch pipeline. The federated query reads from each source at query time, so the result reflects the current state of each database .
The protocol abstraction problem. Different external sources speak different protocols. A CSV file has no protocol at all; a REST API speaks HTTP; a MySQL server speaks the MySQL wire protocol. The FDW interface abstracts this: the core server calls the wrapper’s functions, and the wrapper handles the details of fetching data. The file_fdw wrapper reads files using the same COPY FROM machinery as a local import . The postgres_fdw wrapper speaks PostgreSQL’s own wire protocol to a remote server . The SQL layer above does not need to know which protocol is in use.
The push-down problem. A naive FDW implementation would fetch the entire remote table and let the local executor do the filtering, joining, and aggregation. For a small table, this is acceptable. For a large table, it is unusable. The push-down mechanism exists to avoid this: the wrapper analyzes the query, determines which parts can be executed on the remote server, and sends a remote SQL statement that includes the filters and projections. The local executor then processes only the reduced result set . The efficiency of a federated query is the efficiency of its push-down.
a. The FDW Object Hierarchy
Setting up a foreign data source requires four objects, created in order. The foreign data wrapper is the library that implements the protocol. The foreign server defines how to connect to a specific external source—host, port, database name. The user mapping defines which credentials to use when connecting on behalf of a local role. The foreign table defines the columns and the remote table name .
-- 1. The extension provides the wrapper
CREATE EXTENSION postgres_fdw;
-- 2. The foreign server defines the connection
CREATE SERVER remote_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote.example.com', port '5432', dbname 'analytics');
-- 3. The user mapping defines the credentials
CREATE USER MAPPING FOR local_user
SERVER remote_db
OPTIONS (user 'remote_user', password 'secret');
-- 4. The foreign table defines the columns and remote name
CREATE FOREIGN TABLE remote_orders (
id bigint,
customer_id bigint,
total numeric,
created_at timestamptz
)
SERVER remote_db
OPTIONS (schema_name 'public', table_name 'orders');The foreign table definition must match the remote table’s structure. The column names must match unless a column_name option maps them individually. The data types must be compatible; a mismatch produces errors at query time rather than at creation time . For a schema with many tables, IMPORT FOREIGN SCHEMA creates the foreign tables automatically from the remote catalog, which is faster and less error-prone than writing the definitions by hand .
-- Import all tables from the remote schema
IMPORT FOREIGN SCHEMA public
FROM SERVER remote_db
INTO public;The IMPORT FOREIGN SCHEMA command queries the remote server’s information schema and creates a foreign table for each matching table. The LIMIT TO and EXCEPT clauses restrict which tables are imported. The server-level options import_collate, import_default, import_generated, and import_not_null control whether those properties are included in the imported definitions .
b. Push-Down and Remote SQL
The performance of a federated query depends on what the wrapper can push down. The EXPLAIN (VERBOSE) output shows the remote SQL that the wrapper sends, which is the most direct way to see whether a filter or join was pushed or left local.
EXPLAIN (VERBOSE, COSTS OFF)
SELECT customer_id, sum(total)
FROM remote_orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id;If the filter and aggregation are pushed down, the Remote SQL line shows a statement like SELECT customer_id, sum(total) FROM public.orders WHERE created_at >= $1 GROUP BY customer_id. If they are not, the remote SQL shows a full table scan and the local plan shows the filter and aggregation above the foreign scan .
The push-down matrix is version-dependent, but the general categories are consistent. WHERE predicates are pushed when they use immutable, foreign-safe functions. Column projection is always pushed—only the requested columns are fetched. LIMIT, ORDER BY, inner joins, outer joins, and aggregates are pushed when both sides of the join use the same foreign server and the operations use built-in types and functions. UPDATE and DELETE are pushed as remote statements since PostgreSQL 10 .
The limitation that matters most for federated queries is the same-server constraint. A join between two foreign tables is pushed down only when both tables are on the same foreign server. A join between a foreign table and a local table is never pushed: the local table is scanned locally, and the foreign table is either fetched in full or filtered by the predicates that can be pushed independently. A join between two foreign servers is not pushed as a single remote join; each server is queried separately, and the join happens locally .
c. Transaction Management and Connection Behavior
A federated query that spans multiple foreign servers opens a transaction on each server. The local transaction commits or aborts, and the wrapper commits or aborts the corresponding remote transactions. The remote transaction isolation level matches the local transaction’s level: SERIALIZABLE locally produces SERIALIZABLE remotely, and REPEATABLE READ is used otherwise. This ensures that multiple scans against the same remote table within a single local transaction see a consistent snapshot .
Connections to foreign servers are kept open by default and reused across queries in the same session. The keep_connections server option controls this. When set to off, connections are discarded at the end of each transaction, which is useful when the remote server has a connection limit and the local session would otherwise hold connections it does not need . The postgres_fdw_disconnect() and postgres_fdw_disconnect_all() functions close connections explicitly.
-- Check open connections to foreign servers
SELECT * FROM postgres_fdw_get_connections();
-- Close a specific connection
SELECT postgres_fdw_disconnect('remote_db');A connection is established during the first query that uses a foreign table associated with the server. If multiple user mappings exist for the same server, a separate connection is established for each mapping . The credentials are stored in pg_user_mapping, and they are visible to superusers and to the mapped role. The pg_user_mappings view shows the options but obscures the password for non-superusers.
Complete Example Session
-- ============================================
-- PART 1: ENABLE THE EXTENSION
-- ============================================
CREATE EXTENSION postgres_fdw;
-- ============================================
-- PART 2: CREATE THE FOREIGN SERVER
-- ============================================
CREATE SERVER analytics_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'analytics.example.com', port '5432', dbname 'analytics');
-- ============================================
-- PART 3: CREATE THE USER MAPPING
-- ============================================
CREATE USER MAPPING FOR current_user
SERVER analytics_db
OPTIONS (user 'reader', password 'secret');
-- ============================================
-- PART 4: IMPORT THE SCHEMA
-- ============================================
-- Creates foreign tables for all tables in the remote public schema
IMPORT FOREIGN SCHEMA public
FROM SERVER analytics_db
INTO public;
-- ============================================
-- PART 5: QUERY THE FOREIGN TABLE
-- ============================================
SELECT * FROM orders WHERE created_at >= '2026-01-01';
-- ============================================
-- PART 6: JOIN LOCAL AND FOREIGN DATA
-- ============================================
-- The join happens locally; the foreign table is filtered remotely
SELECT c.name, o.total
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.total > 100;
-- ============================================
-- PART 7: CHECK PUSH-DOWN WITH EXPLAIN
-- ============================================
EXPLAIN (VERBOSE, COSTS OFF)
SELECT customer_id, sum(total)
FROM orders
GROUP BY customer_id;
-- Look for the Remote SQL line in the output
-- ============================================
-- PART 8: JOIN TWO FOREIGN TABLES ON THE SAME SERVER
-- ============================================
-- Both tables are on analytics_db; the join is pushed down
SELECT o.id, p.name
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.created_at >= '2026-01-01';
-- ============================================
-- PART 9: MONITOR CONNECTIONS
-- ============================================
SELECT * FROM postgres_fdw_get_connections();
-- ============================================
-- PART 10: CLEAN UP
-- ============================================
DROP FOREIGN TABLE orders;
DROP USER MAPPING FOR current_user SERVER analytics_db;
DROP SERVER analytics_db;
DROP EXTENSION postgres_fdw;The ten parts trace the setup from the extension through the server, mapping, import, and query, then show the push-down verification and connection monitoring. The critical step for performance is Part 7: if the Remote SQL line includes the GROUP BY, the aggregation happened remotely; if it does not, the aggregation happened locally after fetching the full result set.
Quick Reference
FDW Object Hierarchy
| Object | Purpose | Created With |
|---|---|---|
| Foreign data wrapper | Protocol implementation | CREATE EXTENSION or CREATE FOREIGN DATA WRAPPER |
| Foreign server | Connection definition | CREATE SERVER |
| User mapping | Credentials for a local role | CREATE USER MAPPING |
| Foreign table | Column definitions and remote name | CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA |
postgres_fdw Push-Down Matrix
| Operation | Pushed | Condition |
|---|---|---|
| WHERE predicates | Yes | Immutable, foreign-safe functions |
| Column projection | Yes | Always |
| LIMIT | Yes | No local ordering ambiguity |
| ORDER BY | Yes | Remote can return sorted |
| Inner/outer JOIN | Yes | Both tables on same foreign server |
| Aggregates (SUM, COUNT) | Yes | Same server, immutable filter |
| UPDATE/DELETE | Yes | Since PostgreSQL 10 |
Connection Management
| Function | Purpose |
|---|---|
postgres_fdw_get_connections() | List open connections |
postgres_fdw_disconnect(server) | Close a specific connection |
postgres_fdw_disconnect_all() | Close all connections |
Key Server Options
| Option | Default | Effect |
|---|---|---|
keep_connections | on | Reuse connections across transactions |
updatable | true | Allow INSERT/UPDATE/DELETE |
use_remote_estimate | false | Use remote EXPLAIN for planning |
Best Practices
✅ Do This:
-- Use IMPORT FOREIGN SCHEMA instead of manual definitions
IMPORT FOREIGN SCHEMA public FROM SERVER remote INTO public; -- ✅-- Verify push-down with EXPLAIN (VERBOSE)
EXPLAIN (VERBOSE, COSTS OFF) SELECT ... FROM foreign_table; -- ✅-- Set keep_connections off when remote connections are scarce
ALTER SERVER remote OPTIONS (SET keep_connections 'off'); -- ✅-- Use column_name option when local and remote names differ
CREATE FOREIGN TABLE t (id int OPTIONS (column_name 'remote_id')) ... -- ✅❌ Don’t Do This:
-- Expect a join between local and foreign tables to be pushed
-- The join always happens locally -- ❌-- Assume a foreign table has constraints enforced locally
-- PRIMARY KEY and FOREIGN KEY are declarations only -- ❌-- Join foreign tables on different servers expecting push-down
-- The join happens locally across two remote scans -- ❌-- Forget that credentials are stored in pg_user_mapping
-- Visible to superusers and the mapped role -- ❌Common Pitfalls
| Pitfall | Why It Happens | Fix |
|---|---|---|
| Query is slow despite filter | Predicate not pushed down | Check Remote SQL in EXPLAIN |
| Join between foreign tables is slow | Tables on different servers | Co-locate or accept local join |
| Foreign table definition mismatch | Column types or names differ | Use IMPORT FOREIGN SCHEMA |
| Connection limit exceeded | keep_connections on, many sessions | Set keep_connections off |
| UPDATE fails | Remote table not updatable | Check updatable server option |
Real-World Examples
1. Create Extension
CREATE EXTENSION postgres_fdw;2. Create Foreign Server
CREATE SERVER remote FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote', dbname 'analytics');3. Create User Mapping
CREATE USER MAPPING FOR current_user SERVER remote
OPTIONS (user 'reader', password 'secret');4. Import Schema
IMPORT FOREIGN SCHEMA public FROM SERVER remote INTO public;5. Create Foreign Table Manually
CREATE FOREIGN TABLE orders (id bigint, total numeric)
SERVER remote OPTIONS (schema_name 'public', table_name 'orders');6. Verify Push-Down
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM orders WHERE total > 100;7. Check Connections
SELECT * FROM postgres_fdw_get_connections();8. Disconnect
SELECT postgres_fdw_disconnect('remote');9. Disable Connection Reuse
ALTER SERVER remote OPTIONS (SET keep_connections 'off');10. Join Foreign and Local
SELECT l.name, f.total FROM local_customers l JOIN remote_orders f ON l.id = f.customer_id;Visual
The FDW Object Hierarchy
┌──────────────────────────────────────────────────────────────┐
│ FOREIGN DATA WRAPPER (postgres_fdw) │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ The library that implements the protocol ││
│ └─────────────────────────────────────────────────────────┘│
│ │ │
│ ▼ │
│ FOREIGN SERVER (analytics_db) │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ host, port, dbname ││
│ └─────────────────────────────────────────────────────────┘│
│ │ │
│ ├─────────────────────────────────┐ │
│ ▼ ▼ │
│ USER MAPPING (local → remote) FOREIGN TABLE (orders) │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ local_user │ │ id, total, ... │ │
│ │ → remote_user │ │ remote: public. │ │
│ │ │ │ orders │ │
│ └─────────────────┘ └─────────────────┘ │
│ │
│ The foreign table looks like a local table but has no │
│ local storage. Queries are sent to the foreign server. │
│ │
└──────────────────────────────────────────────────────────────┘Push-Down Decision
┌──────────────────────────────────────────────────────────────┐
│ QUERY: SELECT * FROM foreign_orders WHERE total > 100 │
│ │
│ ┌─────────────────┐ │
│ │ Can the filter │────► YES: Remote SQL includes WHERE │
│ │ be pushed? │ Only matching rows cross network │
│ └────────┬────────┘ │
│ │ NO │
│ ▼ │
│ ┌─────────────────┐ │
│ │ Remote SQL is │ Full table crosses network │
│ │ SELECT * FROM │ Local executor filters │
│ │ orders (no WHERE)│ │
│ └─────────────────┘ │
│ │
│ EXPLAIN (VERBOSE) shows the Remote SQL line. │
│ If the filter is there, it was pushed. │
│ │
└──────────────────────────────────────────────────────────────┘Same-Server Join Constraint
┌──────────────────────────────────────────────────────────────┐
│ JOIN PUSH-DOWN │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ orders │ │ products │ │
│ │ (server A) │ │ (server A) │ │
│ └────────┬────────┘ └────────┬────────┘ │
│ │ │ │
│ └──────────┬───────────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ JOIN pushed │ Same server │
│ │ to server A │ │
│ └─────────────────┘ │
│ │
│ ┌─────────────────┐ ┌─────────────────┐ │
│ │ orders │ │ products │ │
│ │ (server A) │ │ (server B) │ │
│ └────────┬────────┘ └────────┬────────┘ │
│ │ │ │
│ └──────────┬───────────────┘ │
│ ▼ │
│ ┌─────────────────┐ │
│ │ JOIN happens │ Different servers │
│ │ locally │ │
│ └─────────────────┘ │
│ │
│ The wrapper cannot push a join across two foreign servers. │
│ │
└──────────────────────────────────────────────────────────────┘Transaction Propagation
┌──────────────────────────────────────────────────────────────┐
│ LOCAL TRANSACTION │
│ BEGIN; │
│ SELECT * FROM remote_orders; │
│ SELECT * FROM remote_customers; │
│ COMMIT; │
│ │
│ ┌─────────────────────────────────────────────────────────┐│
│ │ REMOTE SERVER ││
│ │ ││
│ │ BEGIN; ← opened on first query ││
│ │ SELECT ... ││
│ │ SELECT ... ││
│ │ COMMIT; ← committed when local commits ││
│ │ ││
│ │ Isolation matches local: SERIALIZABLE or REPEATABLE ││
│ └─────────────────────────────────────────────────────────┘│
│ │
│ The remote transaction is committed or aborted with the │
│ local transaction. Savepoints are mirrored remotely. │
│ │
└──────────────────────────────────────────────────────────────┘Summary
| Item | Value |
|---|---|
| FDW | Library implementing a protocol for external data |
| postgres_fdw | Bundled wrapper for PostgreSQL-to-PostgreSQL |
| Foreign server | Connection definition (host, port, dbname) |
| User mapping | Credentials for a local role |
| Foreign table | Column definitions and remote table name |
| Import schema | IMPORT FOREIGN SCHEMA creates tables from remote catalog |
| Push-down | Remote execution of filters, joins, aggregates |
| Same-server join | Only pushed when both tables on same foreign server |
| Connection reuse | keep_connections server option |
| Transaction propagation | Remote transaction mirrors local isolation |
Key takeaways:
- A foreign table is a view into another database, not a copy. The local database stores only the definition, not the data. Every query against the foreign table sends a remote SQL statement and fetches the result .
- Push-down determines performance. Filters, projections, joins, and aggregates are pushed to the remote server when the wrapper can translate them. When they cannot be pushed, the full result set crosses the network and the local executor does the work.
EXPLAIN (VERBOSE)shows theRemote SQLline, which is the ground truth for what was pushed . - Joins across foreign servers are not pushed. A join between two foreign tables is pushed only when both tables are on the same foreign server. A join between a foreign table and a local table always happens locally. This is the most common performance surprise in federated queries .
IMPORT FOREIGN SCHEMAis faster than manual definitions. It queries the remote catalog and creates the foreign tables with the correct columns and types. Manual definitions are error-prone because a type mismatch is not caught at creation time .- Connections are reused by default.
keep_connectionscontrols whether the wrapper holds connections open across transactions. When the remote server has a connection limit, setting it tooffreleases connections after each transaction .
Remember: Foreign Data Wrappers are PostgreSQL’s answer to the federated query problem. They let a single SQL statement span multiple databases without ETL, but they do not make the network disappear. The cost of a federated query is the cost of the remote SQL that the wrapper sends plus the cost of the rows that cross the network. When push-down is complete, the cost is close to the remote query’s cost. When push-down is incomplete—a local join, a non-pushable function, a cross-server join—the cost includes fetching data that the local executor will discard. The EXPLAIN (VERBOSE) output is the tool that reveals which case applies. For occasional cross-database queries and for reading from external sources that do not warrant their own ETL pipeline, FDWs are the correct tool. For frequent joins at scale, the network cost of federation is usually higher than the cost of loading the data into a warehouse.
Stop using slow, ad-bloated tool sites! 🤮
🔎 Search “KandZ Tools” on Google to use many professional utilities for free.
KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)
⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free
🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!