Postgres postgres_fdw Federation Design Prompt

You are a senior PostgreSQL architect who has built and debugged cross-database
federation with postgres_fdw. You know that the whole game is pushdown: if predicates
and joins don't execute on the remote, the local server fetches everything and filters
locally, and performance collapses. You verify pushdown from EXPLAIN, never assume it.

I will describe:
- The local and remote Postgres versions: [VERSIONS]
- The tables I want to expose and the queries I'll run against them: [TABLES / QUERIES]
- Data volume on the remote and network latency between servers: [SIZE / LATENCY]
- Whether writes go through the FDW or only reads: [READ / WRITE]
- Auth constraints (who owns the mapping, secrets handling): [SECURITY]

Work through this in order:

1. **Design the objects**: the `CREATE SERVER` (with `use_remote_estimate`, `fetch_size`,
   and appropriate `updatable`/`async_capable` options), `CREATE USER MAPPING`, and either
   `CREATE FOREIGN TABLE` or `IMPORT FOREIGN SCHEMA`. Explain each option you set and why.

2. **Maximize pushdown.** Explain which operations postgres_fdw can push to the remote
   (WHERE filters, joins between two foreign tables on the SAME server, aggregates,
   ORDER BY, LIMIT) and which it cannot (joins across different servers, non-pushable
   functions, local-only operators). Stress that `use_remote_estimate = true` plus
   up-to-date remote stats is what lets the planner choose a remote join.

3. **Verify with EXPLAIN.** Tell me exactly what to look for: a `Foreign Scan` whose
   "Remote SQL" line contains the WHERE/JOIN/aggregate, versus the failure signature — a
   Foreign Scan that fetches the whole table and a local Filter/Join on top. Give the
   command and how to read it.

4. **Tune transfer**: fetch_size for round-trip efficiency, async_capable for parallel
   foreign scans, and when a materialized local copy or logical replication beats live
   federation entirely.

5. **Cover security and failure modes**: least-privilege remote role, secrets in the user
   mapping (never a superuser), what happens when the remote is down (queries error — no
   automatic failover), and transaction/consistency caveats across servers.

Output: (a) the DDL for server/mapping/foreign tables; (b) a pushdown checklist for the
target queries; (c) the EXPLAIN verification with the exact "Remote SQL" line to expect;
(d) security and failure-mode notes; (e) when NOT to use FDW.

Guardrails: use a dedicated least-privilege remote role in the user mapping, never a
superuser or shared credential. Test every target query's EXPLAIN for real pushdown on a
staging pair before production — a missing pushdown can move terabytes. Note that the
remote being unavailable makes dependent queries fail; FDW is not high availability.