Cross Database

By the end of this lesson you'll be able to query data that lives in a different database — or even a different server or engine — using ordinary SQL. You'll set up a PostgreSQL Foreign Data Wrapper, JOIN a local table to a remote one, and know which tool (FDW, dblink , linked servers, FEDERATED) fits each job — plus the traps that make these queries slow or unsafe.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Imagine your company has filing cabinets in London, New York, and Tokyo. A Foreign Data Wrapper is like installing a dumbwaiter to each remote office: you stay at your London desk, ask for a folder, and the system fetches it for you. You get one drawer that looks local but is really pulling paper across the ocean — which is exactly why what you ask for matters. Request "all invoices since Monday" and only those cross the wire; request "everything" and you wait while the whole archive is shipped.

customers lives in your local database. page_views lives on a remote analytics server and reaches you as the foreign table remote.page_views . The whole lesson JOINs across these two.

A Foreign Data Wrapper (FDW) is an extension that teaches PostgreSQL how to read another data source. With postgres_fdw , that source is another PostgreSQL database — and once it's wired up, its tables look and behave like local ones.

Setup is four steps: enable the wrapper, declare the remote server (where), create a user mapping (who connects, with credentials stored once instead of in every query), then import the remote table definitions into a local schema.

Fill in the three blanks to register a remote server, map your credentials, and declare a single foreign table by hand. The expected answers are in the comments.

This is the payoff: a foreign table behaves like any other table, so you can JOIN it to local data in one ordinary query. Below, the local customers table joins the foreign remote.page_views table to count views per customer.

Two blanks — the keyword that combines tables, and the local column that lines up with v.customer_id .

dblink runs a single query on a remote PostgreSQL server without defining a foreign table first. It's handy for one-off admin tasks. The catch: you must declare the result columns and their types every time, so it's verbose for anything you run often.

SQL Server's equivalent is a linked server . After registering one with sp_addlinkedserver , you can address remote tables with a 4-part name [Server].[Database].[Schema].[Table] .

But 4-part names can drag a whole remote table back to be filtered locally . OPENQUERY instead sends your query string to run on the remote server, so the filtering and aggregation happen there and only the result returns — much better pushdown.

MySQL offers the FEDERATED storage engine: a local table that stores no data of its own and instead forwards every read and write to a table on another MySQL server, named by a connection URL. It must be enabled on the server (it's off by default), and it has real limits — no transactions across the link and limited index pushdown — so treat it as a convenience, not a foundation.

Q: Is a foreign table a copy of the remote data?

No. It stores no rows locally — it's a live pointer. Every query reaches across the network to the remote server, which is why latency and pushdown matter so much.

Q: postgres_fdw or dblink — which should I use?

Use postgres_fdw for anything recurring: you set it up once and then write normal SQL. Reach for dblink only for ad-hoc, one-off queries where defining a foreign table isn't worth it.

Q: Can I run one transaction across two databases?

Not safely with a plain BEGIN…COMMIT . A local commit can't undo a foreign write. True atomicity across systems needs two-phase commit, which most wrappers don't fully provide — so design writes to touch one system at a time.

Usually a filter didn't push down, so the whole remote table was fetched and filtered locally. Check the plan with EXPLAIN , keep WHERE conditions simple, and prefer OPENQUERY on SQL Server to force remote-side execution.

Put it together — a brief, a blank canvas, and the expected result in the comments. Write it, then adapt it for a real FDW setup to confirm.

Practice quiz

What does a Foreign Data Wrapper (FDW) let PostgreSQL do?

  • Encrypt all network traffic automatically
  • Replicate data into the local database
  • Read another data source as if its tables were local tables
  • Compress remote tables

Answer: Read another data source as if its tables were local tables. An FDW like postgres_fdw teaches Postgres to query a remote source as local-looking tables.

What are the four setup steps for postgres_fdw, in order?

  • CREATE EXTENSION, CREATE SERVER, CREATE USER MAPPING, IMPORT FOREIGN SCHEMA
  • CREATE TABLE, INSERT, GRANT, COMMIT
  • CREATE INDEX, ANALYZE, VACUUM, REINDEX
  • CREATE ROLE, GRANT, REVOKE, DROP

Answer: CREATE EXTENSION, CREATE SERVER, CREATE USER MAPPING, IMPORT FOREIGN SCHEMA. Enable the wrapper, declare the server, map credentials, then import the remote schema.

Where are the credentials for connecting to a foreign server stored?

  • Inline in each SELECT statement
  • In the table's column definitions
  • In the client application only
  • In a USER MAPPING, not in every query

Answer: In a USER MAPPING, not in every query. CREATE USER MAPPING stores credentials once, so they are not repeated in queries.

What is 'pushdown' in a cross-database query?

  • Copying the whole remote table locally first
  • Sending the WHERE filter to the remote server so only matching rows travel back
  • Pushing the result into a local cache
  • Lowering the query's priority

Answer: Sending the WHERE filter to the remote server so only matching rows travel back. Pushdown evaluates filters on the remote side, the single biggest speed-up for FDW queries.

Does a foreign table store a local copy of the remote data?

  • No, it stores no rows locally; it is a live pointer queried over the network
  • Yes, it caches every row locally
  • Yes, it copies the data nightly
  • Only the first 1000 rows are copied

Answer: No, it stores no rows locally; it is a live pointer queried over the network. A foreign table holds no rows; each query reaches across the network to the remote server.

What does dblink do that an FDW foreign table does not require?

  • It encrypts the connection automatically
  • It caches results permanently
  • You must declare the result columns and their types every time
  • It works without any credentials

Answer: You must declare the result columns and their types every time. dblink runs an ad-hoc remote query but you must declare the column types each call.

On SQL Server, why does OPENQUERY often outperform a 4-part name?

  • It compresses the result set
  • It sends the inner query to run remotely, so filtering happens on the remote side
  • It caches the linked server connection
  • It bypasses authentication

Answer: It sends the inner query to run remotely, so filtering happens on the remote side. A 4-part name can drag the whole table back to filter locally; OPENQUERY pushes work remotely.

What is the MySQL FEDERATED engine?

  • A replication mode for MySQL clusters
  • A full-text search engine
  • A columnar storage format
  • A local table that stores no data and forwards reads/writes to a remote MySQL table

Answer: A local table that stores no data and forwards reads/writes to a remote MySQL table. FEDERATED is a local shortcut (a connection URL) to a table on another MySQL server.

Can a single BEGIN...COMMIT give atomicity across two databases via an FDW?

  • Yes, FDWs guarantee distributed transactions
  • No, a local commit cannot roll back a foreign write without two-phase commit
  • Yes, but only in MySQL
  • Only if both servers run the same version

Answer: No, a local commit cannot roll back a foreign write without two-phase commit. Transactions do not safely span systems; true atomicity needs two-phase commit, which most FDWs lack.

What is the main performance risk of every cross-database query?

  • It always uses a Seq Scan
  • It disables indexes locally
  • Network latency, since the query crosses a network each time
  • It doubles disk usage

Answer: Network latency, since the query crosses a network each time. Every foreign query crosses the network; filter early and return as few rows as possible.