What are foreign Data Wrappers ( FDW ) in postgresql and mysql.

A foreign data wrapper is a specification to access remote data, also called SQL management of External data (SQL/MED) . It was added to SQL standard in 2003. This feature has been developing by PostgreSQL.

So, in SQL/MED, there is a table on a remote server called foreign table. Now PostgreSQL foreign Data wrappers (FDW) are that use SQL/MED to manage foreign tables.

Accessing these foreign tables on remote server:

We can access these foreign tables on the remote server after installing some specific software. Lets say we have two different remote servers, namely, postgresql and mysql. Which have foreign_pg_tbl ad foreign_my_tbl, respectively. We can access these foreign table from the remote server to our local server by using and issuing the SELECT queries as follows.

To access table from remote postgresql Server.

Localdb=# -- foreign_pg_tbl is on the remote postgresql server.
localdb-# SELECT count(*) FROM foreign_pg_tbl;
count

output:
2000

To access table from remote mysql Server.

Localdb=# -- foreign_my_tbl is on the remote mysql server.
localdb-# SELECT count(*) FROM foreign_my_tbl;

count
10,000

we can also use the join operations on the foreign tables just like we use it on the local tables stored.

For example

Localdb=# SELECT count(*) FROM foreign_pg_tbl AS p, foreign_my_tbl AS m WHERE p.id = m.id;
Count
10,000

That's just a basic overview about FDWs. In the coming sections we will explore more about it🚀💻. If you are interested and want to read more about this you can check references.

References

https://www.interdb.jp/pg/pgsql04.html