Joining tables across separate PostgreSQL databases

AWS DMS replicates table to table; it cannot join across databases. Where a target table needs columns from two separate PostgreSQL databases, the join belongs in PostgreSQL, using the postgres_fdw foreign data wrapper to expose the remote tables locally. A view or materialized view over the result is then a normal local object, and DMS or plain SQL can read it.

1. Enable the extension

In the database that will do the joining:

CREATE EXTENSION postgres_fdw;

On Amazon RDS and Aurora this requires the extension to be permitted by the instance’s parameter group; it is allowed by default on current versions, but a locked-down rds.allowed_extensions list will reject it.

2. Define the foreign server

CREATE SERVER foreign_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'second-db-hostname.region.rds.amazonaws.com',
         port '5432',
         dbname 'second_database');

3. Map a local role to remote credentials

CREATE USER MAPPING FOR current_user
SERVER foreign_server
OPTIONS (user 'remote_username', password :'remote_password');

The password given here is stored in the pg_user_mapping catalog and is readable by superusers and by the role the mapping belongs to — it is not a secret from the local database. Use a dedicated remote account with read-only access to only the tables the join needs, supply the value as a psql variable sourced from the secret store rather than as a literal, and include it in the normal credential rotation.

4. Declare the foreign tables

CREATE FOREIGN TABLE foreign_table (
    id         integer NOT NULL,
    name       text,
    value      numeric,
    created_at timestamp
)
SERVER foreign_server
OPTIONS (schema_name 'public', table_name 'original_table_name');

Column types must match the remote definitions; a mismatch surfaces at query time rather than at creation. Foreign tables accept only NOT NULL, CHECK, DEFAULT and GENERATED constraints — a PRIMARY KEY clause is rejected — and PostgreSQL does not enforce the ones it does accept. They are assertions about what the remote server enforces, used for planning, so an inaccurate one produces wrong results rather than an error.

For more than a couple of tables, let PostgreSQL read the definitions itself:

IMPORT FOREIGN SCHEMA public
LIMIT TO (original_table_name, another_table)
FROM SERVER foreign_server
INTO remote;

5. Join local and foreign tables

CREATE VIEW combined_view AS
SELECT
    l.id,
    l.customer_id,
    l.order_date,
    f.name  AS product_name,
    f.value AS product_price
FROM local_orders l
JOIN foreign_table f ON l.product_id = f.id;

Every query against this view reaches across the network. postgres_fdw pushes down what it can — restrictions, joins between two tables on the same foreign server, and aggregates — but a join between a local table and a foreign one is executed locally, which means fetching the foreign rows. EXPLAIN (ANALYZE, VERBOSE) shows the remote query that was actually issued, and is the only reliable way to know which happened.

6. Materialize where the read pattern justifies it

CREATE MATERIALIZED VIEW combined_mat_view AS
SELECT
    l.id,
    l.customer_id,
    l.order_date,
    f.name  AS product_name,
    f.value AS product_price
FROM local_orders l
JOIN foreign_table f ON l.product_id = f.id;

CREATE UNIQUE INDEX idx_combined_mat_view_id ON combined_mat_view(id);

Refreshing rebuilds the whole thing:

REFRESH MATERIALIZED VIEW CONCURRENTLY combined_mat_view;

CONCURRENTLY avoids locking readers out for the duration of the refresh, and requires the unique index above. The trade is staleness: between refreshes the data is as old as the last one, so the refresh interval is a product decision rather than an operational detail.

AWS considerations

• Security groups. The security group on the second instance must allow inbound traffic from the first on the database port. This is the most common cause of an FDW connection that hangs rather than erroring.
• Network topology. Both databases need to be in the same VPC, or in peered VPCs with routes and DNS resolution configured. Cross-Region adds latency to every remote fetch.
• IAM authentication. Where the remote instance uses IAM database authentication, the user mapping cannot hold a static password; the token is short-lived, so either use a conventional password for the FDW account or refresh the mapping, which in practice means the former.
• Latency. Cross-database queries cost a network round trip per remote scan. For frequent access patterns, materialized views are usually the right answer; for occasional reporting, a plain view is simpler and stays current.
• Parameter groups. postgres_fdw needs no special configuration beyond being permitted, but statement timeouts and connection limits on the remote instance apply to the wrapper’s connections like any other.