Foreign Data Wrappers in PostgreSQL

In this article, we will discuss two of the most interesting and practical features in PostgreSQL. Namely, Foreign Data Wrappers (FDW) and Parallel Query.

A table that is located on a remote server in SQL is known as a foreign table. In PostgreSQL, the tool used to manage foreign tables using SQL are known as Foreign Data Wrappers (FDW)

The foreign table from the remote server can be accessed after installing the required extension and tweaking the appropriate settings. Furthermore, even join operations can be executed with foreign tables that are located in different servers which are like the local tables.

Here is a brief overview of how the FDWs perform:

• The analyzer generates a query tree from the input SQL.

• A connection to the remote server is made using the executor/planner.

• In case the use_remote_estimate option is turned on, which is switched of by default, the planner will execute the EXPLAIN command that helps the user by generating a cost estimate for each plan path.

• Next, the executor will transfer a plain text SQL statement to the remote server and will consequently receive the results.

• Lastly, the received data is processed by the executor if necessary. E.g. the executor will perform a join processing in case a multi-table query is executed.

There are also a number of useful multi-table operations in PostgreSQL that can be used to perform various functions. Some of which are:

• Sort operations:
  Sort operations such as ORDER BY are processed on the local server. The local server fetches all the required rows from the remote server before the sort operation is executed.

• Aggregate functions:
  Similar to sort operations, aggregate functions are also processed on the local server. Some examples of aggregate functions include: AVG() and COUNT(). The executor sends the relevant query to the remote server and then retrieves the relevant query results.