Foreign Data Wrappers, Foreign Tables, Pushdown, Transactions, and Federated Queries

Learning outcomes

ServiceHub must read partner dispatch records that live in another PostgreSQL database. Copying that data on every request creates stale replicas; giving the application a second database connection pushes federation logic into every service. A Foreign Data Wrapper (FDW) lets PostgreSQL expose external data as foreign tables, but network, authentication, remote optimizer, and cross-system transaction limits remain real.

1. Create the disposable partner database and least-privilege remote role

Run this administrative setup from the normal local cluster. PostgreSQL roles are cluster-wide, but table privileges remain database/object specific.

DROP DATABASE IF EXISTS servicehub_partner WITH (FORCE);DO $$BEGIN  IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'ch19_partner_fdw') THEN    CREATE ROLE ch19_partner_fdw LOGIN;  END IF;END$$;CREATE DATABASE servicehub_partner;

\password ch19_partner_fdw\connect servicehub_partnerCREATE SCHEMA partner;CREATE TABLE partner.dispatch (  dispatch_id bigint PRIMARY KEY,  region text NOT NULL,  status text NOT NULL,  scheduled_at timestamptz NOT NULL,  estimated_minutes integer NOT NULL CHECK (estimated_minutes >= 0));INSERT INTO partner.dispatchSELECT 190000 + g,       (ARRAY['north','south','east','west'])[(g % 4)+1],       (ARRAY['queued','assigned','closed'])[(g % 3)+1],       TIMESTAMPTZ '2026-08-18 06:00+00' + (g || ' minutes')::interval,       15 + (g % 120)FROM generate_series(1,5000) AS g;GRANT CONNECT ON DATABASE servicehub_partner TO ch19_partner_fdw;GRANT USAGE ON SCHEMA partner TO ch19_partner_fdw;GRANT SELECT, INSERT, UPDATE, DELETE ON partner.dispatch TO ch19_partner_fdw;ANALYZE partner.dispatch;\connect servicehub_lab

The password belongs only to this disposable lab. Do not put production credentials into lesson HTML, source control, application logs, or a shared SQL migration file.

2. Install postgres_fdw and define the remote server

postgres_fdw is a PostgreSQL-supplied extension. Unlike the trusted list used in Lesson 1, installing it normally requires an administrative/superuser path. After installation, ordinary roles can be granted controlled USAGE on a foreign server.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;DROP SERVER IF EXISTS ch19_partner_server CASCADE;CREATE SERVER ch19_partner_serverFOREIGN DATA WRAPPER postgres_fdwOPTIONS (  host '127.0.0.1',  port '55432',  dbname 'servicehub_partner');CREATE USER MAPPING FOR servicehub_ownerSERVER ch19_partner_serverOPTIONS (  user 'ch19_partner_fdw',  password '<LAB_PASSWORD>');GRANT USAGE ON FOREIGN SERVER ch19_partner_server TO servicehub_owner;

Substitute the password interactively when running the lab; do not preserve a real secret in the file. PostgreSQL restricts visibility of sensitive user-mapping options. Use pg_user_mappings for governance rather than querying the underlying catalog as an ordinary user.

3. Define a foreign table with an explicit schema contract

DROP FOREIGN TABLE IF EXISTS app.ch19_partner_dispatch;CREATE FOREIGN TABLE app.ch19_partner_dispatch (  dispatch_id bigint NOT NULL,  region text NOT NULL,  status text NOT NULL,  scheduled_at timestamptz NOT NULL,  estimated_minutes integer NOT NULL)SERVER ch19_partner_serverOPTIONS (  schema_name 'partner',  table_name 'dispatch');ALTER FOREIGN TABLE app.ch19_partner_dispatch OWNER TO servicehub_owner;

A foreign table stores no partner rows locally. Its column definition is a local contract used for parsing, planning, and conversion. postgres_fdw does not automatically validate every local/remote type mismatch at CREATE time, so schema drift can surface later at query execution.

4. Observe FDW metadata safely

SELECT s.srvname,       f.fdwname,       pg_get_userbyid(s.srvowner) AS server_owner,       s.srvoptionsFROM pg_foreign_server AS sJOIN pg_foreign_data_wrapper AS f ON f.oid = s.srvfdwWHERE s.srvname = 'ch19_partner_server';SELECT srvname, usename, umoptionsFROM pg_user_mappingsWHERE srvname = 'ch19_partner_server';SELECT c.oid::regclass AS foreign_table,       s.srvname,       ft.ftoptionsFROM pg_foreign_table AS ftJOIN pg_class AS c ON c.oid = ft.ftrelidJOIN pg_foreign_server AS s ON s.oid = ft.ftserverWHERE c.oid = 'app.ch19_partner_dispatch'::regclass;

The mapping view masks options when the current role lacks the rights to see them. Treat database catalog access as part of secret governance; do not build monitoring that indiscriminately exports user-mapping option arrays.

5. EXPLAIN VERBOSE shows what is actually sent remotely

EXPLAIN (VERBOSE, COSTS OFF)SELECT dispatch_id, scheduled_atFROM app.ch19_partner_dispatchWHERE region = 'north'  AND status = 'assigned'ORDER BY scheduled_atLIMIT 20;

Inspect the Remote SQL line. postgres_fdw attempts to ship safe immutable built-in conditions, projections, ordering, limits, joins, and aggregates when it can prove compatible semantics. The goal is to reduce rows/columns crossing the network.

EXPLAIN (VERBOSE, COSTS OFF)SELECT region, count(*), avg(estimated_minutes)FROM app.ch19_partner_dispatchWHERE status <> 'closed'GROUP BY regionORDER BY region;

If the aggregate is pushed down, the remote SQL contains grouping/aggregation instead of returning all qualifying base rows. If a local expression/function cannot be safely shipped, PostgreSQL can fetch rows and evaluate that piece locally.

6. Why some expressions remain local

EXPLAIN (VERBOSE, COSTS OFF)SELECT dispatch_id,       scheduled_at,       clock_timestamp() AS observed_locallyFROM app.ch19_partner_dispatchWHERE region = 'east'LIMIT 10;

Remote pushdown is correctness-sensitive. postgres_fdw avoids shipping arbitrary user functions/operators unless they are built-in or explicitly declared in the server's extensions option and meet immutability requirements. It cannot assume a function has identical definition/semantics on the other server merely because the name exists.

7. Remote transaction snapshots can surprise READ COMMITTED users

When a local transaction first touches a foreign server, postgres_fdw opens a corresponding remote transaction. Unless the local transaction is SERIALIZABLE, the remote transaction uses REPEATABLE READ, giving snapshot-consistent repeated scans on that remote server.

BEGIN;SELECT statusFROM app.ch19_partner_dispatchWHERE dispatch_id = 190001;-- Keep this transaction open.-- In another direct connection to servicehub_partner, update 190001 and COMMIT.SELECT statusFROM app.ch19_partner_dispatchWHERE dispatch_id = 190001;COMMIT;

The second foreign scan in the same local transaction can still see the earlier remote snapshot even if the direct partner session committed a change. This is deliberate consistency behavior, not stale connection caching.

8. Controlled remote failure

BEGIN;ALTER SERVER ch19_partner_serverOPTIONS (SET port '59999');SELECT count(*) FROM app.ch19_partner_dispatch;-- Expected: connection failure from postgres_fdw/libpq.ROLLBACK;SELECT count(*) FROM app.ch19_partner_dispatch;

The foreign query fails; PostgreSQL does not silently return an empty relation. The rollback restores server metadata. Applications using federation need timeouts, retry classification, circuit-breaking/partial-service decisions, and observability for remote connection wait events.

9. The cross-system transaction boundary

postgres_fdw mirrors local transactions and savepoints with remote transactions/subtransactions. But it does not support preparing the remote transaction for two-phase commit. With multiple remote systems, commit work can occur across them without a global prepared transaction that makes all final commits atomic under failures.

10. Verification and cleanup

SET ROLE servicehub_owner;SELECT count(*) AS partner_rowsFROM app.ch19_partner_dispatch;RESET ROLE;-- At the END of Chapter 19:-- DROP FOREIGN TABLE app.ch19_partner_dispatch;-- DROP USER MAPPING FOR servicehub_owner SERVER ch19_partner_server;-- DROP SERVER ch19_partner_server;-- DROP EXTENSION postgres_fdw;

Authoritative references

Extension, FDW, language, and server-code behavior is privilege-, package-, version-, and platform-sensitive. These PostgreSQL 18 primary sources define the mechanisms used here.

• PostgreSQL 18 — postgres_fdw
• PostgreSQL 18 — CREATE SERVER
• PostgreSQL 18 — CREATE USER MAPPING
• PostgreSQL 18 — CREATE FOREIGN TABLE
• PostgreSQL 18 — pg_user_mappings