Foreign Data Wrappers: Cross-Database Queries Without ETL

1. What a foreign data wrapper technically does

A foreign data wrapper, or FDW, is a driver module that encapsulates an external data source so it can be addressed inside your own database like a regular table. The wrapper handles the translation: a SQL statement against the virtual table gets converted into the appropriate query for the target system, whether that's another SQL-dialect statement, a file scan, or an API call, and the result comes back as a relational row set.

The concept isn't tied to a single product. It shows up under different names: foreign data wrapper following the SQL/MED standard, linked server, external table, or external data source in a Polybase-style setup. They all share the same core idea: a table definition that holds no local data but instead points to the external source on every access. The actual data storage stays where it is, no copy is created and no synchronization problem arises.

That makes the approach fundamentally different from a classic ETL pipeline. Instead of periodically extracting, transforming and loading data, you query the live source directly. That means always-current data, but it also means the query's performance depends on the reachability and speed of the external system, a tradeoff you should accept deliberately.

-- Example: create a foreign server and foreign table (SQL/MED syntax)
CREATE SERVER reporting_db
  FOREIGN DATA WRAPPER generic_fdw
  OPTIONS (host 'reporting.internal', port '5432', dbname 'reporting');

CREATE USER MAPPING FOR analyst
  SERVER reporting_db
  OPTIONS (user 'readonly_user', password 'secret');

CREATE FOREIGN TABLE remote_orders (
  order_id    BIGINT,
  customer_id BIGINT,
  total_cents INTEGER,
  created_at  TIMESTAMP
)
  SERVER reporting_db
  OPTIONS (schema_name 'public', table_name 'orders');

2. Practical use case: a reporting query across two databases

A typical scenario: the operational database of an order system holds raw order data, while a separate analytics database stores aggregated marketing metrics such as campaign cost per customer. A report needs to compute revenue per campaign for the last thirty days, meaning it must combine data from both systems. Without an FDW, you'd either write a daily export job that copies order data into the analytics database, or implement the logic in the application layer with two separate database connections.

With a foreign data wrapper, the orders table gets registered once as a foreign table inside the analytics database. From then on you can write a perfectly normal JOIN between a local and a remote table, including filtering, grouping and aggregation in a single SQL statement. The query planner decides which operations it can delegate to the foreign source and which it must run locally.

The benefit isn't just skipping a batch pipeline, it's consistency: there's no delay from an overnight sync job, the report always reflects the current state of both systems. For reporting applications where freshness matters more than raw query speed, that's a significant simplification.

-- Revenue per campaign for the last 30 days, order data comes via FDW
SELECT
  c.campaign_name,
  COUNT(o.order_id)          AS order_count,
  SUM(o.total_cents) / 100.0 AS revenue_eur
FROM campaign_costs c
JOIN remote_orders o
  ON o.customer_id = c.acquired_customer_id
WHERE o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY c.campaign_name
ORDER BY revenue_eur DESC;

3. Filter and join pushdown: the decisive performance question

The single biggest factor for FDW query performance is how much work can be delegated to the remote source, known as pushdown. With filter pushdown, the query planner recognizes a WHERE condition on the foreign table and sends it as part of the query to the target system, instead of pulling every row and filtering locally. A good FDW driver translates a condition like created_at >= CURRENT_DATE - INTERVAL '30 days' directly into a matching WHERE clause on the target source.

Join pushdown goes a step further: if both the local and the remote table belong to the same foreign server, the entire join can theoretically run on the other side. In practice, though, that only works reliably when both tables live on the same foreign server. As soon as a local and a remote table need to be joined, as in the reporting example above, some amount of data inevitably has to be transferred and processed locally.

Not every driver supports pushdown to the same extent. Some only manage simple equality comparisons, others also handle aggregations like COUNT or SUM. It pays to trace what actually runs remotely versus locally using EXPLAIN before relying on a particular query shape.

4. Where foreign data wrappers hit their limits

Without working pushdown, the worst case reads the entire foreign table before any filtering happens, effectively a full table scan over the network. For a remote table with several million rows, a query that would take milliseconds locally can slow down to several minutes. That trap is particularly insidious because it creeps up gradually as the source's data volume grows, without any change to the SQL code itself.

Another problem is the lack of cross-system transaction guarantees. An FDW access is read-only or, at best, wrapped in its own locally scoped transaction, but a genuine distributed transaction with two-phase commit across two independent database systems is the exception, not the rule. For reporting purposes that's usually harmless, for writing operational processes FDW is typically the wrong choice.

Statistics are also a weak point: the local query planner often only has a rough idea, or none at all, of the foreign table's cardinalities, which can lead to poor join ordering. Some systems let you manually import or refresh these statistics, which is a sensible maintenance step for recurring reports.

-- Check whether filters are actually pushed down to the foreign source
EXPLAIN (VERBOSE, ANALYZE)
SELECT order_id, total_cents
FROM remote_orders
WHERE created_at >= CURRENT_DATE - INTERVAL '7 days';
-- Look for "Remote SQL" or "Foreign Scan" with a WHERE clause in the plan

5. Not just databases: CSV files and APIs as virtual tables

Foreign data wrappers aren't limited to relational databases as targets. Drivers exist that expose a CSV file on the filesystem, a JSON file, or even the response payload of a REST API as a foreign table. That's handy for one-off or occasional analyses where a full import script isn't worth building, say a monthly export spreadsheet from a business department that needs to be reconciled with your own revenue data.

File-based sources avoid the network problem, but bring other limitations: a CSV wrapper typically can't filter before the file has been fully parsed, because the file format has no concept of an index. For small to medium files that's not an issue, for very large files it's worth checking whether a regular import into a temporary table isn't the better choice.

API-based foreign tables are the most fragile case, because they depend on an external service's availability and rate limits. A join that triggers one API call per row can severely slow down your own query and overload the external system at the same time. Constructs like that should stay limited to small, targeted data volumes and never be used in bulk batch processes.

6. Write access: possible, but handle with care

Many modern FDW implementations support INSERT, UPDATE and DELETE on the foreign table alongside SELECT. Technically feasible, practically risky. A DELETE on a foreign table without a WHERE clause removes data in a foreign system, possibly without the same safety nets, backups, triggers, or audit logs, that would apply locally.

More importantly, transaction semantics get murky: if a local transaction modifies both a local table and a foreign table and then fails, a clean rollback isn't guaranteed unless the foreign server supports a distributed transaction. In the worst case, the local change gets rolled back while the remote change has already committed, a classic split-brain scenario for distributed writes.

As a rule of thumb: write-oriented FDW access suits administrative one-off cases, such as fixing a single record during a migration, but not regular application logic. For production write integrations between systems, dedicated APIs or message queues with explicit error handling remain the more robust choice.

7. Security: credentials, network segmentation and permissions

A foreign data wrapper needs credentials for the target system, typically stored as a user mapping inside your own database. These credentials should follow the principle of least privilege: a read-only user that can only reach the tables actually needed, not the full administrative access that might feel convenient for a quick test environment.

On the network side, an FDW opens a persistent or recurring connection between two systems that may live in different security zones. Anyone connecting an analytics database to a production customer system should route that connection through a separate, tightly configured network segment rather than simply opening the production database to arbitrary internal clients.

Finally, keep in mind that permission models don't carry over automatically. A user who has access to the foreign table locally effectively reaches the target system with the privileges of the stored user mapping, regardless of what privileges they'd actually have there. That indirection needs to be explicitly accounted for in any permissions audit.

8. When an alternative to FDW is the better choice

Foreign data wrappers aren't a substitute for every form of system integration. With very high query volume against the same remote table, say a daily batch report over millions of rows, a classic ETL process with a materialized copy of the data is often faster and more predictable, because it avoids the per-query network overhead and can use local indexes on the copied data.

For integrations that need to propagate changes in near real time, change data capture with a message queue or event stream is usually the more suitable architecture, since it works push-based instead of actively polling on every query. FDW is best suited to moderate query volumes where freshness matters more than maximum throughput, classic reporting rather than high-load transaction processing.

A pragmatic rule of thumb: if an FDW-based query regularly grows past a few seconds of runtime in production, or the target system feels a noticeable load, that's a signal to consider a materialized intermediate layer, such as a periodically refreshed table or a materialized view, instead of forcing the live access further.

9. Checklist before putting a foreign data wrapper into production

Before deploying an FDW in a production reporting process, a short but thorough check pays off. First: does the specific driver support filter pushdown for the relevant query patterns, verified with EXPLAIN and not just assumed from the documentation? Second: how does the query behave when the target system is briefly unreachable, does it fail with a clear error or hang indefinitely?

Third: are the credentials used scoped to the minimum necessary, and does the connection run through an adequately secured network segment? Fourth: is there a fallback plan if query volume grows and the FDW solution stops scaling, such as switching to a materialized copy? Answering these four points up front avoids the most common surprises in production.

Finally, monitoring deserves attention: since FDW queries are part of your own query plan but import runtime behavior from a foreign system, the latency and error rates of these queries should be monitored separately, so a problem in the target system doesn't first show up as a vague performance regression in your own system.

10. Summary

11. FAQ: Foreign Data Wrappers: Key Takeaways