Executing Federated Queries Across Multiple PostgreSQL Instances

This guide walks through configuring postgres_fdw to execute federated queries across multiple PostgreSQL nodes, as part of a broader federated query execution strategy within Cross-Partition Querying & Aggregation Strategies.

Prerequisites

Before enabling postgres_fdw, verify each of the following items on both the coordinator and every remote node:

• PostgreSQL 14 or later on all participating instances (SELECT version();)
• TCP port 5432 open bidirectionally between coordinator and each remote node; test with pg_isready -h <remote-ip> -p 5432
• pg_hba.conf on each remote node permits scram-sha-256 connections from the coordinator’s IP — the trust method must not be used in production
• A read-only database role exists on each remote node (e.g. app_reader) with CONNECT and SELECT privileges on the target tables
• max_connections on remote nodes is high enough to absorb FDW multiplexing overhead (each coordinator query may open one connection per remote table accessed)
• Superuser or pg_extension_owner role on the coordinator to install the extension
• Secrets for remote credentials stored in a secrets manager or environment variable — never in plaintext SQL scripts

Step 1 — Install the Extension and Define Remote Servers

Enable postgres_fdw on the coordinator and declare each remote node as a named foreign server.

-- Requires superuser or pg_extension_owner on the coordinator
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Remote node A: analytics database
CREATE SERVER remote_analytics
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (
    host '10.0.2.15',
    port '5432',
    dbname 'analytics_db',
    -- Allow the planner to fetch cost estimates from the remote node
    use_remote_estimate 'true'
  );

-- Remote node B: inventory database
CREATE SERVER remote_inventory
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (
    host '10.0.3.22',
    port '5432',
    dbname 'inventory_db',
    use_remote_estimate 'true'
  );

SRE tip. Name foreign servers after their logical role (remote_analytics, remote_inventory) rather than their IP. When nodes are replaced, you update a single ALTER SERVER … OPTIONS (SET host …) instead of hunting for every reference.

Step 2 — Map Local Roles to Remote Credentials

Create a user mapping for each coordinator role that will access remote data. Credentials are stored in the PostgreSQL catalog encrypted at rest; the connection itself should use TLS (sslmode=require or verify-full).

-- Map the local application role to remote credentials on node A
CREATE USER MAPPING FOR app_role
  SERVER remote_analytics
  OPTIONS (
    user 'app_reader',
    password 'REDACTED'       -- inject from secrets manager at deploy time
  );

-- Same for node B
CREATE USER MAPPING FOR app_role
  SERVER remote_inventory
  OPTIONS (
    user 'app_reader',
    password 'REDACTED'
  );

Operational note. Rotate remote passwords by running ALTER USER MAPPING FOR app_role SERVER remote_analytics OPTIONS (SET password '...'). Existing idle FDW connections are closed and re-authenticated on next use — no restart required.

Step 3 — Import Foreign Schemas and Create Foreign Tables

Bulk-import the remote schema so the coordinator’s planner sees accurate column types and can push predicates down to the correct remote object.

-- Import all tables from the remote public schema into a local fdw schema
CREATE SCHEMA IF NOT EXISTS fdw;

IMPORT FOREIGN SCHEMA public
  LIMIT TO (orders, order_items)
  FROM SERVER remote_analytics
  INTO fdw;

IMPORT FOREIGN SCHEMA public
  LIMIT TO (products, warehouses)
  FROM SERVER remote_inventory
  INTO fdw;

For tables not imported via IMPORT FOREIGN SCHEMA, declare them manually with exact type parity — type mismatches force local materialisation and bypass remote index scans:

CREATE FOREIGN TABLE fdw.remote_orders (
  id          BIGINT      NOT NULL,
  customer_id INT         NOT NULL,
  total       NUMERIC(12,2),
  region      TEXT,
  created_at  TIMESTAMPTZ
)
SERVER remote_analytics
OPTIONS (schema_name 'public', table_name 'orders', fetch_size '5000');

SRE tip. Set fetch_size to 5000–10000 for analytical workloads. The default of 100 causes hundreds of round-trips for large result sets, making every cross-node aggregation artificially slow.

Step 4 — Tune Pushdown and Verify the Execution Plan

Predicate pushdown is the single biggest performance lever: with it, the remote node applies WHERE filters and aggregations against local indexes before sending rows across the network. Without it, the coordinator pulls entire tables locally.

EXPLAIN (ANALYZE, VERBOSE, FORMAT TEXT)
SELECT
  o.region,
  COUNT(*)           AS order_count,
  SUM(o.total)       AS revenue
FROM fdw.remote_orders o
WHERE o.created_at >= '2025-01-01'
  AND o.region      = 'us-east'
GROUP BY o.region;

In the EXPLAIN output, look for a Foreign Scan node that contains a Remote SQL block:

Foreign Scan on fdw.remote_orders
  Output: region, total, created_at
  Remote SQL: SELECT region, total, created_at
              FROM public.orders
              WHERE ((created_at >= '2025-01-01')) AND (region = 'us-east')

If the WHERE clause appears only in a local Filter node above the Foreign Scan, pushdown has failed. Common causes: stale remote statistics (fix: ANALYZE fdw.remote_orders;), type mismatch between local column definition and remote schema, or use_remote_estimate not set on the server.

Operational note. Enable use_remote_estimate 'true' on every foreign server so the planner uses actual remote row counts rather than the default estimate of 1000 rows. This is the single most impactful setting for pushdown quality on large remote tables.

Step 5 — Route Connections Through a Pooler

Direct postgres_fdw connections bypass connection poolers and can rapidly exhaust max_connections on remote nodes — each coordinator query that touches a foreign table opens a new backend process on the remote. Route through proxy routing architectures such as PgBouncer to pool FDW traffic.

Configure PgBouncer on each remote node in transaction pool mode:

[databases]
analytics_db = host=127.0.0.1 port=5432 dbname=analytics_db

[pgbouncer]
pool_mode         = transaction
max_client_conn   = 500
default_pool_size = 25
server_idle_timeout = 30
tcp_keepalive     = 1
tcp_keepidle      = 60
tcp_keepintvl     = 10

Update the foreign server definition to point at PgBouncer’s port (typically 6432):

ALTER SERVER remote_analytics OPTIONS (SET port '6432');

SRE tip. transaction pool mode is required for FDW connections because postgres_fdw does not use PostgreSQL protocol-level session state between queries. session mode wastes a pooler slot for the lifetime of the coordinator session, eliminating the pooling benefit.

Step 6 — Implement Fallback Routing for High Availability

When a remote node is degraded, circuit-break at the application layer rather than queuing indefinitely. Combine statement_timeout on the coordinator with a read-replica fallback:

-- Set a hard timeout for FDW sub-queries on this session
SET LOCAL statement_timeout = '8s';

-- Primary federated query
SELECT o.region, SUM(o.total)
FROM fdw.remote_orders o
WHERE o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY o.region;

In the application layer, catch timeout exceptions and reroute to a local cross-shard aggregation over cached materialised views:

import psycopg2

def revenue_by_region(conn, fallback_conn):
    try:
        with conn.cursor() as cur:
            cur.execute("SET LOCAL statement_timeout = '8s'")
            cur.execute("""
                SELECT region, SUM(total)
                FROM fdw.remote_orders
                WHERE created_at >= NOW() - INTERVAL '30 days'
                GROUP BY region
            """)
            return cur.fetchall()
    except psycopg2.errors.QueryCanceled:
        # Remote node timed out — serve from cached materialised view
        with fallback_conn.cursor() as cur:
            cur.execute("""
                SELECT region, revenue_sum
                FROM reporting.revenue_by_region_cache
                WHERE snapshot_date = CURRENT_DATE
            """)
            return cur.fetchall()

Operational note. Materialise fallback views nightly (or hourly for near-real-time workloads) using REFRESH MATERIALIZED VIEW CONCURRENTLY so the fallback path always has fresh-enough data without blocking reads.

Verification

Confirm the full setup is functional before enabling production traffic:

-- 1. Verify connectivity: a single row fetch exercises the full auth + network path
SELECT 1 FROM fdw.remote_orders LIMIT 1;

-- 2. Confirm remote statistics are current
SELECT
  relname       AS foreign_table,
  last_analyze,
  n_live_tup    AS estimated_rows
FROM pg_stat_user_tables
WHERE relname IN ('remote_orders', 'remote_orders')
ORDER BY last_analyze NULLS FIRST;

-- 3. Check active FDW connections on the remote node (run on remote_analytics)
SELECT pid, usename, application_name, client_addr, state, query_start
FROM pg_stat_activity
WHERE application_name LIKE '%fdw%'
   OR client_addr = '<coordinator-ip>'
ORDER BY query_start;

Expected output for check 1: a single row 1 with no errors. Expected output for check 2: last_analyze within the past 24 hours and estimated_rows reflecting actual table size. If last_analyze is NULL, run ANALYZE fdw.remote_orders; immediately.

Failure Mode Table

FAQ

Related

• Federated Query Execution — parent section covering coordinator architecture, pushdown strategies, and result merging across heterogeneous nodes
• Cross-Partition Querying & Aggregation Strategies — the top-level guide to all cross-node query patterns on this site
• Proxy Routing Architectures — how to pool and route FDW traffic through PgBouncer and ProxySQL to avoid connection exhaustion