Imagine your orders are stored in Amazon Aurora PostgreSQL, but customer information lives in another PostgreSQL database.
Normally, you might export the customer data, copy it into Aurora, and maintain an ETL pipeline to keep everything synchronized.
But what if you could query both databases using a single SQL statement?
That's the idea behind federated queries.
PostgreSQL supports federated access through Foreign Data Wrappers (FDWs). With the postgres_fdw extension, Aurora PostgreSQL can access tables in a remote PostgreSQL database and join them with local tables.
In this tutorial, we'll build a small project that combines orders in Aurora with customer information in a remote database—without copying the entire customer table into Aurora.
Important: This article focuses on PostgreSQL foreign data access, not a claim that every data source can be queried automatically by Aurora. Supported sources and capabilities depend on the integration and extensions used.
Why Is This Useful on AWS?
Consider an e-commerce application:
- Amazon Aurora PostgreSQL: Stores orders and transactions.
- Remote PostgreSQL database: Stores customer profiles.
- Reporting application: Needs customer names alongside order totals.
Without federated access, you might create an ETL job to copy customer information into another database.
With postgres_fdw, Aurora can query the remote table through SQL.
Benefits can include:
- Less data duplication.
- Fewer ETL jobs for straightforward integration needs.
- Reduced synchronization complexity.
- Access to current remote data when queries execute.
- Familiar SQL for combining datasets.
Federated queries don't guarantee zero data movement or zero cost. Query results still travel across the network, and remote database compute and networking can incur charges.
AWS documentation: Federated query support for Aurora PostgreSQL and RDS for PostgreSQL
Our Mini Project: Join Orders and Customers
We'll create two database tables in separate PostgreSQL databases.
Remote PostgreSQL
└── customers
|
| postgres_fdw
v
Amazon Aurora PostgreSQL
└── orders
|
v
One SQL JOIN
|
v
Combined order and customer results
Prerequisites
You'll need:
- An Aurora PostgreSQL cluster.
- A reachable remote PostgreSQL database.
- A PostgreSQL client such as
psql. - Appropriate database permissions.
- Network access between the two databases.
You can also test the concept locally with two PostgreSQL databases before deploying it to AWS.
Step 1: Create the remote customer table
Connect to the remote PostgreSQL database and run:
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY,
customer_name VARCHAR(100),
city VARCHAR(100)
);
INSERT INTO customers
(customer_id, customer_name, city)
VALUES
(1, 'Ali Khan', 'Karachi'),
(2, 'Sara Ahmed', 'Lahore'),
(3, 'Usman Malik', 'Islamabad');
The customer information remains in the remote database.
Step 2: Create the orders table in Aurora
Connect to Aurora PostgreSQL:
CREATE TABLE orders (
order_id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount NUMERIC(10, 2)
);
INSERT INTO orders
(order_id, customer_id, amount)
VALUES
(1001, 1, 2500.00),
(1002, 2, 1800.00),
(1003, 3, 3200.00);
Aurora now owns the orders, while the remote database owns the customer profiles.
Step 3: Enable postgres_fdw
Run this statement in Aurora:
CREATE EXTENSION IF NOT EXISTS postgres_fdw;
This enables the foreign data wrapper extension, subject to the engine version and permissions available in your environment.
Step 4: Register the remote database
Replace the hostname and database name with your actual remote database details:
CREATE SERVER customer_server
FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (
host 'remote-db.example.com',
port '5432',
dbname 'customerdb',
sslmode 'require'
);
Make sure Aurora can reach the remote database. Configure routing, security groups, and database access accordingly.
Step 5: Configure the user mapping
Create a mapping for the local database role that will execute the federated query:
CREATE USER MAPPING FOR app_user
SERVER customer_server
OPTIONS (
user 'remote_readonly_user',
password 'REPLACE_WITH_SECRET'
);
Replace the example role names with your actual database roles.
For production, use an appropriate secrets-management approach rather than embedding real credentials in scripts or source control. Give the remote account only the permissions it needs.
Step 6: Create a foreign table
In Aurora, create a local definition for the remote table:
CREATE FOREIGN TABLE remote_customers (
customer_id INTEGER,
customer_name VARCHAR(100),
city VARCHAR(100)
)
SERVER customer_server
OPTIONS (
schema_name 'public',
table_name 'customers'
);
This creates a foreign-table definition, not a second copy of the customer records.
Step 7: Execute the federated query
Now run the following SQL statement in Aurora:
SELECT
o.order_id,
c.customer_name,
c.city,
o.amount
FROM orders o
JOIN remote_customers c
ON o.customer_id = c.customer_id
ORDER BY o.order_id;
Expected results:
| Order ID | Customer | City | Amount |
|---|---|---|---|
| 1001 | Ali Khan | Karachi | 2500.00 |
| 1002 | Sara Ahmed | Lahore | 1800.00 |
| 1003 | Usman Malik | Islamabad | 3200.00 |
Aurora combines local order records with customer information from the remote database. You don't need a separate export-and-import job just to perform this join.
What Happens Behind the Scenes?
PostgreSQL uses postgres_fdw to access the remote server. Depending on the query and the operations supported for pushdown, PostgreSQL may execute filters remotely before returning results.
You can inspect the execution plan:
EXPLAIN VERBOSE
SELECT
o.order_id,
c.customer_name,
o.amount
FROM orders o
JOIN remote_customers c
ON o.customer_id = c.customer_id
WHERE c.city = 'Karachi';
Look for a Foreign Scan node and the remote SQL details. These can help you understand how the remote data is accessed.
For large datasets, filter early, select only the required columns, and evaluate query plans before production deployment.
Does This Mean Zero Data Movement and Zero Cost?
Not literally.
The advantage is that you may not need a separate ETL or replication process solely to access remote records.
However:
- Query results still travel over the network.
- The remote database still consumes compute resources.
- Network transfer and supporting infrastructure may incur charges.
- Inefficient queries can transfer excessive data.
- Network latency and remote database availability affect execution.
The goal is less unnecessary data duplication and simpler integration, not the elimination of all data transfer and cost.
If you repeatedly query large datasets for historical analytics, a data lake, replication strategy, or dedicated analytics platform may be a better fit.
Security Best Practices
Before using federated queries in production:
- Use private network connectivity where possible.
- Require TLS for remote database connections.
- Follow least-privilege access for local and remote roles.
- Store credentials securely.
- Monitor query duration and remote database load.
- Test behavior when the remote database is unavailable.
- Avoid unnecessary full-table scans.
Federated queries simplify integration, but they don't remove the need for security, performance testing, and monitoring.
When Should You Use Federated Queries?
Consider this approach when you need to join current operational data across PostgreSQL databases, simplify reporting, or avoid maintaining a separate synchronized copy for a straightforward query.
For large-scale analytics, high query concurrency, and complex transformations, compare federated queries with a dedicated data lake or warehouse architecture.
Final Thoughts
Amazon Aurora PostgreSQL can query more than the tables stored directly in its cluster. With postgres_fdw, it can access supported remote PostgreSQL data and combine it with local records using familiar SQL.
Our mini project showed how to join orders in Aurora with customer information in another database without first copying the entire customer table.
My takeaway: Before building a data-copying pipeline, ask whether you can query the data where it already lives.
Have you used PostgreSQL Foreign Data Wrappers or another federated query approach in AWS? Share your experience in the comments.
Top comments (0)