PostgreSQL Foreign Data Wrappers: postgres_fdw, Remote Queries

Quick answer. postgres_fdw lets PostgreSQL query a remote PostgreSQL database as if it were a local table. You enable the extension, create a foreign server pointing at the remote host (always with sslmode 'require'), add a user mapping, then import foreign schemas to query read replicas, reporting, or legacy systems directly. Foreign Data Wrappers let PostgreSQL query data from external sources as if it were local tables — including other PostgreSQL instances, MySQL, SQLite, CSV files, and even REST APIs (via community FDWs). The most useful for SaaS teams is postgres_fdw: query a read replica, a reporting database, or a legacy system from your application database without application-level data wrangling.

postgres_fdw: Query a Remote PostgreSQL Database

-- Step 1: Install the extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Step 2: Create the foreign server (connection to remote PostgreSQL)
CREATE SERVER remote_analytics
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (
    host      'analytics-db.internal',
    port      '5432',
    dbname    'analytics',
    sslmode   'require'   -- Always use SSL for cross-database connections
  );

-- Step 3: Create user mapping (local role → remote credentials)
-- Never use superuser credentials here
CREATE USER MAPPING FOR app_user
  SERVER remote_analytics
  OPTIONS (
    user     'fdw_readonly',    -- Read-only role on the remote
    password 'strong_password'  -- Store in Vault or Secrets Manager in practice
  );

-- Step 4a: Import all tables from a schema (easiest approach)
IMPORT FOREIGN SCHEMA analytics_schema
  FROM SERVER remote_analytics
  INTO local_foreign;  -- Local schema to hold foreign table definitions

-- Step 4b: Import specific tables only
IMPORT FOREIGN SCHEMA public
  LIMIT TO (events, dau_rollup, pageviews)
  FROM SERVER remote_analytics
  INTO local_foreign;

-- Step 4c: Manually create a specific foreign table
CREATE FOREIGN TABLE local_foreign.remote_events (
  id           UUID NOT NULL,
  workspace_id UUID NOT NULL,
  name         TEXT NOT NULL,
  properties   JSONB,
  occurred_at  TIMESTAMPTZ NOT NULL
)
  SERVER remote_analytics
  OPTIONS (
    schema_name 'public',
    table_name  'events'
  );

Querying Foreign Tables

-- Query as if local — PostgreSQL handles the network round-trip
SELECT
  name,
  COUNT(*) AS event_count,
  COUNT(DISTINCT workspace_id) AS unique_workspaces
FROM local_foreign.remote_events
WHERE occurred_at >= NOW() - INTERVAL '7 days'
GROUP BY name
ORDER BY event_count DESC
LIMIT 10;

-- Cross-database JOIN: local users with remote events
-- Foreign table on left, local table on right (for best performance)
SELECT
  u.name,
  u.email,
  COUNT(e.id) AS events_last_7d
FROM local_foreign.remote_events e
JOIN users u ON u.id = e.user_id         -- users is a LOCAL table
WHERE e.occurred_at >= NOW() - INTERVAL '7 days'
  AND u.workspace_id = '550e8400-e29b-41d4-a716-446655440000'
GROUP BY u.id, u.name, u.email
ORDER BY events_last_7d DESC
LIMIT 20;

WHERE Pushdown: Making Queries Fast

-- postgres_fdw pushes WHERE clauses to the remote server when possible
-- Check EXPLAIN to verify pushdown is happening

EXPLAIN (VERBOSE, ANALYZE) SELECT *
FROM local_foreign.remote_events
WHERE workspace_id = '550e8400-e29b-41d4-a716-446655440000'
  AND occurred_at >= '2027-05-01';

-- Good output: "Remote SQL: SELECT ... FROM public.events WHERE ..."
-- ✅ WHERE pushed to remote — remote scans only matching rows
-- ❌ "Foreign Scan on remote_events" with no Remote SQL WHERE — fetches all rows locally

-- Force pushdown by ensuring column types match exactly
-- If local type doesn't match remote type, PostgreSQL can't push the predicate

-- Pushdown works for:
-- ✅ Equality: WHERE id = $1
-- ✅ Range: WHERE occurred_at BETWEEN $1 AND $2
-- ✅ LIKE: WHERE name LIKE 'project.%'
-- ✅ AND/OR combinations
-- ❌ Functions that differ between versions (some string functions)
-- ❌ User-defined functions (PostgreSQL can't guarantee they exist remotely)

Performance Tuning

-- Option 1: fetch_size — how many rows to fetch per round-trip
-- Default is 100 — increase for bulk reads
ALTER FOREIGN TABLE local_foreign.remote_events
  OPTIONS (ADD fetch_size '10000');

-- Option 2: use_remote_estimate — use remote table statistics for query planning
ALTER SERVER remote_analytics
  OPTIONS (ADD use_remote_estimate 'true');

-- Refresh remote statistics in local pg_class
ANALYZE local_foreign.remote_events;

-- Option 3: Async execution (PostgreSQL 14+)
-- Multiple foreign tables scanned in parallel
ALTER SERVER remote_analytics
  OPTIONS (ADD async_capable 'true');

-- Check FDW performance
SELECT
  relname,
  n_live_tup,
  last_analyze
FROM pg_stat_user_tables
WHERE schemaname = 'local_foreign';

file_fdw: Query CSV Files as Tables

-- Import a CSV file as a foreign table (great for one-time data imports)
CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER csv_files
  FOREIGN DATA WRAPPER file_fdw;

-- The CSV file must be accessible to the PostgreSQL server process
CREATE FOREIGN TABLE imported_customers (
  id           INTEGER,
  name         TEXT,
  email        TEXT,
  signup_date  TEXT,
  plan         TEXT
)
  SERVER csv_files
  OPTIONS (
    filename  '/tmp/customers-export.csv',
    format    'csv',
    header    'true',
    delimiter ','
  );

-- Query, transform, and insert into real tables
INSERT INTO customers (id, name, email, created_at, plan)
SELECT
  id,
  name,
  lower(trim(email)),          -- Clean up email
  signup_date::date,           -- Parse date string
  COALESCE(plan, 'free')       -- Default plan if NULL
FROM imported_customers
WHERE email IS NOT NULL
  AND email ~* '^[^@]+@[^@]+\.[^@]+$'  -- Basic email validation
ON CONFLICT (email) DO NOTHING;         -- Skip existing

SELECT COUNT(*) FROM imported_customers;  -- Count without importing

Materialized View Over Foreign Table

For frequently-read cross-database data, cache it locally with a materialized view:

-- Refresh the local copy hourly instead of round-tripping on every query
CREATE MATERIALIZED VIEW local_foreign.events_summary AS
SELECT
  DATE(occurred_at AT TIME ZONE 'UTC') AS event_date,
  workspace_id,
  name AS event_name,
  COUNT(*) AS event_count
FROM local_foreign.remote_events
WHERE occurred_at >= NOW() - INTERVAL '90 days'
GROUP BY 1, 2, 3
WITH DATA;

CREATE UNIQUE INDEX ON local_foreign.events_summary
  (event_date, workspace_id, event_name);

-- Refresh concurrently (non-blocking, requires unique index):
REFRESH MATERIALIZED VIEW CONCURRENTLY local_foreign.events_summary;

-- Schedule with pg_cron or an external cron job:
-- SELECT cron.schedule('0 * * * *', 'REFRESH MATERIALIZED VIEW CONCURRENTLY local_foreign.events_summary');

Security: Read-Only Remote Role

-- On the REMOTE database: create a minimal-privilege role for FDW access
CREATE ROLE fdw_readonly WITH LOGIN PASSWORD 'strong_password';

-- Grant SELECT only on the specific tables needed
GRANT CONNECT ON DATABASE analytics TO fdw_readonly;
GRANT USAGE   ON SCHEMA public TO fdw_readonly;
GRANT SELECT  ON TABLE public.events, public.dau_rollup TO fdw_readonly;

-- REVOKE everything else (belt-and-suspenders)
REVOKE CREATE ON SCHEMA public FROM fdw_readonly;

-- On the LOCAL database: restrict who can query foreign tables
GRANT USAGE  ON SCHEMA local_foreign TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA local_foreign TO app_user;
-- Revoke from all others:
REVOKE ALL ON SCHEMA local_foreign FROM PUBLIC;

When NOT to Use FDW

Cost Analysis

Related Topics

• PostgreSQL Logical Decoding and CDC
• PostgreSQL Materialized Views
• PostgreSQL Partitioning Advanced Patterns
• SaaS Usage Analytics
• AWS OpenSearch Analytics

---

What We Bring to the Table

Foreign data wrappers eliminate the application-layer glue code needed to join data across databases. Our team sets up postgres_fdw with read-only remote roles (never app superuser credentials), IMPORT FOREIGN SCHEMA for automatic table discovery, and verifies WHERE pushdown via EXPLAIN before deploying. For frequently-accessed cross-database data, we layer a materialized view on top with CONCURRENTLY refresh scheduled via pg_cron.

What we deliver:

• postgres_fdw extension + foreign server with sslmode=require
• Read-only fdw_readonly role on remote: GRANT SELECT on specific tables
• CREATE USER MAPPING with credentials (Secrets Manager integration)
• IMPORT FOREIGN SCHEMA LIMIT TO for selective table import
• EXPLAIN VERBOSE verification of WHERE clause pushdown
• fetch_size=10000 and use_remote_estimate=true performance tuning
• Materialized view over foreign table with REFRESH CONCURRENTLY + pg_cron schedule
• file_fdw CSV import pattern with email validation + ON CONFLICT DO NOTHING

Talk to our team about your multi-database architecture →

Or explore our cloud and data engineering services.