PostgreSQL Foreign Data Wrappers Tutorial

Master PostgreSQL Foreign Data Wrappers (FDW) to seamlessly query and integrate data from external sources including other databases, files, and web services.

What Are Foreign Data Wrappers?

Foreign Data Wrappers (FDW) implement the SQL/MED standard, allowing PostgreSQL to access external data sources as if they were local tables.

💡 Key Benefit: Query remote databases, CSV files, or web APIs using standard SQL without ETL processes.

Supported Data Sources

🔧 postgres_fdw: PostgreSQL to PostgreSQL

Installation

-- Install the extension
CREATE EXTENSION postgres_fdw;

-- Verify installation
SELECT * FROM pg_available_extensions WHERE name = 'postgres_fdw';

Step 1: Create Foreign Server

-- Define the remote PostgreSQL server
CREATE SERVER remote_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote.example.com',
        port '5432',
        dbname 'remote_db'
    );

-- View servers
SELECT * FROM pg_foreign_server;

Step 2: Create User Mapping

-- Map local user to remote credentials
CREATE USER MAPPING FOR current_user
    SERVER remote_server
    OPTIONS (
        user 'remote_user',
        password 'remote_password'
    );

-- For all users
CREATE USER MAPPING FOR PUBLIC
    SERVER remote_server
    OPTIONS (user 'remote_user', password 'remote_password');

-- View mappings
SELECT * FROM pg_user_mappings;

Step 3: Create Foreign Tables

-- Manual foreign table definition
CREATE FOREIGN TABLE remote_orders (
    id INTEGER,
    customer_id INTEGER,
    order_date DATE,
    total DECIMAL(10,2),
    status VARCHAR(50)
)
SERVER remote_server
OPTIONS (
    schema_name 'public',
    table_name 'orders'
);

-- Import entire schema automatically
IMPORT FOREIGN SCHEMA public
    FROM SERVER remote_server
    INTO local_schema;

-- Import specific tables
IMPORT FOREIGN SCHEMA public
    LIMIT TO (orders, customers, products)
    FROM SERVER remote_server
    INTO local_schema;

Step 4: Query Foreign Tables

-- Query as if local
SELECT * FROM remote_orders WHERE status = 'pending';

-- Join with local tables
SELECT
    l.customer_name,
    r.order_date,
    r.total
FROM local_customers l
JOIN remote_orders r ON l.id = r.customer_id;

-- Aggregations
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(total) AS revenue
FROM remote_orders
GROUP BY 1
ORDER BY 1;

📊 Performance Optimization

Connection Pooling

-- Use connection caching
ALTER SERVER remote_server
    OPTIONS (SET keep_connections 'on');

-- Set fetch size for large queries
ALTER SERVER remote_server
    OPTIONS (ADD fetch_size '10000');

Push Down Operations

postgres_fdw pushes operations to remote server when possible:

-- These operations are pushed down:
-- - WHERE clauses
-- - JOINs between foreign tables on same server
-- - LIMIT/OFFSET
-- - ORDER BY
-- - Aggregations (with options)

-- Enable aggregate pushdown
ALTER SERVER remote_server
    OPTIONS (ADD extensions 'postgres_fdw');

-- Enable parallel queries on foreign tables
ALTER FOREIGN TABLE remote_orders
    OPTIONS (ADD async_capable 'true');

Check Query Planning

-- Verify pushdown with EXPLAIN
EXPLAIN VERBOSE
SELECT * FROM remote_orders WHERE status = 'pending';

-- Look for "Remote SQL:" in output
EXPLAIN (ANALYZE, VERBOSE)
SELECT COUNT(*) FROM remote_orders WHERE order_date > '2025-01-01';

file_fdw: Query CSV Files

Setup

CREATE EXTENSION file_fdw;

CREATE SERVER file_server
    FOREIGN DATA WRAPPER file_fdw;

Create Foreign Table for CSV

-- Simple CSV
CREATE FOREIGN TABLE csv_data (
    id INTEGER,
    name TEXT,
    email TEXT,
    created_at DATE
)
SERVER file_server
OPTIONS (
    filename '/data/users.csv',
    format 'csv',
    header 'true'
);

-- Query the file
SELECT * FROM csv_data WHERE created_at > '2025-01-01';

-- With custom delimiter
CREATE FOREIGN TABLE tsv_data (
    col1 TEXT,
    col2 TEXT,
    col3 INTEGER
)
SERVER file_server
OPTIONS (
    filename '/data/data.tsv',
    format 'csv',
    delimiter E'\t',
    header 'true'
);

Program Output as Data Source

-- Read output from a command
CREATE FOREIGN TABLE system_processes (
    user_name TEXT,
    pid INTEGER,
    cpu NUMERIC,
    mem NUMERIC,
    command TEXT
)
SERVER file_server
OPTIONS (
    program 'ps aux | tail -n +2 | awk ''{print $1","$2","$3","$4","$11}''',
    format 'csv'
);

SELECT * FROM system_processes WHERE cpu > 1;

mysql_fdw: Connect to MySQL

Installation

# Install mysql_fdw extension
sudo apt install postgresql-16-mysql-fdw

Configuration

CREATE EXTENSION mysql_fdw;

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

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

CREATE FOREIGN TABLE mysql_customers (
    id INTEGER,
    name VARCHAR(100),
    email VARCHAR(255)
)
SERVER mysql_server
OPTIONS (
    dbname 'production',
    table_name 'customers'
);

🔧 Advanced FDW Patterns

Cross-Database Sharding

-- Connect to multiple shards
CREATE SERVER shard_1 FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'shard1.db.com', dbname 'app');

CREATE SERVER shard_2 FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'shard2.db.com', dbname 'app');

-- Import tables from each shard
IMPORT FOREIGN SCHEMA public LIMIT TO (orders)
    FROM SERVER shard_1 INTO shard1_schema;

IMPORT FOREIGN SCHEMA public LIMIT TO (orders)
    FROM SERVER shard_2 INTO shard2_schema;

-- Create unified view
CREATE VIEW all_orders AS
    SELECT * FROM shard1_schema.orders
    UNION ALL
    SELECT * FROM shard2_schema.orders;

Data Migration Pattern

-- Migrate data from foreign table to local
CREATE TABLE local_orders AS
SELECT * FROM remote_orders WHERE order_date >= '2025-01-01';

-- Incremental migration
INSERT INTO local_orders
SELECT * FROM remote_orders
WHERE order_date > (SELECT MAX(order_date) FROM local_orders);

Materialized Foreign Data

-- Cache foreign data locally
CREATE MATERIALIZED VIEW cached_products AS
SELECT * FROM remote_products;

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY cached_products;

-- Use for queries
SELECT * FROM cached_products WHERE category = 'Electronics';

📊 Monitoring Foreign Tables

Query Statistics

-- Monitor FDW connections
SELECT
    srvname AS server,
    numbackends AS connections,
    xact_commit AS transactions,
    blks_read,
    blks_hit
FROM pg_stat_database
WHERE datname = current_database();

-- Check foreign table sizes (estimated)
SELECT
    foreign_table_schema,
    foreign_table_name,
    pg_size_pretty(pg_relation_size(foreign_table_schema || '.' || foreign_table_name)) AS size
FROM information_schema.foreign_tables;

Connection Health

-- Test foreign server connectivity
DO $$
DECLARE
    result INTEGER;
BEGIN
    SELECT 1 INTO result FROM remote_orders LIMIT 1;
    RAISE NOTICE 'Connection successful';
EXCEPTION
    WHEN OTHERS THEN
        RAISE NOTICE 'Connection failed: %', SQLERRM;
END $$;

⚠️ Security Considerations

Secure Credential Storage

-- Avoid storing passwords in user mappings
-- Option 1: Use .pgpass file on server

-- Option 2: Use password file option
CREATE USER MAPPING FOR app_user
    SERVER remote_server
    OPTIONS (
        user 'remote_user',
        password_file '/secure/passwords/remote_server'
    );

Restrict Access

-- Grant access to specific users only
GRANT USAGE ON FOREIGN SERVER remote_server TO analyst_role;
GRANT SELECT ON FOREIGN TABLE remote_orders TO analyst_role;

-- Revoke from public
REVOKE ALL ON FOREIGN SERVER remote_server FROM PUBLIC;

SSL Configuration

-- Require SSL for foreign connections
CREATE SERVER secure_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (
        host 'remote.example.com',
        dbname 'secure_db',
        sslmode 'require',
        sslcert '/path/to/client.crt',
        sslkey '/path/to/client.key',
        sslrootcert '/path/to/ca.crt'
    );

✅ Best Practices

Performance Tips

1. Use updatable false for read-only tables:

   ALTER FOREIGN TABLE remote_table OPTIONS (ADD updatable 'false');
   

2. Increase fetch_size for large queries:

   ALTER SERVER remote_server OPTIONS (ADD fetch_size '50000');
   

3. Push down aggregations:

   ALTER SERVER remote_server OPTIONS (ADD extensions 'postgres_fdw');
   

4. Use EXPLAIN VERBOSE to verify pushdown

Design Guidelines

Common Use Cases

Use Case 1: Data Warehouse Integration

-- Query transactional DB from warehouse
CREATE FOREIGN TABLE source_orders (...)
SERVER oltp_server;

-- ETL using SQL
INSERT INTO warehouse.fact_orders
SELECT
    o.id,
    d.date_key,
    p.product_key,
    o.quantity,
    o.amount
FROM source_orders o
JOIN warehouse.dim_date d ON o.order_date = d.date_value
JOIN warehouse.dim_product p ON o.product_id = p.source_id;

Use Case 2: Legacy System Integration

-- Connect to Oracle legacy system
CREATE FOREIGN TABLE legacy_customers (...)
SERVER oracle_legacy;

-- Sync customers
MERGE INTO modern_customers m
USING legacy_customers l ON m.legacy_id = l.id
WHEN MATCHED THEN UPDATE SET name = l.name
WHEN NOT MATCHED THEN INSERT (legacy_id, name) VALUES (l.id, l.name);

Conclusion

PostgreSQL Foreign Data Wrappers provide powerful data federation capabilities, enabling seamless integration with external data sources. By understanding FDW configuration and optimization techniques, you can build flexible, integrated data architectures.

Key Takeaways

• ✅ Use postgres_fdw for PostgreSQL-to-PostgreSQL integration
• ✅ Leverage pushdown for remote query optimization
• ✅ Cache frequently accessed foreign data with materialized views
• ✅ Implement proper security with SSL and restricted access
• ✅ Monitor performance with EXPLAIN VERBOSE

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in PostgreSQL, SQL optimization, and enterprise database solutions.

Related Articles

• PostgreSQL Database Migration Strategies
• PostgreSQL Partitioning Best Practices
• PostgreSQL Performance Tuning Configuration

Need help with PostgreSQL optimization? Contact us for expert database consulting.