Why is a microservices cross service query hard with database-per-service?

In a monolith, "show each order with the customer's name and current shipping status" is one SQL statement: join orders, customers, and shipments, and let the query planner handle it inside a single transaction. With database-per-service, those three tables live in three databases owned by three teams. There is no join to write. The query becomes a distributed problem that involves network calls, partial failures, pagination across systems that sort differently, and data that may not agree at a given instant.

The obvious shortcuts break the reasons you split the services. If the order service reads the customer service's tables directly, the customer team can no longer rename a column, change a type, or move to a different storage engine without coordinating with every reader. Their schema has quietly become a public API with no versioning. A shared database also couples deployments, because one service's migration can break another's queries. It couples load as well: an expensive reporting query from one team can slow down another team's writes. Ownership exists on paper only.

This leaves two legitimate approaches to a microservices cross service query:

  • API composition: a composer calls each owning service's API and joins the results in memory.
  • Data replication: a service keeps its own local read model, populated from events that other services publish.

The position taken here is to start with composition. Replicate only when a specific query's access pattern makes composition fail. The rest of the article is for backend and platform engineers who own read paths spanning services and need to make that call.

How does the API composition pattern work?

A composer sits in front of the owning services and does the join itself. It can be an API gateway, a backend-for-frontend, or a dedicated query service. For an "orders with customer names" view, it fetches a page of orders from the order service, collects the customer IDs, asks the customer service for those records, and merges the two in memory before applying any remaining filters and pagination.

This is the default because it needs no new infrastructure, every response reflects current data, and each service stays the single authority for its own records. A well-built composer issues independent calls in parallel and gives each call its own timeout. When a non-critical dependency fails, it returns a partial response, such as orders with customer names marked unavailable, instead of failing the whole request.

Where composition breaks

  • Cross-owner filtering or sorting. "Orders over $500 from customers in Germany, sorted by customer name" forces the composer to over-fetch from both services and filter in memory.
  • Deep pagination. Page 40 of a cross-service sort means materializing the 39 pages before it.
  • Large fan-out and N+1 calls. Fetching each customer one at a time multiplies requests.

The main fix for N+1 is a batch endpoint that accepts a list of IDs and returns all matching records in one call.

Trade-offs

Availability is the product of the availability of every dependency. As an illustrative estimate, three dependencies at 99.9% each yield about 99.7%. Latency is bounded by the slowest call. Wider fan-out raises the chance that some call lands in its tail, and it multiplies load on downstream services. Retries without budgets, backoff, and jitter compound that load and can turn one slow dependency into an outage.

type Order = { id: string; customerId: string; totalCents: number };
type Customer = { id: string; name: string };
type Shipment = { orderId: string; status: string };

const ORDERS = "";
const CUSTOMERS = "";
const SHIPPING = "";

// Every downstream call gets its own deadline so one slow service cannot stall the request.
async function getJson<T>(url: string, timeoutMs: number): Promise<T> {
  const res = await fetch(url, { signal: AbortSignal.timeout(timeoutMs) });
  if (!res.ok) throw new Error(`${url} -> ${res.status}`);
  return (await res.json()) as T;
}

export async function ordersView(page: number) {
  // Orders are the critical dependency: if this call fails, the whole request fails.
  const orders = await getJson<Order[]>(`${ORDERS}/orders?page=${page}&limit=50`, 800);
  const customerIds = [...new Set(orders.map((o) => o.customerId))].join(",");
  const orderIds = orders.map((o) => o.id).join(",");

  // Batch endpoints (one call per service, not one per ID), issued in parallel.
  // allSettled lets one enrichment fail without rejecting the other.
  const [customers, shipments] = await Promise.allSettled([
    getJson<Customer[]>(`${CUSTOMERS}/customers?ids=${customerIds}`, 300),
    getJson<Shipment[]>(`${SHIPPING}/shipments?orderIds=${orderIds}`, 300),
  ]);

  const names = new Map(customers.status === "fulfilled" ? customers.value.map((c) => [c.id, c.name]) : []);
  const statuses = new Map(shipments.status === "fulfilled" ? shipments.value.map((s) => [s.orderId, s.status]) : []);

  // In-memory join; a missing value is marked unavailable instead of failing the response.
  return {
    partial: customers.status === "rejected" || shipments.status === "rejected",
    items: orders.map((o) => ({
      ...o,
      customerName: names.get(o.customerId) ?? null,
      shipmentStatus: statuses.get(o.id) ?? null,
    })),
  };
}

A TypeScript composer that fetches a page of orders, then calls batch customer and shipping endpoints in parallel with per-call timeouts, and joins the results in memory, returning a partial response flagged as such when an enrichment call fails.

How does data replication build a local read model?

Replication removes that runtime dependency by moving the join from query time to write time. Each owning service publishes events when its data changes, either domain events such as OrderShipped or row-level change events. A consumer subscribes and maintains a local read model, a CQRS-style view shaped for one query, such as a denormalized "order summary" table that already holds customer name and shipping status.

Composition (request path, at query time)
  client ──► composer ──┬──► Orders API ────► orders DB
                        ├──► Customers API ─► customers DB
                        └──► Shipping API ──► shipping DB

Replication (event path, before query time)
  producer DB ──► outbox / CDC ──► broker ──► consumer ──► read model
                                                               ▲
  client ──► query service ────────────────────────────────────┘

What it buys:

  • One local query. The query service reads one store, so there is no fan-out and no tail amplification.
  • Indexed cross-service filters. A filter such as "orders over a threshold for customers in a region" becomes an indexed query instead of fetching candidates from one service and filtering them in memory.
  • Isolation from upstream outages. If the customer service is down, reads still succeed against the last replicated state.

What it costs:

  • Freshness. The view lags the source by however long the event takes to reach the consumer and be applied.
  • Schema coupling. The event contract becomes a public API. Renaming a field can break consumers you don't own, so contracts need versioning and a compatibility policy.
  • Copies of sensitive fields. Every replicated email address or address line is another store to secure. Access control has to be enforced there too. Deletion requests must reach every copy, and each copy needs its own retention rules.
 COMPOSITION: join at query time (synchronous request path)

 +--------+   +----------+   +---------------+   +--------------+
 | client |-->| composer |-->| Orders API    |-->| orders DB    |
 +--------+   +-----+----+   +---------------+   +--------------+
                    |        +---------------+   +--------------+
                    +------->| Customers API |-->| customers DB |
                    |        +---------------+   +--------------+
                    |        +---------------+   +--------------+
                    +------->| Shipping API  |-->| shipping DB  |
                             +---------------+   +--------------+
  fan-out per read: latency tracks slowest call; an outage fails it

 REPLICATION: join at write time (asynchronous event path)

 +-------------+   +-----------+   +--------+   +----------+   +----------+
 | producer DB |-->| outbox or |-->| broker |-->| consumer |-->|   read   |
 | (owner)     |   |    CDC    |   |        |   | (apply)  |   |  model   |
 +-------------+   +-----------+   +--------+   +----------+   +----------+
                                                                    ^
 +--------+   +---------------+                                     |
 | client |-->| query service |-- one local, indexed query ---------+
 +--------+   +---------------+
  lag = time from source commit until the event is applied to the view

Composition fans out to every owning service on each request, while replication moves the join to write time by streaming change events through an outbox or CDC and a broker into a local read model that the query service reads directly.

How do you implement reliable replication with an outbox or CDC?

The obvious approach is a dual write: commit the order row, then publish OrderPlaced to the broker. It fails in both directions. If the process crashes after the commit, the event is lost and the view never learns about the order. If you publish first and the transaction rolls back, consumers see an order that never existed. No retry ordering fixes this, because the database and the broker cannot share a single atomic commit.

The transactional outbox removes the second write. In one transaction, insert the business row and an outbox row holding the event ID, aggregate ID, version, and payload. A relay polls unpublished outbox rows in order, publishes them, and marks them sent. Delivery becomes at-least-once.

Change data capture gets the same guarantee by tailing the database's write-ahead or replication log. You add no outbox table and no relay polling, but you take on schema coupling and connector operations.

At-least-once delivery means duplicates are normal, so consumers must be idempotent:

  • Partition by entity key so all events for one order stay in sequence.
  • Upsert conditionally: INSERT ... ON CONFLICT (order_id) DO UPDATE ... WHERE view.version < excluded.version. This drops replays and stale versions.
  • Route poison messages to a dead-letter queue instead of blocking the partition.

To rebuild a view, replay retained events or backfill from a source snapshot, then resume from the log position where the snapshot was taken.

Run periodic reconciliation that compares counts or checksums against the source. A lost event or a misconfigured partition causes silent drift, and nothing else will report it.

-- PostgreSQL. Producer: business row and outbox row commit or roll back together.
BEGIN;
INSERT INTO orders (order_id, customer_id, status, total_cents, version)
VALUES ('ord_1042', 'cus_77', 'PLACED', 4999, 1);

INSERT INTO outbox (event_id, aggregate_id, version, event_type, payload)
VALUES (gen_random_uuid(), 'ord_1042', 1, 'OrderPlaced',
        '{"order_id":"ord_1042","customer_id":"cus_77","status":"PLACED","total_cents":4999}'::jsonb);
COMMIT;

-- Relay: claim the oldest unpublished rows (outbox.id is a bigserial).
-- Run one relay at a time, or one per shard, so per-order order is preserved.
BEGIN;
SELECT id, event_id, aggregate_id, version, event_type, payload
FROM outbox
WHERE published_at IS NULL
ORDER BY id
LIMIT 100
FOR UPDATE SKIP LOCKED;
-- Publish each row with message key = aggregate_id and wait for broker acks.
-- A crash here republishes the batch later: at-least-once, never lost.
UPDATE outbox SET published_at = now() WHERE id = ANY($1);  -- $1 = acked ids
COMMIT;

-- Consumer (read side): idempotent upsert that ignores duplicates and stale versions.
INSERT INTO order_view (order_id, customer_id, status, total_cents, version, updated_at)
VALUES ($1, $2, $3, $4, $5, now())
ON CONFLICT (order_id) DO UPDATE
SET customer_id = EXCLUDED.customer_id,
    status      = EXCLUDED.status,
    total_cents = EXCLUDED.total_cents,
    version     = EXCLUDED.version,
    updated_at  = EXCLUDED.updated_at
WHERE order_view.version < EXCLUDED.version;
-- 0 rows affected means a replay or out-of-date event: acknowledge it and move on.

A PostgreSQL transactional outbox. The order and its event commit atomically, a relay publishes unsent rows keyed by order ID, and the view consumer upserts only when the incoming version is newer.

How do latency and staleness compare in practice?

Every number below is an assumption chosen to show the arithmetic. Illustrative estimate, not a measurement.

Assumed inputs: each downstream service has p50 = 20 ms, p99 = 200 ms, and 99.9% availability. Calls are independent and issued in parallel, so the composed response waits for the slowest call.

With fan-out n, the chance that at least one call exceeds its p99 is 1 − 0.99ⁿ. For n = 4 that is about 3.9%. For n = 10 it is about 9.6%. A 200 ms delay that each service hits 1% of the time therefore shows up near the composed p96 or p90, and the composed p99 lands further out. Availability multiplies the same way: 0.999⁴ ≈ 99.6% and 0.999¹⁰ ≈ 99.0%. That is roughly 4× and 10× the failure rate of a single dependency.

Assumed property Composition (n = 4) Replication
Read latency Slowest of 4 calls One local query, ~5 ms p99 assumed
Read availability ≈ 99.6% Local database only
Staleness None at read time Publish 500 ms + broker 50 ms + consumer lag 100 ms ≈ 650 ms

Replication removes the compounding from the read path and turns it into a staleness bound. That bound holds only while consumers keep pace with incoming events. Assume a consumer handles 2,000 events/s against a normal load of 1,000/s. A 60-second spike at 5,000/s builds a backlog of 180,000 events, which drains at the 1,000/s spare capacity, so it takes about 3 minutes. Staleness rises for the whole drain period, not only during the spike.

A full rebuild replays history, so lag can reach hours. Keep serving reads from the old model until the new one catches up.

When should you use composition, replication, or a hybrid?

Start from the query, not the architecture. Each trait below pushes toward one approach, and the reason matters more than the label.

Query trait Favors composition Favors replication
Consistency need Must reflect the latest write Seconds of lag are acceptable
Read/write ratio Rarely read, often changed Read far more than written
Query shape Lookup by ID Filter and sort across services
Volume and pagination Small result sets, shallow pages Deep pages over large sets
Fan-out Two or three calls Many services per request
Ownership Owning team changes schema often Source schema is stable

Cross-service filtering is where composition breaks. You can't sort orders by customer region without first fetching every candidate from both services. A local read model can index those columns.

Hybrid designs

A common hybrid replicates only identifiers and the fields used for filtering and sorting into an index view. The query runs against that view to get a page of IDs, then composes current details from the owning services. You get correct pagination over a small, slow-changing projection, and the fields users act on stay fresh. The cost is more moving parts: a projection, its consumers, and a composer. It also blurs the source of truth. Engineers start reading cached status fields as authoritative. Document which fields in the view are hints and which come live from the owner.

Alternatives

  • Merge the services. If two services' data is always queried together, the boundary is probably wrong.
  • Build a search index fed by events. This suits full-text and faceted queries.
  • Use a reporting pipeline for analytical queries. These don't belong on the request path.

When should you avoid data replication, and how does the choice affect engineering cost?

When to avoid each approach

Skip replication when a business rule cannot tolerate a stale read. Account balances, inventory reservations, and authorization checks must ask the owning service, because a replica that is a few seconds behind can approve an overdraft or grant revoked access. Skip it as well for low-traffic queries that composition already serves within its latency budget, since a read model adds infrastructure without a measurable gain. Sensitive data with strict deletion or residency obligations is a third case: every copy is another store to purge, audit, and keep in the right region.

Skip composition when a query fans out to many services per request, sorts across fields owned by different services, or paginates deeply. In those cases the composer has to over-fetch and merge in memory, and both cost and latency grow with page depth.

Where the engineering cost goes

Composition's cost lives in request-path code: timeouts, fallbacks, and handling partial failure. Replication's cost lives in infrastructure that teams often underestimate. It needs a broker, lag monitoring, dead-letter queues, event versioning, a tested rebuild procedure, and reconciliation jobs that detect drift.

To save time, default to composition backed by batching endpoints. Replicate per query, not whole entities. Run one outbox or CDC pipeline and have every view consume from it. Automate rebuilds and reconciliation from day one, because adding them after the first data-drift incident is much more expensive.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to microservices cross service query, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.