4. Foreign Data Wrappers (FDW)

In 2003, the SQL standard added a specification to access remote data called SQL Management of External Data (SQL/MED). PostgreSQL has been developing this feature since version 9.1 to realize a portion of SQL/MED.

In SQL/MED, a table on a remote server is called a foreign table. PostgreSQL’s Foreign Data Wrappers (FDW) use SQL/MED to manage foreign tables, which are similar to local tables.

After installing the necessary extension and configuring the appropriate settings, foreign tables on remote servers can be accessed.

For example, consider two remote servers, one running PostgreSQL (with a table named foreign_pg_tbl) and another running MySQL (with a table named foreign_my_tbl). These foreign tables can be accessed from the local server using queries such as the following:

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

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

Similar to local tables, FDWs also enable join operations between foreign tables stored on different servers.

localdb=# SELECT count(*) FROM foreign_pg_tbl AS p, foreign_my_tbl AS m WHERE p.id = m.id;
 count
-------
 10000

Many FDW extensions have been developed and listed on the Postgres wiki. However, almost all of them are not properly maintained, with the exception of postgres_fdw, which is officially developed and maintained by the PostgreSQL Global Development Group as an extension to access a remote PostgreSQL server.