In PostgreSQL, accessing remote data has been made possible with the development of SQL Management of External Data (SQL/MED) since version 9.1. Foreign Data Wrappers (FDW) use SQL/MED to manage foreign tables similar to local tables. This feature allows accessing foreign tables from local servers and executing join operations with foreign tables stored on different servers. PostgreSQL has developed many FDW extensions, including the officially developed and maintained extension, postgres_fdw, for accessing remote PostgreSQL servers.

This article discusses the fourth chapter of The Internals of PostgreSQL 4.1, which explains in detail the working of FDW in PostgreSQL.

Overview of FDW in PostgreSQL

To use the FDW feature, one needs to install the appropriate extension and execute setup commands such as CREATE FOREIGN TABLE, CREATE SERVER, and CREATE USER MAPPING. Once set up, the functions defined in the extension are invoked during query processing to access the foreign tables.

The query tree of the input SQL is created by the analyzer/analyser using the definitions of the foreign tables stored in the pg_catalog.pg_class and pg_catalog.pg_foreign_table catalogs. To connect to the remote server, the planner (or executor) uses a specific library to connect to the remote database server, with connection parameters such as the username, server's IP address, and port number stored in the pg_catalog.pg_user_mapping and pg_catalog.pg_foreign_server catalogs using the CREATE USER MAPPING and CREATE SERVER commands.

Creating a Plan Tree Using EXPLAIN Commands (Optional)

PostgreSQL's FDW supports obtaining statistics of the foreign tables to estimate the plan tree of a query. Some FDW extensions, such as postgres_fdw, mysql_fdw, tds_fdw, and jdbc2_fdw, use this feature. If the use_remote_estimate option is set to on using the ALTER SERVER command, the planner queries the cost of plans to the remote server by executing the EXPLAIN command. Otherwise, the embedded constant values are used by default.

Creating a Query Tree

The query tree is created by the analyzer/analyser using the CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA command, which defines foreign tables stored in the pg_catalog.pg_class and pg_catalog.pg_foreign_table catalogs.

Connecting to the Remote Server

The planner (or executor) uses a specific library to connect to the remote database server, such as libpq to connect to the remote PostgreSQL server and libmysqlclient to connect to the MySQL server. Connection parameters such as the username, server's IP address, and port number are stored in the pg_catalog.pg_user_mapping and pg_catalog.pg_foreign_server catalogs.

Deparsing

To generate the plan tree, the planner creates a plain text SQL statement from the plan tree's scan paths of the foreign tables. The plain text SQL statement is sent to the remote server, and the executor receives the result and processes the received data if necessary. For instance, if a multi-table query is executed, the executor performs the join processing of the received data and other tables.

Conclusion

Foreign Data Wrappers in PostgreSQL are a useful feature that allows accessing remote tables from local servers and executing join operations with foreign tables stored on different servers. Understanding the workings of FDW is crucial to optimize the performance of the queries. The officially developed and maintained extension, postgres_fdw, is the most reliable and well-maintained FDW extension available.