Postgres Foreign Data Wrappers (FDW) let you query external data sources as if they were local Postgres tables, blurring the lines between your database and the outside world.
Let’s see it in action. Imagine you have a PostgreSQL database and a MySQL database. You want to query a table users in MySQL from your PostgreSQL instance.
First, you need to install the mysql_fdw extension. On Debian/Ubuntu, this is typically sudo apt-get install postgresql-XX-mysql-fdw, where XX is your Postgres version. Then, in your Postgres database:
CREATE EXTENSION mysql_fdw;
Next, you define a "foreign server" that points to your MySQL instance. This server object holds connection details.
CREATE SERVER mysql_server
FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '192.168.1.100', port '3306');
Now, you need to define user mapping for authentication. This tells Postgres how to log in to the MySQL server.
CREATE USER MAPPING FOR current_user
SERVER mysql_server
OPTIONS (username 'mysql_user', password 'mysql_password');
Finally, you create a "foreign table" in Postgres that mirrors the structure of the users table in MySQL.
CREATE FOREIGN TABLE mysql_users (
id INT,
username VARCHAR(50),
email VARCHAR(100)
)
SERVER mysql_server
OPTIONS (dbname 'my_database', table_name 'users');
Now, you can query mysql_users as if it were a regular Postgres table:
SELECT id, username FROM mysql_users WHERE id > 10;
PostgreSQL will translate this query, send it to the MySQL server via the FDW, retrieve the results, and present them to you.
The primary problem FDWs solve is data integration without complex ETL processes. Instead of moving data, you access it where it lives. This is particularly useful for:
- Hybrid Cloud/On-Premise: Accessing on-premise data from a cloud Postgres instance or vice-versa.
- Microservices: Querying data from other services’ databases directly.
- Legacy Systems: Interfacing with older databases that are difficult to migrate.
- Data Warehousing: Combining data from operational databases with analytical stores.
Internally, when you query a foreign table, PostgreSQL’s query planner recognizes it’s not a local table. It then calls the FDW extension’s functions. The FDW is responsible for translating the SQL query into the native query language of the foreign data source (e.g., MySQL’s SQL). It establishes a connection, sends the translated query, fetches the results, and then returns them to PostgreSQL as if they were local rows. The FDW also handles pushing down operations like WHERE clauses, ORDER BY, and even JOINs (if the foreign system supports them and the FDW implementation allows) to minimize data transfer.
You have several levers to control FDW behavior:
optionsinCREATE SERVER: These are connection parameters. For some FDWs, you might specifysslmode,connect_timeout, or database-specific options.optionsinCREATE USER MAPPING: These are authentication details. Most commonlyusernameandpassword, but some FDWs support other mechanisms.optionsinCREATE FOREIGN TABLE: These are table-specific. Crucially,dbname(or equivalent) andtable_name(or equivalent) are required. You might also specifyschema_nameif the foreign table is not in the default schema.ALTER SERVER,ALTER USER MAPPING,ALTER FOREIGN TABLE: You can modify these settings after creation.
The most surprising mechanical detail is how much can be pushed down. For example, if you have a foreign table foreign_orders and a local table local_customers, and you perform SELECT * FROM foreign_orders JOIN local_customers ON foreign_orders.customer_id = local_customers.id, the FDW might be able to push the join condition down to the foreign data source if the FDW and the foreign system support it. This means the foreign system does the heavy lifting of combining rows before sending only the result to your Postgres instance, which is far more efficient than pulling all rows from both sides and joining them locally.
The next concept you’ll likely encounter is optimizing FDW performance, especially with large datasets or high-latency connections.