-- 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%';
