Skip to content
Tutorials Center
My learning

Topics

Lesson 58 of 90 ยท Extensions and Ecosystem

Foreign Data Wrappers and Cross-Database Access

Query data outside your database with PostgreSQL foreign data wrappers: postgres_fdw, file_fdw, IMPORT FOREIGN SCHEMA, remote pushdown and dblink.

Real systems rarely keep everything in one database. Reports live in a separate reporting database, a finance team keeps its own cluster, a data export arrives as a CSV file every morning. Copying all of that into your main database just to query it is slow and keeps going stale. PostgreSQL has a better answer: foreign data wrappers, or FDWs. An FDW lets you declare a foreign table that looks like a normal table in your database but fetches its rows from somewhere else when you query it.

This follows the SQL/MED standard, and it works with other PostgreSQL servers, plain files, other database systems and even web services. You will learn the building blocks (server, user mapping, foreign table), postgres_fdw and file_fdw, how to import a whole remote schema at once, how to check that filters are pushed down to the remote side, and where the older dblink extension still fits.

All FDW commands need a running PostgreSQL server and a second place to connect to, so the PostgreSQL blocks are read-only. The live editors use SQLite stand-ins: an attached second database that you join against, and a CSV text that is parsed into rows by a view.

Why a plain query cannot cross databases

In PostgreSQL, one connection is attached to one database. You cannot write SELECT * FROM other_db.public.customers and have it work, because the schema and tables of another database are not visible to your session. (A three-part name is only accepted when the first part is the current database.) This is different from MySQL and SQL Server, where cross-database queries are routine, and from SQLite, where you can ATTACH another file. Schemas are the PostgreSQL way to separate things inside one database (see Clusters, Databases and Schemas); to reach another database you need an extension.

SQLite's ATTACH gives you a feel for what you want to achieve. The next editor attaches a second in-memory database called reporting, then joins a local table with a table that "lives somewhere else":

SQL Joining across two databases (SQLite ATTACH)

Press Run (or Ctrl + Enter) to see the result here.

In PostgreSQL the same idea is built from three objects.

Three objects make a foreign table

Server Where the data lives and how to reach it.
User mapping Which remote login your local role uses.
Foreign table A local name for a remote table or file.

Create them in this order. Then query the foreign table like any other.

1 / 2

postgres_fdw: another PostgreSQL database

postgres_fdw is the contrib wrapper for talking to another PostgreSQL database, on the same server or a different one. It is not a trusted extension (see Extensions), so a superuser or your hosting provider must enable it.

SQL
CREATE EXTENSION postgres_fdw;

-- 1. The remote server
CREATE SERVER reporting_srv
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'reporting.internal', port '5432', dbname 'reporting');

-- 2. Which remote login the local role "app" uses
CREATE USER MAPPING FOR app
    SERVER reporting_srv
    OPTIONS (user 'readonly_reports', password 'not-a-real-password');

-- 3. A foreign table that mirrors the remote customers table
CREATE FOREIGN TABLE remote_customers (
    id      integer,
    name    text,
    country text
)
SERVER reporting_srv
OPTIONS (schema_name 'public', table_name 'customers');

After that, remote_customers behaves like a table in your queries:

SQL
SELECT c.name, COUNT(*) AS orders
FROM orders AS o
JOIN remote_customers AS c ON c.id = o.customer_id
WHERE c.country = 'UK'
GROUP BY c.name;

A few things worth knowing:

  • The column names and types in CREATE FOREIGN TABLE are your claim about the remote table. PostgreSQL does not check them at creation time. If the remote table changes, queries fail or return wrong data until you update the definition.
  • The remote login should have the least privilege you need, often read-only. Roles, Privileges and GRANT explains how to build such a role. For non-superusers, the connection must use a password (or a trusted method configured on the remote side).
  • INSERT, UPDATE and DELETE on a foreign table are sent to the remote server too, when the wrapper supports it, and postgres_fdw does. But there is no two-phase commit: if your local transaction commits and a later remote step fails, the two sides can disagree. Treat cross-server writes with care.
  • Useful server or table options include use_remote_estimate (ask the remote server for row estimates), fetch_size (rows fetched per round trip, 100 by default) and async_capable (let several foreign scans run in parallel).

IMPORT FOREIGN SCHEMA

Writing a CREATE FOREIGN TABLE for fifty tables is tedious and error-prone. IMPORT FOREIGN SCHEMA asks the remote side for its table definitions and creates the foreign tables for you:

SQL
CREATE SCHEMA remote;

IMPORT FOREIGN SCHEMA public
    FROM SERVER reporting_srv
    INTO remote;

-- Or only some tables:
IMPORT FOREIGN SCHEMA public
    LIMIT TO (customers, products)
    FROM SERVER reporting_srv
    INTO remote;

Putting the imported tables into their own local schema (remote) keeps names from colliding with your own tables. The import is a snapshot of the structure: when the remote side changes, drop the affected foreign tables and import again.

Pushdown: let the remote side do the filtering

A foreign table is only fast if the remote server does as much work as possible. If PostgreSQL pulled the whole remote customers table and then applied country = 'UK' locally, a million-row table would cross the network for a handful of results. Good wrappers push down filters, and postgres_fdw pushes down:

  • WHERE conditions that use built-in, immutable functions and operators,
  • joins between foreign tables on the same server,
  • aggregates and sorts, and LIMIT in many cases.

EXPLAIN VERBOSE shows exactly what was sent, in a line called Remote SQL:

SQL
EXPLAIN (VERBOSE, COSTS OFF)
SELECT name FROM remote_customers WHERE country = 'UK' ORDER BY name LIMIT 5;
TEXT
Foreign Scan on public.remote_customers
  Output: name
  Remote SQL: SELECT name FROM public.customers WHERE ((country = 'UK'::text)) ORDER BY name ASC NULLS LAST LIMIT 5::bigint

(This is a representative shape. The exact text varies by version.) When the WHERE condition appears inside Remote SQL, it is pushed down. When it appears in a separate Filter: line under the Foreign Scan, PostgreSQL is fetching all the rows and filtering them itself. That usually happens when the condition uses a function the remote server might not have or whose behavior might differ, such as a user-defined function. EXPLAIN and EXPLAIN ANALYZE teaches you to read these plans.

Pushdown matters most for big remote tables. Files are the other common source, so the next section shows file_fdw.

file_fdw: querying files as tables

file_fdw is a contrib wrapper that exposes a file on the database server's disk as a read-only table. It supports the same formats as COPY:

SQL
CREATE EXTENSION file_fdw;

CREATE SERVER local_files FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE csv_sales (
    sku   text,
    qty   integer,
    price numeric(10, 2)
)
SERVER local_files
OPTIONS (filename '/var/data/sales.csv', format 'csv', header 'true');

SELECT sku, qty * price AS revenue FROM csv_sales ORDER BY revenue DESC;

Every query re-reads the file from the start, so this is a convenient way to peek at an export or load it with INSERT ... SELECT, not a way to run heavy analytics on a large file. Reading arbitrary server files is powerful, so creating these tables needs a superuser or the pg_read_server_files role. Do not give it to application roles. For loading data into real tables, see Bulk Loading and COPY.

Here is a stand-in that does what file_fdw does with a small CSV text: split it into lines, split each line into columns, and expose the result as a view you can query.

SQL A CSV text exposed as a table (file_fdw stand-in)

Press Run (or Ctrl + Enter) to see the result here.

Like file_fdw, the view re-reads (re-parses) the source on every query, and nothing is stored in a real table.

Before FDWs existed, the dblink extension let you run a query on another database from inside SQL. It still works, and it is occasionally handy:

SQL
CREATE EXTENSION dblink;

SELECT *
FROM dblink(
       'host=reporting.internal dbname=reporting user=readonly_reports password=not-a-real-password',
       'SELECT id, name FROM customers WHERE country = ''UK'''
     ) AS t(id integer, name text);

Compared with postgres_fdw, dblink has real drawbacks. You have to spell out the column types on every call. The remote query is just a string, so there is no planning and nothing is pushed down for you: you have to put the WHERE clause inside that string yourself. It is easy to build the string unsafely (see SQL Injection and Safe Queries in PostgreSQL). It also puts a password in the SQL text, where it can leak into logs. Choose postgres_fdw for anything permanent. dblink shines for one-off scripts and for a few special tricks, such as opening a named connection with dblink_connect to run a statement in a separate transaction.

Other wrappers and when to use them

Wrappers exist for many sources: mysql_fdw, oracle_fdw, tds_fdw (SQL Server), MongoDB, Redis, Parquet and CSV libraries, and more. Quality and pushdown support vary a lot, and most are third-party extensions that your host may not allow. Use FDWs when:

  • you need occasional access to another database without copying data;
  • you are migrating from another system, and want INSERT INTO local SELECT ... FROM foreign;
  • reports must combine data from two PostgreSQL clusters.

Avoid them when a query will be run constantly under load, because every query depends on the network and on the other server being up. For constant use, replicate the data instead (see Streaming and Logical Replication).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the correct order to set up a postgres_fdw foreign table?

    The server says where the remote database is, the user mapping says which remote login to use, and the foreign table refers to the server. Each one depends on the one before it.
  2. 2. You run EXPLAIN VERBOSE on a query over a foreign table. Where do you see that a WHERE condition was pushed down to the remote server?

    Remote SQL shows the text sent to the other server. A condition inside it was pushed down. A Filter line means PostgreSQL fetched the rows and filtered them locally.
  3. 3. What does IMPORT FOREIGN SCHEMA do?

    It reads the remote table definitions and creates matching foreign tables. It does not copy data, and it is a one-time snapshot of the structure.
  4. 4. Why is dblink usually a worse choice than postgres_fdw for permanent use?

    With dblink the remote query is an opaque string with a column list on every call. The planner cannot optimise it, so you must put filters in the string yourself.
  5. 5. Which statement about file_fdw is true?

    file_fdw exposes a file on the database server's disk as a read-only foreign table, and creating one needs special privileges because it reads server files.

Summary

  • A PostgreSQL connection sees one database, so queries across databases need foreign data wrappers (or dblink).
  • A foreign table is built from a server, a user mapping and the foreign table definition. IMPORT FOREIGN SCHEMA creates many foreign tables at once.
  • postgres_fdw reads and writes other PostgreSQL databases; it pushes down filters, joins, sorts and aggregates where it safely can. Check Remote SQL in EXPLAIN VERBOSE.
  • file_fdw exposes server-side files as read-only tables, and needs special privileges.
  • dblink runs query strings remotely, with no pushdown and no planning help; prefer postgres_fdw.
  • Use FDWs for occasional access and migrations; replicate data that is queried constantly.
  • Cross-server writes are not atomic. Give the remote login the least privilege it needs.

Next: Time-Series, Queues and Caching in PostgreSQL, using plain PostgreSQL for jobs that people often assign to other systems.