A federation layer joins a 2 billion-row lakehouse fact table to a 40000-row customer table in Postgres. Each side queried alone answers in seconds. Joined they run for 40 minutes and the federation engine's memory alarm fires. Nothing is down and nothing returns an error. What is happening?

The first three things to look at

1. The query plan's split point - which operators the engine pushed to each source and which it kept. This answers the question on its own.
2. Bytes read per source, from the plan or the connector metrics. Two billion rows crossing the network is unmistakable.
3. Whether either source reports the join. Postgres pg_stat_statements will show a plain SELECT of the whole customer table, not a join.

The diagnosis

Pushdown works inside one source. A filter, a projection and an aggregate over lakehouse columns can be executed by the lakehouse engine; the same for Postgres. A join whose two sides live in different systems cannot be pushed anywhere, because neither source can see the other's rows - so the federation engine becomes the execution site and the data has to come to it.

The engine then picks a side to stream. If it chooses to build its hash table on the Postgres side and stream the fact table past it, it still pulls 2 billion rows across the network and through its own memory. That is the 40 minutes, and it is why memory alarms rather than CPU.

The misleading signal

Each side being fast alone. It suggests both sources are healthy, which they are, and it sends people to tune the sources. The cost is in the layer between them, and it is invisible in either source's monitoring.

Why the other options fail

• "Postgres needs an index." Forty thousand rows is a sequential scan of a few megabytes. An index on the small side changes nothing when the large side is being shipped wholesale.
• "Stale statistics and a bad join order." Plausible inside one engine, and here no join order avoids moving one of the two tables. Reversing the order would help only if the engine could push the small side down - which is the fix, not the cause.
• "Collation mismatch." Real in federation and it produces wrong results or disabled pushdown of predicates, not a 50000-fold data movement.
• "A cold result cache." The second run is identical. A cache would mask the problem without removing it, and it would serve stale rows the next time Postgres changes.

The fix

Reverse the direction of travel: move the 40000 rows to the data, not the data to the rows. Two forms, both cheap. Materialise the customer table into the lakehouse on a schedule - 40000 rows is a few megabytes, an hourly refresh costs nothing, and the join becomes local. Or, if the engine supports it, force a broadcast of the small side so the join executes where the fact table lives.

Decision rule: federate reads that stay inside one source, or that are small on both sides. The moment a join is large-to-small and runs more than a few times a day, copy the small side. Federation's promise is "no copies"; a 5 MB hourly copy is the cheapest exception you will ever make.

When this is the wrong answer

If the small table may not be copied - residency rules, a contract, data the other side is not allowed to hold - then no copy is available and the join must stay remote. Push every filter you can, run it on a schedule rather than interactively, bound it with a row limit and a timeout on the connector, and tell the consumer the latency is structural rather than a bug to be fixed.