skip to content

In PostgreSQL, what is a foreign table, and how does querying one differ from querying an ordinary local table?

level: juniorimportance: should knowfreq 38%

answer

  1. definition here, rows elsewhere
  2. a wrapper fetches rows at query time
  3. nothing stored, so nothing to index
  4. declared constraints are trusted, not checked
  5. nobody analyzes it but you

basics

~20 s

A foreign table is a local definition whose rows live elsewhere; a foreign data wrapper such as postgres_fdw fetches them at query time. It has no local storage or indexes, and PostgreSQL assumes its declared constraints hold without enforcing them.

solid answer

~40 s

A foreign table is PostgreSQL's SQL/MED object: a column list plus options, created with `CREATE FOREIGN TABLE` or `IMPORT FOREIGN SCHEMA`, whose data belongs to something else. A foreign data wrapper turns each scan into work elsewhere: `postgres_fdw` sends SQL to another PostgreSQL server, and `file_fdw` reads a file or program output on the database server, read-only. Because nothing is stored locally, `CREATE INDEX` is refused and the remote side's indexes do that job; `CHECK` and `NOT NULL` constraints are trusted for planning but not checked by PostgreSQL; autovacuum never analyzes a foreign table, so you run `ANALYZE` yourself; and every query pays network round trips and fails when the remote is down. `dblink` is the older alternative: a function that returns `record`, not a table.

code

sql · 12 lines
sql
-- On the finance reporting database
SELECT invoice_id, amount_due
FROM billing_remote.invoices
WHERE status = 'overdue';

-- Refused: a foreign table has no local storage to index
CREATE INDEX ON billing_remote.invoices (status);
-- ERROR:  cannot create index on relation "invoices"
-- DETAIL:  This operation is not supported for foreign tables.

-- Autovacuum never does this for a foreign table
ANALYZE billing_remote.invoices;

go deeper

for a junior

Recall that a foreign table is a definition without storage, that a wrapper fetches its rows from elsewhere at query time, and name postgres_fdw and file_fdw as the two contrib wrappers.

for a middle

Explain the server, user mapping and foreign table chain, why indexes and constraint checks live on the remote side, and why you run ANALYZE on a foreign table yourself.

for a senior

Show that a foreign table ties a report to another server's uptime and latency, that a false declared constraint misleads the planner, and that stale local statistics produce bad join plans.

for a principal

Weigh a live foreign table against replicating or copying the data: one source of truth and fresh reads versus coupling to another team's availability, schema changes and load.

## What a foreign table is PostgreSQL implements part of the SQL standard's **SQL/MED** (Management of External Data). A **foreign table** looks like a table to every query: it has a name, a schema, typed columns and privileges, and you can `SELECT` from it, join it to local tables and, when its wrapper supports it, `INSERT`, `UPDATE`, `DELETE` and `TRUNCATE` it. What it does not have is storage. Its rows live in another system, and a **foreign data wrapper** (FDW) fetches them each time a query runs. Picture a finance reporting database that needs the billing team's invoices. The billing team runs its own PostgreSQL server. Instead of copying the invoices every night, the finance database declares `billing_remote.invoices` as a foreign table. A report that reads it sends a query to the billing server and streams the rows back. The same route is also how you reach **another database in the same cluster**: PostgreSQL has no cross-database queries, so even a database next door on the same server is reached through `postgres_fdw` or `dblink`. ## The objects behind a foreign table A foreign table sits at the bottom of a short chain of catalog objects: 1. **The wrapper**, installed by an extension such as `postgres_fdw` or `file_fdw`. It supplies the code that knows how to reach the data. 2. **A foreign server** (`CREATE SERVER`): one remote data source and its connection options. 3. **A user mapping** (`CREATE USER MAPPING`): which remote credentials a local role uses on that server. 4. **The foreign table** itself (`CREATE FOREIGN TABLE`, or many at once with `IMPORT FOREIGN SCHEMA`): the column list plus the remote schema and table names. `file_fdw` needs no user mapping, because it reads the database server's own filesystem. ## What changes compared with a local table | Aspect | Local table | Foreign table | |---|---|---| | Storage | heap files in the data directory | none; rows come from the wrapper | | Indexes | `CREATE INDEX` works | refused; the remote table's own indexes do the work | | `CHECK` / `NOT NULL` | enforced on every write | assumed true for planning, not enforced by core PostgreSQL | | Statistics | autovacuum runs `ANALYZE` | autovacuum never analyzes it; run `ANALYZE` on a schedule | | Availability | depends on this server | also depends on the remote server and the network | | Cost of a scan | local I/O | remote work plus network round trips | Three of these surprise people in interviews: - **Constraints are promises, not checks.** The manual states that constraints on foreign tables are not enforced by core PostgreSQL, because rows can change on the remote side by other routes anyway. The planner still trusts them, so a wrong `CHECK` can produce wrong answers. Enforce the rule on the remote table and declare it locally only to match. - **Statistics go stale silently.** The autovacuum daemon has no way to know when remote data changed, so it never issues `ANALYZE` for foreign tables. Without statistics the planner guesses, which hurts when a foreign table is joined to local ones. - **Some SQL is unsupported.** `postgres_fdw` cannot run `INSERT ... ON CONFLICT DO UPDATE` against a foreign table; `ON CONFLICT DO NOTHING` works only without a conflict target. ## The wrappers you will meet - **`postgres_fdw`** reaches another PostgreSQL server. It sends `WHERE` clauses, joins, aggregates, sorts and limits to the remote side when it can prove that is safe, and it can modify remote tables. - **`file_fdw`** reads a file, or the standard output of a program, in any format `COPY FROM` accepts. It is read-only. Setting its `filename` option needs superuser or the `pg_read_server_files` role, and `program` needs `pg_execute_server_program`, because those options decide what the server process reads or runs. - **`dblink`** is an older contrib module rather than a table wrapper. You call `dblink(connstr, sql)` and declare the result columns in the `FROM` clause, because the function returns `record`. Its query text is fixed, so a local `WHERE` written around the call is applied only after every row has arrived. Third-party wrappers exist for other databases and file formats. They follow the same object chain, but how much they push down, and whether they can write, depends entirely on the wrapper. ## When a foreign table is the right tool - **Occasional or reporting reads** of data another team owns, where a copy would go stale. - **Ad hoc reconciliation** between two databases, such as matching finance ledger rows against billing invoices. - **Small lookups** joined into local queries, where the remote side can use its own index. It is a poor fit for hot request paths: every query now depends on a second server's latency and uptime.

  • Why does PostgreSQL not enforce a CHECK constraint declared on a foreign table?
    Rows can change on the remote side through routes the local server never sees, so checking only writes made through the foreign table would give false safety. PostgreSQL treats the constraint as a promise: the planner may rely on it, and wrong answers follow if it is false. Enforce the rule on the remote table and declare it locally to match.
  • When would you still reach for dblink instead of postgres_fdw?
    Rarely. postgres_fdw gives table syntax, pushdown of filters and joins, and a remote transaction tied to the local one. dblink suits ad hoc remote statements that are not table reads, or asynchronous calls with `dblink_send_query` and `dblink_get_result`. Its result needs a column definition list, and the local planner cannot look inside its query text.

  • A foreign table keeps a local copy of the remote rows.
  • You can add a local index to a foreign table to speed up lookups.
  • PostgreSQL enforces a CHECK constraint declared on a foreign table.
  • Autovacuum keeps foreign-table statistics current like any other table.
  • file_fdw can write rows back into the CSV file it reads.
  • PostgreSQL can join tables from two databases in one cluster without an extension.