Postgres Foreign Data Wrappers: Querying Other Databases as If They Were Tables

Most application teams treat the database as a closed boundary: data lives in one Postgres instance, the application talks to that instance, and any cross-database operation happens at the application layer through separate connections. Foreign Data Wrappers (FDWs) invert this assumption. They let one Postgres instance expose another data source—a remote Postgres database, a MySQL server, a file on disk, a REST endpoint, a Redis key-value store—as a virtual table that can be joined, filtered, and aggregated using normal SQL. The feature is useful, well-implemented, and routinely misused.

What FDWs actually do

The SQL/MED standard (Management of External Data) was adopted by Postgres in version 9.1, with the postgres_fdw and file_fdw extensions shipping in the base distribution. A foreign table looks and behaves like a regular table for most read operations: SELECT, JOIN, WHERE, ORDER BY, LIMIT all work. The planner pushes down filters and projections to the remote side when possible, and the executor pulls rows back through the FDW interface.

The setup involves three objects: a foreign server defining the connection target, a user mapping holding credentials, and one or more foreign tables defining the remote schema. Once configured, the foreign tables behave like local tables in queries but execute against the remote source.

Postgres 9.3 added the ability to execute joins on the foreign side when both tables come from the same server, which dramatically improved performance for cross-table queries against another Postgres instance. Postgres 10 added aggregate pushdown. Postgres 14 added bulk insert for postgres_fdw. The feature has matured into a production-grade tool over a decade of incremental work.

Where FDWs fit

The case where FDWs shine is cross-database querying without ETL infrastructure. A reporting database needs to join customer data from one production database against billing data from another, both running Postgres on separate instances. Without FDWs, the team either runs a periodic ETL job to consolidate the data into a third database, or builds application-layer aggregation logic that pulls from both sources and combines in memory. With FDWs, the reporting database defines foreign tables pointing at both production sources, and SQL JOIN does the work directly.

The second case is heterogeneous database integration. A team migrating from MySQL to Postgres might run both in parallel for months, with the Postgres side exposing the MySQL data via mysql_fdw for application code that has already migrated. The team can deprecate the MySQL connection from the application side while still running queries against MySQL data through the Postgres front door.

The third case is legacy data exposure. CSV files dropped onto a server by external partners can be exposed via file_fdw as virtual tables and queried with SQL. The setup is simpler than building an ingestion pipeline for occasional ad-hoc queries against fresh files.

The fourth case is sharding facade. If an application has been sharded across multiple Postgres instances by tenant, a coordinator instance can expose all shards via postgres_fdw foreign tables and present a unified read view for cross-shard queries. The pattern is used by some commercial database vendors (Citus, for example, was originally built on this foundation) and works well when the cross-shard query pattern is reporting-style rather than transactional.

The performance gotchas

The optimizer is smart but not magic. The planner pushes down what it can prove is safe to push, but a surprising number of operations stay on the local side.

Functions that the remote side does not have or that have different semantics are not pushed down. A WHERE clause involving a function the planner cannot vouch for stays local, which means the entire foreign table is pulled across the wire and filtered locally. Custom functions, user-defined types, and locale-dependent operations are common offenders.

Joins between local and foreign tables are not pushed; they execute locally. The planner pulls the foreign rows back to the local instance and performs the join there, which is fine for small foreign result sets and disastrous for large ones.

Aggregation pushdown is supported in modern Postgres but has limits. Complex aggregates involving multiple columns, custom aggregate functions, or HAVING clauses with subqueries may stay local.

The diagnostic for FDW queries is EXPLAIN with the VERBOSE option, which shows the remote SQL the planner intends to send. The remote SQL is usually shorter than expected when pushdown is failing, and the local plan shows the filter and aggregation steps running locally on the pulled rows.

The connection management trap

postgres_fdw uses a persistent connection pool per local backend. Each backend connection in the local Postgres opens its own remote connection on first use of the foreign table, and the connection stays open for the lifetime of the backend. This means a local connection pool of 50 connections translates to potentially 50 remote connections, multiplied by the number of distinct foreign servers that backend touches.

The default behavior is correct for OLTP workloads where the same backend repeatedly queries the foreign source. It is wrong for analytical workloads where a single backend opens dozens of foreign sessions across many remote servers, since the remote-side connection slots fill up rapidly.

The remote-side max_connections needs to accommodate the worst-case local pool size times the number of foreign servers, with headroom. Teams that miss this calculation discover the failure mode under load when the FDW starts returning "too many connections" errors from remote sources.

Postgres 14 added the postgres_fdw idle session timeout via the keep_connections option, which closes remote connections after a configurable idle period. Tuning this for the workload reduces the worst-case connection burden but adds reconnection latency.

What FDWs do not do

FDWs are read-mostly. INSERT, UPDATE, DELETE work for postgres_fdw and a few other wrappers, but the performance is row-at-a-time even with bulk insert support, and transactional guarantees across FDW boundaries are limited. The two-phase commit support in Postgres 14+ improves this but does not eliminate the coordination cost.

FDWs do not replicate data. Each query goes to the remote source live. There is no caching, no eventual consistency, no offline access. If the remote source is down, queries fail. The behavior is correct for tight integration but wrong for resilience-sensitive use cases that need local copies of remote data.

FDWs do not normalize schemas. The foreign table has to match the remote schema closely, with type coercion handled at the boundary. Type mismatches cause query failures, and schema changes on the remote side require coordinated foreign table updates on the local side.

FDWs do not enable transparent failover. If the remote source is replicated and the connection target needs to change, the foreign server definition must be updated, which requires an administrator action. The dblink extension is similarly limited.

Three patterns that fail

First, the synchronous-cross-FDW transaction. A team treats foreign tables as transactional partners, wrapping writes to local and foreign tables in the same BEGIN/COMMIT block expecting atomicity. The two-phase commit support in 14+ helps but is not turned on by default and is operationally complex. The right answer is usually outbox pattern or saga-style compensation rather than relying on FDW transactions.

Second, the unbounded foreign join. A query joins a local table with a foreign table without a tight predicate that the planner can push down. The remote side returns the entire foreign table, the local side joins, and the query runs orders of magnitude slower than expected. EXPLAIN VERBOSE makes the failure visible but only if the team looks.

Third, the FDW-as-caching-layer attempt. A team treats foreign tables as cached data with periodic refresh, not realizing that every query hits the remote source live. The right answer is materialized views over foreign tables, which the planner treats as local tables with explicit refresh discipline.

Our use

Across DocuMint, CronPing, FlagBit, and WebhookVault, we run SQLite as the database layer, which has no equivalent of postgres_fdw. The closest SQLite analog is the ATTACH DATABASE command, which lets queries reference tables in another SQLite database on the same filesystem. ATTACH is functionally similar but lacks the network and cross-server dimensions that make postgres_fdw genuinely useful.

The Postgres migration plan for each product includes consideration of FDW use cases. The most plausible candidate is the reporting database pattern: when we eventually consolidate analytics across products, a separate Postgres instance with foreign tables pointing at each product's Postgres database would let us run cross-product analytics queries without building an ETL pipeline. The pattern would extend the FDW design into a small data warehouse without buying separate data warehouse infrastructure, which fits our scale.

The deeper observation about Foreign Data Wrappers is that they are one of the features that distinguishes Postgres from other open-source databases. The MySQL equivalent (FEDERATED storage engine) is less mature and less actively maintained. The SQLite equivalent (ATTACH DATABASE) is more limited in scope. The Postgres community has invested in the SQL/MED standard implementation over a decade and produced a tool that works well for the use cases it was designed for. The lesson is that database features with niche but high-value use cases reward sustained development, and the cumulative effect across many such features is why Postgres has become the default open-source database for new projects.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.