PostgreSQL Foreign Data Wrappers: Query Remote Databases

Tip: postgres_fdw for Remote PostgreSQL

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'remote.host', dbname 'remotedb');
CREATE USER MAPPING FOR current_user SERVER remote_server
    OPTIONS (user 'remote_user', password 'secret');

Gotcha: FDW Queries Are Slow

Data is fetched over the network. Push filters to the remote server with WHERE clauses.

Tip: Import Remote Schema

IMPORT FOREIGN SCHEMA public FROM SERVER remote_server INTO local_schema;

Auto-creates foreign tables for all remote tables.

Gotcha: No Pushdown for All Operations

Complex queries with subqueries may not be pushed to the remote server. Check with EXPLAIN.

Tip: file_fdw for CSV Files

CREATE EXTENSION file_fdw;
CREATE FOREIGN TABLE csv_data (...) SERVER file_server
    OPTIONS (filename '/data/import.csv', format 'csv');

Query CSV files as if they were tables.

Gotcha: Foreign Tables Are Read-Only

Most FDWs don't support writes. postgres_fdw does, but with limitations.

Tip: Partial Indexes Are Underutilized

CREATE INDEX ON orders (status) WHERE status = 'pending' creates a tiny index that covers only the rows your query needs. It's faster to scan and cheaper to maintain than a full-column index.

Tip: work_mem Is Per Operation, Not Per Query

Setting work_mem = 64MB means each sort, hash join, or aggregate gets 64MB. A query with 4 sort operations uses 256MB. Monitor temp_files to tune this.

Gotcha: NULL Sorting Is Non-Obvious

By default, NULLs sort AFTER non-null values in ascending order. ORDER BY col DESC puts NULLs FIRST. Use NULLS LAST or NULLS FIRST to be explicit.

Senior Insight

Foreign Data Wrappers (FDW) let PostgreSQL query external databases as if they were local tables. I've used postgres_fdw to join data across PostgreSQL instances, and mysql_fdw to migrate from MySQL. The performance of FDW queries is worse than local queries because data must be transferred over the network. FDW is excellent for reads and ETL; I don't recommend it for transactional writes.

Source: pganalyze Blog (https://pganalyze.com/blog), PostgreSQL Docs (https://www.postgresql.org/docs/current/), Crunchy Data Blog (https://www.crunchydata.com/blog)