FDWs (Foreign Data Wrappers):
PostgreSQL's Foreign Data Wrappers (FDW) provide access to distant data sources via SQL/MED (SQL Management of External Data), a 2003 addition to the SQL standard. A table on a different server is referred to as a foreign table in SQL/MED. Foreign tables that are similar to local tables are managed by PostgreSQL's FDW using SQL/MED.
You must install the required extension and provide the proper parameters in order to access foreign tables located on distant servers. Once configured, you can use SELECT queries to query the foreign tables in the same way you would local ones. Even foreign tables hosted on multiple servers can be used in join procedures.
How FDWs Perform:
There are various steps in the PostgreSQL FDW functionality. On the basis of the supplied SQL and the definitions of foreign tables kept in system catalogues, the analyzer first builds a query tree. The planner or executor then uses particular libraries dependent on the FDW extension being used to connect to the remote server. The system catalogues also contain information on the connection parameters.
Depending on whether it is enabled, the planner can utilise the EXPLAIN commands to gather data and calculate the cost of query plans for foreign tables. Postgres_fdw is one of the FDW extensions that supports this capability. Deparsing is the procedure through which the planner converts the plan tree into a plain text SQL statement.
The result is obtained once the executor delivers the deparsed SQL statement to the remote server. The procedure for delivering SQL commands and getting results may differ depending on the FDW extension. For instance, although postgres_fdw sends a more complicated series of SQL commands including transactions, cursor declarations, and fetching results, mysql_fdw transmits statements without using a transaction.
Features of the Postgres_fdw Extension
The PostgreSQL Global Development Group officially maintains the postgres_fdw extension as a module. It is a part of the PostgreSQL source code tree and is updated with every new version.
Over the course of several iterations, the addon has undergone significant improvements and optimizations. For instance, in version 9.6, postgres_fdw gained the ability to execute UPDATE or DELETE actions entirely on the remote server, as well as sorting and joining operations. Later versions expanded the usage of ORDER BY and LIMIT clauses, handled partitioned foreign tables, and included support for pushing aggregate functions to the remote server.
In addition to SELECT operations, Postgres_fdw can handle DML statements (INSERT, UPDATE, DELETE) on foreign tables.
Multi-Table Query
Postgres_fdw uses single-table SELECT queries to fetch each foreign table independently while processing a multi-table query, and then it joins the results on the local server.
Even if the foreign tables were on the same remote server in prior versions (9.5 and earlier), postgres_fdw would fetch them separately and handle the join locally. To execute remote join operations when the foreign tables are on the same server and the use_remote_estimate option is enabled, the extension was improved in version 9.6 and later.
Rows from each foreign table are fetched during execution, and the fetched rows are then sorted on the local server.
Top comments (0)