Summary of Chapter# 4 : "Foreign Data Wrappers (FDW)" from the book "The Internals of PostgreSQL"
Tags: postgres, apacheage, database, agenssql
Published: 2023-07-08 01:47:14

This blog aims to assist you in understanding the concepts of Chapter:4 [Foreign Data Wrappers (FDW)] from the book **_[The Internals of PostgreSQL](https://www.interdb.jp/pg/)_**.

> _**Note: Ensure that you have a thorough understanding of [Chapter 3](https://dev.to/vkt1271/summary-of-chapter-3-query-processing-from-the-book-the-internals-of-postgresql-5g05) 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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9d81ce55l7ohkz41rwob.png)

**Ways to Access Remote Servers on your system**

1. By using **Select** command 

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/kp3w3bj0dvet9r99mlu1.png)

2. By using **Join** operation

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/76v1renzh216ewzvo9wa.png)

---------------------------------------------------------------

## 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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/oyjw0qaatc01b3v49jph.png)

`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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/44iifqwnu2dqzdar2r9c.png)

`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:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gq467ls4k7uyeecnd3ce.png)

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

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/b3dmykkku56eu7on74mg.png)

----------------------------------------------------------------

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](https://dev.to/vkt1271/summary-of-chapter-5-concurrency-control-from-the-book-the-internals-of-postgresql-part-1-2i99)

If you want to understand [PostgreSQL In-Depth](https://www.youtube.com/playlist?list=PLhr1KZpdzukeAZuX0pNZ1lLDkaZWBOhQT).