(opens in a new tab)

Postgres Foreign Data Wrapper (postgres_fdw) Guide

August 24, 2026 by Chat2DB Team

Every so often a query needs data that lives in a different database entirely: a legacy order system, a partner's read replica, a reporting warehouse that nobody wants to touch with a full ETL job for one ad hoc join. PostgreSQL's answer to this is the Foreign Data Wrapper (FDW) mechanism, and postgres_fdw is the specific wrapper for talking to another PostgreSQL server. This guide covers what an FDW actually is, how to wire one up end to end, how to prove that filters are being pushed down to the remote side instead of dragging whole tables across the network, and the real limitations you should plan around before you rely on it in production.

What a Foreign Data Wrapper actually is

The SQL standard defines "SQL Management of External Data" (SQL/MED) as a way to expose data that lives outside the database as if it were an ordinary table. PostgreSQL implements this through foreign data wrappers: a wrapper is a small handler that knows how to talk to a specific external system, translate PostgreSQL's query requirements into that system's native protocol, and hand rows back in a form the planner and executor can use.

postgres_fdw is the wrapper PostgreSQL ships for the case where the "external system" is another PostgreSQL database. It connects over libpq, the same client protocol your regular psql session uses, and it is unusually capable compared to most other wrappers because both ends speak the same SQL dialect. That symmetry is what makes predicate and join pushdown practical: the local planner can construct a valid remote SQL statement and send the actual filtering, sorting, and even joining work to the other server instead of pulling everything back first.

Other wrappers exist for other jobs. file_fdw, which ships in the same contrib module, reads flat files (CSV, for instance) as foreign tables — useful for one-off imports, but with none of the pushdown intelligence since a flat file has no query engine behind it. Third-party wrappers such as mysql_fdw or oracle_fdw bridge to other database engines, translating as much of the SQL as each remote system's dialect allows. None of them will be as complete as postgres_fdw when both sides are PostgreSQL, which is why this guide focuses on that one wrapper rather than trying to generalize across all of them.

Setting up postgres_fdw step by step

Getting from zero to a queryable foreign table is three DDL statements, run once per remote database you want to connect to.

Enable the extension

postgres_fdw ships as a contrib extension, so it has to be enabled per database before anything else works:

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

Define the remote server

A SERVER object records how to reach the remote host — hostname, port, and database name — without embedding any credentials:

CREATE SERVER sales_remote
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'db.internal.example.com', port '5432', dbname 'sales');

You can define as many servers as you have remote databases; each one is an independent connection target that later objects reference by name.

Map local roles to remote credentials

Credentials are kept separate from the server definition through a USER MAPPING, which associates a local role with the username and password it should present when connecting through that server:

CREATE USER MAPPING FOR current_user
  SERVER sales_remote
  OPTIONS (user 'app_reader', password 'a_strong_password');

You can create a mapping FOR PUBLIC if every local role should connect with the same remote identity, or one mapping per role if you want to preserve per-user auditing on the remote side. Typing all three of these statements out by hand for every new integration gets tedious fast, especially once you add TLS options or multiple mappings; the Postgres FDW Setup Generator (opens in a new tab) will produce the CREATE SERVER, CREATE USER MAPPING, and IMPORT FOREIGN SCHEMA boilerplate for you from a short form, which is a faster starting point than copying old scripts around.

Defining foreign tables

With the server and mapping in place, PostgreSQL still doesn't know what tables exist on the other end — that's the job of foreign tables, and there are two ways to create them.

Bulk import with IMPORT FOREIGN SCHEMA

The fastest path, and the one worth reaching for first, is to import an entire remote schema at once. PostgreSQL connects, inspects the remote catalog, and creates matching foreign table definitions locally:

CREATE SCHEMA IF NOT EXISTS local_sales;
 
IMPORT FOREIGN SCHEMA public
  FROM SERVER sales_remote
  INTO local_sales;

Every table in the remote public schema now has a corresponding foreign table under local_sales with the same column names and types. You can narrow this with LIMIT TO (orders, customers) or exclude specific tables with EXCEPT (audit_log) if you only want part of the schema mirrored.

Manual CREATE FOREIGN TABLE

Sometimes you don't want the whole table, or you want to rename or retype specific columns — maybe the remote side stores a status as text and you'd rather see it as an enum, or you only care about four columns out of thirty. In that case, define the foreign table by hand:

CREATE FOREIGN TABLE local_sales.orders (
  id          integer,
  customer_id integer,
  total       numeric(10,2),
  placed_at   timestamptz
)
  SERVER sales_remote
  OPTIONS (schema_name 'public', table_name 'orders');

The column list you provide must be compatible with what actually exists on the remote table — PostgreSQL doesn't validate this at creation time, so a mismatch surfaces as a runtime error the first time you query it.

Querying and proving pushdown

Once the foreign tables exist, they behave like ordinary tables in SELECT, JOIN, aggregates, and views — there's no special syntax to query them. The interesting part is confirming that PostgreSQL is actually doing the filtering remotely instead of shipping the entire table over and filtering locally, which is the whole point of using an FDW instead of, say, dblink.

EXPLAIN VERBOSE shows this directly, because postgres_fdw reports the exact SQL statement it sends to the remote server:

EXPLAIN VERBOSE
SELECT id, customer_id, total
FROM   local_sales.orders
WHERE  total > 100;
 
--                                QUERY PLAN
-- ---------------------------------------------------------------------
--  Foreign Scan on local_sales.orders  (cost=100.00..146.73 rows=13 width=20)
--    Output: id, customer_id, total
--    Remote SQL: SELECT id, customer_id, total FROM public.orders WHERE ((total > 100::numeric))

The Remote SQL: line is the proof. The WHERE total > 100 clause was translated and shipped as part of the query executed on the remote server, so only matching rows travel across the network — this is predicate pushdown. Joins between two foreign tables on the same server can push down too, appearing as a single Foreign Scan with a joined Remote SQL statement instead of two separate scans stitched together locally, as long as both tables share the same server and the join condition is something the remote planner can execute.

Limitations worth knowing before you rely on this

An imported or manually created foreign table only carries column names and types across — nothing else about the remote table's structure comes with it. Primary keys, foreign keys, unique constraints, check constraints, indexes, and triggers on the remote table are invisible locally; PostgreSQL has no way to enforce or even see them through the wrapper. If you need referential integrity on the local side, you have to declare it yourself, understanding that PostgreSQL can't actually verify it against the remote data.

The planner also starts out blind to how much data a foreign table holds, because a foreign table has no local statistics by default. Run ANALYZE on it after creation, exactly as you would on a regular table:

ANALYZE local_sales.orders;

This samples rows over the connection and records cardinality and distribution estimates locally, which meaningfully improves plan choices for joins involving the foreign table. Without it, the planner tends to fall back on rough defaults and can pick worse join orders.

Finally, not every clause pushes down. Pushdown depends on the remote side being able to execute the exact same operator with the exact same behavior — a WHERE clause using a custom function, a collation that differs between the two databases, or a data type without a matching remote definition will all cause PostgreSQL to fetch rows and apply the filter locally instead. EXPLAIN VERBOSE is the way to check this on any query you're relying on for performance; if you don't see the relevant condition inside Remote SQL:, it isn't pushing down.

Tuning the cost model

postgres_fdw estimates the cost of a foreign scan the same way it estimates a local one, but it has no visibility into the remote planner unless you explicitly ask for it. Three options, settable on CREATE SERVER or ALTER SERVER, control this:

ALTER SERVER sales_remote
  OPTIONS (ADD use_remote_estimate 'true');
 
ALTER SERVER sales_remote
  OPTIONS (ADD fetch_size '500');

use_remote_estimate tells PostgreSQL to run an EXPLAIN against the remote server before choosing a local plan, which produces far more accurate row-count estimates at the cost of an extra round trip during planning — worthwhile for complex joins, unnecessary overhead for simple lookups. fetch_size controls how many rows are pulled per network round trip when streaming results back (the default is 100); raising it reduces round-trip overhead for large result sets at the cost of more memory per fetch. Two related options, fdw_startup_cost and fdw_tuple_cost, let you tell the planner how expensive it is just to open a connection and transfer each row, which matters when you're comparing a foreign scan against a plan that avoids the remote call altogether — a high-latency link should carry a higher fdw_startup_cost so the planner doesn't underestimate the penalty of reaching for it.

Writable foreign tables and the transaction caveat

Since PostgreSQL 9.3, foreign tables backed by postgres_fdw are writable — INSERT, UPDATE, and DELETE all work against them, and PostgreSQL translates them into the equivalent statement on the remote side:

INSERT INTO local_sales.orders (id, customer_id, total, placed_at)
VALUES (10042, 88, 249.00, now());

The caveat that matters here is transactional: a local transaction that writes to a foreign table and also writes to local tables is not atomic across both in the way a single-database transaction is. PostgreSQL does not perform two-phase commit against the remote server by default, so if the connection drops or the server crashes between the remote commit and the local commit, you can end up with the write applied on one side and not the other. Treat postgres_fdw writes as "best effort, same as a network call from application code," not as a substitute for a real distributed transaction coordinator, and design any process that writes cross-database through an FDW so that it can tolerate or detect that partial-failure case — idempotent writes, a reconciliation job, or simply avoiding write-heavy patterns through the FDW in the first place.

Where this actually earns its keep

postgres_fdw is at its best in a handful of specific situations. Incremental migration off an older database is one: point foreign tables at the legacy system, migrate consumers to query through them, and cut over table by table without one big-bang switchover. Ad hoc cross-database reporting and joins are another — pulling a handful of reference tables from another service's database into a join, without standing up a pipeline for something you might run twice. A lightweight form of read sharding also works reasonably well for read-heavy lookup tables that live on another node, since the query planner handles the remote call the same way it handles a local index scan.

Where it stops being the right tool is sustained, high-volume write traffic across databases, or any case where you need the two sides to stay in lockstep with strict consistency guarantees. At that point, logical replication gives you an actual replicated copy with proper conflict handling, and a dedicated ETL or CDC pipeline gives you the transformation and retry logic that ad hoc foreign tables were never designed to provide.

Wrapping up

postgres_fdw turns a handful of DDL statements — CREATE EXTENSION, CREATE SERVER, CREATE USER MAPPING, and either IMPORT FOREIGN SCHEMA or CREATE FOREIGN TABLE — into a live bridge between two PostgreSQL databases, with real predicate and join pushdown that you can verify directly through EXPLAIN VERBOSE. Remember to ANALYZE foreign tables after creating them, tune use_remote_estimate and fetch_size if performance matters, and treat cross-database writes as non-atomic rather than assuming distributed-transaction safety you don't actually have.

Once the foreign tables are in place, browsing and managing them doesn't have to mean going back to raw SQL every time. Chat2DB (download it at https://chat2db.ai/download (opens in a new tab) or use the web version at https://app.chat2db.ai (opens in a new tab)) connects to your PostgreSQL database and lets you explore foreign tables in the same tree view as native ones, run and inspect EXPLAIN output, and edit rows — a convenient way to sanity-check a new FDW setup without switching tools.