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.

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.

What You'll Learn

Real-World Analogy

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.

Our Two Tables: one local, one foreign

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.

1. PostgreSQL FDW — Set Up a Foreign Server

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.

Your Turn: wire up a foreign server

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.

2. JOIN Local and Foreign Tables

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.

Your Turn: join across the two tables

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

3. dblink — Ad-Hoc Remote Queries

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.

4. SQL Server — Linked Servers, 4-Part Names & OPENQUERY

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.

5. MySQL — The FEDERATED Engine

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.

The Trade-Offs (read this before production)

• Latency: every foreign query crosses a network. A JOIN that's instant on local tables can take seconds when one side is remote. Filter early and return as few rows as possible.
• Pushdown isn't guaranteed: the planner pushes simple filters and joins to the remote side, but functions, complex expressions, and some JOIN shapes are evaluated locally — meaning the full table is dragged over first. Always check the EXPLAIN plan.
• Transactions don't span systems: a single BEGIN…COMMIT does not give you atomicity across two databases. If you write to a local and a foreign table, one can succeed while the other fails (true distributed transactions need two-phase commit, which most FDWs don't fully provide).
• Security: credentials in a user mapping run as that remote user — scope it to read-only and least privilege, and never paste passwords into ad-hoc dblink strings that land in logs.

Common Errors (and the fix)

• "Query is slow / pulls millions of rows": don't assume predicate pushdown always happens. Wrapping a foreign column in a function (e.g. LOWER(v.country) ) or using a type the remote can't match often forces a full fetch. Run EXPLAIN (VERBOSE) and keep filters simple so they push down.
• "Half my data was written, half wasn't": you expected a cross-system transaction. A local COMMIT can't roll back a foreign write. Write to one system per transaction, or design an idempotent retry — don't rely on atomicity across the wire.
• "password authentication failed for user" / "permission denied": the USER MAPPING credentials are wrong or the remote role lacks SELECT . Fix the mapping and grant least-privilege read access on the remote side.
• "could not connect to server" / timeouts: network latency or a firewall. The host/port in CREATE SERVER must be reachable from the database host (not your laptop), and the remote must allow the connection.
• "relation 'remote.page_views' does not exist": you queried before importing. Run IMPORT FOREIGN SCHEMA (or CREATE FOREIGN TABLE ) into the schema you reference, and qualify it: remote.page_views , not just page_views .

📘 Quick Reference

Tool

Engine

Best for

Frequently Asked Questions

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.

Mini-Challenge: Top Spenders Across Databases

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.

🎉 Lesson Complete

• ✅ postgres_fdw setup is four steps: extension → SERVER → USER MAPPING → IMPORT FOREIGN SCHEMA
• ✅ A foreign table JOINs to local tables in one ordinary query
• ✅ dblink handles one-off remote queries; FDW is better for recurring ones
• ✅ SQL Server uses linked servers, 4-part names, and OPENQUERY ; MySQL has the FEDERATED engine
• ✅ Mind the trade-offs: latency, no guaranteed pushdown, no cross-system transactions, least-privilege credentials
• ✅ Next: Performance Testing — measure and benchmark queries so you can prove these cross-database calls are fast enough

Practice quiz

Continue this course

• Previous: Database Design Patterns
• Next: Performance Testing

Related lessons

• JOINs
• aggregate functions