Query processing

Foreign data wrappers

A foreign data wrapper plugs into PostgreSQL planning and execution through callback methods. postgres_fdw can estimate remote work, deparse safe expressions into remote SQL, push down joins or upper operations when allowed, and fetch result rows from another PostgreSQL server.

In plain words

A foreign table looks like a local table in SQL, but an FDW decides how to get its rows. The planner asks the wrapper for size estimates and possible paths. The chosen path becomes a ForeignScan; at execution time the wrapper opens the remote resource and returns rows through executor callbacks.

With postgres_fdw, PostgreSQL can send safe filters to the remote server instead of fetching every row. It can also push down some joins, grouping/aggregation, sorting and limits when the conditions are safe. EXPLAIN (VERBOSE) shows the generated Remote SQL.

Two important FDW options are use_remote_estimate, which can ask the remote server for EXPLAIN-based estimates during planning, and fetch_size, the number of rows fetched per remote batch (100 by default).

For DBAs and operators

The FDW callback boundary

The core planner does not contain postgres_fdw-specific planning logic. An FDW handler returns an FdwRoutine of callbacks. For scans, postgres_fdw supplies GetForeignRelSize, GetForeignPaths and GetForeignPlan; execution uses BeginForeignScan, IterateForeignScan, ReScanForeignScan and EndForeignScan. It also exposes callbacks for foreign joins, upper relations, modifications and asynchronous execution.

Pushdown and costing

postgres_fdw separates clauses that are safe to execute remotely from ones that must stay local, then deparses the remote part into SQL. With use_remote_estimate = true it obtains remote estimates; otherwise it uses local statistics/cost options. Pushdown is an optimization, not a semantic shortcut: expressions must be considered safe/shippable for the remote server.

Execution

A connection to the foreign server is established when needed and normally reused for the session. Rows are fetched in batches controlled by fetch_size. postgres_fdw also maps a local transaction to a remote transaction; PostgreSQL documents remote SERIALIZABLE when the local transaction is SERIALIZABLE, and remote REPEATABLE READ otherwise.

EXPLAIN (VERBOSE, COSTS OFF)
SELECT id, total
  FROM remote_orders
 WHERE total > 1000
 ORDER BY total DESC
 LIMIT 5;
-- Look for: Remote SQL: SELECT ...

Source trail

PostgreSQL 18 postgres_fdw documentation.
postgres_fdw.c — planner/executor callback implementation.
deparse.c — creation of remote SQL.

For developers: inside the source

Core FDW API

FdwRoutine in fdwapi.h; foreign.c finds it (GetFdwRoutineForRelation()) and resolves GetForeignServer() / GetUserMapping(). get_relation_info() stores it in rel->fdwroutine.
Planner call sites: set_foreign_size() → GetForeignRelSize and set_foreign_pathlist() → GetForeignPaths (allpaths.c); add_paths_to_joinrel() → GetForeignJoinPaths; GetForeignUpperPaths from planner.c; create_foreignscan_plan() → GetForeignPlan (createplan.c).
Executor: nodeForeignscan.c: ExecInitForeignScan() → BeginForeignScan, ForeignNext() → IterateForeignScan.
Nodes: ForeignPath (built with create_foreignscan_path(), create_foreign_join_path(), create_foreign_upper_path()) and ForeignScan with fdw_exprs (expressions the planner fixes up), fdw_private (opaque to the core), fdw_scan_tlist and fdw_recheck_quals.

postgres_fdw internals

postgres_fdw_handler() fills the routine. postgresGetForeignRelSize() builds a PgFdwRelationInfo (postgres_fdw.h) and calls classifyConditions(), which splits remote_conds from local_conds using is_foreign_expr() → foreign_expr_walker() and is_shippable() (shippable.c: built-in objects, or objects of extensions listed in the server's extensions option).
estimate_path_cost_size() costs paths; with use_remote_estimate it calls get_remote_estimate(), which runs EXPLAIN on the remote server.
postgresGetForeignPlan() calls deparseSelectStmtForRel() (deparse.c) and keeps the SQL in fdw_private.
postgresBeginForeignScan() gets a connection with GetConnection() (connection.c): a ConnCacheEntry hash keyed by user mapping OID. A new connection runs configure_remote_session() (search_path = pg_catalog, timezone, datestyle, …) and begin_remote_xact() starts the remote transaction.
The first postgresIterateForeignScan() calls create_cursor() (DECLARE c<n> CURSOR FOR …); fetch_more_data() sends FETCH <fetch_size> FROM c<n> and fills a tuple array. pgfdw_xact_callback() commits or aborts the remote transaction with the local one.
Async Append (async_capable option): postgresIsForeignPathAsyncCapable(), postgresForeignAsyncRequest(), postgresForeignAsyncConfigureWait(), postgresForeignAsyncNotify().

Writing or debugging an FDW

The callback contract is in fdwhandler.sgml; contrib/file_fdw is the smallest complete example.
EXPLAIN (VERBOSE) prints Remote SQL via postgresExplainForeignScan(). postgres_fdw_get_connections() and postgres_fdw_disconnect_all() inspect and reset the connection cache.
Tests: contrib/postgres_fdw/sql/postgres_fdw.sql (a loopback server covering pushdown, joins, aggregates, async) and src/test/regress/sql/foreign_data.sql for the catalog side.