PostgreSQL Foreign Data Wrappers — The In-Core FDW Mechanism

Contents:

• Theoretical Background
• Common DBMS Design
• PostgreSQL’s Approach
• Source Walkthrough
• Source verification (as of 2026-06-05)
• Beyond PostgreSQL — Comparative Designs & Research Frontiers
• Sources

Theoretical Background

A foreign data wrapper is the database answer to a recurring need: a query must read data that does not live in the local storage engine. The data might be on a remote PostgreSQL server, in a CSV file, behind a REST API, in another RDBMS, or in a columnar analytics store. The question every system must answer is: how do you let SQL reference external data without swallowing the whole external system into the engine?

There are three broad families of answers:

1. ETL / materialization — copy the external data into local tables on a schedule. Queries are fast and use the normal storage engine, but the data is stale between loads and the copy doubles the storage cost. This is not a database feature at all; it is an external pipeline.

2. Federation / mediation — the database keeps a description of the external source and translates each query against it into operations the source understands, fetching rows on demand. No copy is made; freshness is automatic. The cost is that the optimizer must reason about a source whose statistics, cost model, and capabilities it does not control. This is the classic mediator–wrapper architecture from the data-integration literature (Wiederhold’s mediator model, the Garlic and TSIMMIS research systems): a thin wrapper per source exposes a uniform interface, and a mediator (here, the planner) composes wrappers into query plans.

3. Polystore / multistore — a query engine sits above several specialized stores and routes sub-queries to whichever store answers them best. This generalizes federation to heterogeneous engines with very different capabilities (BigDAWG, Myria). PostgreSQL’s FDW layer is a single-engine federation mechanism, but the per-source capability negotiation it performs (which quals/joins/aggregates can be pushed down) is the same problem polystores solve at larger scale.

PostgreSQL’s FDW machinery implements family 2, standardized as SQL/MED (“Management of External Data”), part of the SQL:2003 standard. SQL/MED defines the catalog vocabulary — foreign-data wrapper, foreign server, user mapping, foreign table — and PostgreSQL realizes it with a C callback API. The wrapper is a loadable extension that supplies a handler function; the handler returns a struct of function pointers; the planner and executor call those pointers at well-defined moments. The engine owns the parse tree, the optimizer search, and the executor tuple pipeline; the wrapper owns everything about talking to the remote source.

The conceptual contract has two halves:

• Description — static metadata in the catalogs. The foreign table’s column list and the OPTIONS (...) bag (e.g. a remote table name, a file path) tell the wrapper what the external object is. A foreign server names a connection endpoint; a user mapping supplies per-role credentials.

• Behavior — the FdwRoutine callbacks. They tell the engine how to size, plan, and execute access to that object. The split mirrors the mediator–wrapper split: catalogs are the mediator’s source descriptions, callbacks are the wrapper’s runtime.

Database System Concepts (Silberschatz, Korth, Sudarshan), in its chapters on data integration and heterogeneous/distributed databases, frames this as the difference between a tightly coupled distributed database (one engine, shared catalog, shared transaction manager) and a loosely coupled federation of autonomous sources reached through wrappers. The key tension it names — query optimization across an autonomous source whose cost model you do not own — is exactly what PostgreSQL’s GetForeignRelSize / GetForeignPaths callbacks exist to resolve: the engine delegates the source-specific cost and capability decisions to the wrapper, then folds the wrapper’s ForeignPath back into its own cost-based path competition.

The design space a SQL/MED implementer chooses within:

1. Row-at-a-time vs. set-at-a-time fetch — does the executor pull one tuple per callback, or does the wrapper materialize a batch? PostgreSQL’s IterateForeignScan is row-at-a-time by contract, but a wrapper is free to buffer a cursor’s worth of rows internally and hand them out one at a time.

2. How much computation to push down — only the table scan, or also filters, joins, aggregates, sorts, LIMIT? More push-down means less data crossing the wire but a more complex wrapper. PostgreSQL exposes this as a graduated set of optional callbacks (GetForeignPaths for quals, GetForeignJoinPaths for joins, GetForeignUpperPaths for aggregate/sort/limit).

3. Writable vs. read-only — must the source support INSERT/UPDATE/ DELETE? PostgreSQL makes the modify callbacks entirely optional; a read-only wrapper simply leaves them NULL.

Common DBMS Design

Federation engines converge on a small set of structural conventions. Naming them makes PostgreSQL’s specific symbols read as one set of choices inside a shared playbook rather than as arbitrary invention.

A per-source descriptor object loaded by name

Every federation layer represents each external source as a first-class, catalogued object with a stable identifier and a name. SQL/MED calls it a foreign server; other systems call it a linked server (SQL Server), a database link (Oracle DBLINK), or a connection. The descriptor records the wrapper type plus a free-form options bag, so the engine never hardcodes connection details.

A capability-negotiation handshake at plan time

Because the source is autonomous, the optimizer cannot assume it can do arithmetic, evaluate a regex, or perform a hash join. The universal pattern is a capability query: at plan time the engine asks the wrapper which operations it is willing to take on, and the wrapper answers by removing clauses from the locally-evaluated set (pushing them down) or keeping them (forcing local evaluation). PostgreSQL realizes this inside GetForeignPaths / GetForeignPlan: the wrapper splits scan_clauses into a remote part and a locally-rechecked part.

A cursor-like runtime interface

At execution the engine drives a small, stateful interface that mirrors a database cursor: open (establish the remote query / connection), next (fetch a row), close (release resources). Optional rescan resets the cursor for nested-loop re-execution. PostgreSQL names these BeginForeignScan, IterateForeignScan, ReScanForeignScan, EndForeignScan.

Credentials decoupled from the source descriptor

A single source is reached by many roles, each with its own credentials. Federation layers therefore separate the what (server) from the who (per-user credentials). SQL/MED’s user mapping binds (role, server) to an options bag holding username/password. PostgreSQL stores this in pg_user_mapping and resolves it via GetUserMapping, falling back to a PUBLIC mapping when no role-specific one exists.

Function-pointer dispatch for the wrapper body

The engine cannot link against every possible source driver. The portable pattern is indirection through a vtable: the wrapper is a loadable module exposing one entry point that returns a struct of function pointers, and the engine calls through that struct. This is the same shape PostgreSQL uses for table access methods (TableAmRoutine), index access methods (IndexAmRoutine), and custom scans (CustomScanMethods). For FDWs the vtable is FdwRoutine, returned by the handler function and fetched via GetFdwRoutine.

Theory ↔ PostgreSQL mapping

Concept	PostgreSQL name
Wrapper type / driver	foreign-data wrapper (pg_foreign_data_wrapper, ForeignDataWrapper)
Source descriptor	foreign server (pg_foreign_server, ForeignServer)
Per-user credentials	user mapping (pg_user_mapping, UserMapping)
Catalogued external object	foreign table (pg_foreign_table, ForeignTable)
Wrapper vtable	FdwRoutine struct (returned by handler, makeNode(FdwRoutine))
Handler entry point	fdwhandler function returning fdw_handler (FDW_HANDLEROID)
Vtable fetch	GetFdwRoutine, GetFdwRoutineForRelation
Capability negotiation	GetForeignPaths / GetForeignPlan clause split
Cursor open / next / close	BeginForeignScan / IterateForeignScan / EndForeignScan
Plan-time size estimate	GetForeignRelSize (from set_foreign_size)
Executor plan node	ForeignScan (built by create_foreignscan_plan)
Executor state node	ForeignScanState (scanstate->fdwroutine, fdw_state)
Write path	PlanForeignModify + ExecForeignInsert/Update/Delete

PostgreSQL’s Approach

PostgreSQL splits the FDW mechanism into three tiers: a catalog tier (four system catalogs describing wrappers, servers, mappings, tables), a DDL tier (foreigncmds.c, maintaining those catalogs), and a dispatch + integration tier (foreign.c resolving the handler to an FdwRoutine, then planner and executor calling through it). A wrapper author supplies exactly one C function — the handler — and fills in the callbacks they choose to implement.

The FdwRoutine vtable: mandatory and optional callbacks

The handler returns a makeNode(FdwRoutine) struct. Seven scan callbacks are mandatory; everything else is optional and may be left NULL. The header states the contract directly:

// FdwRoutine — src/include/foreign/fdwapi.h
typedef struct FdwRoutine
{
    NodeTag     type;

    /* Functions for scanning foreign tables */
    GetForeignRelSize_function GetForeignRelSize;
    GetForeignPaths_function GetForeignPaths;
    GetForeignPlan_function GetForeignPlan;
    BeginForeignScan_function BeginForeignScan;
    IterateForeignScan_function IterateForeignScan;
    ReScanForeignScan_function ReScanForeignScan;
    EndForeignScan_function EndForeignScan;

    /*
     * Remaining functions are optional.  Set the pointer to NULL for any that
     * are not provided.
     */
    /* Functions for remote-join planning */
    GetForeignJoinPaths_function GetForeignJoinPaths;
    /* Functions for remote upper-relation (post scan/join) planning */
    GetForeignUpperPaths_function GetForeignUpperPaths;
    /* Functions for updating foreign tables */
    AddForeignUpdateTargets_function AddForeignUpdateTargets;
    PlanForeignModify_function PlanForeignModify;
    BeginForeignModify_function BeginForeignModify;
    ExecForeignInsert_function ExecForeignInsert;
    /* ... ExecForeignBatchInsert, ExecForeignUpdate, ExecForeignDelete, ... */
    EndForeignModify_function EndForeignModify;
    /* ... direct-modify, row-locking, EXPLAIN, ANALYZE, IMPORT, TRUNCATE, */
    /*     parallelism, path reparameterization, async execution ...      */
} FdwRoutine;

The header’s own comment is the design rule a wrapper author must follow:

// FdwRoutine comment — src/include/foreign/fdwapi.h
 * More function pointers are likely to be added in the future.  Therefore
 * it's recommended that the handler initialize the struct with
 * makeNode(FdwRoutine) so that all fields are set to NULL.  This will
 * ensure that no fields are accidentally left undefined.

makeNode zero-fills the allocation, so an unimplemented optional callback is automatically NULL, and the engine checks for NULL before calling any optional pointer. This is how a read-only, scan-only wrapper stays tiny: it sets seven pointers and leaves ~40 others NULL.

Resolving a relation to its vtable

When the planner or executor needs the callbacks for a foreign table, it walks: foreign table OID → foreign server OID → FDW OID → handler function OID → call the handler → FdwRoutine. GetFdwRoutine performs the last step, calling the handler through the function manager and type-checking the result:

// GetFdwRoutine — src/backend/foreign/foreign.c
FdwRoutine *
GetFdwRoutine(Oid fdwhandler)
{
    Datum       datum;
    FdwRoutine *routine;

    /* Check if the access to foreign tables is restricted */
    if (unlikely((restrict_nonsystem_relation_kind & RESTRICT_RELKIND_FOREIGN_TABLE) != 0))
        ereport(ERROR, ...);

    datum = OidFunctionCall0(fdwhandler);
    routine = (FdwRoutine *) DatumGetPointer(datum);

    if (routine == NULL || !IsA(routine, FdwRoutine))
        elog(ERROR, "foreign-data wrapper handler function %u did not return an FdwRoutine struct",
             fdwhandler);
    return routine;
}

The executor-facing helper GetFdwRoutineForRelation caches the result in the relcache entry (rd_fdwroutine) so repeated lookups during a query are free:

// GetFdwRoutineForRelation — src/backend/foreign/foreign.c
FdwRoutine *
GetFdwRoutineForRelation(Relation relation, bool makecopy)
{
    FdwRoutine *fdwroutine;
    FdwRoutine *cfdwroutine;

    if (relation->rd_fdwroutine == NULL)
    {
        /* Get the info by consulting the catalogs and the FDW code */
        fdwroutine = GetFdwRoutineByRelId(RelationGetRelid(relation));
        /* Save the data for later reuse in CacheMemoryContext */
        cfdwroutine = (FdwRoutine *) MemoryContextAlloc(CacheMemoryContext,
                                                        sizeof(FdwRoutine));
        memcpy(cfdwroutine, fdwroutine, sizeof(FdwRoutine));
        relation->rd_fdwroutine = cfdwroutine;
        return fdwroutine;
    }
    /* ... return cached copy, optionally palloc'd ... */
    return relation->rd_fdwroutine;
}

The resolution chain is itself a sequence of syscache lookups (GetForeignServerIdByRelId → GetFdwRoutineByServerId → GetFdwRoutine), each reading one catalog row.

flowchart TD
    A["foreign table OID<br/>(RelationGetRelid)"] --> B["GetForeignServerIdByRelId<br/>reads pg_foreign_table.ftserver"]
    B --> C["GetFdwRoutineByServerId<br/>reads pg_foreign_server.srvfdw<br/>then pg_foreign_data_wrapper.fdwhandler"]
    C --> D["GetFdwRoutine<br/>OidFunctionCall0(fdwhandler)"]
    D --> E["handler returns<br/>makeNode(FdwRoutine)"]
    E --> F["IsA(routine, FdwRoutine)?<br/>else ERROR"]
    F --> G["cache in Relation.rd_fdwroutine<br/>(GetFdwRoutineForRelation)"]

The four catalogs and their in-memory mirrors

foreign.c provides a Get* accessor per catalog that reads the syscache row and palloc’s a C struct. GetForeignServer is representative — it unpacks the pg_foreign_server tuple, decoding the srvoptions text array into a DefElem list with untransformRelOptions:

// GetForeignServerExtended — src/backend/foreign/foreign.c
server = (ForeignServer *) palloc(sizeof(ForeignServer));
server->serverid = serverid;
server->servername = pstrdup(NameStr(serverform->srvname));
server->owner = serverform->srvowner;
server->fdwid = serverform->srvfdw;
/* Extract the srvoptions */
datum = SysCacheGetAttr(FOREIGNSERVEROID, tp,
                        Anum_pg_foreign_server_srvoptions, &isnull);
server->options = isnull ? NIL : untransformRelOptions(datum);

User mappings add the credential-fallback rule: look up (userid, serverid), and if no row exists, retry with InvalidOid to find a PUBLIC mapping:

// GetUserMapping — src/backend/foreign/foreign.c
tp = SearchSysCache2(USERMAPPINGUSERSERVER,
                     ObjectIdGetDatum(userid), ObjectIdGetDatum(serverid));
if (!HeapTupleIsValid(tp))
{
    /* Not found for the specific user -- try PUBLIC */
    tp = SearchSysCache2(USERMAPPINGUSERSERVER,
                         ObjectIdGetDatum(InvalidOid), ObjectIdGetDatum(serverid));
}

Planner integration: size → paths → plan

The planner treats a foreign table almost like any base relation, except three hooks delegate to the wrapper. During the size phase, set_foreign_size calls GetForeignRelSize so the wrapper can refine the row/width estimates; during path generation, set_foreign_pathlist calls GetForeignPaths so the wrapper can add one or more ForeignPath nodes that compete in the normal cost-based path tournament:

// set_foreign_pathlist — src/backend/optimizer/path/allpaths.c
static void
set_foreign_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
{
    /* Call the FDW's GetForeignPaths function to generate path(s) */
    rel->fdwroutine->GetForeignPaths(root, rel, rte->relid);
}

Once a ForeignPath wins, create_foreignscan_plan calls GetForeignPlan to materialize the ForeignScan plan node, then copies engine-owned fields (cost, server OID, relids) so the wrapper need not:

// create_foreignscan_plan — src/backend/optimizer/plan/createplan.c
scan_plan = rel->fdwroutine->GetForeignPlan(root, rel, rel_oid,
                                            best_path,
                                            tlist, scan_clauses,
                                            outer_plan);
/* Copy cost data from Path to Plan; no need to make FDW do this */
copy_generic_path_info(&scan_plan->scan.plan, &best_path->path);
scan_plan->checkAsUser = rel->userid;
scan_plan->fs_server = rel->serverid;

flowchart LR
    subgraph Planner
      P1["set_foreign_size<br/>→ GetForeignRelSize"] --> P2["set_foreign_pathlist<br/>→ GetForeignPaths"]
      P2 --> P3["create_foreignscan_plan<br/>→ GetForeignPlan"]
    end
    P3 --> N["ForeignScan plan node<br/>fdw_private, fdw_exprs"]
    subgraph Executor
      N --> E1["ExecInitForeignScan<br/>→ BeginForeignScan"]
      E1 --> E2["ForeignNext loop<br/>→ IterateForeignScan"]
      E2 --> E3["ExecEndForeignScan<br/>→ EndForeignScan"]
    end

Executor integration: the scan cursor

ExecInitForeignScan builds the ForeignScanState, looks up the vtable (via the relcache for base-relation scans, or GetFdwRoutineByServerId for join/upper scans that have no single relation), stores it in scanstate->fdwroutine, and calls BeginForeignScan to open the cursor:

// ExecInitForeignScan — src/backend/executor/nodeForeignscan.c
if (scanrelid > 0)
{
    currentRelation = ExecOpenScanRelation(estate, scanrelid, eflags);
    scanstate->ss.ss_currentRelation = currentRelation;
    fdwroutine = GetFdwRoutineForRelation(currentRelation, true);
}
else
{
    /* We can't use the relcache, so get fdwroutine the hard way */
    fdwroutine = GetFdwRoutineByServerId(node->fs_server);
}
/* ... */
scanstate->fdwroutine = fdwroutine;
scanstate->fdw_state = NULL;
/* ... */
if (node->operation != CMD_SELECT)
{
    if (estate->es_epq_active == NULL)
        fdwroutine->BeginDirectModify(scanstate, eflags);
}
else
    fdwroutine->BeginForeignScan(scanstate, eflags);

scanstate->fdw_state is a void * scratch slot the wrapper owns; the engine never touches its contents. Each call to ExecForeignScan flows through ForeignNext, which calls IterateForeignScan inside the per-tuple memory context so the wrapper’s allocations are reset every row:

// ForeignNext — src/backend/executor/nodeForeignscan.c
oldcontext = MemoryContextSwitchTo(econtext->ecxt_per_tuple_memory);
if (plan->operation != CMD_SELECT)
    slot = node->fdwroutine->IterateDirectModify(node);
else
    slot = node->fdwroutine->IterateForeignScan(node);
MemoryContextSwitchTo(oldcontext);

A NULL-or-empty slot from IterateForeignScan signals end of scan. ExecReScanForeignScan calls ReScanForeignScan to rewind the cursor for nested-loop re-execution, and ExecEndForeignScan calls EndForeignScan to release resources. The contract is exactly the open/next/rescan/close cursor interface named in the Common DBMS Design section.

The write path: ModifyTable callbacks

Writes are optional. At plan time, create_modifytable_plan calls PlanForeignModify so the wrapper can build the remote DML text and stash it in fdw_private. At executor init, ExecInitModifyTable calls BeginForeignModify for each foreign result relation that supplies it:

// ExecInitModifyTable (foreign init) — src/backend/executor/nodeModifyTable.c
if (!resultRelInfo->ri_usesFdwDirectModify &&
    resultRelInfo->ri_FdwRoutine != NULL &&
    resultRelInfo->ri_FdwRoutine->BeginForeignModify != NULL)
{
    List       *fdw_private = (List *) list_nth(node->fdwPrivLists, i);

    resultRelInfo->ri_FdwRoutine->BeginForeignModify(mtstate,
                                                     resultRelInfo,
                                                     fdw_private,
                                                     i, eflags);
}

Per-row, the executor calls ExecForeignInsert, ExecForeignUpdate, or ExecForeignDelete through ri_FdwRoutine. If the wrapper additionally provides GetForeignModifyBatchSize and ExecForeignBatchInsert, the executor groups inserts into batches to amortize round-trips. There are two distinct write strategies:

• Row-by-row modify — the engine fetches each target row (often via a ForeignScan sub-plan that selects the rows to change), then calls the per-row modify callback. This works for any source but pays a round-trip per row.

• Direct modify — when the wrapper can translate the whole UPDATE/DELETE into a single remote statement, PlanDirectModify returns true at plan time, and the executor drives BeginDirectModify / IterateDirectModify / EndDirectModify through the ForeignScan node instead. ri_usesFdwDirectModify records the choice; note the guard above skips BeginForeignModify in that case.

The DDL tier: maintaining the catalogs

foreigncmds.c implements CREATE/ALTER/DROP for all four object types. CreateForeignDataWrapper requires superuser, resolves the HANDLER/VALIDATOR function names to OIDs, and inserts a pg_foreign_data_wrapper row. The handler-name resolution enforces the return-type contract that makes the vtable dispatch type-safe:

// lookup_fdw_handler_func — src/backend/commands/foreigncmds.c
handlerOid = LookupFuncName((List *) handler->arg, 0, NULL, false);
/* check that handler has correct return type */
if (get_func_rettype(handlerOid) != FDW_HANDLEROID)
    ereport(ERROR,
            (errcode(ERRCODE_WRONG_OBJECT_TYPE),
             errmsg("function %s must return type %s",
                    NameListToString((List *) handler->arg), "fdw_handler")));

The optional VALIDATOR function (signature (text[], oid)) is called through transformGenericOptions whenever options are set on a wrapper, server, mapping, or table, letting a wrapper reject nonsensical options at DDL time rather than at query time. foreign.c ships a deprecated reference validator, postgresql_fdw_validator, that checks options against a small libpq conninfo whitelist.

ImportForeignSchema is the most interesting DDL path: it bridges into the vtable. It calls the wrapper’s ImportForeignSchema callback, which returns a list of CREATE FOREIGN TABLE SQL strings; the command then parses and executes each one through ProcessUtility:

// ImportForeignSchema — src/backend/commands/foreigncmds.c
fdw_routine = GetFdwRoutine(fdw->fdwhandler);
if (fdw_routine->ImportForeignSchema == NULL)
    ereport(ERROR,
            (errcode(ERRCODE_FDW_NO_SCHEMAS),
             errmsg("foreign-data wrapper \"%s\" does not support IMPORT FOREIGN SCHEMA",
                    fdw->fdwname)));
/* Call FDW to get a list of commands */
cmd_list = fdw_routine->ImportForeignSchema(stmt, server->serverid);

The per-table filter (LIMIT TO / EXCEPT) is applied by the core helper IsImportableForeignTable, so the wrapper only decides what tables exist; the engine decides which to actually create.

Source Walkthrough

Symbols grouped by tier. Files are under /data/hgryoo/references/postgres/.

The vtable and its accessors (fdwapi.h, foreign.c)

• FdwRoutine (struct) — the callback vtable; 7 mandatory scan callbacks plus ~40 optional ones (join/upper push-down, modify, direct-modify, row-locking, EXPLAIN, ANALYZE, IMPORT, TRUNCATE, parallel, async).
• GetForeignRelSize_function / GetForeignPaths_function / GetForeignPlan_function (typedefs) — the three planner callback signatures.
• BeginForeignScan_function / IterateForeignScan_function / ReScanForeignScan_function / EndForeignScan_function (typedefs) — the executor cursor callback signatures.
• GetFdwRoutine — call a handler OID, type-check the returned FdwRoutine; honors restrict_nonsystem_relation_kind.
• GetFdwRoutineByServerId — server OID → FDW → handler → GetFdwRoutine.
• GetFdwRoutineByRelId — relation OID → GetForeignServerIdByRelId → GetFdwRoutineByServerId.
• GetFdwRoutineForRelation — relcache-cached wrapper (rd_fdwroutine); the preferred executor entry point.
• GetForeignServerIdByRelId — read pg_foreign_table.ftserver.
• IsImportableForeignTable — apply LIMIT TO / EXCEPT filter.
• GetExistingLocalJoinPath — fetch a local join path for EPQ rechecks of pushed-down foreign joins.

Catalog accessors (foreign.c)

• GetForeignDataWrapper / GetForeignDataWrapperExtended / GetForeignDataWrapperByName — pg_foreign_data_wrapper → ForeignDataWrapper.
• GetForeignServer / GetForeignServerExtended / GetForeignServerByName — pg_foreign_server → ForeignServer.
• GetUserMapping — (userid, serverid) → UserMapping, with PUBLIC (InvalidOid) fallback.
• GetForeignTable — pg_foreign_table → ForeignTable.
• GetForeignColumnOptions — per-attribute attfdwoptions.
• get_foreign_data_wrapper_oid / get_foreign_server_oid — name → OID.
• postgresql_fdw_validator — deprecated reference validator (libpq conninfo whitelist).
• pg_options_to_table — SRF exposing an options array as name/value rows.

DDL commands (foreigncmds.c)

• CreateForeignDataWrapper — superuser-only; resolves handler/validator; inserts pg_foreign_data_wrapper.
• lookup_fdw_handler_func / lookup_fdw_validator_func — name → OID with FDW_HANDLEROID return-type / (text[],oid) argument-type checks.
• parse_func_options — parse HANDLER / VALIDATOR from the parse tree.
• optionListToArray / transformGenericOptions — DefElem list ↔ text[] array; the latter calls the validator.
• CreateForeignServer — insert pg_foreign_server; depends on the FDW.
• CreateUserMapping — insert pg_user_mapping.
• CreateForeignTable — insert pg_foreign_table (the heap relation having been created by the generic table-creation path first).
• ImportForeignSchema — call the wrapper’s ImportForeignSchema callback, filter, and ProcessUtility each returned CREATE FOREIGN TABLE.

Planner integration (allpaths.c, createplan.c, appendinfo.c)

• set_foreign_size — set_foreign_size_estimates, then rel->fdwroutine->GetForeignRelSize, then clamp_row_est.
• set_foreign_pathlist — rel->fdwroutine->GetForeignPaths.
• create_foreignscan_plan — rel->fdwroutine->GetForeignPlan; copies cost, checkAsUser, fs_server, fs_relids into the ForeignScan.
• PlanForeignModify call site (in create_modifytable_plan) — build fdwPrivLists.
• AddForeignUpdateTargets call site (in appendinfo.c) — add junk columns the wrapper needs to identify rows.

Executor integration (nodeForeignscan.c, nodeModifyTable.c)

• ExecInitForeignScan — build ForeignScanState, resolve vtable, call BeginForeignScan (or BeginDirectModify).
• ForeignNext — per-tuple-context call to IterateForeignScan (or IterateDirectModify).
• ForeignRecheck — EPQ recheck via RecheckForeignScan + fdw_recheck_quals.
• ExecForeignScan — ExecScan(ForeignNext, ForeignRecheck).
• ExecReScanForeignScan — ReScanForeignScan.
• ExecEndForeignScan — EndForeignScan (or EndDirectModify).
• ExecForeignScanEstimate / …InitializeDSM / …InitializeWorker / …ReInitializeDSM — parallel-scan DSM plumbing to the optional EstimateDSMForeignScan family.
• ExecInitModifyTable (foreign branch) — BeginForeignModify.
• ExecForeignInsert / ExecForeignBatchInsert / ExecForeignUpdate / ExecForeignDelete call sites — per-row / per-batch DML.
• ExecEndModifyTable (foreign branch) — EndForeignModify.

Position hints (as of 2026-06-05, REL_18 273fe94)

Symbol	File	Line
FdwRoutine (struct)	src/include/foreign/fdwapi.h	204
GetForeignRelSize_function (typedef)	src/include/foreign/fdwapi.h	27
GetForeignPlan_function (typedef)	src/include/foreign/fdwapi.h	35
IterateForeignScan_function (typedef)	src/include/foreign/fdwapi.h	46
GetFdwRoutine (decl)	src/include/foreign/fdwapi.h	285
GetForeignDataWrapperExtended	src/backend/foreign/foreign.c	50
GetForeignServerExtended	src/backend/foreign/foreign.c	124
GetUserMapping	src/backend/foreign/foreign.c	201
GetForeignTable	src/backend/foreign/foreign.c	255
GetFdwRoutine	src/backend/foreign/foreign.c	326
GetForeignServerIdByRelId	src/backend/foreign/foreign.c	356
GetFdwRoutineByServerId	src/backend/foreign/foreign.c	378
GetFdwRoutineByRelId	src/backend/foreign/foreign.c	420
GetFdwRoutineForRelation	src/backend/foreign/foreign.c	443
IsImportableForeignTable	src/backend/foreign/foreign.c	483
postgresql_fdw_validator	src/backend/foreign/foreign.c	626
GetExistingLocalJoinPath	src/backend/foreign/foreign.c	742
lookup_fdw_handler_func	src/backend/commands/foreigncmds.c	486
lookup_fdw_validator_func	src/backend/commands/foreigncmds.c	510
parse_func_options	src/backend/commands/foreigncmds.c	529
CreateForeignDataWrapper	src/backend/commands/foreigncmds.c	569
CreateForeignServer	src/backend/commands/foreigncmds.c	854
CreateUserMapping	src/backend/commands/foreigncmds.c	1116
CreateForeignTable	src/backend/commands/foreigncmds.c	1420
ImportForeignSchema	src/backend/commands/foreigncmds.c	1500
set_foreign_size	src/backend/optimizer/path/allpaths.c	913
set_foreign_pathlist	src/backend/optimizer/path/allpaths.c	937
create_foreignscan_plan	src/backend/optimizer/plan/createplan.c	4115
PlanForeignModify call site	src/backend/optimizer/plan/createplan.c	7375
AddForeignUpdateTargets call site	src/backend/optimizer/util/appendinfo.c	944
ForeignNext	src/backend/executor/nodeForeignscan.c	41
ForeignRecheck	src/backend/executor/nodeForeignscan.c	78
ExecForeignScan	src/backend/executor/nodeForeignscan.c	118
ExecInitForeignScan	src/backend/executor/nodeForeignscan.c	142
ExecEndForeignScan	src/backend/executor/nodeForeignscan.c	297
ExecReScanForeignScan	src/backend/executor/nodeForeignscan.c	323
ExecForeignInsert call site	src/backend/executor/nodeModifyTable.c	1032
ExecForeignBatchInsert call site	src/backend/executor/nodeModifyTable.c	1383
ExecForeignDelete call site	src/backend/executor/nodeModifyTable.c	1621
ExecForeignUpdate call site	src/backend/executor/nodeModifyTable.c	2499
BeginForeignModify call site	src/backend/executor/nodeModifyTable.c	4862
EndForeignModify call site	src/backend/executor/nodeModifyTable.c	5241

Source verification (as of 2026-06-05)

Verified facts

• The FdwRoutine has exactly seven mandatory scan callbacks; the rest are optional. Verified in fdwapi.h: the struct comment after the seven scan fields reads “Remaining functions are optional. Set the pointer to NULL for any that are not provided.” The struct comment also mandates makeNode(FdwRoutine) so unset fields are NULL.

• The handler function must return type fdw_handler (FDW_HANDLEROID). Verified in lookup_fdw_handler_func (foreigncmds.c): if (get_func_rettype(handlerOid) != FDW_HANDLEROID) raises ERRCODE_WRONG_OBJECT_TYPE. GetFdwRoutine additionally asserts IsA(routine, FdwRoutine) at call time, so a handler returning a bogus pointer is caught.

• GetFdwRoutineForRelation caches the vtable in the relcache. Verified in foreign.c: when relation->rd_fdwroutine == NULL it copies the freshly-resolved routine into CacheMemoryContext and stores it on the relcache entry; the comment warns the cached pointer “will be lost in any relcache reset.”

• Creating a foreign-data wrapper requires superuser. Verified in CreateForeignDataWrapper: if (!superuser()) ereport(ERROR, ... "permission denied to create foreign-data wrapper"). Servers, mappings, and tables have finer-grained ACL checks (USAGE on the FDW/server), but the wrapper itself is superuser-only because it names arbitrary C functions.

• User-mapping lookup falls back to PUBLIC. Verified in GetUserMapping: on a miss for (userid, serverid) it retries SearchSysCache2 with InvalidOid as the user before erroring with “user mapping not found.”

• The planner calls GetForeignRelSize from set_foreign_size and GetForeignPaths from set_foreign_pathlist. Verified in allpaths.c: both call through rel->fdwroutine->.... set_foreign_size clamps the resulting row estimate with clamp_row_est so a wrapper cannot drive it to zero, and ensures rel->tuples >= rel->rows.

• create_foreignscan_plan asserts rel->fdwroutine != NULL and copies engine-owned fields after GetForeignPlan. Verified in createplan.c: copy_generic_path_info, scan_plan->checkAsUser = rel->userid, scan_plan->fs_server = rel->serverid. The comment notes the FDW “might remove restriction clauses that it intends to execute remotely, or even add more.”

• IterateForeignScan is called inside the per-tuple memory context. Verified in ForeignNext: MemoryContextSwitchTo(econtext-> ecxt_per_tuple_memory) wraps the IterateForeignScan call, so a wrapper’s per-row allocations are reclaimed on the next ExecScan reset.

• Direct modify and row-by-row modify are mutually exclusive per result rel. Verified in ExecInitModifyTable: BeginForeignModify is skipped when resultRelInfo->ri_usesFdwDirectModify is true. ExecInitForeignScan calls BeginDirectModify instead of BeginForeignScan when node->operation != CMD_SELECT.

• ImportForeignSchema only accepts CreateForeignTableStmt back from the wrapper. Verified in foreigncmds.c: each returned parse tree is checked with IsA(cstmt, CreateForeignTableStmt) and an error is raised otherwise; the schema name is forced to the IMPORT statement’s local_schema.

Open questions

1. How does a wrapper communicate pushed-down quals between GetForeignPlan and BeginForeignScan? The mechanism is the ForeignScan.fdw_private list (built in GetForeignPlan, read in BeginForeignScan via node->ss.ps.plan), plus fdw_exprs for runtime parameter values. The exact serialization contract (what must be copyObject-able vs. node-tree) is wrapper-defined and not enforced by the core; tracing how fdw_private survives copyObject during plan caching would clarify the constraints. Investigation path: _copyForeignScan in copyfuncs and ForeignScan.fdw_private usage.

2. What exactly is the cost contract for GetForeignPaths? The wrapper sets startup_cost / total_cost on the ForeignPath, but those numbers are in the engine’s cost units (seq_page_cost etc.) which have no meaning for a remote source. How a wrapper is expected to calibrate remote latency into local cost units — and how badly a miscalibration distorts join ordering — is not specified in-core. Investigation path: compare cost_seqscan units against how set_foreign_size_estimates seeds width.

3. When are EPQ rechecks actually exercised for foreign joins? GetExistingLocalJoinPath exists to rebuild a local join path for EvalPlanQual, but it only handles unparameterized Hash/Merge/NestLoop paths. What happens for a parameterized pushed-down join under SELECT ... FOR UPDATE is unclear from this file alone. Investigation path: trace RefetchForeignRow / GetForeignRowMarkType callers in the executor’s EPQ machinery.

Beyond PostgreSQL — Comparative Designs & Research Frontiers

PostgreSQL’s FDW layer is a deliberately minimal federation kernel: a catalog vocabulary plus a callback vtable, with all source-specific intelligence pushed into the wrapper. Placing it next to the data-integration research lineage and to other production engines shows both what it inherited and where it stops.

The mediator–wrapper lineage

The FDW design is a direct descendant of the mediator–wrapper architecture from the 1990s data-integration literature. Wiederhold’s 1992 “Mediators in the Architecture of Future Information Systems” coined the split PostgreSQL still uses: a thin wrapper per source that translates a uniform query interface into source-native operations, and a mediator that composes wrappers into answers. PostgreSQL’s planner is the mediator; the FdwRoutine is the wrapper interface.

Two research systems fixed the vocabulary PostgreSQL’s callbacks echo:

• Garlic (IBM Almaden, Roth & Schwarz 1997, “Don’t Scrap It, Wrap It!”). Garlic’s central idea is capability-based query planning: a wrapper does not merely fetch rows, it advertises which query fragments it can execute, and the optimizer enumerates plans that respect those capabilities. This is exactly the contract PostgreSQL’s GetForeignPaths / GetForeignJoinPaths / GetForeignUpperPaths implement — the wrapper adds paths it is willing to execute remotely and the engine’s cost-based search chooses among them. Garlic’s “plan a fragment, cost it, let the mediator compose” loop is the intellectual ancestor of PostgreSQL folding a ForeignPath into its normal path tournament.

• TSIMMIS (Stanford, Garcia-Molina et al. 1997). TSIMMIS emphasized semi-structured sources and wrapper-generation toolkits, but its lasting contribution to the FDW shape is the query-template model: a wrapper declares the parameterized queries it supports, and unsupported predicates are evaluated by the mediator after fetch. PostgreSQL’s qual split in GetForeignPlan (remote vs. locally-rechecked scan_clauses, with fdw_recheck_quals re-evaluated in ForeignRecheck) is the same push-some/keep-some division.

The standardization of this research into SQL/MED (ISO/IEC 9075-9, SQL:2003) gave PostgreSQL its catalog nouns — foreign-data wrapper, foreign server, user mapping, foreign table. PostgreSQL implements the datalink and foreign-table portions of SQL/MED but, like most engines, only a pragmatic subset of the full standard.

Where PostgreSQL stops: federation, not polystore

PostgreSQL’s FDW is a single-engine federation mechanism. It can make many sources look like PostgreSQL tables, but the optimizer is still PostgreSQL’s row-oriented, cost-based planner, and the executor is still PostgreSQL’s tuple-at-a-time Volcano pipeline. It does not route a query to the engine best suited to answer it. That is the polystore / multistore frontier:

• BigDAWG (MIT/Intel/MIT-LL, Duggan et al. 2015) organizes stores into islands of information, each with its own query language and semantics, and inserts explicit CAST operators to move data between islands. Where an FDW makes a Cassandra table pretend to be relational, BigDAWG keeps the array store array-shaped and the relational store relational, choosing the island per sub-query. PostgreSQL’s GetForeignUpperPaths push-down is the closest in-core analog — it lets an aggregate run remotely — but there is only ever one execution model (Postgres’s) composing the result.

• Myria and Apache Calcite-based federations (e.g. Dremio, Trino’s connector model) generalize further: a single logical plan is split across heterogeneous backends with cross-engine cost models. Calcite’s adapter is the spiritual cousin of FdwRoutine, but Calcite reasons about relational algebra rewrites across adapters, whereas PostgreSQL’s wrapper negotiation is per-relation and per-join-pair, bounded by what the core planner already enumerates.

The trade PostgreSQL made is clarity for reach: a wrapper author implements a handful of callbacks and inherits the entire mature PostgreSQL optimizer and executor, but can never make the engine reason in a non-relational algebra.

Comparison to other production engines

Engine	Federation primitive	Push-down model	Credential scope
PostgreSQL	FDW + FdwRoutine vtable	qual/join/upper paths added to cost search	user mapping (role, server), PUBLIC fallback
Oracle	Database links / Heterogeneous Services (gateways)	distributed query optimizer rewrites remote SQL	per-link credentials in the link definition
SQL Server	Linked servers + OLE DB providers	provider “rowset” capabilities; remote query when supported	linked-server login mappings
MySQL	FEDERATED storage engine	almost none — full scan + local filter	connection string in CREATE TABLE
Trino / Presto	Connectors (SPI)	connector pushdown API (predicate/aggregation/topN)	per-catalog config / session

PostgreSQL sits in the middle of this spectrum. It is far more capable than MySQL’s FEDERATED engine (which essentially never pushes work down) and more open than Oracle/SQL Server (any C extension can be a wrapper, not just vendor gateways), but it is less aggressive than Trino’s connector SPI, which was designed from scratch around a rich pushdown vocabulary rather than retrofitting one onto an existing relational planner.

Active frontiers in the PostgreSQL FDW itself

The in-core machinery is still evolving along several axes visible in the FdwRoutine optional callbacks:

• Asynchronous execution (IsForeignPathAsyncCapable, ForeignAsyncRequest, ForeignAsyncConfigureWait, ForeignAsyncNotify). Since PG14 an Append over multiple foreign scans can issue remote queries concurrently and consume whichever responds first, turning a partitioned foreign table into a scatter-gather fan-out. This is the in-core seed of a distributed execution model.

• Batch insert (GetForeignModifyBatchSize + ExecForeignBatchInsert). Amortizing per-row round-trips is the single biggest latency lever for a remote writable wrapper; the core gives the wrapper a knob and groups rows on its behalf.

• Partition-wise / sharding patterns. Foreign tables as partitions of a local partitioned table, combined with async append and join push-down, approximate a sharded cluster without a separate coordinator. The unresolved research question is global transaction atomicity: PostgreSQL has no built-in distributed two-phase commit across FDW connections (a wrapper can opt into 2PC via the transaction-callback hooks, but the engine does not coordinate it for you). Atomic cross-shard commit remains an out-of-core concern, addressed by extensions and forks rather than the in-core FDW layer.

• Direct modify and join push-down maturity. PlanDirectModify and GetForeignJoinPaths already let a capable wrapper (such as the contrib postgres_fdw) collapse an entire UPDATE ... FROM or a multi-table join into one remote statement. Extending the upper-relation push-down (GetForeignUpperPaths) to cover window functions, DISTINCT, and richer LIMIT/OFFSET shapes is incremental ongoing work.

The throughline: PostgreSQL keeps the FDW kernel small and standard-anchored, and treats every increase in remote-execution sophistication as a new optional callback a wrapper may or may not implement — never as a change to the mandatory seven-callback scan contract.

Sources

Code paths (REL_18, commit 273fe94, observed 2026-06-05)

• src/include/foreign/fdwapi.h — the FdwRoutine vtable, all callback typedefs (GetForeignRelSize_function … the async family), and the GetFdwRoutine* / GetForeignServerIdByRelId declarations. This header is the canonical contract a wrapper author programs against.
• src/include/foreign/foreign.h — the in-memory catalog mirror structs (ForeignDataWrapper, ForeignServer, UserMapping, ForeignTable) and their Get* accessor declarations.
• src/backend/foreign/foreign.c — handler→vtable dispatch (GetFdwRoutine, GetFdwRoutineByServerId, GetFdwRoutineByRelId, GetFdwRoutineForRelation), the four catalog accessors, GetUserMapping with its PUBLIC fallback, IsImportableForeignTable, GetExistingLocalJoinPath, and the deprecated postgresql_fdw_validator.
• src/backend/commands/foreigncmds.c — the DDL tier: CreateForeignDataWrapper (superuser check, handler/validator resolution), CreateForeignServer, CreateUserMapping, CreateForeignTable, ImportForeignSchema, and the option-array transform helpers (lookup_fdw_handler_func, transformGenericOptions).
• src/backend/optimizer/path/allpaths.c — set_foreign_size, set_foreign_pathlist (the GetForeignRelSize / GetForeignPaths call sites).
• src/backend/optimizer/plan/createplan.c — create_foreignscan_plan (the GetForeignPlan call site and engine-owned-field copy-back); the PlanForeignModify call site in create_modifytable_plan.
• src/backend/executor/nodeForeignscan.c — the scan-cursor driver (ExecInitForeignScan, ForeignNext, ForeignRecheck, ExecReScanForeignScan, ExecEndForeignScan) plus the parallel-scan DSM plumbing.
• src/backend/executor/nodeModifyTable.c — the write-path call sites (BeginForeignModify, ExecForeignInsert/Update/Delete, ExecForeignBatchInsert, EndForeignModify).

Papers and standards

• Wiederhold, G. (1992). “Mediators in the Architecture of Future Information Systems.” IEEE Computer 25(3):38-49. The mediator–wrapper split PostgreSQL’s planner/FdwRoutine division descends from.
• Roth, M. T. & Schwarz, P. (1997). “Don’t Scrap It, Wrap It! A Wrapper Architecture for Legacy Data Sources.” VLDB ‘97. The Garlic capability-based query-planning model that GetForeignPaths / GetForeignJoinPaths realize.
• Garcia-Molina, H. et al. (1997). “The TSIMMIS Approach to Mediation: Data Models and Languages.” Journal of Intelligent Information Systems 8(2):117-132. Query-template / push-some-keep-some mediation, mirrored by the scan_clauses split and fdw_recheck_quals.
• Duggan, J. et al. (2015). “The BigDAWG Polystore System.” ACM SIGMOD Record 44(2):11-16. The polystore frontier PostgreSQL’s single-engine federation deliberately stops short of.
• ISO/IEC 9075-9: SQL/MED (Management of External Data), SQL:2003. The standard defining foreign-data wrapper, foreign server, user mapping, and foreign table — the catalog vocabulary PostgreSQL implements.
• Database System Concepts (Silberschatz, Korth, Sudarshan, 7e) — the data-integration and heterogeneous/distributed-database chapters (tightly- vs. loosely-coupled federation; optimization over autonomous sources). Captured under knowledge/research/dbms-general/.
• Database Internals (Petrov, 2019) — distributed-query and request-routing framing for the async-append / scatter-gather direction.

Sibling docs (cross-references — mechanism owned there, not duplicated here)

• postgres-planner-overview.md — the path/cost search a ForeignPath competes in; set_foreign_size / set_foreign_pathlist are the FDW entry points into it.
• postgres-executor.md — the ExecScan / Volcano tuple pipeline that ForeignNext and ForeignRecheck plug into.
• postgres-ddl-execution.md — the generic ProcessUtility / table-creation path that CreateForeignTable and ImportForeignSchema build on.
• postgres-extensions.md — how a wrapper ships as a loadable extension and registers its handler function.
• postgres-architecture-overview.md — Axis 4 (pluggable access methods), where the FDW vtable sits alongside TableAmRoutine / IndexAmRoutine as a function-pointer extensibility interface.