The pitch for query federation is compelling: one interface, every data source, no data movement. Write a single SQL query against Postgres, MongoDB, and S3 simultaneously. It looks great on the architecture slide. In production, the friction accumulates quickly.
The pushdown problem
The fundamental challenge with federation is predicate pushdown. When you join a Postgres table against a MongoDB collection, the query layer has to decide which filters to push down to each data source and which to evaluate in the federation engine after fetching data. If it pushes down wrong, you fetch millions of rows over the wire and filter them locally. If it can't push down at all (because the source doesn't support the predicate), you fetch everything.
MongoDB's query model supports a subset of what SQL WHERE clauses can express. S3 supports even less (SELECT at scan time, with limitations). The federated layer has to maintain a capability matrix for every source type and reason carefully about what each source can handle.
Consistency semantics
A federated query across Postgres and MongoDB is not atomic. The Postgres snapshot is taken at one moment, the MongoDB read at another. If the data in both sources is changing while your query runs, you can get results that are internally inconsistent: rows that reference IDs that have since been deleted, counts that don't add up, time ranges that don't align. For reporting use cases this is often acceptable. For operational queries that drive user-facing features, it usually isn't.
When federation is the right answer
Federation works well for analytical workloads where freshness requirements are measured in hours rather than seconds, where the data volume from each source is predictable, and where you control all the sources well enough to manage schema changes across them. It's a poor fit for operational queries where latency, consistency, and correctness are all critical at the same time.