Summary of Chapter# 4 : "Foreign Data Wrappers (FDW)" from the book "The Internals of PostgreSQL"

This blog aims to assist you in understanding the concepts of Chapter:4 [Foreign Data Wrappers (FDW)] from the book The Internals of PostgreSQL.

Note: Ensure that you have a thorough understanding of Chapter 3 and basics of PostreSQL before we proceed to Chapter 4, as it forms the foundation for our exploration.

So, Let's Start:

Foreign Data Wrappers

• A specification by which we can access remote data, called SQL Management of External Data (SQL/MED).

• In SQL/MED, a table on a remote server is called a foreign table.

• PostgreSQL's Foreign Data Wrappers (FDW) are that use SQL/MED to manage foreign tables which are similar to local tables.

Basic concept of Foreign Data Wrappers (FDW) in PostgreSQL is depicted in figure below:

Ways to Access Remote Servers on your system

1. By using Select command

An Example using Select in PostgreSQL is depicted in figure below:

1. By using Join operation

An Example using Join in PostgreSQL is depicted in figure below:

---

How Foreign Data Wrappers (FDW) perform in Query Processing

• In order to use FDW, you need proper extensions and learn how to create foreign table, server, and user_mapping.

The process, How FDWs perform in PostgreSQL is depicted in figure below:

1. Creating a Query Tree

• The analyzer/analyser creates the query tree of the input SQL using the definitions of the foreign tables, which are stored in the pg_catalog.pg_class and pg_catalog.pg_foreign_table catalogs using the CREATE FOREIGN TABLE or IMPORT FOREIGN SCHEMA command.

2. Connecting to the Remote Server

• To make a connection with remote servers, planner uses some specific libraries.

• To connect to the remote PostgreSQL server, postgres_fdw uses the libpq.

• To connect to the mysql server, mysql_fdw uses the libmysqlclient developed by EnterpriseDB.

3. Deparesing

• The postgres_fdw recreates a plain SELECT text from the query tree that has been created by parsing and analysing, which is called deparsing in PostgreSQL.

Example of the plan tree that scans a foreign table in PostgreSQL is depicted in figure below:

4. Sending SQL Statements and Receiving Result

• The executor sends the deparsed SQL statements to the remote server and receives the result.

The process of typical sequence of SQL statements to execute a SELECT query in postgres_fdw in PostgreSQL is depicted in figure below:

The process of typical sequence of SQL statements to execute a SELECT query in mysql_fdw in PostgreSQL is depicted in figure below:

---

I hope, this blog has helped you in understanding the concepts of Foreign Data Wrappers in PostreSQL.

Check out summary of Chapter : 5 Part-1

If you want to understand PostgreSQL In-Depth.