Database Per Service in .NET Microservices: Which Joined Queries Get More Expensive, and How We Handle Them

Database-per-service makes a clean promise: each service owns its schema, nothing outside that service touches its tables directly, and teams can evolve their data model without coordinating a deploy across half the organization. That promise holds. What gets said less often is what happens to the queries that, in a monolith with a single database, used to be a one-line JOIN and that, once the databases split, turn into a design problem you have to solve again, service by service.

The query we lost

In an order system built on .NET with Entity Framework Core, a typical query before splitting the databases looked like this:

var orders = await db.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .Select(o => new OrderWithCustomer
    {
        OrderId = o.Id,
        Total = o.Total,
        CustomerName = o.Customer.Name,
        DiscountTier = o.Customer.DiscountTier
    })
    .ToListAsync();

Postgres resolves that JOIN with an index on the foreign key and moves on. Once Orders and Customers live in separate databases owned by separate services, that query stops existing as such. The Orders service no longer has the Customers table, and it shouldn't -that is exactly the boundary the pattern asks us to respect. The problem isn't that we "can no longer do a JOIN" -we knew that going in. The problem is that we still need that combined data, and now someone has to decide where it gets computed and with what level of consistency.

Which joined queries actually get expensive

Not every cross-service query suffers equally. The ones that get expensive share a recognizable shape: they need to combine a non-trivial set of rows from one service with data from another service, and they need it on the hot path of an HTTP response.

The most common case is a paginated list filtered on an attribute that lives in the other service. Asking for "pending orders from customers on the Gold discount tier" requires, in a split schema, first fetching every pending order and then resolving each involved customer's tier, because the filter can't be pushed down into the Orders database. The naive way to solve it is the one everyone reaches for first, and it's the one to avoid:

var orders = await ordersDb.Orders
    .Where(o => o.Status == OrderStatus.Pending)
    .ToListAsync();

var result = new List<OrderWithCustomer>();
foreach (var order in orders)
{
    var customer = await customerServiceClient.GetCustomerAsync(order.CustomerId);
    if (customer.DiscountTier == "Gold")
        result.Add(new OrderWithCustomer(order, customer));
}

This is an N+1 problem wearing a microservice costume, and it's worse than the original N+1 you get from a misused ORM: each iteration isn't a SQL query against the same database, it's a full network round trip, with its own latency and its own chance of timing out. This version multiplies that latency by the number of orders, and it also couples the availability of the Orders list to the real-time availability of the Customers service.

Composing at the API gateway: when it's enough

The first tool we reach for is composing the response at the layer that aggregates calls -a BFF or the API gateway itself- instead of inside the Orders service. The difference from the example above isn't avoiding the cross-service call, it's making it once instead of once per row:

var orders = await ordersServiceClient.GetPendingAsync();
var customerIds = orders.Select(o => o.CustomerId).Distinct();
var customers = await customerServiceClient.GetByIdsAsync(customerIds);
var customersById = customers.ToDictionary(c => c.Id);

var result = orders
    .Where(o => customersById[o.CustomerId].DiscountTier == "Gold")
    .Select(o => new OrderWithCustomer(o, customersById[o.CustomerId]));

This requires the Customers service to expose a batch endpoint (GetByIdsAsync taking a list, not an endpoint meant for a single id called in a loop), and with that the network cost goes from scaling with the number of orders to a fixed two calls. This composition works well when the filter on the other service doesn't narrow the set much -we're still pulling almost every pending order- and when both data sets are small enough to live in memory for the duration of the request. It stops working when the filter is highly selective and the pending-orders set is large: at that point we're fetching and discarding most of the rows at the composition layer, which is exactly the work an index on a shared database would have done for free.

A local materialized copy: when selectivity demands it

For that second case -frequent queries with a selective filter on an attribute that changes rarely in the other service- the alternative we ended up using is keeping a local, read-only copy of whatever we need to filter on. It isn't a Postgres materialized view in the strict sense, since it spans separate databases; it's the same principle applied through events: the Orders service keeps a thin table with each customer's discount tier, kept current by listening to the events the Customers service publishes when that tier changes.

public class CustomerDiscountTierChangedHandler : IEventHandler<CustomerDiscountTierChanged>
{
    private readonly OrdersDbContext _db;

    public async Task HandleAsync(CustomerDiscountTierChanged evt)
    {
        var cached = await _db.CustomersCache
            .FirstOrDefaultAsync(c => c.CustomerId == evt.CustomerId);

        if (cached is null)
        {
            _db.CustomersCache.Add(new CustomerCache
            {
                CustomerId = evt.CustomerId,
                DiscountTier = evt.NewDiscountTier
            });
        }
        else
        {
            cached.DiscountTier = evt.NewDiscountTier;
        }

        await _db.SaveChangesAsync();
    }
}

With this local table, the original query is a one-line JOIN again, now against CustomersCache in the same Orders database:

var orders = await ordersDb.Orders
    .Join(ordersDb.CustomersCache, o => o.CustomerId, c => c.CustomerId, (o, c) => new { o, c })
    .Where(x => x.o.Status == OrderStatus.Pending && x.c.DiscountTier == "Gold")
    .Select(x => new OrderWithCustomer(x.o, x.c))
    .ToListAsync();

The price we pay to get that speed back is eventual consistency: between the moment a customer moves up a tier and the moment that event arrives and gets processed, the local copy is stale. For an internal listing filter, that window is usually fine. For a decision that touches money at the exact moment of the request -applying the discount at checkout, say- it isn't, and the right answer there is neither of the two patterns above: it's a direct synchronous call to the Customers service for that one piece of data, accepting the latency and availability coupling only at that critical point, not across the whole listing.

The signs that the split happened too soon

After working through enough of these cases, we learned to recognize when the problem isn't implementation but a service boundary drawn in the wrong place. The clearest sign is when the same business use case needs to read and write, within the same logical transaction, attributes that live in two different services and that must stay consistent with each other with no acceptable staleness window. If that happens once in the system, a single synchronous call like the checkout example handles it. If it keeps happening for the same pair of entities, that's a sign Orders and Customers -or whichever two services sit in that spot- should never have been split along that axis: the service boundary doesn't match the transactional consistency boundary the business actually needs.

The second sign is the number of local cache tables a service ends up accumulating just to filter and sort. When a service ends up holding copies of attributes from three or four other services, it has stopped owning a bounded domain: it's rebuilding a shared database through events, with extra steps and more propagation latency than the original ever had. At that point, the question isn't which composition pattern to use, but whether those services should merge back into one with a shared schema for that subdomain.

What we check before signing off on a database split

Before accepting database-per-service between two services, we identify which business queries cross that boundary today and classify each one: if the filter isn't very selective, gateway composition with batch endpoints is enough. If it's selective and eventual consistency is tolerable for that use case, we build a local copy through events, explicitly accepting the staleness window instead of ignoring it. If consistency has to be immediate, we isolate that one read as a direct synchronous call, without trying to solve it with a cache. And if we find that several critical queries between the same pair of services keep demanding that third option, we take it as the sign that the service cut, not the query, is what needs rethinking.