postgresql fdw foreign-data-wrapper data-integration federation

Foreign Data Wrappers for external data access

Maria Garcia Feb 2026

2 tabs

-- Install postgres_fdw extension
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

-- Create foreign server
CREATE SERVER remote_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (
    host 'remote-host.example.com',
    port '5432',
    dbname 'remote_database'
  );

-- Create user mapping
CREATE USER MAPPING FOR current_user
  SERVER remote_db
  OPTIONS (
    user 'remote_user',
    password 'remote_password'
  );

-- Import foreign schema
IMPORT FOREIGN SCHEMA public
  LIMIT TO (users, orders)
  FROM SERVER remote_db
  INTO public;

-- Query foreign table
SELECT * FROM users WHERE created_at >= '2024-01-01';
-- Query executes on remote server

-- Create specific foreign table
CREATE FOREIGN TABLE remote_products (
  id INT,
  name VARCHAR(100),
  price DECIMAL(10,2),
  created_at TIMESTAMP
)
SERVER remote_db
OPTIONS (
  schema_name 'public',
  table_name 'products'
);

-- Join local and remote tables
SELECT
  l.username,
  COUNT(r.id) AS order_count
FROM local_users l
LEFT JOIN remote_orders r ON l.id = r.user_id
GROUP BY l.username;

-- File FDW (query CSV files)
CREATE EXTENSION file_fdw;

CREATE SERVER file_server
  FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE csv_import (
  id INT,
  name TEXT,
  email TEXT,
  created_at TEXT
)
SERVER file_server
OPTIONS (
  filename '/data/users.csv',
  format 'csv',
  header 'true'
);

SELECT * FROM csv_import LIMIT 10;

-- Import CSV data to local table
INSERT INTO users (id, name, email, created_at)
SELECT
  id,
  name,
  email,
  created_at::TIMESTAMP
FROM csv_import;

-- Writable foreign tables
UPDATE remote_products
SET price = price * 1.1
WHERE category = 'electronics';

INSERT INTO remote_products (name, price)
VALUES ('New Product', 99.99);

-- Transaction limitations
BEGIN;
UPDATE local_table SET status = 'processed';
UPDATE remote_table SET status = 'processed';
COMMIT;
-- If remote fails, local changes still committed!

-- Better: Use 2-phase commit (if supported)
-- Or handle in application logic

-- MySQL FDW (requires mysql_fdw extension)
/*
CREATE EXTENSION mysql_fdw;

CREATE SERVER mysql_server
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host 'mysql-host', port '3306');

CREATE USER MAPPING FOR current_user
  SERVER mysql_server
  OPTIONS (username 'mysql_user', password 'password');

CREATE FOREIGN TABLE mysql_products (
  id INT,
  name TEXT,
  price NUMERIC
)
SERVER mysql_server
OPTIONS (dbname 'mydb', table_name 'products');

SELECT * FROM mysql_products;
*/

-- Predicate pushdown (WHERE clause sent to remote)
EXPLAIN VERBOSE
SELECT * FROM remote_users
WHERE status = 'active'
  AND created_at >= '2024-01-01';
-- WHERE clause pushed to remote server

-- Without pushdown (fetches all rows)
SELECT * FROM remote_users
WHERE complex_function(status) = true;
-- complex_function() prevents pushdown

-- Data federation (query multiple sources)

-- Server 1: Customer database
CREATE SERVER customer_db FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'customers.example.com', dbname 'customers');

-- Server 2: Orders database
CREATE SERVER order_db FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'orders.example.com', dbname 'orders');

-- Server 3: Inventory database
CREATE SERVER inventory_db FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'inventory.example.com', dbname 'inventory');

CREATE USER MAPPING FOR current_user SERVER customer_db
  OPTIONS (user 'reader', password 'pass');
CREATE USER MAPPING FOR current_user SERVER order_db
  OPTIONS (user 'reader', password 'pass');
CREATE USER MAPPING FOR current_user SERVER inventory_db
  OPTIONS (user 'reader', password 'pass');

-- Import schemas
IMPORT FOREIGN SCHEMA public FROM SERVER customer_db INTO customer;
IMPORT FOREIGN SCHEMA public FROM SERVER order_db INTO orders;
IMPORT FOREIGN SCHEMA public FROM SERVER inventory_db INTO inventory;

-- Federated query across all systems
SELECT
  c.customer_name,
  o.order_date,
  o.total,
  i.stock_quantity
FROM customer.customers c
JOIN orders.orders o ON c.id = o.customer_id
JOIN orders.order_items oi ON o.id = oi.order_id
JOIN inventory.products i ON oi.product_id = i.id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '7 days';

-- Materialized view over foreign tables (cache)
CREATE MATERIALIZED VIEW customer_summary AS
SELECT
  c.id,
  c.customer_name,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_revenue
FROM customer.customers c
LEFT JOIN orders.orders o ON c.id = o.customer_id
GROUP BY c.id, c.customer_name;

CREATE INDEX idx_customer_summary_id ON customer_summary(id);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY customer_summary;

-- Read-only replica via FDW
CREATE SERVER read_replica FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'replica.example.com', dbname 'mydb');

CREATE USER MAPPING FOR current_user SERVER read_replica
  OPTIONS (user 'readonly', password 'pass');

-- Route expensive queries to replica
IMPORT FOREIGN SCHEMA public
  LIMIT TO (large_table)
  FROM SERVER read_replica
  INTO replica;

SELECT * FROM replica.large_table
WHERE complex_condition = true;

-- Multi-database aggregation
CREATE VIEW global_sales AS
SELECT 'US' AS region, * FROM us_db.sales
UNION ALL
SELECT 'EU' AS region, * FROM eu_db.sales
UNION ALL
SELECT 'ASIA' AS region, * FROM asia_db.sales;

SELECT
  region,
  DATE_TRUNC('month', sale_date) AS month,
  SUM(amount) AS total_sales
FROM global_sales
GROUP BY region, month;

-- Legacy system integration
-- Query old system without migration
CREATE SERVER legacy_system FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'legacy.internal', dbname 'legacy_db');

CREATE FOREIGN TABLE legacy_customers (
  customer_id INT,
  customer_name VARCHAR(100),
  legacy_data XML  -- Old system uses XML
)
SERVER legacy_system
OPTIONS (table_name 'customers');

-- Gradually migrate data
INSERT INTO new_customers (id, name, migrated_from)
SELECT customer_id, customer_name, 'legacy'
FROM legacy_customers
WHERE customer_id NOT IN (SELECT id FROM new_customers);

-- Query multiple CSV files
CREATE FOREIGN TABLE sales_2023 (...)
SERVER file_server
OPTIONS (filename '/data/sales_2023.csv', format 'csv');

CREATE FOREIGN TABLE sales_2024 (...)
SERVER file_server
OPTIONS (filename '/data/sales_2024.csv', format 'csv');

CREATE VIEW all_sales AS
SELECT * FROM sales_2023
UNION ALL
SELECT * FROM sales_2024;

-- Performance tuning
-- fetch_size: Rows fetched per round trip
ALTER SERVER remote_db OPTIONS (ADD fetch_size '10000');

-- use_remote_estimate: Use remote statistics
ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');

-- View FDW statistics
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE tablename LIKE '%foreign%';

2 files · sql Explain with highlit

Foreign Data Wrappers (FDW) query external data sources as tables. I use postgresfdw for remote PostgreSQL databases. filefdw reads CSV files. mysql_fdw connects to MySQL. Understanding FDW limitations prevents surprises. Predicates push down to remote servers when possible. JOINs between foreign tables can be slow. Transactions don't span FDW boundaries. Proper FDW use federates data without ETL. Essential for data integration, legacy systems, microservices. PostgreSQL FDW enables SQL queries across heterogeneous data sources.