I wrote a separate article about foreign tables, but I realized that article kept assuming the foreign data wrapper infrastructure was already set up — so I want to back up and cover foreign data wrappers (FDWs) themselves in more depth: what they are, the ecosystem around them, how to install and configure the common ones, and the operational lessons I’ve picked up connecting PostgreSQL to everything from MySQL to plain CSV files.
What a Foreign Data Wrapper Is
A foreign data wrapper is an extension that implements the actual communication logic between PostgreSQL and an external data source. It’s the piece that knows how to translate a PostgreSQL query (or part of one) into whatever protocol the remote system speaks, execute it, and translate the results back into PostgreSQL rows. Foreign tables are the user-facing objects you query; foreign data wrappers are the engine underneath that makes them work.
This is part of the SQL/MED standard (SQL Management of External Data), and PostgreSQL’s extension architecture makes it possible for third parties — and Postgres core itself — to ship wrappers for almost any data source you can imagine.
The FDW Ecosystem
PostgreSQL ships a couple of wrappers in the core distribution, and there’s a much larger ecosystem of community and commercial wrappers beyond that.
Bundled with PostgreSQL:
postgres_fdw— connects to other PostgreSQL databases.file_fdw— reads flat files (CSV, text) as tables.
Popular third-party wrappers (mostly via the multicorn framework or native C implementations):
mysql_fdw— connects to MySQL/MariaDB.oracle_fdw— connects to Oracle.tds_fdw— connects to SQL Server / Sybase.mongo_fdw— connects to MongoDB.redis_fdw— connects to Redis.- Wrappers for REST APIs, Google Sheets, S3, and more, often built on the
multicornPython framework which makes writing a custom FDW much less painful than doing it in raw C.
I’ve personally used postgres_fdw, file_fdw, and mysql_fdw in real projects; the others I’ve experimented with but haven’t relied on in production, so I’ll focus most of my concrete examples on those three, while explaining the general pattern that applies to all of them.
The General Setup Pattern
No matter which wrapper you’re using, the setup follows the same four steps:
- Install the extension (sometimes it’s bundled, sometimes it requires an OS package).
CREATE EXTENSIONto register it inside your database.CREATE SERVERto describe the connection.CREATE USER MAPPINGto supply credentials.
After that, you create or import foreign tables as I covered in my other article. Let’s go through the setup for a few different wrappers so you can see how the pattern adapts to each source.
Setting Up postgres_fdw
This one’s built in, so there’s no OS-level installation step.
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
CREATE SERVER analytics_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'analytics.internal', port '5432', dbname 'analytics');
CREATE USER MAPPING FOR CURRENT_USER
SERVER analytics_db
OPTIONS (user 'readonly', password 'change_me');
Useful server-level options for postgres_fdw include sslmode (I always set this to require or stronger for anything crossing a network boundary I don’t fully trust) and fetch_size, which controls how many rows are pulled per round-trip — tuning this can meaningfully affect performance on large result sets.
ALTER SERVER analytics_db OPTIONS (ADD sslmode 'require');
ALTER SERVER analytics_db OPTIONS (ADD fetch_size '5000');
Setting Up file_fdw
Also built in. Useful for treating recurring file exports as queryable tables without a separate import step.
CREATE EXTENSION IF NOT EXISTS file_fdw;
CREATE SERVER csv_server FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE inventory_export (
sku TEXT,
quantity INTEGER,
warehouse TEXT
)
SERVER csv_server
OPTIONS (filename '/data/exports/inventory.csv', format 'csv', header 'true');
Note that file_fdw reads from the filesystem of the PostgreSQL server itself, not your client machine — the file needs to actually exist on disk where the database process can read it, and the OS user running PostgreSQL needs read permission on it.
Setting Up mysql_fdw
This one requires installing the extension at the OS level first, since it’s not bundled with PostgreSQL. On a Debian/Ubuntu system, that typically looks like installing a package (the exact package name depends on your PostgreSQL version and distribution — check your package manager or build from source per the project’s instructions) before you can CREATE EXTENSION it.
CREATE EXTENSION IF NOT EXISTS mysql_fdw;
CREATE SERVER mysql_legacy_db
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host 'legacy-mysql.internal', port '3306');
CREATE USER MAPPING FOR CURRENT_USER
SERVER mysql_legacy_db
OPTIONS (username 'readonly', password 'change_me');
CREATE FOREIGN TABLE legacy_customers (
id INTEGER,
name TEXT,
email TEXT
)
SERVER mysql_legacy_db
OPTIONS (dbname 'legacy_app', table_name 'customers');
I used this on a project migrating off an old MySQL-backed system — it let the new PostgreSQL application query legacy data directly during the transition period instead of needing a full one-shot cutover.
Understanding Pushdown
The single biggest factor in whether an FDW performs well or poorly is pushdown — how much of the query’s work (filtering, sorting, joining, aggregating) the wrapper can hand off to the remote system instead of pulling raw rows across and processing them locally.
postgres_fdw has the best pushdown support of any wrapper, since both sides speak the same SQL dialect — it can push down WHERE clauses, JOINs between two foreign tables on the same server, and even some aggregates. Wrappers for non-PostgreSQL systems vary widely in how much they can push down, since the remote system might not support the same operations or SQL dialect at all.
You can always check what’s actually being pushed down with EXPLAIN VERBOSE:
EXPLAIN VERBOSE
SELECT * FROM legacy_customers WHERE email LIKE '%@acme.com';
If the plan shows a Foreign Scan with a Remote SQL line containing the filter, it’s being pushed down. If instead you see the filter applied as a separate step after a full foreign scan, the entire table is being pulled across before filtering — which can be a serious performance problem on large tables.
Setting Server and Table-Level Options
Beyond connection details, most wrappers expose tuning options at both the server and table level. A few I use regularly with postgres_fdw:
-- Ask the remote server for accurate row estimates (costs an extra round trip, improves plan quality)
ALTER SERVER analytics_db OPTIONS (ADD use_remote_estimate 'true');
-- Control batching for bulk inserts to the remote table
ALTER FOREIGN TABLE remote_orders OPTIONS (ADD batch_size '1000');
use_remote_estimate in particular has fixed more “why is this join so slow” mysteries for me than almost any other single setting — without it, PostgreSQL’s local planner has to guess at the row counts on the other side, and a bad guess can lead to a disastrous join strategy.
Security Considerations
A few things I always double check when setting up any FDW:
- User mappings store credentials in the local database. By default, only the mapping’s owner and superusers can see the password via
pg_user_mappings, but treat it as sensitive regardless — use a dedicated low-privilege remote account, not an admin one. - Restrict who can use the server.
CREATE SERVERandCREATE USER MAPPINGrequire elevated privileges by default, but you canGRANT USAGE ON FOREIGN SERVERto specific roles if you want finer control over who can create foreign tables against it. - Use SSL/TLS for any connection crossing an untrusted network. Don’t assume internal networks are automatically safe.
- Be careful with
file_fdwand file paths. Since it reads from the server’s filesystem, make sure only trusted paths are exposed and that the option isn’t something an application-layer bug could manipulate into reading arbitrary files.
Common Use Cases
- Connecting to another PostgreSQL database for reporting or cross-database joins (
postgres_fdw). - Querying legacy or third-party databases during a migration (
mysql_fdw,oracle_fdw,tds_fdw). - Reading recurring file exports without writing custom import scripts (
file_fdw). - Federating data from NoSQL sources like MongoDB or Redis into SQL-based reporting.
- Building custom integrations with the
multicornframework when no existing wrapper covers your data source — I’ve seen teams write lightweight FDWs against internal REST APIs this way.
Troubleshooting Tips
CREATE EXTENSION fails with “could not open extension control file.” This means the extension isn’t installed at the OS/package level yet — CREATE EXTENSION only registers an already-installed extension inside the database, it doesn’t install the underlying software. Install the appropriate package or build the extension first.
Connection refused or timeout when querying a foreign table. Check network reachability between the two hosts independently of PostgreSQL (a simple telnet or nc test to the port), and check the remote system’s own access control (for postgres_fdw, that’s pg_hba.conf on the remote server; for others, it’s whatever the remote system uses).
Queries are much slower than expected. Check pushdown with EXPLAIN VERBOSE first. If pushdown looks fine, check use_remote_estimate and fetch_size/batch_size tuning options for your specific wrapper.
Data type mismatches or unexpected NULLs. Not every remote system has a clean 1:1 mapping to PostgreSQL types. Check the wrapper’s documentation for how it maps types, and consider explicitly casting problematic columns in the foreign table definition.
Best Practices
- Match the wrapper to a real, ongoing need — for a one-time data migration, a plain export/import might genuinely be simpler than standing up an FDW.
- Always check pushdown behavior with
EXPLAIN VERBOSEbefore trusting an FDW-backed query in a performance-sensitive path. - Use least-privilege credentials on the remote side, always.
- Enable
use_remote_estimate(forpostgres_fdw) when join performance matters. - Keep an eye on wrapper maturity. The bundled wrappers (
postgres_fdw,file_fdw) are rock solid; third-party wrappers vary in how actively maintained they are — check the project’s activity before depending on one for anything critical. - Document your FDW setup (servers, user mappings, table sources) somewhere outside the database itself, since this kind of cross-system wiring is easy to forget about six months later when something breaks.
- Treat foreign tables as a specialized tool, not a default integration pattern — for very high-throughput or latency-sensitive access to another system, a proper application-level integration or data replication pipeline is usually still the better long-term answer.
Writing a Minimal Custom Wrapper with Multicorn
Occasionally none of the existing wrappers fit — I ran into this connecting to an internal REST API that had no PostgreSQL-facing wrapper at all. Rather than writing a wrapper in C, the multicorn framework lets you write one in Python, which made it approachable enough to build in an afternoon.
The general shape of a multicorn-based wrapper is a Python class implementing an execute method that the framework calls to fetch rows:
from multicorn import ForeignDataWrapper
class RestApiFDW(ForeignDataWrapper):
def __init__(self, options, columns):
super().__init__(options, columns)
self.url = options.get('url')
self.columns = columns
def execute(self, quals, columns):
import requests
response = requests.get(self.url)
for record in response.json():
yield {col: record.get(col) for col in columns}
Once installed and registered, it’s used exactly like any other FDW:
CREATE EXTENSION IF NOT EXISTS multicorn;
CREATE SERVER rest_api_server
FOREIGN DATA WRAPPER multicorn
OPTIONS (wrapper 'myapp.RestApiFDW');
CREATE FOREIGN TABLE api_orders (
id INTEGER,
total NUMERIC
)
SERVER rest_api_server
OPTIONS (url 'https://internal-api.example.com/orders');
I want to be upfront that this is a genuinely minimal, illustrative example — a production version would need pagination handling, error handling, credential management, and ideally pushing the quals (query qualifiers) down into the API request rather than fetching everything and filtering afterward. But it demonstrates the core value proposition: almost any data source that can be reached from Python can become a queryable SQL table with a relatively small amount of code, without needing to touch PostgreSQL’s C internals.
Comparing FDWs to Other Integration Approaches
It’s worth being honest about when an FDW is the wrong tool. I’ve seen teams reach for postgres_fdw as a substitute for actual data replication when they really needed one — a foreign table query is a live network round-trip every time it’s queried, with no local caching by default. If a dashboard runs the same expensive foreign-table aggregation every few seconds for many concurrent users, that’s a workload better served by a materialized view refreshed on a schedule, or genuine logical replication into a local reporting table, rather than hitting the remote system fresh every single time. I generally reach for FDWs when access is occasional, reporting-oriented, or exploratory — and reach for replication or ETL when access is frequent, latency-sensitive, or needs to survive the remote system being briefly unavailable.
Frequently Asked Questions
Can I use an FDW inside a transaction with local tables? Yes — postgres_fdw participates in the local transaction for consistency purposes, but keep in mind the remote side is still a separate database with its own commit; true two-phase distributed transaction guarantees aren’t automatic, so a failure right at commit time can, in rare cases, leave the two sides inconsistent. I treat foreign-table writes as best-effort rather than as strong as a local-only transaction.
Do foreign tables show up in pg_dump? The foreign table definition does, but not the underlying remote data, since that data doesn’t actually live in your local database. Restoring a dump elsewhere requires the server and user mapping to be reachable and re-created in the new environment too.
Is there a performance penalty just from a table being “foreign”? Only in the sense that a foreign scan is a network round-trip instead of a local disk/memory read — there’s no inherent overhead beyond that, and with good pushdown, a well-indexed foreign table query can be very fast.
Can two foreign tables from different servers be joined efficiently? PostgreSQL will still return correct results, but it generally can’t push the join itself down to either remote server (since they’re different systems), so it pulls both sides locally and joins them in PostgreSQL — usually fine for moderate data volumes, less fine for huge ones.
How do I know which FDW is actively maintained before depending on it? I check the project’s repository activity — recent commits, open issue responsiveness, and compatibility notes for the PostgreSQL versions I’m running. The bundled wrappers (postgres_fdw, file_fdw) ship with PostgreSQL core and follow its release cadence, so they’re always a safe baseline; third-party wrappers vary widely, and I’ve occasionally found one that hadn’t been updated in years and quietly broke against a newer PostgreSQL major version. For anything going into production, I’d rather spend twenty minutes checking a project’s health up front than discover the problem during an upgrade.
Do I need to worry about FDW compatibility across PostgreSQL major version upgrades? Yes — third-party wrappers need to be recompiled or repackaged for each new major PostgreSQL version, and that doesn’t always happen immediately after a new release. Before scheduling a major version upgrade, I check whether every non-bundled FDW I depend on already has a compatible build for the target version, the same way I’d check any other extension, since discovering a missing package mid-upgrade is a much worse position to be in than catching it during planning.
Wrapping Up
Foreign data wrappers are the piece of PostgreSQL that turns it from “just a relational database” into something closer to a query federation layer that happens to also be a fantastic relational database. Once you understand the four-step setup pattern — extension, server, user mapping, foreign table — the same mental model applies whether you’re connecting to another PostgreSQL instance, a legacy MySQL system, or a folder full of CSV exports. I’d encourage you to start with postgres_fdw since it’s already available wherever PostgreSQL is installed, get comfortable with the pattern, and then branch out to other wrappers as your actual integration needs demand it.