TimescaleDB’s Foreign Data Wrappers (FDWs) let you query data in other PostgreSQL databases, or even non-PostgreSQL sources, as if it were local.
Let’s see this in action. Imagine you have a sales_europe table in a separate PostgreSQL instance for European sales data, and your main TimescaleDB instance handles global sales.
Main TimescaleDB Instance (e.g., tsdb_global)
-- Install the postgres_fdw extension if you haven't already
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
-- Create a foreign server definition
-- 'foreign_server_europe' is a name you choose
-- 'postgres' is the type of foreign data wrapper
CREATE SERVER foreign_server_europe
FOREIGN DATA WRAPPER postgres
OPTIONS (host '192.168.1.100', port '5432', dbname 'tsdb_europe');
-- Define user mapping for authentication
-- 'current_user' means use the current PostgreSQL user's credentials
CREATE USER MAPPING FOR current_user
SERVER foreign_server_europe
OPTIONS (user 'sales_user', password 'supersecretpassword');
-- Create a foreign table definition
-- This mirrors the structure of the 'sales_europe' table in the remote database
CREATE FOREIGN TABLE sales_europe (
sale_id BIGSERIAL PRIMARY KEY,
product_name VARCHAR(100),
amount NUMERIC(10, 2),
sale_time TIMESTAMPTZ
)
SERVER foreign_server_europe
OPTIONS (schema_name 'public', table_name 'sales_europe');
-- Now you can query it like a local table
SELECT * FROM sales_europe WHERE sale_time >= '2023-01-01';
-- You can even join it with local TimescaleDB tables
SELECT
g.sale_id,
g.amount AS global_amount,
e.amount AS europe_amount
FROM sales_global g
JOIN sales_europe e ON g.product_name = e.product_name
WHERE g.sale_time::DATE = e.sale_time::DATE;
Remote PostgreSQL Instance (tsdb_europe)
-- This is just a regular PostgreSQL table
CREATE TABLE sales_europe (
sale_id BIGSERIAL PRIMARY KEY,
product_name VARCHAR(100),
amount NUMERIC(10, 2),
sale_time TIMESTAMPTZ
);
INSERT INTO sales_europe (product_name, amount, sale_time) VALUES
('Gadget', 150.75, '2023-01-15 10:30:00+00'),
('Widget', 25.00, '2023-01-16 11:00:00+00');
This setup allows your tsdb_global instance to access sales_europe without physically moving or replicating the data. The postgres_fdw extension handles the communication, fetching data from the remote tsdb_europe instance when a query references the sales_europe foreign table.
The core problem FDWs solve is data distribution and access. Instead of a monolithic database holding everything, you can segregate data by region, by type, or by lifecycle (e.g., hot vs. cold data) and still query it cohesively. This is particularly useful in microservice architectures or when integrating with legacy systems. The FDW acts as a transparent proxy, translating your local SQL query into a remote PostgreSQL query, retrieving the results, and presenting them as if they came from a local table.
Internally, when you query a foreign table, the FDW on the client (your tsdb_global instance) receives the query. It then communicates with the FDW on the server (the tsdb_europe instance) to determine the remote table’s structure. The client FDW rewrites the query to be executed remotely. The server FDW executes this rewritten query against the actual local table (sales_europe on tsdb_europe), fetches the data, and sends it back to the client FDW. The client FDW then presents this data to your PostgreSQL session as if it were a local table.
This process allows for pushdown of operations. If you filter sales_europe with WHERE sale_time > '2023-01-01', the postgres_fdw will send this WHERE clause to the remote server. The remote tsdb_europe PostgreSQL instance will filter the sales_europe table before sending any data back, making the query much more efficient. Joins between local and foreign tables are also optimized; PostgreSQL will decide whether to pull data from the foreign table to the local server for joining, or push down parts of the join to the foreign server if possible.
One aspect that significantly impacts performance, and often catches people off guard, is the network latency and the cost of fetching data. By default, the FDW might fetch entire columns from the remote server even if your query only needs a few rows. However, PostgreSQL’s query planner is quite sophisticated. For foreign tables, it can estimate the cost of fetching data from the remote server. If the estimated cost of fetching a large amount of data and processing it locally is higher than the cost of performing the operation remotely, the planner will try to push down as much of the operation as possible. This means WHERE clauses, ORDER BY, and even parts of JOIN conditions can be sent to the remote server for execution, drastically reducing the amount of data transferred over the network. The FDW’s ability to leverage EXPLAIN and ANALYZE on the foreign server is crucial for understanding and tuning these operations.
The next step is often dealing with performance tuning for complex queries involving multiple FDWs or when the remote data volume is massive.