Postgres SQL for Dummies: Foreign Data Wrappers (Part 4)

In this part 4 of the series, we will be discussing the Foreign Data Wrappers support in Postgres. It is certainly a very interesting feature.

Before getting to the the FDWs lets first discuss a bit about the SQL/MED feature of SQL which is used by FDWs. In SQL/MED, a table on a remote server is called a foreign table, and FDWs allows managing foreign tables just like local tables. You need to install the extensions and then you can access remote tables just like local tables using a select query. But how does this work exactly? Lets find out:

1. The analyzer/analyser creates the query tree of the input SQL.
2. The planner is responsible for connecting to the remote server.
3. An optional cost estimate check can also be done by planner if we turn on the use_remote_estimate option.
4. Plan text statement is created from SQL text known as deparsing.
5. The plan text is sent by the executor to the remote server and its results is also received by the executor.

It should be noted that for optimization, depending on the type of query some operations might be performed locally such as part of ORDER_BY and some part of aggregate functions.