Foreign data wrappers concept in PostgreSQL - Step-by-Step Execution

Concept Flow - Foreign data wrappers concept

Start Query on Local DB

↓

Check if Table is Foreign

↓

Use FDW to

↓

Connect to

↓

Remote Source

↓

Return Local Data

↓

Fetch Data

↓

Return Data to Local DB

↓

Query Result Returned

When a query accesses a foreign table, PostgreSQL uses the foreign data wrapper (FDW) to connect to the remote source, fetch data, and return it as if it were local.

Execution Sample

PostgreSQL

CREATE FOREIGN TABLE foreign_customers (
  id INT,
  name TEXT
) SERVER foreign_server;

SELECT * FROM foreign_customers;

This creates a foreign table linked to a remote server and queries all rows from it.

Execution Table

Step	Action	Evaluation	Result
1	Query starts on local DB	SELECT * FROM foreign_customers	Query recognized as foreign table access
2	Check if table is foreign	foreign_customers is foreign	Yes, use FDW
3	FDW connects to remote server	Connection established	Ready to fetch data
4	FDW sends query to remote source	SELECT * FROM foreign_customers	Remote source executes query
5	Remote source returns data	Rows received	Data ready to send back
6	FDW returns data to local DB	Data received	Data available for local query
7	Local DB returns query result	Rows from foreign table	User sees remote data as local
8	Query ends	No more data	Execution complete

💡 Query ends after all remote data is fetched and returned to local database

Variable Tracker

Variable	Start	After Step 3	After Step 5	Final
connection_status	disconnected	connected	connected	closed after query
data_buffer	empty	empty	rows received	rows returned
query_state	not started	running remote query	data fetched	completed

Key Moments - 3 Insights

Why does the local database need to check if the table is foreign before querying?

Because foreign tables require using the FDW to fetch data from a remote source, unlike local tables which are accessed directly. This is shown in execution_table step 2.

What happens if the remote server is unreachable during the query?

The FDW cannot fetch data, so the query will fail or timeout. This would stop the process at execution_table step 3 where connection is established.

Does the local database store the remote data permanently?

No, the data is fetched on demand and returned for the query. It is not stored locally unless explicitly copied. This is shown in execution_table steps 5 and 6.

Visual Quiz - 3 Questions

Look at the execution_table, at which step does the FDW send the query to the remote source?

A Step 3

B Step 4

C Step 5

D Step 6

💡 Hint

Check the 'Action' column for when the FDW sends the query to the remote source.

According to variable_tracker, what is the state of 'data_buffer' after step 5?

A rows returned

B empty

C rows received

D disconnected

💡 Hint

Look at the 'data_buffer' row under 'After Step 5' column.

If the table was not foreign, which step in execution_table would be skipped?

A Step 4

B Step 6

C Step 2

D Step 7

💡 Hint

Step 4 involves sending query to remote source, which only happens for foreign tables.

Concept Snapshot

Foreign Data Wrappers (FDW) let PostgreSQL query external data sources as if they were local tables.
Create foreign tables linked to remote servers.
When queried, FDW connects remotely, fetches data, and returns it.
Local DB treats foreign data like local data during query.
Useful for integrating different databases or external data seamlessly.

Full Transcript

Foreign data wrappers in PostgreSQL allow the database to access tables that are not stored locally but exist on remote servers or other data sources. When a query runs on a foreign table, PostgreSQL checks if the table is foreign. If yes, it uses the FDW to connect to the remote server, send the query, fetch the data, and return it as if it were local. This process involves establishing a connection, sending the query remotely, receiving rows, and returning them to the local database. The data is fetched on demand and not stored permanently unless copied. If the remote server is unreachable, the query fails. This mechanism enables seamless integration of external data sources into PostgreSQL queries.