Foreign Data Wrappers (FDW): Accessing Remote Data in PostgreSQL

In 2003, the SQL Management of External Data (SQL/MED) specification was introduced as part of the SQL standard. PostgreSQL has been actively developing its Foreign Data Wrappers (FDW) feature since version 9.1 to implement a portion of SQL/MED.

The Concept of FDW:

In SQL/MED, a table residing on a remote server is referred to as a foreign table. PostgreSQL's FDW leverages SQL/MED to manage these foreign tables, making them similar to local tables in terms of access and manipulation.

Accessing Foreign Tables:
To access foreign tables on remote servers, you need to install the required extension and configure the appropriate settings. Let's consider an example with two remote servers: "postgresql" and "mysql," each hosting a foreign table, "foreign_pg_tbl" and "foreign_my_tbl," respectively. By issuing SELECT queries as shown below, you can access these foreign tables from the local server.

-- Accessing the foreign_pg_tbl on the remote postgresql server
SELECT count(*) FROM foreign_pg_tbl;

-- Accessing the foreign_my_tbl on the remote mysql server
SELECT count(*) FROM foreign_my_tbl;

Joining Foreign Tables:

FDW enables executing join operations with foreign tables stored on different servers, just like local tables. For instance, you can perform a join operation between "foreign_pg_tbl" and "foreign_my_tbl" using the following query:

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


FDW Extensions:

Various FDW extensions have been developed and listed in the Postgres wiki. However, it's worth noting that most of these extensions are not actively maintained. The "postgres_fdw" extension stands out as the officially developed and maintained FDW extension by the PostgreSQL Global Development Group. It allows access to remote PostgreSQL servers.

Conclusion:

PostgreSQL's Foreign Data Wrappers provide a powerful mechanism for accessing and managing remote data through foreign tables. With FDW, you can seamlessly integrate remote data sources into your local PostgreSQL database, enabling efficient querying and joining of foreign tables. While various FDW extensions exist, it's advisable to use the officially supported "postgres_fdw" extension for accessing remote PostgreSQL servers.