postgres_fdw

What postgres_fdw does

postgres_fdw implements the SQL/MED (Management of External Data) standard for accessing external PostgreSQL databases. I appreciate this — standards exist for a reason, and SQL/MED has been part of the SQL specification since 2003. The extension creates a live connection to a remote PostgreSQL server and exposes remote tables as foreign tables in your local database. Queries against these foreign tables are translated into remote SQL, executed on the remote server, and the results are returned over the network.

The extension is smart about what it sends to the remote server. Rather than fetching all rows and filtering locally, postgres_fdw pushes down WHERE clauses, JOINs between foreign tables on the same server, aggregates, sorts, and LIMIT clauses when it can. There is an elegance to this: the work travels to where the data lives, rather than the data travelling to where the work happens. This query pushdown is what makes postgres_fdw practical for real workloads — it minimizes the data transferred over the network.

postgres_fdw also supports write operations. INSERT, UPDATE, and DELETE on foreign tables are executed as remote statements within a remote transaction that is coordinated with the local transaction. If the local transaction rolls back, the remote changes roll back too.

When to use postgres_fdw

Common scenarios where postgres_fdw is the right tool:

• Cross-database queries — query tables in a different PostgreSQL database on the same or a different server without ETL pipelines or data duplication
• Data federation — combine data from multiple PostgreSQL instances into a single query, such as aggregating metrics from regional databases
• Gradual migrations — move tables between servers one at a time while both old and new applications continue to work, using foreign tables to bridge the gap
• Read replicas for specific tables — expose a subset of tables from a production database to an analytics or reporting database
• Cross-database foreign keys — reference data in another database without duplicating it locally (not enforced by constraints, but queryable)
• Development and testing — query production data from a staging environment without full database copies

Installation and setup

postgres_fdw is a contrib module that ships with PostgreSQL. No additional packages or shared library loading is required — a simple CREATE EXTENSION is all it takes.

-- postgres_fdw ships with PostgreSQL (contrib module)
CREATE EXTENSION postgres_fdw;

One statement. The door to the other estate is now open.

Step 1: Create a foreign server

A foreign server object stores the connection details for the remote PostgreSQL database. It accepts the same connection parameters as libpq — host, port, dbname, and any SSL options.

-- Create a foreign server pointing to the remote database
CREATE SERVER remote_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'remote-host.example.com', port '5432', dbname 'remote_db');

Step 2: Create a user mapping

A user mapping associates a local PostgreSQL user with credentials on the remote server. Each local user who needs to query foreign tables must have a mapping.

-- Map a local user to a remote user
CREATE USER MAPPING FOR current_user
  SERVER remote_server
  OPTIONS (user 'remote_user', password 'remote_password');

Step 3: Create foreign tables

You can define foreign tables one at a time with CREATE FOREIGN TABLE, or import an entire schema at once with IMPORT FOREIGN SCHEMA.

-- Create a foreign table manually
CREATE FOREIGN TABLE remote_orders (
  id          bigint,
  customer_id bigint,
  total       numeric(10,2),
  created_at  timestamptz
)
SERVER remote_server
OPTIONS (schema_name 'public', table_name 'orders');

-- Import all tables from a remote schema at once
IMPORT FOREIGN SCHEMA public
  FROM SERVER remote_server
  INTO local_schema;

-- Import only specific tables
IMPORT FOREIGN SCHEMA public
  LIMIT TO (orders, customers, products)
  FROM SERVER remote_server
  INTO local_schema;

IMPORT FOREIGN SCHEMA is the preferred approach when you need access to many tables. It reads the remote schema's table definitions and creates matching foreign tables locally, including column names, types, and NOT NULL constraints. Far tidier than defining them one at a time.

Querying and writing

Once foreign tables are in place, you use them like any other table. SELECT, INSERT, UPDATE, and DELETE all work.

-- Query a foreign table like any local table
SELECT customer_id, sum(total)
FROM remote_orders
WHERE created_at >= '2026-01-01'
GROUP BY customer_id
ORDER BY sum(total) DESC
LIMIT 10;

-- Join a local table with a foreign table
SELECT c.name, sum(ro.total) AS lifetime_value
FROM customers c
JOIN remote_orders ro ON ro.customer_id = c.id
GROUP BY c.name
ORDER BY lifetime_value DESC;

-- Write to a foreign table
INSERT INTO remote_orders (customer_id, total, created_at)
VALUES (42, 99.95, now());

UPDATE remote_orders SET total = 109.95 WHERE id = 1001;
DELETE FROM remote_orders WHERE id = 1001;

Joins between local and foreign tables work, but the join itself executes locally — PostgreSQL fetches the foreign data first, then joins it with local data. Joins between two foreign tables on the same remote server can be pushed down and executed remotely, which is significantly faster.

Performance tuning

Foreign table queries involve network round trips, so performance tuning matters rather more than with local tables. A query that completes in 2ms locally may take 50ms when it must travel across the network, wait in a queue, execute, and return. The key levers:

• use_remote_estimate — tells the planner to ask the remote server for row count estimates instead of using local defaults. This produces better query plans, especially for complex queries.
• fetch_size — controls how many rows are retrieved per network round trip (default: 100). Increase this for queries that return large result sets to reduce round trip overhead.
• batch_size — controls how many rows are sent per INSERT round trip (default: 1, available in PostgreSQL 14+). Setting this to 100 or higher can improve bulk insert performance by 5-10x.
• Materialized views — for frequently queried foreign data that does not need to be real-time, cache the results in a local materialized view with local indexes.

-- Enable use_remote_estimate for better plans on the remote server
ALTER SERVER remote_server OPTIONS (ADD use_remote_estimate 'true');

-- Increase fetch_size for large result sets (default: 100)
ALTER SERVER remote_server OPTIONS (ADD fetch_size '1000');

-- Increase batch_size for bulk inserts (default: 1, available in PostgreSQL 14+)
ALTER FOREIGN TABLE remote_orders OPTIONS (ADD batch_size '100');

-- Cache foreign table results with a materialized view
CREATE MATERIALIZED VIEW recent_orders AS
SELECT * FROM remote_orders
WHERE created_at >= now() - interval '7 days';

CREATE INDEX ON recent_orders (customer_id);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY recent_orders;

Foreign tables do not have local indexes — a point worth remembering before one begins debugging a query plan that seems inexplicably reluctant. If you need indexed lookups on remote data, either ensure the remote table has appropriate indexes (which postgres_fdw can use via pushdown) or materialize the data locally.

Query pushdown details

Query pushdown is the most important performance feature of postgres_fdw, and the feature that separates it from a blunt instrument. The more work that happens on the remote server, the less data crosses the network.

Custom functions, user-defined operators, and expressions involving local tables are not pushed down. Use EXPLAIN VERBOSE to see exactly which parts of a query are sent to the remote server — the remote SQL appears in the plan output.

Cloud availability

Cloud providers may restrict outbound connections to specific network configurations (VPC peering, private endpoints, allowlisted IPs). Check your provider's networking documentation before setting up cross-server foreign data wrappers.

How Gold Lapel relates

If I may be direct: foreign table queries are the most common source of latency that teams fail to notice, because the queries look identical to local ones. Every execution crosses the network, and there are no local indexes to assist. Gold Lapel pays particular attention to this.

When Gold Lapel observes the same foreign table query pattern appearing repeatedly with similar parameters, it recognizes an opportunity. Rather than allowing your application to make the same cross-server journey hundreds of times per hour, it can recommend — or create automatically — a materialized view that caches the foreign data locally, complete with appropriate indexes. The remote server is consulted once. Subsequent queries are served from the local cache. The network round trips simply cease.

Gold Lapel also tracks foreign table query execution times alongside local queries, which surfaces a distinction that is otherwise surprisingly difficult to make: is this endpoint slow because of a missing index, or because it is waiting on a remote server? The answer determines whether you need index tuning, a materialized view, or a conversation about your architecture. I find that knowing which question to ask is often the greater part of the answer.