Modern applications often have operational data in PostgreSQL and historical or analytical data in a lakehouse. This separation is useful because the two workloads have different requirements, but it also creates a practical problem for developers: business questions often need information from both systems at the same time.
For example, an application may store customers, orders, payments, and inventory in PostgreSQL while storing years of customer activity, application events, clickstream records, and historical transactions in an Iceberg-based lakehouse.
A business user may then ask a question such as:
"Which active customers placed orders recently but have not used the application in the last 30 days?"
The answer may require current customer information from PostgreSQL and historical activity information from the lakehouse.
The challenge is not simply writing a SQL join. The real challenge is deciding where the join should happen, how much data should move between systems, how to filter the lakehouse data before joining it, and whether the query is suitable for an interactive application request.
This article explains practical patterns for joining lakehouse data with operational PostgreSQL data, including query planning, filtering, data movement, performance, security, and production design.
Why Operational and Lakehouse Data Are Separate
PostgreSQL is commonly used for operational workloads.
Typical tables might include:
customers
orders
payments
products
inventory
subscriptionsThese tables support application operations such as creating an order, updating a customer profile, checking inventory, and processing payments.
A lakehouse contains a different type of data:
application_events
clickstream
historical_orders
audit_events
device_events
logs
analytics_eventsThis data can grow very quickly.
A simplified architecture looks like this:
Application
|
v
PostgreSQL
|
| Data pipeline / CDC
v
Lakehouse
|
v
Iceberg Tables
|
v
Object StorageThe operational database remains optimized for application transactions while the lakehouse provides a scalable environment for historical and analytical workloads.
The problem begins when a business query needs both.
A Simple Cross-System Join
Imagine PostgreSQL contains the current customer information:
customers
-------------------------
customer_id
name
status
region
created_atThe lakehouse contains application activity:
customer_events
-------------------------
event_id
customer_id
event_type
event_time
device_typeA business query might need the number of events generated by each active customer.
Conceptually, the query looks like this:
SELECT
c.customer_id,
c.name,
COUNT(e.event_id) AS event_count
FROM customers c
JOIN customer_events e
ON e.customer_id = c.customer_id
WHERE c.status = 'Active'
GROUP BY
c.customer_id,
c.name;The SQL looks straightforward.
The architecture behind that SQL is not.
The query may need to move between PostgreSQL and the lakehouse through a federation or query layer. The performance depends on where filtering and aggregation happen, how much data is transferred, and how the query engine executes the join.
Where Should the Join Happen?
This is the first important design question.
There are several possible approaches.
PostgreSQL Performs the Join
The application can retrieve lakehouse data through a foreign table, external table, connector, or another integration mechanism and allow PostgreSQL to participate in the query.
Conceptually:
PostgreSQL
|
+---- Current customer data
|
+---- External lakehouse data
|
v
Query engineThis can be convenient because application developers can work with familiar SQL.
However, it does not automatically mean that PostgreSQL should process the entire lakehouse dataset.
The Lakehouse Engine Performs the Join
Another option is to send the PostgreSQL data or a filtered subset of it to an analytical engine and perform the join there.
This can be useful when the lakehouse side is much larger and the analytical engine is better suited to large scans and aggregations.
Conceptually:
PostgreSQL
|
| Relevant rows
v
Lakehouse Query Engine
|
+---- Iceberg data
|
v
Join + AggregateA Federation Layer Coordinates the Join
A federation layer can sit between the systems and determine how the query is executed.
SQL Query
|
v
Federation Layer
/ \
/ \
v v
PostgreSQL Iceberg
| |
v v
Operational Analytical
Data DataThe advantage is that the application can have one query interface while the underlying engine handles the data sources.
The important point is that the query planner must still make good decisions. Federation does not magically make large cross-system joins cheap.
Start With the Small Side
One of the most practical rules for cross-system joins is to reduce the amount of data involved before the join.
Suppose PostgreSQL contains 20 million customers and the lakehouse contains 30 billion events.
If the application only needs customers from one region, there is no reason to involve all 20 million customers.
Instead:
20 million customers
|
v
Filter region
|
v
200,000 customers
|
v
Join with lakehouse dataThe same principle applies to the lakehouse side.
If the query only needs events from the last seven days, filter those events before joining them with PostgreSQL.
This is one of the most important differences between a query that scales reasonably and one that becomes expensive.
Filter Before the Join
Consider this query:
SELECT
c.customer_id,
c.name,
COUNT(e.event_id) AS event_count
FROM customers c
JOIN customer_events e
ON e.customer_id = c.customer_id
WHERE c.status = 'Active'
AND e.event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY
c.customer_id,
c.name;The business requirement is clear, but the query planner still needs to decide how to execute it.
The ideal logical flow is:
PostgreSQL customers
|
v
Active customers
|
v
Lakehouse events
|
v
Last 30 days
|
v
Join
|
v
AggregateYou do not want to process every historical event when only recent events are required.
The exact physical execution depends on the federation or analytical engine, so developers should inspect the query plan rather than assume that filters were pushed to the appropriate system.
Predicate Pushdown Matters
Predicate pushdown means applying filters as close as possible to the data source.
Suppose the lakehouse contains billions of events but the query needs only:
event_time >= last 30 days
event_type = 'purchase'A good execution strategy pushes these conditions into the lakehouse query.
Instead of:
Read billions of events
|
v
Transfer data
|
v
Filter purchase eventsthe goal is closer to:
Iceberg data
|
v
Filter time range
|
v
Filter event type
|
v
Read required rows
|
v
JoinThis can reduce both computation and network traffic.
Predicate pushdown is therefore one of the first things to check when a cross-system query becomes slow.
Projection Pushdown Is Also Important
Filtering rows is only one part of reducing data movement.
You should also reduce the number of columns being read.
Avoid:
SELECT *
FROM customer_events;If the join only needs three columns, select those columns:
SELECT
customer_id,
event_time,
event_type
FROM customer_events
WHERE event_time >= CURRENT_DATE - INTERVAL '30 days';Columnar lakehouse formats are particularly useful here because analytical engines can often read only the columns required by the query.
This becomes increasingly important as the number of columns in an event table grows.
Example: Finding Inactive Customers
Consider a practical requirement:
"Find active customers who have not generated any application activity during the last 30 days."
PostgreSQL contains:
customers
----------------
customer_id
name
statusThe lakehouse contains:
customer_events
----------------
customer_id
event_time
event_typeA query can conceptually look like:
SELECT
c.customer_id,
c.name
FROM customers c
LEFT JOIN customer_events e
ON e.customer_id = c.customer_id
AND e.event_time >= CURRENT_DATE - INTERVAL '30 days'
WHERE c.status = 'Active'
AND e.customer_id IS NULL;The important detail is the placement of the date filter.
It belongs with the lakehouse-side join condition in this example because the business question is whether the customer has a matching event during the specified period.
Moving conditions around without understanding the semantics of LEFT JOIN can change the result.
This is a good example of why cross-system SQL requires both database knowledge and business understanding.
Avoid Joining Raw Event Data When Aggregation Is Enough
Suppose the requirement is:
"Show the number of purchases for each customer."
You do not necessarily need to join every raw event row to PostgreSQL.
The lakehouse side can first aggregate the events:
SELECT
customer_id,
COUNT(*) AS purchase_count
FROM customer_events
WHERE event_type = 'purchase'
AND event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id;The result is much smaller:
customer_id | purchase_count
------------+---------------
101 | 12
102 | 4
103 | 27That smaller result can then be joined with PostgreSQL customer data.
Conceptually:
Raw Iceberg Events
|
v
Filter
|
v
Aggregate
|
v
Small Result
|
v
PostgreSQL Customers
|
v
Final ResultThis is often a much better design than transferring raw events.
Use Materialized or Pre-Aggregated Data for Repeated Queries
Some queries are executed repeatedly.
For example, a dashboard might display:
Orders today
Orders this month
Active customers
Purchases per customer
Customer activity
Revenue by regionIf the same expensive lakehouse calculations are executed every time someone opens the dashboard, response time and compute usage can increase.
A better approach may be to create pre-aggregated analytical datasets.
For example:
Raw Events
|
v
Aggregation Pipeline
|
v
CustomerDailyMetricsThe dashboard then queries:
CustomerDailyMetricsinstead of scanning billions of raw events.
This moves expensive work from request time to data-processing time.
Joining on Stable Keys
Cross-system joins should use stable identifiers.
For example:
customer_id
order_id
product_id
account_idThe same identifier should have consistent meaning in both systems.
Join the conversation! Your thoughts help the community grow.