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.

Fig. 4.1. Basic concept of FDW.

After installing the necessary extension and making the appropriate settings, you can access foreign tables on remote servers. For example, suppose there are two remote servers, namely PostgreSQL and MySQL, which have the foreign_pg_tbl and foreign_my_tbl tables, respectively. In this example, you can access the foreign tables from the local server by issuing the following SELECT 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

You can also execute join operations with foreign tables stored in different servers, just like you would with local tables.

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.

Chapter Contents