PostgreSQL Foreign Data Wrappers: postgres_fdw, Remote Queries, and Multi-Database Joins

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 and Timeline Estimates

See Also

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

---

Working With Viprasol

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.