Foreign data Wrappers (FDW)
- PostgreSQL's Foreign Data Wrappers (FDW) are a feature that uses SQL/MED to manage foreign tables, which are similar to local tables. SQL/MED is a specification that allows for accessing remote data, and PostgreSQL has implemented a portion of it since version 9.1. FDWs allow PostgreSQL to access data stored in other databases or systems as if it were a local table in PostgreSQL. This feature provides greater flexibility and interoperability for PostgreSQL, making it easier to integrate with other systems.
- To use the FDW feature, you need to install the appropriate extension and execute setup commands, such as CREATE FOREIGN TABLE, CREATE SERVER and CREATE USER MAPPING
- To use foreign tables in PostgreSQL, the analyzer/analyser creates a query tree based on their definitions stored in the pg_catalog.pg_class and pg_catalog.pg_foreign_table catalogs. The planner or executor then uses a specific library to connect to the remote server, with connection parameters stored in the pg_catalog.pg_user_mapping and pg_catalog.pg_foreign_server catalogs. The use_remote_estimate option can be set to on to obtain statistics for planning using the EXPLAIN command, with postgres_fdw being the only extension to reflect the results. Finally, the planner generates a plain SQL statement from the plan tree's scan paths of the foreign tables using deparsing.
- After deparsing, the executor sends the SQL statements to the remote server and receives the result. The method for sending the SQL statements to the remote server varies based on the developer of each extension. For instance, mysql_fdw sends SQL statements without using a transaction. This process allows PostgreSQL to access and manipulate data in foreign tables as if it were a local table, making it a powerful tool for data integration and migration across different systems.
- When executing a multi-table query, postgres_fdw fetches each foreign table using a single-table SELECT statement and then joins them on the local server. In version 9.5 or earlier, even if the foreign tables are stored in the same remote server, postgres_fdw fetches them individually and joins them on the local server. However, in version 9.6 or later, postgres_fdw has been improved and can execute the remote join operation on the remote server when the foreign tables are on the same server and the use_remote_estimate option is on. This improvement can significantly improve the performance of multi-table queries involving foreign tables on the same server.
- In version 9.5 or earlier, the sort operation, such as ORDER BY, is processed on the local server, i.e. the local server fetches all the target rows from the remote server prior to the sort operation.
Top comments (0)