We split a monolith database into two, kept a few cross-service queries working with postgres_fdw, and called the migration a success. A week later one dashboard query that used to take 200ms was taking 90 seconds. The SQL was identical.
The difference was that one of the tables it joined now lived on another server, reached through a foreign table. The query looked exactly as local as before. Under the hood, PostgreSQL was pulling the entire remote table across the network and joining locally, because it could not push the join down.
Foreign data wrappers are wonderful and dangerous for the same reason: a remote table looks just like a local one, so the cost of touching it is completely hidden in the SQL.
Pushdown is the whole game
postgres_fdw is fast when it can push work to the remote server: send the WHERE clause so the remote filters, send the join so the remote joins, send the aggregate so the remote aggregates, and ship back only the small result. It is slow when it cannot, because then it fetches rows and does the work locally.
The single most important habit with FDW is reading the plan to see what got pushed down. EXPLAIN shows a Foreign Scan node, and the remote SQL it will send. If your filter is not in that remote SQL, you are filtering locally after transferring everything.
EXPLAIN (VERBOSE, ANALYZE)
SELECT o.id, c.name
FROM orders o
JOIN remote_customers c ON c.id = o.customer_id
WHERE o.created_at >= now() - interval '1 day';
-- In VERBOSE output, read "Remote SQL:" under the Foreign Scan.
-- Is the filter/join there, or is it scanning the whole remote table?Why pushdown fails
Joins only push down when both tables are on the same foreign server and the wrapper can prove it is safe. A join between a local table and a remote one cannot be pushed to the remote, so the remote side is fetched and joined locally. That was our dashboard: local orders joined to remote customers, no pushdown possible.
Pushdown also breaks when a predicate uses a function or operator the wrapper will not send, when collations differ between the two servers, or when a column type does not map cleanly. Any of these silently turns a pushed-down filter into a local one over a full transfer.
Give the planner remote estimates
By default the local planner guesses about remote table sizes, and it guesses badly. Turn on use_remote_estimate so it asks the remote server to EXPLAIN and returns real costs. This is often the difference between a sane plan and a catastrophic one, because the planner finally knows the remote table has ten million rows.
Pair it with running ANALYZE on the foreign tables so local statistics about them are not stale. Stale or default stats are a common reason the planner chooses to pull everything.
-- Ask the remote for real cost estimates
ALTER SERVER remote_db OPTIONS (ADD use_remote_estimate 'true');
-- Refresh local stats about the foreign table
ANALYZE remote_customers;Tune the transfer: fetch_size and batch_size
When data does have to move, move it in chunks. fetch_size controls how many rows postgres_fdw retrieves per round trip on reads; the default is small, and raising it dramatically cuts latency on large result sets by reducing network round trips. batch_size does the same for inserts into foreign tables, grouping rows per statement.
These are set on the server or per foreign table. For any workload that reads or writes more than a handful of remote rows, the defaults leave a lot of performance on the table.
-- Bigger reads and writes per round trip
ALTER SERVER remote_db OPTIONS (ADD fetch_size '10000');
ALTER FOREIGN TABLE remote_events OPTIONS (ADD batch_size '1000');Design around the network boundary
The most reliable fix is architectural: do not write queries that join local and remote tables in the hot path. If you need remote data joined to local data frequently, replicate the needed slice locally (logical replication, a materialized view refreshed on a schedule, or a periodic sync) and join against the local copy.
Use FDW for what it is good at: occasional cross-system reads, admin queries, and pushing down self-contained remote work. Treat it as a network call wearing a SQL costume, because that is what it is, and put the same scrutiny on it that you would on any cross-service API call in a request path.
- Always read the Remote SQL in EXPLAIN VERBOSE to confirm pushdown.
- Local-to-remote joins do not push down; co-locate or replicate instead.
- Enable use_remote_estimate and ANALYZE foreign tables.
- Raise fetch_size and batch_size for bulk read/write transfers.
- Keep cross-server joins out of latency-sensitive request paths.
The practical standard
The best PostgreSQL performance work is boring in the right way. Name the failure mode, capture the before plan or metric, make one change, and compare the exact same signal afterward. Anything else is just a more confident guess.