Cross-Database Queries & Foreign Data Wrappers

Query remote databases, CSV files, and external systems using Foreign Data Wrappers and linked servers.

✅ What You'll Learn

• PostgreSQL Foreign Data Wrappers (FDW)
• SQL Server Linked Servers and 4-part naming
• Querying CSV files with file_fdw
• Cross-database JOIN and performance considerations

🌐 Why Cross-Database Queries?

🎯 Real-World Analogy: Imagine you work at a company with offices in London, New York, and Tokyo. Each office has its own filing cabinet. Cross-database queries let you search all three filing cabinets from your desk in London — without flying to each office.

In real systems, data lives in different databases: orders in PostgreSQL, analytics in ClickHouse, logs in Elasticsearch, user data in MySQL. FDWs let you JOIN across all of them with standard SQL.

⚠️ Performance Warning: Cross-database queries pull data over the network. Always filter early (push WHERE clauses to the remote side) and avoid pulling millions of rows across databases.

Try It: PostgreSQL Foreign Data Wrappers

Set up and query a remote PostgreSQL database as local tables

Try it Yourself »

SQL

-- PostgreSQL Foreign Data Wrappers (FDW)
-- Query remote databases as if they were local tables

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

-- Step 2: Create a foreign server
CREATE SERVER remote_analytics
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'analytics-db.example.com',
             port '5432',
             dbname 'analytics');

-- Step 3: Map local user to remote user
CREATE USER MAPPING FOR current_user
    SERVER remote_analytics
    OP
...

Try It: Linked Servers & dblink

Query across SQL Server instances and use dblink for ad-hoc queries

Try it Yourself »

SQL

-- SQL Server: Linked Servers
-- Query across SQL Server instances

-- Create a linked server
-- EXEC sp_addlinkedserver
--     @server = 'ANALYTICS_SERVER',
--     @srvproduct = '',
--     @provider = 'SQLNCLI',
--     @datasrc = 'analytics-db.example.com';

-- Query using 4-part naming:
-- [Server].[Database].[Schema].[Table]
SELECT
    o.order_id,
    o.total,
    c.campaign_name
FROM local_db.dbo.orders o
JOIN [ANALYTICS_SERVER].marketing_db.dbo.campaigns c
    ON o.campaign_id = c.campaign_
...

Try It: Query CSV Files with SQL

Use file_fdw to query CSV files directly with SQL statements

Try it Yourself »

SQL

-- Foreign Data Wrappers for files (CSV, JSON)
-- Query CSV files directly with SQL!

CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER csv_files FOREIGN DATA WRAPPER file_fdw;

-- Create a foreign table pointing to a CSV file
CREATE FOREIGN TABLE import_log (
    timestamp TEXT,
    level TEXT,
    message TEXT,
    source TEXT
) SERVER csv_files
OPTIONS (
    filename '/var/log/app/import.csv',
    format 'csv',
    header 'true',
    delimiter ','
);

-- Query the CSV like a regular tab
...

📋 Quick Reference

Method	Database	Best For
postgres_fdw	PostgreSQL	Remote PostgreSQL tables
Linked Servers	SQL Server	Cross-instance queries
dblink	PostgreSQL	Ad-hoc remote queries
file_fdw	PostgreSQL	CSV/log file queries
FEDERATED	MySQL	Remote MySQL tables

🎉 Lesson Complete!

You can now query across databases and file systems! Next, learn about performance testing and benchmarking.

Cross-Database Queries & Foreign Data Wrappers

✅ What You'll Learn

🌐 Why Cross-Database Queries?

Try It: PostgreSQL Foreign Data Wrappers

Try It: Linked Servers & dblink

Try It: Query CSV Files with SQL

📋 Quick Reference

🎉 Lesson Complete!

Cookie & Privacy Settings