Demystifying the Internals of PostgreSQL - Chapter 4

Welcome back to our journey into The Internals of PostgreSQL.
In the last blog in this series, we covered Chapter 3 'Query Processing'.
Now we're going to cover Chapter 4 'Foreign Data Wrappers and Parallel Query'. Let's jump right in.

Foreign Data Wrappers (FDW)

SQL Management of External Data (SQL/MED) is a part of the SQL Standard that was added in 2003. It states that a table on a remote server is called a foreign table. PostgreSQL's Foreign Data Wrappers (FDW) use SQL/MED to manage these foreign tables.
Once you install the required extension and configure your settings appropriately, you can begin accessing foreign tables on remote servers. For example, you can use SELECT queries to access foreign tables stored in different servers.
Many different FDW extensions have been developed and are listed in the Postgres wiki but the only properly maintained one is the postgres_fdw extension which has been officially developed and maintained by PostgreSQL Global Development Group.

To use the FDW feature, you will need to not only install the required extension but also execute setup commands like CREATE FOREIGN TABLE, CREATE SERVER and CREATE USER MAPPING.
The work flow of the FDW feature in PostgreSQL is as follows:

1. The Analyzer creates a query tree for the given SQL query using the foreign tables definitions. These definitions are stored in the pg_catalog.pg_class and pg_catalog.pg_foreign_table catalogs.
2. The Planner or Executor then connects to the remote server using the appropriate library. For example, postgres_fdw uses libpq to connect to a remote PostgreSQL server, and mysql_fdw uses libmysqlclient to connect to a mysql server.
3. If the use_remote_estimate option has been enabled, EXPLAIN commands are executed by the Planner for cost estimation of each plan path. If not, the embedded constant values are used by default.
4. Planner creates a plain text SQL statement from the plan tree. This process is called deparsing in PostgreSQL.
5. Executor sends the plain text SQL statement created by the Planner to the remote server and receives the result.

This section also details how the postgres_fdw extension performs and how it has evolved over the course of multiple versions of PostgreSQL starting with version 9.3. I highly recommend reading through it yourself to understand better as this section contains many examples which can help you better understand how different SQL operations and functions are handled by the FDW.

This section also includes Parallel Query however it is currently under construction