Microservices killed your SQL JOINs. Now what?

In a monolith, showing an order with its customer, product and payment status is one query:

SELECT o.id, u.name, p.product_name, pay.status
FROM orders o
JOIN users u    ON o.user_id = u.id
JOIN products p ON o.product_id = p.id
JOIN payments pay ON o.payment_id = pay.id
WHERE o.id = 100;

Fast, simple, and the database optimiser does the hard work. Then you split the system into microservices, each with its own database — and that query becomes impossible. The order service cannot reach into the user service’s tables; doing so would couple the services at the database level and defeat the isolation you migrated for. This is the trade nobody advertises when praising microservices: you give up JOINs.

What used to be one SQL statement now means multiple network calls, in-memory aggregation, and eventually a different architecture. Two patterns cover the vast majority of production systems.

Pattern 1: API composition

The straightforward answer: one service takes responsibility for calling the others and assembling the result. The “join” happens in application memory.

public OrderResponse getOrder(Long orderId) {
    OrderDto order     = loadOrder(orderId);
    UserDto user       = userClient.getUser(order.userId());
    ProductDto product = productClient.getProduct(order.productId());
    PaymentDto payment = paymentClient.getPayment(orderId);
    return new OrderResponse(order, user, product, payment);
}

For a detail page or a dashboard widget this is genuinely fine: easy to implement, no new infrastructure, low operational cost. If your query touches two or three services and returns one entity’s worth of data, stop here.

Where composition falls apart

Now add filtering across services. Say you need the 10 most recent orders where the product category is Fresh and the customer is VIP. Fetch 10 orders, filter by product: 7 survive. Filter by customer level: 5 survive. You needed 10 — so you loop: fetch the next batch, call two services again, filter, repeat until enough rows survive.

Each iteration multiplies network calls, memory and latency, and the worst case scales with the size of your order history, not the size of your result. On millions of rows, a query that “joins” across three services this way is not slow — it’s unshippable. Cross-service filtering, sorting and pagination are the signal that composition has hit its ceiling.

Pattern 2: CQRS with a read model

Instead of assembling data at query time, assemble it at write time. Command Query Responsibility Segregation splits the system in two: the write model stays as it is (each service owns its data), and a separate read model stores pre-joined, denormalised documents optimised for exactly the queries you need.

The plumbing: each service publishes its changes — via Kafka events, or change data capture with a tool like Debezium reading the database log — and a consumer maintains a wide document in a query store, commonly Elasticsearch. The earlier nightmare query becomes trivial, because everything already lives in one document:

POST orders/_search
{
  "size": 10,
  "query": {
    "bool": {
      "must": [
        { "term": { "userLevel": "VIP" } },
        { "term": { "category": "Fresh" } }
      ]
    }
  }
}

No fan-out, no loops, and you gain full-text search, aggregations, sorting and pagination essentially for free. For search pages, reporting, BI and anything analytics-shaped, the difference in performance and flexibility is not incremental — it’s categorical.

What CQRS costs you

Nothing is free, and CQRS bills you twice.

Infrastructure. A message broker, a query store, a CDC connector, one more service to deploy and monitor. Every component is an operational surface.

Eventual consistency. The read model lags the write model — milliseconds to seconds depending on your pipeline. A user updates their profile, queries the search page, and briefly sees the old value. For search and reporting that’s almost always acceptable; for workflows that need read-your-own-writes, you must design around it explicitly. And you should monitor the lag: a silently stalled consumer means confidently serving stale data.

Choosing

	API composition	CQRS + read model
Query complexity	simple, few services	cross-service filters, search, analytics
Infrastructure	none extra	broker + query store + sync
Consistency	strong (reads live data)	eventual
Best for	detail pages, dashboards	search pages, reporting, high-throughput reads

Two rules of thumb hold up well in practice. Never let one service query another’s database directly — every shortcut there becomes permanent coupling. And don’t introduce CQRS before a real query forces you to; it’s a solution with a carrying cost, not a default.

The larger lesson sits upstream of both patterns: the convenience of SQL JOINs is one of the things you pay for microservices with. For small and mid-size systems, a well-modelled monolith with good SQL is often faster to build, faster at runtime and easier to operate. Choose the simplest architecture that meets today’s constraints, and reach for these patterns when — not before — the constraints demand them.