[Summary] Chapter#04 "The Internals of PostgreSQL" Foreign Data Wrappers

Foreign Data Wrappers(FDW):

In SQL/MED tables on a remote server called foreign table, Foreign Data Wrapper uses SQL/MED to manage foreign tables which are similar to local tables.
After installing necessary extensions and making the appropriate settings, you can access the foreign table on the remote servers.

Overview:
All the appropriate extensions must be installed and executed the setup to use FDW: Like
Create Foreign Table
Create Server
Create User Mapping

How FDW performs in postgreSQL:

• The analyzer creates the query tree of the input
  Using the Create Foreign Table or Import Foreign Schema command, the analyzer creates a query tree of input SQL.

• The planner connects the remote server
  Using Create user mapping and Create Server commands is used to connect the parameters such as username and IP address and port number, to connect remote server planner uses specific library to connect to the remote database server.

• Planner execute EXPLAIN command for estimation the cost of each path

• Planner creates the plain text SQL

• Executor sends the plain text SQL

Deparesing:
Planner creates plain text SQL statements from plan tree scan paths of foreign tables to generate plan trees.

Sending SQL Statements and Receiving Results:
After deparsing the executor sends the deparsed SQL statement to the remote server and receives the result.
Developer of each extension decides the methods of sending SQL statements like mysql_fdw sends the SQL statement without using a transaction.

Following shown the SQL statement to execute a SELECT query in postgres_fdw

• Start the remote transaction
• Declare a cursor
• Execute FETCH commands to obtain result
• Receive the result from the remote server
• Close the cursor
• *Commit the remote transaction *

How the Postgres_fdw Extension Performs:
Postfres_fdw extension is a module maintained by PostgreSQL Global Development Group, postgres_fdw processes a single table Query and following postgres_fdw processes a multiple table query.

Multi-Table Query:
Postgres_fdw fetches foreign tables using the SELECT statement and then joins them on the local server. Postgres_fdw executes remote join operation on remote server when foreign tables are on the same server.

Sort Operation:
Postgres_fdw can execute the SELECT statement with ORDER BY clause on the remote server.

Aggregate Functions:
Aggregate functions such as AVG() and cont() are processed on the local server.

Reference