4. Foreign Data Wrappers (FDW)

In 2003, the SQL standard introduced a specification for accessing remote data called SQL Management of External Data (SQL/MED). Since version 9.1 (released in September 2011), PostgreSQL has been developing features to support the SQL/MED standard.

In SQL/MED, a table located on a remote server is called a foreign table. PostgreSQL uses Foreign Data Wrappers (FDW) to manage these foreign tables, allowing users to query them as if they were regular local tables.

By installing the necessary extensions and configuring the appropriate settings, foreign tables on remote servers become accessible for querying.

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

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

Just like local tables, FDWs enable join operations between foreign tables even when they are stored on different remote servers:

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

A wide variety of FDW extensions have been developed and are listed on the Postgres wiki. However, many of these are not actively maintained.

A notable exception is postgres_fdw, which is officially developed and maintained by the PostgreSQL Global Development Group as a dedicated extension for accessing remote PostgreSQL servers.