Foreign data wrappers and tablespaces

conceptThe concept

SQL/MED in PostgreSQL has four objects: a foreign data wrapper (the driver, installed by an extension), a server (where to connect), a user mapping (which credentials a local role uses on that server) and foreign tables (column definitions mapped to remote data). IMPORT FOREIGN SCHEMA creates foreign tables for a whole remote schema at once.

A tablespace is a name for a directory. pg_default is the data directory's base/, pg_global holds shared catalogs. CREATE TABLESPACE fast LOCATION '/mnt/nvme/pg' registers another directory; tables and indexes can then be created there or moved with ALTER TABLE ... SET TABLESPACE, which rewrites them under an ACCESS EXCLUSIVE lock. temp_tablespaces sends sort and hash spill files to a chosen volume.

whyWhy it matters

FDWs replace fragile export-import jobs: a reporting database can join live reference data from another service, a migration can copy tables with INSERT ... SELECT from the old server, and partner CSV drops can be queried in place. Tablespaces exist for the rarer case of putting specific data on faster or bigger storage on a self-managed server.

Use caseReal-world scenario

During a database split, the new billing database reads the customers table from the old monolith through postgres_fdw for three months. Queries filter by customer id, which is pushed down to the remote server, and a dashboard compares row counts on both sides until the cutover.

Try itExamples

Run and edit these in the interactive lesson.

BEGIN;
CREATE EXTENSION file_fdw;
CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;
COPY (SELECT id, name, city FROM customers ORDER BY id LIMIT 5)
  TO '/tmp/partner_customers.csv' WITH (FORMAT csv, HEADER);
CREATE FOREIGN TABLE partner_customers (id int, name text, city text)
  SERVER files OPTIONS (filename '/tmp/partner_customers.csv', format 'csv', header 'true');
SELECT * FROM partner_customers ORDER BY id;
EXPLAIN (COSTS OFF) SELECT * FROM partner_customers WHERE city = 'London';
ROLLBACK;

BEGIN;
CREATE EXTENSION file_fdw;
CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;
COPY (SELECT id, email FROM customers WHERE tier = 'platinum' ORDER BY id LIMIT 20)
  TO '/tmp/vip_list.csv' WITH (FORMAT csv);
CREATE FOREIGN TABLE vip_list (customer_id int, email text)
  SERVER files OPTIONS (filename '/tmp/vip_list.csv', format 'csv');
SELECT count(DISTINCT v.customer_id) AS vips, count(o.id) AS orders, sum(o.total) AS revenue
FROM vip_list v JOIN orders o ON o.customer_id = v.customer_id;
SELECT s.srvname, w.fdwname, f.ftoptions
FROM pg_foreign_table f
JOIN pg_foreign_server s ON s.oid = f.ftserver
JOIN pg_foreign_data_wrapper w ON w.oid = s.srvfdw;
ROLLBACK;

The whole aggregate ran remotely; only one row per customer crossed the network. If Remote SQL lacks your WHERE clause, every row is being fetched and filtered locally.

CREATE EXTENSION postgres_fdw;
CREATE SERVER monolith FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'monolith.internal', port '5432', dbname 'shop', fetch_size '1000', use_remote_estimate 'true');
CREATE USER MAPPING FOR billing_app SERVER monolith
  OPTIONS (user 'billing_reader', password '...');      -- or use a password file / certificate
CREATE SCHEMA legacy;
IMPORT FOREIGN SCHEMA public LIMIT TO (customers, orders) FROM SERVER monolith INTO legacy;

EXPLAIN (VERBOSE, COSTS OFF)
SELECT customer_id, count(*) FROM legacy.orders WHERE created_at >= now() - interval '1 day' GROUP BY 1;
--  Foreign Scan
--    Relations: Aggregate on (legacy.orders)
--    Remote SQL: SELECT customer_id, count(*) FROM public.orders WHERE ((created_at >= $1::timestamptz)) GROUP BY 1

SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size
FROM pg_tablespace ORDER BY spcname;
SELECT relname, reltablespace, pg_relation_filepath(oid) AS file
FROM pg_class WHERE relname IN ('orders', 'pg_database') ORDER BY relname;

BEGIN;
CREATE TABLESPACE fast LOCATION '/tmp/fast';
ROLLBACK;

-- the directory must exist, be empty and be owned by the postgres OS user
CREATE TABLESPACE fast LOCATION '/mnt/nvme/pgdata';
GRANT CREATE ON TABLESPACE fast TO app_owner;

CREATE INDEX CONCURRENTLY orders_created_idx ON orders (created_at) TABLESPACE fast;
ALTER TABLE events SET TABLESPACE fast;      -- full rewrite, ACCESS EXCLUSIVE lock

-- spill files on a separate volume
ALTER SYSTEM SET temp_tablespaces = 'fast';

internalsUnder the hood

The planner asks the wrapper for estimates and which clauses it can push down. postgres_fdw sends a Remote SQL query (see EXPLAIN VERBOSE), fetches rows in batches of fetch_size (100 by default) and, with use_remote_estimate, runs remote EXPLAINs for better costs. Functions and operators are pushed down only if they are built-in or listed in the server's extensions option. For tablespaces, the catalog stores only a symlink under pg_tblspc/; backups, replicas and restores must all have the same path available, which is why managed cloud services do not let you create them.

Avoid / preferDo this, not that

SELECT * FROM legacy.orders o
JOIN local_customers c ON c.id = o.customer_id
WHERE c.country = 'IN';

SELECT * FROM legacy.orders o
WHERE o.customer_id IN (SELECT id FROM local_customers WHERE country = 'IN');
-- check EXPLAIN VERBOSE: Remote SQL should contain the filter

-- "the disk is full, add a tablespace on a USB disk"
CREATE TABLESPACE overflow LOCATION '/media/usb/pg';

-- grow the data volume, or archive and partition old data

PitfallsCommon mistakes

• Storing a remote password in a user mapping that every role can read, instead of a mapping per role or certificate authentication.
• Assuming a filter on a foreign table is pushed down without checking Remote SQL.
• Using tablespaces on a single disk to "organise" data; it adds operational risk with no performance gain.
• Forgetting that ALTER TABLE ... SET TABLESPACE rewrites the table under an exclusive lock.

LimitsWhen not to use it

Do not use postgres_fdw as a permanent replacement for replication or a data pipeline on hot paths: every query depends on the other server's availability and latency. Do not create tablespaces on managed services (they are not allowed) or on self-managed servers with a single volume.

SecuritySecurity notes

Only superusers can use file_fdw on arbitrary paths, because reading server files is a privilege escalation. For postgres_fdw, grant USAGE on the server to specific roles, give the remote user minimal privileges, require TLS in the server options, and note that non-superusers must authenticate with a password or certificate unless a superuser marks the mapping password_required false.

ChallengeTry it yourself

BEGIN;
CREATE EXTENSION file_fdw;
CREATE SERVER files FOREIGN DATA WRAPPER file_fdw;
COPY (SELECT sku, name, price FROM products ORDER BY price DESC LIMIT 3)
  TO '/tmp/top_products.csv' WITH (FORMAT csv, HEADER);
CREATE FOREIGN TABLE top_products (sku text, name text, price numeric)
  SERVER files OPTIONS (filename '/tmp/top_products.csv', format 'csv', header 'true');
SELECT * FROM top_products ORDER BY price DESC;
ROLLBACK;

InterviewQuestions you should be able to answer

QuizCheck your understanding

4 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

Go deeperOfficial documentation

• CREATE EXTENSION
• Additional supplied modules
• Packaging related objects