Exploring PostgreSQL: Foreign Data Wrappers (FDW).

PostgreSQL is a robust open-source relational database management system that is well-known for its adaptability and versatility. Foreign Data Wrappers (FDW) are one of its notable features, allowing PostgreSQL to connect to and interact with data sources outside of the database. In this post, we'll look at PostgreSQL FDW, explaining what it is, and why it's useful.

What is PostgreSQL FDW?

Foreign Data Wrappers (FDW) is a PostgreSQL extension that lets you access and manipulate data from remote or external data sources as if they were local tables. This feature simplifies data integration, migration, and the creation of linked databases, allowing you to deal with data from different sources in real time seamlessly.

Why Use PostgreSQL FDW?

• Data Integration: FDW enables you to import data from several databases and file systems into your PostgreSQL environment without the need for complex ETL operations.

• Real-time Data Aggregation: In real-time, you can construct aggregated views that include data from local and remote sources, making it easier to examine data from different sources.

• Data Migration: When transitioning from one database system to PostgreSQL, FDW can simplify the migration process by providing access to your old database while migrating data gradually.

• FDW supports a wide range of external data sources, including other relational databases, NoSQL databases, REST APIs, CSV files, and others.

How to set up PostgreSQL FDW

To set up PostgreSQL FDW you need to:

1. Enable FDW in PostgreSQL:To use FDWs, PostgreSQL must be compiled with FDW support. Most modern distributions have this enabled by default, but you can verify it by checking the pg_config utility. If FDW support is missing, you might need to recompile PostgreSQL with the --with-fdw flag.

2. install the postgres_fdw extension: You can do this by running the following command:
   

CREATE EXTENSION postgres_fdw;

1. Create a Foreign Server: Once you have created a foreign server, you can create a foreign table. A foreign table is a virtual table that represents the data in the external database. You can create a foreign table by running the following command

CREATE SERVER remote_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remote_host', dbname 'remote_db', port '5432');

4.Create User Mapping: Establish a user mapping to provide the required authentication information for the foreign server. This links a local PostgreSQL user to a remote user.

CREATE USER MAPPING FOR local_user
SERVER remote_db
OPTIONS (user 'remote_user', password 'remote_password');

1. Create Foreign Tables: Finally, create foreign tables that represent the external data in your local database. You define the structure of these tables, mapping them to the remote data source.

CREATE FOREIGN TABLE foreign_table (
  id INT,
  name TEXT
)
SERVER remote_db
OPTIONS (schema_name 'some_schema', table_name 'foreign_table');

How FDW works.

When running a query against a Foreign Table with FDW, the operations performed on the local and remote Postgres servers are shown in the flow chart below.

1. When running a query against a Foreign Table with FDW, the operations performed on the local and remote Postgres servers are shown in the flow chart below.
2. If the use_remote_estimate option is enabled, Planner connects to a remote server.
3. If the use_remote_estimate option is enabled, Planner calculates the cost by executing the EXPLAIN command on the remote server. If the use_remote_estimate option is not activated, Planner calculates the cost based on statistical information from the locally stored Foreign Table. 4 .The Planner goes through a deparsing process to convert the Plan Tree back into a plain text SQL statement and delivers it to the Executor. 5 . Executor passes the SQL statement to the remote server and receives the result back.

The use_remote_estimate option is an option that sets whether to calculate and
return the cost directly from the remote server using the EXPLAIN command before
performing a query against the remote server. The default value is false and can be
set to a remote server or remote table. If this option is set on both the remote
server and the table, the remote table's settings take precedence, and queries
targeting the remote table may behave differently from other tables on the same
remote server.

PostgreSQL Foreign Data Wrappers are a powerful tool for integrating external data sources seamlessly into your PostgreSQL database. You can harness the full potential of FDWs to query and manipulate data across heterogeneous systems, making your data management tasks more efficient and flexible.