This chapter covers Foreign Data Wrappers and Parallel Query
SQL/MED, added to the SQL standard in 2003, allows access to remote data through features like foreign tables. PostgreSQL's Foreign Data Wrappers (FDW) implement SQL/MED and enable management of foreign tables similar to local tables. By installing the necessary extension and configuring the settings, foreign tables on remote servers can be accessed in PostgreSQL. Multiple servers, such as PostgreSQL and MySQL, can have their respective foreign tables. SELECT queries can be used to access these foreign tables from the local server, and join operations between foreign tables from different servers are also possible. The postgres_fdw extension is the officially developed and maintained FDW extension by the PostgreSQL Global Development Group, while other FDW extensions listed in the Postgres wiki may not receive regular maintenance. Detailed information on PostgreSQL's FDW is provided in Section 4.1.1, including an overview and Section 4.1.2 explaining the functionality of the postgres_fdw extension.
To utilize the FDW feature in PostgreSQL, the appropriate extension must be installed and setup commands (e.g., CREATE FOREIGN TABLE, CREATE SERVER, CREATE USER MAPPING) need to be executed. During query processing, the FDW functions are invoked:
1. Creating a query table
The analyzer/analyser creates the query tree of the input SQL using the definitions of the foreign tables
2. Connecting to Remote Server
To connect to the remote server, the planner (or executor) uses the specific library to connect to the remote database server.
3. Creating a Plan Tree using EXPLAIN commands
PostgreSQL's FDW, including extensions like postgres_fdw, mysql_fdw, tds_fdw, and jdbc2_fdw, supports obtaining statistics from foreign tables for query planning. By setting use_remote_estimate to on, the planner can query the remote server for plan cost estimation using the EXPLAIN command. Only postgres_fdw can utilize the results of EXPLAIN commands accurately, as it provides both start-up and total costs, while other DBMS FDW extensions may lack the necessary information for cost estimation.
4. Deparsing
The planner generates a plain text SQL statement based on the scan paths of foreign tables to create the plan tree. Extensions like mysql_fdw, redis_fdw, or rw_redis_fdw can generate SELECT statements specific to their respective foreign data sources.
*5. Sending SQL statements *
After deparsing, the executor sends the deparsed SQL statements to the remote server and receives the result.
In order to understand how postgre_fdw performs, we see how it deals with multi-table queries, sort operation and aggregate functions.
1. Multi-Table Query
To execute a multi-table query, postgres_fdw fetches each foreign table using a single-table SELECT statement and then join them on the local server.
2. Sort Operations
In version 9.5 or earlier, the sort operation, such as ORDER BY, is processed on the local server, which fetches all the target rows from the remote server prior to the sort operation while in version 9.6 or later, postgres_fdw can execute the SELECT statements with an ORDER BY clause on the remote server if possible.
3. Aggregate Functions
In version 9.6 or earlier, similar to the sort operation mentioned in the previous subsection, the aggregate functions such as AVG() and cont() are processed on the local server while in version 10 or later, postgres_fdw executes the SELECT statement with the aggregate function on the remote server if possible.
Top comments (0)