LLM Best Practices

Foreign Data Wrapper

2 min read

View markdown source

Definition

A Foreign Data Wrapper (FDW) is a set of C hooks defined by the SQL/MED standard and implemented in PostgreSQL extensions. An FDW wraps an external data source, presenting it as one or more foreign tables that appear in SQL queries like local tables. The FDW layer translates SQL operations into the native API of the remote source.

Common FDWs:

  • postgres_fdw: queries another PostgreSQL instance. Supports predicate pushdown (WHERE and JOIN clauses are sent to the remote).
  • file_fdw: reads CSV or plain text files as tables.
  • mysql_fdw, sqlite_fdw: query MySQL or SQLite.
  • wrappers (Supabase): connects to Stripe, S3, Clickhouse, and others.

Performance considerations: predicate pushdown is critical. Without it, the entire remote table is transferred to the local instance for local filtering. Check EXPLAIN to confirm Foreign Scan nodes show pushed-down predicates. Aggregations and joins are not always pushed down; fetch only needed columns.

FDWs are not a replacement for ETL into a local table when query performance or data availability is important. Use FDWs for ad-hoc federation or low-volume lookups; replicate or COPY for high-volume analytics.

When it applies

Use postgres_fdw to federate queries across shards or microservice databases without application-level joins. Use file_fdw to ingest CSV data directly into SQL without a COPY import step during exploration. Avoid FDWs for latency-sensitive queries; every FDW row fetch crosses a network or file I/O boundary.

Example

CREATE EXTENSION postgres_fdw;
 
CREATE SERVER orders_db
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'orders.internal', dbname 'orders', port '5432');
 
CREATE USER MAPPING FOR current_user
  SERVER orders_db
  OPTIONS (user 'reader', password 's3cr3t');
 
IMPORT FOREIGN SCHEMA public
  LIMIT TO (orders) FROM SERVER orders_db INTO local_schema;
 
SELECT * FROM local_schema.orders WHERE user_id = 42;
  • query-plan - check EXPLAIN to confirm predicate pushdown to the remote source.
  • sequential-scan - without pushdown, the FDW performs a full remote scan.
  • foreign-key - different concept: foreign keys enforce referential integrity within a database.
  • postgres - the Postgres deep-dive.

Citing this term

See Foreign Data Wrapper (llmbestpractices.com/glossary/foreign-data-wrapper).

Graph View

Backlinks