You have two Postgres databases. One is your application database. One is your analytics database. A query needs data from both. The usual answer is: copy the data. Write a sync job. Schedule it. Monitor it for drift.
There is a less annoying answer: postgres_fdw.
What a Foreign Data Wrapper Does
A foreign data wrapper lets Postgres query a remote data source as if it were a local table. The query runs locally; Postgres connects to the remote server on demand, executes a pushdown query there, retrieves the rows, and joins or aggregates them locally. No staging table. No sync job. The data is always current.
postgres_fdw is the official extension for connecting one Postgres instance to another. It ships with Postgres and requires no additional installation beyond enabling it.
Setting It Up
-- On the querying database
CREATE EXTENSION postgres_fdw;
CREATE SERVER analytics_db
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'analytics.internal', dbname 'analytics', port '5432');
CREATE USER MAPPING FOR CURRENT_USER
SERVER analytics_db
OPTIONS (user 'fdw_readonly', password 'secret');
-- Import an entire schema as foreign tables
IMPORT FOREIGN SCHEMA public
FROM SERVER analytics_db
INTO analytics_mirror;
After this, SELECT * FROM analytics_mirror.events WHERE created_at > NOW() - INTERVAL '7 days' runs the filter on the remote server and returns only matching rows. Postgres pushes WHERE clauses, JOIN conditions between foreign tables, and LIMIT clauses to the remote. The remote does the heavy lifting.
What the Planner Actually Does
Postgres has a foreign-table cost estimator that affects query planning. It uses statistics from the remote (ANALYZE on foreign tables fetches them) to decide whether to push operations remotely or pull rows locally. If statistics are stale, the planner may pull more data than necessary. Run ANALYZE on your foreign tables periodically if query performance degrades.
You can see what Postgres sends to the remote by running EXPLAIN (VERBOSE, ANALYZE) on a query involving foreign tables. The Foreign Scan node will show the pushdown query under Remote SQL.
When FDW Is the Right Tool
- Cross-database joins that run infrequently or on small result sets
- Read-only access to data owned by another team's database
- Migration staging: read from the old database, write to the new one, run both in parallel
- Replacing a sync job that is the source of half your on-call incidents
When It Is Not
- High-frequency queries over large remote tables — the network round-trip adds latency that local queries don't have
- Aggregations on very large remote datasets where pulling rows locally is expensive — pushdown works, but only for operations Postgres can translate
- Writes —
postgres_fdwsupportsINSERT,UPDATE, andDELETEon foreign tables, but writeable FDW connections are operationally fragile; use them carefully - When you need the remote data in an index — foreign tables cannot be indexed locally
Three Things That Bite People
First: authentication. The user mapping connects to the remote with a specific remote user. That user needs SELECT on the tables you're querying. A separate fdw_readonly role in the remote database is the right pattern; don't use a superuser.
Second: connection pooling. Each foreign data wrapper connection occupies a connection slot on the remote server. If many local sessions query the same foreign server simultaneously, you can exhaust the remote's max_connections. Use keep_connections option carefully, or put a PgBouncer in front of the remote.
Third: transaction isolation. A query that touches both local and foreign tables runs in two separate transactions — one local, one remote. There is no distributed two-phase commit unless you add it. If you need consistency across both, you need application-level coordination, not FDW alone.
More Postgres and infrastructure posts at anethoth.com/engineering/. builds.anethoth.com — public build dossiers for software in progress.