# Federated Query and Cross-Domain Spatial Joins

A query that spans domains is the one operation a mesh makes harder than a monolith, and the whole discipline is about paying that cost deliberately rather than accidentally.

Domain boundaries are drawn so that most queries stay inside one of them, and most do. The remainder are the ones that matter: a flood-risk assessment joining hydrology extents against parcel ownership, a logistics plan intersecting the road network with restricted zones, a regulatory report aggregating three domains’ coverage. In a monolith these were ordinary joins against one database. In a mesh they cross ownership, storage, and often physical boundaries — and executed naively, they pull entire datasets across the network to be joined in the middle. This topic, within Federated Ownership & Routing Architecture, specifies how such queries are planned, bounded, and executed so they remain tractable, and how to recognise the ones that should not be executed synchronously at all — a decision that connects directly to Async Execution for Heavy Spatial Queries.

# Architectural Boundaries & Design Rationale

The governing principle is push the predicate to the data, never pull the data to the predicate. A federated query engine that fetches two domains’ full feature sets and joins them locally has reproduced the centralized model with an added network hop; one that pushes the spatial predicate into each domain and joins only the survivors is doing something the monolith could not — running two filters concurrently, in the domains that own the indexes.

Whether pushdown is possible is a property of the query, and it partitions cleanly.

Fully pushable queries have a predicate each domain can evaluate alone. A bounding-box restriction, an attribute filter, a resolution constraint — each domain applies it locally, returns a small result, and the federation layer performs a cheap final join. These are the queries a mesh handles well, often better than a monolith, because the work parallelises across independently-scaled domains.

Partially pushable queries have a spatial relationship between the domains that neither can evaluate alone, but whose candidates can be narrowed. A join on ST_Intersects between hydrology polygons and parcels cannot be evaluated in either domain, but the bounding boxes of one side can be pushed as a filter to the other, reducing both result sets by orders of magnitude before the exact predicate runs. This two-phase shape — push envelopes, join exactly — is the workhorse of cross-domain spatial work.

Non-pushable queries have a predicate that depends on the full contents of both sides: a nearest-neighbour search with no distance bound, a global topology reconciliation, a clustering operation. These have no bounded form, and admitting them to a synchronous path is how a federation layer becomes an outage.

The second boundary is who is accountable for the result. A cross-domain join produces an answer that no single domain owns, which means no single domain can be responsible for its accuracy, its freshness, or its SLA. The federation layer must therefore report the provenance of every side — which product, which version, materialized when — so that a consumer can evaluate the join’s overall trustworthiness themselves. A federated result presented without per-side provenance implies a guarantee nobody made.

# Specification & Contract Reference

A cross-domain query is declared with an explicit budget. Unbudgeted federated queries are the single largest source of unplanned load in a mesh, because their cost is invisible until they run.

The crs parameter is required rather than defaulted for a specific reason: a spatial join evaluated across two different SRIDs either fails outright or, worse, silently returns nothing because the coordinate ranges do not overlap. PostGIS will refuse mismatched SRIDs; a federation engine joining GeoParquet from two domains may not, and will simply produce an empty result that reads as “no features intersect”. Declaring the join CRS forces both sides to be reprojected explicitly, and makes the reprojection cost visible in the plan.

Provenance is returned alongside every federated result:

{
  "result": { "rows": 1842 },
  "provenance": [
    { "domain": "hydrology", "product": "flood_extents",
      "version": "v3.1.0-crs:EPSG:4326-res:10m",
      "materialized_at": "2026-08-09T02:14:00Z", "rows_scanned": 88421 },
    { "domain": "cadastral", "product": "parcels",
      "version": "v1.2.0-crs:EPSG:4326-res:10m",
      "materialized_at": "2026-08-10T02:07:00Z", "rows_scanned": 412903 }
  ],
  "budget": { "duration_ms": 8412, "bytes_transferred": 41287431, "aborted": false }
}

# Production Implementation

The two-phase join: push envelopes to both domains, then evaluate the exact predicate on the survivors. Expressing it so the planner can reach both spatial indexes is what makes the difference between a query that finishes and one that scans both tables in full.

-- federated_join.sql — envelope pushdown, then exact predicate.
-- Both sides are foreign tables; the WHERE clauses below are pushed to each domain.
-- Parameters: :extent (WKT, EPSG:4326), :join_srid (int), :max_candidates (int)
WITH bounds AS (
    SELECT ST_GeomFromText(:'extent', 4326) AS g
),
-- Phase 1: each domain filters locally against the shared extent. These predicates
-- are index-backed and push down, so only survivors cross the boundary.
hydro AS (
    SELECT h.feature_id, h.geom, h.severity
    FROM hydrology_flood_extents h, bounds b
    WHERE h.geom && b.g                       -- && is the index-backed bbox operator
      AND ST_Intersects(h.geom, b.g)
),
parcels AS (
    SELECT p.parcel_id, p.geom, p.owner_ref
    FROM cadastral_parcels p, bounds b
    WHERE p.geom && b.g
      AND ST_Intersects(p.geom, b.g)
),
-- Guard: refuse to run the exact join if pushdown did not narrow enough.
guard AS (
    SELECT (SELECT count(*) FROM hydro)   AS h_n,
           (SELECT count(*) FROM parcels) AS p_n
)
SELECT p.parcel_id, p.owner_ref, h.severity,
       ST_Area(ST_Intersection(p.geom, h.geom)::geography) AS affected_m2
FROM parcels p
JOIN hydro h
  -- Phase 2: bbox overlap first (cheap, indexed), exact predicate only on survivors.
  ON p.geom && h.geom
 AND ST_Intersects(p.geom, h.geom)
WHERE (SELECT h_n FROM guard) <= :max_candidates
  AND (SELECT p_n FROM guard) <= :max_candidates;

The budget is enforced by the federation layer rather than left to the database, so a breach produces a diagnostic a consumer can act on instead of a timeout.

# federated_query.py — admission, budget enforcement, and provenance.
import time

LIMITS = {
    "max_candidates_per_side": 250_000,
    "max_rows_scanned": 5_000_000,
    "max_duration_ms": 30_000,
    "max_bytes_transferred": 256 * 1024 * 1024,
}


class BudgetExceeded(Exception):
    """Raised with the observed value, so the consumer learns which bound they hit."""


def admit(request: dict) -> None:
    """An unbounded federated join is not admissible on the synchronous path."""
    if not request.get("extent"):
        raise BudgetExceeded("extent is required; an unbounded join has no bounded cost")
    if not request.get("crs"):
        raise BudgetExceeded("crs is required; joining across mismatched SRIDs returns nothing")


def run(request: dict, execute) -> dict:
    admit(request)
    started = time.monotonic()
    observed = {"rows_scanned": 0, "bytes_transferred": 0}

    def on_progress(rows: int, byts: int) -> None:
        observed["rows_scanned"] += rows
        observed["bytes_transferred"] += byts
        elapsed_ms = (time.monotonic() - started) * 1000
        if observed["rows_scanned"] > LIMITS["max_rows_scanned"]:
            raise BudgetExceeded(f"rows_scanned={observed['rows_scanned']}; move to the async path")
        if observed["bytes_transferred"] > LIMITS["max_bytes_transferred"]:
            raise BudgetExceeded("bytes_transferred exceeded — predicate pushdown is not happening")
        if elapsed_ms > LIMITS["max_duration_ms"]:
            raise BudgetExceeded(f"duration_ms={elapsed_ms:.0f}; move to the async path")

    rows, provenance = execute(request, on_progress)
    return {
        "result": {"rows": len(rows)},
        "provenance": provenance,          # per-side version and materialization time
        "budget": {**observed, "duration_ms": int((time.monotonic() - started) * 1000)},
    }

Confirm pushdown is actually occurring — the single most valuable check in this whole topic:

# The plan must show the filter executing remotely. "Foreign Scan" with the WHERE
# clause in "Remote SQL" is pushdown working; a bare fetch followed by a local
# filter means the whole table is crossing the boundary.
psql -c "EXPLAIN (ANALYZE, VERBOSE) $(cat federated_join.sql)" | grep -A2 "Foreign Scan"

# Diagnostic Runbook

1. Read the plan before touching the query. EXPLAIN VERBOSE shows whether the predicate appears in the remote SQL. If it does not, nothing else you change will matter — the whole table is crossing the boundary.
2. A federated query returning zero rows is almost always a CRS mismatch, not an absence of data. Confirm both sides are in the declared join CRS. Coordinates in EPSG:3857 and EPSG:4326 never overlap numerically, so the join is empty and no error is raised.
3. When pushdown stops working after a change, look for a function wrapping the geometry column. ST_Transform(geom, 4326) && bounds is not pushable and not index-backed; reprojecting the bounds into the column’s CRS instead is both.
4. A max_bytes_transferred breach names the failure precisely. It means the envelope phase did not narrow the candidates, which usually means one side has no spatial index on the joined column, or the extent supplied was far larger than the caller intended.
5. Check per-side provenance before investigating a wrong result. Two sides materialized eighteen hours apart will disagree about anything that changed in between, and that is a freshness question rather than a join defect.
6. For a query that is correct but too slow, compare candidate counts to result rows. A join producing 1,800 rows from 500,000 candidates per side is doing 250 billion exact-predicate evaluations’ worth of candidate pairing; the fix is a tighter extent or a finer partitioning, not more workers.
7. If a consumer repeatedly hits the budget, move them to the async path rather than raising the limit. Raising a synchronous budget moves the failure from one consumer to everybody sharing the capacity.

# SLA Targets & Performance Baselines

# Freshness Skew, and Why a Federated Result Is Never a Snapshot

A join across domains reads data that was materialized at different moments, and there is no mechanism in a federated mesh that makes them agree. This is not a defect to be engineered away; it is the direct consequence of domains publishing on their own cadences, which is the property the mesh exists to provide. What it does require is that the skew be visible and bounded rather than silently absorbed.

Consider the flood-risk join. The hydrology domain republishes after each observation cycle; the cadastral domain republishes on a quarterly survey update. A join executed at any moment reads one side that may be hours old and another that may be weeks old, and the result describes a state of the world that never existed at any single instant. For most purposes that is entirely acceptable — parcels move slowly — and for some it is not.

The discipline has three parts. Report the skew, as the interval between the earliest and latest materialization across the inputs, alongside the result. Bound it where it matters, by letting a consumer declare a maximum acceptable skew and having the query fail rather than return a result that exceeds it. And allow pinning, so a consumer who needs reproducibility can name explicit versions on each side instead of taking whatever is current — which is the only way a federated result can be regenerated identically later.

The last row names a failure that is easy to miss. If one input domain’s pipeline has been failing for three days, the join keeps succeeding, its result keeps looking plausible, and nothing in the join layer notices — the stale side simply contributes older geometry. Comparing each side’s materialization timestamp against that product’s declared cadence, rather than against the other side, is what turns that into a detectable condition. A hydrology layer with a daily cadence whose newest artifact is three days old is a stale input regardless of what the cadastral side is doing, and the join should say so.

Skew also interacts with caching in a way worth anticipating. A federated result cached for even a short period inherits the skew of the moment it was computed, and a consumer reading it later sees a result whose reported freshness is older still. Where federated results are cached at all, the cache entry must carry the provenance block rather than only the rows, so the staleness a consumer reasons about is the true one rather than the cache’s own age. Caching the rows and recomputing the provenance on read is the specific mistake here: it produces a result that claims to be fresher than the data it contains.

# Governance & Compliance Notes

A cross-domain join creates a derived dataset, and derived datasets inherit the strictest constraint of their inputs. Joining an unrestricted road network against a residency-constrained parcel layer produces a result that is residency-constrained — which means the federation layer must evaluate residency over the union of inputs before executing, and must refuse a join whose inputs cannot lawfully be combined in the region where execution would occur. This check belongs before planning, not after, because a plan that has already fetched data has already moved it.

Access control composes the same way. A caller entitled to both inputs separately is not automatically entitled to their join, because the join can reveal what neither input does alone — the classic case being a low-resolution public layer joined against a precise restricted one, where the intersection discloses restricted positions. Where that risk exists, the entitlement to a specific join is its own grant, and the federation layer enforces it as a distinct check rather than deriving it from the input permissions.

Finally, the provenance block is audit evidence and should be retained with the same discipline as any other. A decision made on a federated result eighteen months ago is reproducible only if the versions of both inputs were recorded at the time — and only if both of those versions are still addressable, which is a retention obligation the joining consumer has to negotiate with each producing domain rather than assume.

# Related

• Async Execution for Heavy Spatial Queries — where non-pushable joins belong
• Cross-Domain Routing Strategies — how each side of a join is resolved to its domain
• Zero-Trust Security for Spatial Endpoints — the entitlement checks a join composes
• CRS Governance and Reprojection Standards — why a join must declare its CRS
• Federated Ownership & Routing Architecture — up to the section overview