DEV Community

Nile Lazarus
Nile Lazarus

Posted on • Edited on

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

Image of Docusign

🛠️ Bring your solution into Docusign. Reach over 1.6M customers.

Docusign is now extensible. Overcome challenges with disconnected products and inaccessible data by bringing your solutions into Docusign and publishing to 1.6M customers in the App Center.

Learn more

Top comments (0)

Billboard image

Try REST API Generation for Snowflake

DevOps for Private APIs. Automate the building, securing, and documenting of internal/private REST APIs with built-in enterprise security on bare-metal, VMs, or containers.

  • Auto-generated live APIs mapped from Snowflake database schema
  • Interactive Swagger API documentation
  • Scripting engine to customize your API
  • Built-in role-based access control

Learn more

👋 Kindness is contagious

Discover a treasure trove of wisdom within this insightful piece, highly respected in the nurturing DEV Community enviroment. Developers, whether novice or expert, are encouraged to participate and add to our shared knowledge basin.

A simple "thank you" can illuminate someone's day. Express your appreciation in the comments section!

On DEV, sharing ideas smoothens our journey and strengthens our community ties. Learn something useful? Offering a quick thanks to the author is deeply appreciated.

Okay