How to Use Foreign Tables in PostgreSQL

A few years ago I inherited a project where data was scattered across two separate PostgreSQL databases — one for the core application, one for an internal reporting system that had grown organically over time. Every report that needed data from both had to either duplicate data through some fragile ETL script, or query both databases separately in application code and stitch the results together. Once I discovered foreign tables, that whole problem mostly disappeared. I could query the other database as if its tables lived locally, right inside a normal SQL statement. In this article, I’ll explain what foreign tables are, how to set them up, and where they genuinely help versus where I’ve learned to be cautious with them.

What a Foreign Table Actually Is

A foreign table is a table-like object in PostgreSQL that doesn’t store data locally — instead, it represents data that lives in an external system. When you query it, PostgreSQL forwards (parts of) the query to that external system through a foreign data wrapper, gets the results back, and presents them to you as if they were rows in a normal table. You can JOIN a foreign table with a local table, filter it with a WHERE clause, and in many cases even write to it, all using ordinary SQL.

Foreign tables are one piece of a broader feature called SQL/MED (Management of External Data), and PostgreSQL’s implementation of it — via the foreign data wrapper (FDW) system — is genuinely one of my favorite lesser-known features of the database.

I’ll cover foreign data wrappers themselves in more depth in a separate article, but here I want to focus specifically on the practical mechanics of creating and using foreign tables once a wrapper and server are already set up.

The Building Blocks

To use a foreign table, you need three things in place:

1. An extension providing the foreign data wrapper (e.g., postgres_fdw for connecting to another PostgreSQL database).
2. A foreign server definition, which describes the connection details of the remote system.
3. A user mapping, which tells PostgreSQL what credentials to use when connecting on behalf of a given local user.

Only after those three exist can you actually create a foreign table (or import a whole set of them).

Setting Up postgres_fdw

Since connecting one PostgreSQL database to another is the most common scenario I run into, I’ll use postgres_fdw as the running example.

CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER reporting_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'reporting.internal', port '5432', dbname 'reporting');

CREATE USER MAPPING FOR CURRENT_USER
    SERVER reporting_db
    OPTIONS (user 'readonly_user', password 'secure_password_here');

A couple of things I always keep in mind here: the user mapping’s credentials are stored in the local database (visible to superusers and anyone who can see pg_user_mappings with sufficient privilege), so I use a dedicated, least-privilege account on the remote side rather than reusing an admin credential. I also prefer to store the password via a .pgpass file or a secrets manager reference where the deployment tooling supports it, rather than hardcoding it directly in SQL that might end up in a migration file.

Creating a Foreign Table

Once the server and user mapping exist, you define a foreign table that mirrors the structure of the remote table.

CREATE FOREIGN TABLE remote_orders (
    id INTEGER,
    customer_id INTEGER,
    total NUMERIC(10,2),
    created_at TIMESTAMPTZ
)
SERVER reporting_db
OPTIONS (schema_name 'public', table_name 'orders');

Now I can query it just like any local table:

SELECT customer_id, sum(total)
FROM remote_orders
WHERE created_at > now() - interval '30 days'
GROUP BY customer_id
ORDER BY sum(total) DESC
LIMIT 10;

Under the hood, PostgreSQL is smart enough to push filtering and even some aggregation down to the remote server when it can, rather than pulling the entire table across the network and filtering locally. This is one of the things that makes postgres_fdw performant enough for real workloads rather than just a novelty.

Importing an Entire Schema

Manually declaring the column list for every remote table gets tedious fast, especially when there are dozens of tables. IMPORT FOREIGN SCHEMA solves this by introspecting the remote schema and generating foreign tables automatically.

CREATE SCHEMA reporting;

IMPORT FOREIGN SCHEMA public
FROM SERVER reporting_db
INTO reporting;

This creates a foreign table in the local reporting schema for every table in the remote public schema, matching column names and types automatically. I use this constantly when connecting to a database I don’t fully control — it saves a ton of manual DDL and keeps the definitions accurate.

You can also limit which tables get imported:

IMPORT FOREIGN SCHEMA public
LIMIT TO (orders, customers)
FROM SERVER reporting_db
INTO reporting;

Writing to Foreign Tables

Foreign tables aren’t necessarily read-only. postgres_fdw supports INSERT, UPDATE, and DELETE against foreign tables, and PostgreSQL will push those operations to the remote database.

INSERT INTO remote_orders (id, customer_id, total, created_at)
VALUES (9999, 42, 199.99, now());

I’ll be honest — I use writable foreign tables much more sparingly than read-only ones. Writes across a network boundary introduce failure modes (partial failures, network timeouts mid-transaction, unclear consistency guarantees across two separate database instances) that read queries mostly avoid. If I do write through a foreign table, I make sure I understand exactly what happens if the connection drops mid-transaction, and I generally avoid mixing local and remote writes in a single transaction unless I’ve tested the failure scenarios.

Joining Local and Foreign Tables

This is really the killer feature for me — being able to join across systems in a single query.

SELECT c.name, count(o.id) AS order_count, sum(o.total) AS lifetime_value
FROM customers c
JOIN remote_orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY lifetime_value DESC;

Here, customers is a local table and remote_orders is a foreign table on another database entirely, and I get to write one clean query instead of pulling both datasets into application code and joining them in memory.

Foreign Tables to Non-PostgreSQL Systems

postgres_fdw only connects to other PostgreSQL databases, but the FDW architecture is general — there are wrappers for MySQL, Oracle, SQL Server, MongoDB, Redis, flat files, and even generic REST APIs. The mechanics are the same: install the extension, create a server, create a user mapping, create (or import) the foreign table.

For example, file_fdw lets you treat a CSV file on disk as a queryable table:

CREATE EXTENSION IF NOT EXISTS file_fdw;

CREATE SERVER csv_files FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE daily_export (
    order_id INTEGER,
    amount NUMERIC,
    export_date DATE
)
SERVER csv_files
OPTIONS (filename '/var/exports/daily_orders.csv', format 'csv', header 'true');

This is a neat trick when you have recurring exports landing on disk and want to query them with SQL without a separate import step.

Checking Query Plans

Because foreign tables can push work down to the remote system, it’s worth checking EXPLAIN output to understand what’s actually happening, especially when performance matters.

EXPLAIN VERBOSE
SELECT customer_id, sum(total)
FROM remote_orders
WHERE created_at > now() - interval '30 days'
GROUP BY customer_id;

Look for a line like Foreign Scan and, ideally, a Remote SQL note showing exactly what was sent to the remote server. If you see the whole table being pulled over before filtering, that’s a sign the predicate isn’t being pushed down — sometimes because of a function or expression that the remote side can’t evaluate — and you may need to rewrite the query or adjust FDW options to get better pushdown behavior.

Common Use Cases

• Cross-database reporting without building and maintaining ETL pipelines.
• Gradual database splits or migrations, where you’re moving tables out of a monolith database incrementally and still need to join old and new data during the transition.
• Federating data from legacy or third-party systems (via non-PostgreSQL FDWs) into a single query surface.
• Reading flat file exports with file_fdw instead of writing custom import scripts.
• Multi-tenant architectures where each tenant’s data lives in a separate database but a central admin system needs to query across all of them.

Troubleshooting Tips

Queries against a foreign table are painfully slow. Check EXPLAIN VERBOSE for pushdown behavior. Also check use_remote_estimate — by default, postgres_fdw doesn’t ask the remote server for accurate row-count estimates, which can lead to bad local query plans (like choosing a nested loop join when a hash join would be far better). Enabling it can help, at the cost of an extra round-trip for planning:

ALTER FOREIGN TABLE remote_orders OPTIONS (ADD use_remote_estimate 'true');

Connection errors or timeouts. Double-check network connectivity between the two database hosts, firewall rules, and that the remote PostgreSQL instance accepts connections from the local server’s IP in pg_hba.conf.

Permission denied on the foreign table. Remember that both the local GRANT on the foreign table and the remote user’s permissions on the actual table need to allow the operation — it’s two separate permission systems layered on top of each other.

Schema drift. If the remote table’s structure changes and the foreign table definition doesn’t, you’ll get errors or silently wrong data. Foreign tables don’t auto-update when the remote schema changes — you need to re-run IMPORT FOREIGN SCHEMA or manually ALTER FOREIGN TABLE to keep things in sync.

Best Practices

1. Use a dedicated, least-privilege remote user for the user mapping rather than reusing broad credentials.
2. Prefer IMPORT FOREIGN SCHEMA over manually declaring columns for anything beyond a handful of tables.
3. Check EXPLAIN VERBOSE on anything performance-sensitive to confirm filters are being pushed down.
4. Be cautious with writes across foreign tables — understand the failure modes before relying on them for anything critical.
5. Keep foreign table definitions in sync with the remote schema, ideally as part of your migration process rather than as a one-off manual step.
6. Don’t use foreign tables as a substitute for real data architecture decisions. They’re great for reporting, federation, and gradual migrations — they’re not necessarily a good permanent substitute for properly replicating or centralizing data that’s queried constantly under heavy load.
7. Monitor network latency between the databases involved — foreign table performance is only as good as the connection between the two systems, and a flaky or high-latency link will make every foreign query feel slow no matter how well-indexed the remote table is.

A Real-World Example: Gradual Service Extraction

One of the most valuable ways I’ve used foreign tables was during a gradual extraction of a “billing” domain out of a monolithic database into its own service with its own database. Rather than a risky big-bang cutover, I used foreign tables as a bridge during the transition.

-- On the monolith database, connect to the new billing database
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE SERVER billing_db
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'billing-service.internal', dbname 'billing');

CREATE USER MAPPING FOR CURRENT_USER
    SERVER billing_db
    OPTIONS (user 'monolith_reader', password 'change_me');

IMPORT FOREIGN SCHEMA public
LIMIT TO (invoices, payments)
FROM SERVER billing_db
INTO billing;

During the migration period, reporting queries and admin tools that still lived in the monolith could keep joining against billing.invoices and billing.payments as foreign tables, exactly as they had when those tables were local, while the actual billing writes moved over to the new service. This meant I didn’t have to rewrite every downstream query on day one of the migration — I could migrate write paths first, keep read paths working through the foreign tables, and only update those read paths once there was time to do it properly. Once every consumer was migrated off the foreign tables, I dropped them and the bridge was gone.

This “foreign table as a migration bridge” pattern has saved me from more than one risky, all-at-once cutover, and I’d recommend it to anyone facing a similar extraction.

Statistics and ANALYZE for Foreign Tables

Just like local tables, foreign tables benefit from accurate planner statistics, but they’re gathered a bit differently since PostgreSQL can’t inspect the remote table’s storage directly the way it can for a local one.

ANALYZE remote_orders;

For postgres_fdw specifically, running ANALYZE on a foreign table pulls a data sample from the remote side to build local statistics, which combined with use_remote_estimate gives the local planner a much more accurate picture when deciding how to join a foreign table with local ones. I make a habit of re-running ANALYZE on foreign tables periodically, especially after the remote table has grown or changed shape significantly, since PostgreSQL won’t do this automatically for you the way autovacuum does for local tables.

Frequently Asked Questions

Can I put a local index on a foreign table? No — a foreign table has no local storage, so there’s nothing to build a local index on. Any indexing needs to exist on the remote side; what you control locally is whether your query lets the remote side use its own indexes via pushdown.

Can I use foreign tables in a view? Yes, and this is a common pattern — wrapping a foreign table in a local view lets you rename columns, add computed fields, or restrict which columns are exposed, all while consumers just query the view normally.

What happens if the remote server is unreachable? Any query touching that foreign table fails with a connection error; it doesn’t silently return empty results. If a report needs to tolerate partial unavailability, that has to be handled explicitly in application logic or with a fallback query.

Do foreign tables support triggers? Foreign tables can have triggers, but keep in mind they fire locally, around the operation being sent to the remote system — they don’t run on the remote database itself, so any remote-side triggers or constraints still apply independently.

Can I use foreign tables to migrate data once and then stop using them? Absolutely, and this is one of the most common ways I use them. Import the foreign schema, run a one-time INSERT ... SELECT from the foreign table into a real local table, verify the data, and then drop the foreign table, server, and user mapping once the migration is confirmed complete. Foreign tables don’t have to be a permanent architectural fixture — they’re just as useful as a temporary bridge for a single migration task.

Is there a limit to how many foreign tables I can create against one server? Not really a hard limit imposed by the FDW system itself — I’ve imported schemas with hundreds of tables in one IMPORT FOREIGN SCHEMA call without issue. The practical constraint is usually the remote system’s own connection and query capacity, not anything on the local PostgreSQL side.

Wrapping Up

Foreign tables turned what used to be a genuinely painful cross-database reporting problem into a handful of CREATE SERVER and IMPORT FOREIGN SCHEMA statements. They’re not the right tool for every job — I wouldn’t put a foreign table in the hot path of a latency-sensitive application — but for reporting, federation, gradual migrations, and anywhere you’d otherwise be writing brittle ETL glue code, they’ve become one of the first things I reach for. If you haven’t tried them yet, I’d start with postgres_fdw against a second PostgreSQL database you already have — it’s the easiest way to see the value firsthand.