Postgres Foreign Data Wrappers (FDW) let you query tables in other Postgres databases as if they were local tables.
Let’s see it in action. Imagine you have two Postgres instances:
Instance A (Local):
- Database:
local_db - Table:
local_table(id INT, name VARCHAR)
Instance B (Remote):
- Database:
remote_db - Table:
remote_table(id INT, value TEXT)
You want to query remote_table from local_db.
First, on local_db, you need the postgres_fdw extension:
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
Next, define how to connect to the remote server. Replace remote_host, remote_port, and remote_db_name with your actual remote connection details. The user and password are credentials for the remote database.
CREATE SERVER remote_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', port '5432', dbname 'remote_db');
CREATE USER MAPPING FOR current_user -- or a specific user
SERVER remote_server
OPTIONS (user 'remote_user', password 'remote_password');
Now, create a foreign table on the local instance that mirrors the structure of the remote table. The column names and types must match.
CREATE FOREIGN TABLE remote_table (
id INT,
value TEXT
)
SERVER remote_server;
That’s it. You can now query remote_table as if it were local:
SELECT * FROM remote_table WHERE id = 1;
Postgres will translate this query. It will connect to remote_server, execute a SELECT on the actual remote_table in remote_db, and stream the results back to local_db.
The magic of FDWs lies in how Postgres optimizes these cross-database queries. It doesn’t just fetch everything and filter locally. For many operations (like WHERE clauses, LIMIT, and even some joins), Postgres can push down the execution to the remote server. This means the remote server does the heavy lifting, significantly reducing network traffic and improving performance.
Consider a WHERE clause. When you run SELECT * FROM remote_table WHERE id = 5;, Postgres sends a query to the remote server like SELECT id, value FROM remote_table WHERE id = 5;. Only the matching row(s) are returned. If you were to do SELECT * FROM remote_table WHERE value LIKE '%important%';, Postgres would try to push down that LIKE operation as well, so the remote server filters the data.
The FDW doesn’t just provide read access. You can also enable modifications. To allow INSERT, UPDATE, and DELETE operations, you need to grant permissions on the remote table to the user specified in the USER MAPPING. Then, on the local side, you can add these options to your foreign table definition:
ALTER FOREIGN TABLE remote_table
OPTIONS (UPDATE_CONDITION 'true'); -- Or specify columns for specific updates
And then you can perform DML:
INSERT INTO remote_table (id, value) VALUES (10, 'new data');
UPDATE remote_table SET value = 'updated data' WHERE id = 10;
DELETE FROM remote_table WHERE id = 10;
The FDW translates these DML statements into equivalent commands on the remote Postgres instance.
A key performance consideration is how joins are handled. If you join a local table with a foreign table, Postgres will try to push down as much of the join operation as possible to the remote server. However, if the join condition or the filtering required after the join cannot be fully pushed down, Postgres might fetch all matching rows from the remote table and perform the join locally. This can be a performance bottleneck. You can use EXPLAIN to see the query plan and understand where the work is being done.
When you use EXPLAIN on a query involving a foreign table, you’ll see entries like Foreign Scan on remote_table. If the EXPLAIN output shows Remote SQL: SELECT ... FROM remote_table WHERE ... (or similar), it means the operation was successfully pushed down. If it just shows a Seq Scan or Index Scan on the foreign table after fetching data, it means the filtering or joining is happening locally.
The most counterintuitive aspect of FDWs is their ability to handle complex data types and functions, provided the remote server supports them and the FDW implementation has the necessary mappings. For example, you can often use JSON functions or even call stored procedures on the remote database through the FDW, making it appear as if these operations are happening locally. The FDW acts as a sophisticated translator, not just for basic SQL but for a surprisingly wide range of Postgres features.
The next hurdle you’ll likely encounter is dealing with schema changes on the remote database, which requires refreshing the foreign table definition locally.