skip to content

A PostgreSQL finance report over postgres_fdw foreign tables picks poor join plans and spends most of its time on the network; which postgres_fdw options do you tune, and what does each cost?

level: seniorimportance: should knowfreq 35%

answer

  1. estimates first, then transfer
  2. ask the remote planner
  3. foreign tables need manual ANALYZE
  4. rows per FETCH, rows per INSERT

basics

~20 s

Fix estimates with use_remote_estimate (a remote EXPLAIN during planning) or a scheduled ANALYZE of the foreign tables, plus fdw_startup_cost and fdw_tuple_cost. Cut round trips with fetch_size (100 rows per FETCH by default) and batch_size (1 row per INSERT).

solid answer

~40 s

Two problems, two sets of options. **Plans:** by default postgres_fdw estimates locally from statistics that autovacuum never gathers for foreign tables, so run `ANALYZE` on them on a schedule, or set `use_remote_estimate 'true'` on the server or table, which sends `EXPLAIN` to the remote side during planning. That costs round trips at plan time, but it also lets the planner use parameterized remote scans and remotely sorted input. `fdw_startup_cost` (100) and `fdw_tuple_cost` (0.2 since PostgreSQL 17) model connection and per-row transfer overhead. **Transfer:** scans read through a remote cursor `fetch_size` rows at a time, default 100, so a million-row scan is ten thousand round trips; raise it for bulk reads at a memory cost. Inserts send `batch_size` rows per statement, default 1, and batching switches off with `RETURNING` or row-level insert triggers.

code

sql · 14 lines
sql
-- Estimates: ask the billing server instead of guessing
ALTER FOREIGN TABLE billing_remote.invoice_lines
  OPTIONS (ADD use_remote_estimate 'true');

-- Or keep local statistics fresh; autovacuum never does this
ANALYZE billing_remote.customers;

-- Transfer: bigger FETCH batches for the large table only
ALTER FOREIGN TABLE billing_remote.invoice_lines
  OPTIONS (ADD fetch_size '10000');

-- Writes: send 500 rows per remote INSERT
ALTER FOREIGN TABLE billing_remote.adjustments
  OPTIONS (ADD batch_size '500');

go deeper

for a junior

Recall that postgres_fdw fetches remote rows in batches and that foreign tables need ANALYZE run by hand.

for a middle

Explain fetch_size and batch_size with their defaults, and the difference between local statistics and use_remote_estimate.

for a senior

Tune a real slow report: separate estimate problems from transfer problems, price the planning round trips of use_remote_estimate, and know when batching silently switches off.

for a principal

Decide whether a report should query live across servers at all, given its round-trip budget and the remote team's capacity, or move to a replicated copy.

## Two different problems A slow report over `postgres_fdw` usually has one or both of these causes: - **Bad estimates.** The local planner does not know how many rows a remote table holds or how selective a condition is, so it picks the wrong join order or join method. - **Too many round trips.** Even with a good plan, rows travel in small batches, and some write paths go one row per statement. They are tuned with different options, and fixing one does not fix the other. ## Getting the estimates right | Option | Set on | Default | What it does | |---|---|---|---| | `use_remote_estimate` | server or table | `false` | runs `EXPLAIN` on the remote server to get row counts and costs | | `fdw_startup_cost` | server | `100` | added to every foreign scan for connecting, parsing and planning remotely | | `fdw_tuple_cost` | server | `0.2` | extra cost per row transferred | | `analyze_sampling` | server or table | `auto` | where `ANALYZE` on a foreign table samples rows | **Without remote estimates**, postgres_fdw costs a scan from local statistics, and those exist only if someone ran `ANALYZE` on the foreign table. The autovacuum daemon never analyzes foreign tables, because it cannot know when remote data changed. `ANALYZE billing_remote.invoices` scans the remote table (sampling remotely by default via `analyze_sampling`) and stores statistics locally. That is cheap at query time, but it goes stale as the billing data changes. **With `use_remote_estimate 'true'`**, postgres_fdw asks the billing server for its own estimates, so they are current. It costs at least one remote `EXPLAIN` per foreign relation considered during planning, often several for a join, which can outweigh execution for short, frequent queries. Set it per table on the large tables rather than everywhere. It also unlocks plan shapes that local estimates never offer: - **parameterized remote scans**, where a nested loop sends the join value to the remote side for each outer row, letting the billing server use its index (efficient for a few outer rows, a round trip storm for many); - **sorted remote paths**, where the remote server supplies rows in an order a merge join or `ORDER BY` can use. `fdw_tuple_cost` changed in PostgreSQL 17 from 0.01 to 0.2, so plans that once assumed transfer was almost free now favour pushing work to the remote side. Raise it for a slow or distant link. ## Cutting round trips on reads postgres_fdw reads a foreign scan through a remote cursor: it runs `DECLARE ... CURSOR FOR` the remote query, then `FETCH 100` repeatedly. - **`fetch_size`** (server or table, default **100**) is the number of rows per fetch. A two-million-row scan of `invoice_lines` at 100 rows per fetch is 20,000 network round trips; at 10,000 rows it is 200. - The cost of raising it is local memory for each batch, rows times row width, and a longer wait for the first row. - A pushed-down join uses the larger of the two tables' fetch sizes. If the report reads several foreign tables on **different servers** through one `Append`, `async_capable` lets those scans run concurrently. Queries to one server still go one after another over the session's one connection per user mapping. ## Cutting round trips on writes - **`batch_size`** (server or table, default **1**) sets how many rows go into one remote `INSERT`. With the default, loading 50,000 adjustments is 50,000 statements. - postgres_fdw lowers the batch automatically so that columns times rows stays within libpq's limit of 65,535 parameters per statement. - The same option applies to `COPY` into a foreign table, capped at 1,000 rows per batch. - Batching is switched off when the insert uses `RETURNING`, the foreign table has row-level `BEFORE` or `AFTER INSERT` triggers, or a parent view has `WITH CHECK OPTION`. - `UPDATE` and `DELETE` are not batched: they are either pushed down whole or executed one row at a time by `ctid`. ## A tuning order 1. Run `EXPLAIN VERBOSE` and check each `Remote SQL` line first; a filter that stays local dwarfs every option here. 2. Fix estimates: `ANALYZE` the foreign tables on a schedule, or turn on `use_remote_estimate` for the big ones. 3. Raise `fetch_size` on the tables that return many rows. 4. Set `batch_size` for write paths, and avoid `RETURNING` on bulk loads. 5. Re-measure, and watch the billing server's load: cheaper round trips can mean heavier remote queries.

  • Why can use_remote_estimate make a fast query slower?
    Every planning of the query sends `EXPLAIN` to the remote server, often several times for the different join paths considered. For a query that runs in a few milliseconds, those planning round trips can cost more than execution. Turn it on per table for the large, join-heavy tables and rely on scheduled `ANALYZE` for small, stable ones.
  • An INSERT ... SELECT ... RETURNING into a postgres_fdw foreign table with batch_size 500 still sends one row per remote INSERT. Why?
    postgres_fdw disables batching when the insert needs `RETURNING`, when the foreign table has row-level `BEFORE` or `AFTER INSERT` triggers, or when a parent view adds `WITH CHECK OPTION`. Drop `RETURNING` from the bulk load, or read the inserted rows back afterwards.

  • Autovacuum keeps foreign-table statistics fresh, so ANALYZE is never needed.
  • use_remote_estimate is free because it reads cached remote statistics.
  • fetch_size caps the total number of rows a foreign scan can return.
  • batch_size also groups UPDATE and DELETE statements into batches.
  • fdw_tuple_cost defaults to 0.01, so the planner treats transfer as nearly free.

Assumes PostgreSQL 18; the default fdw_tuple_cost of postgres_fdw rose from 0.01 to 0.2 in PostgreSQL 17, which changes plans carried over from 16.