System Design (HLD)Database Decomposition

Database Decomposition

LevelAdvanced

Duration90 mins

TopicDatabase Decomposition

4 / 5

Handling Joins Across Services

When You Can't Just JOIN Anymore

With a shared database, fetching an order with user details and product information was trivial—a single SQL query with a few JOINs. In a decomposed architecture with Database per Service, that simple query becomes impossible. The data lives in three separate databases owned by three different services.

This is not a bug—it's a feature. The inability to JOIN across services is the very mechanism that provides service independence. But it creates a real challenge: how do you assemble data that spans multiple services for a single response?

This page explores the patterns and techniques for handling cross-service data queries without sacrificing the benefits of database decomposition.

What You Will Learn

This page provides comprehensive coverage of cross-service data patterns. You'll learn API Composition for synchronous data aggregation, strategic data denormalization and replication, CQRS for query-optimized read models, materialized views for pre-computed data, and GraphQL Federation for unified query interfaces. Each pattern has specific tradeoffs suitable for different scenarios.

Understanding the Cross-Service Query Problem

Let's make the problem concrete with a typical e-commerce example. Before decomposition, you had a simple query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Display order history page: one query, all data
SELECT 
  o.id AS order_id,
  o.created_at AS order_date,
  o.status,
  o.total,
  u.name AS customer_name,
  u.email AS customer_email,
  p.name AS product_name,
  p.image_url,
  oi.quantity,
  oi.price_at_purchase
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = '12345'
ORDER BY o.created_at DESC
LIMIT 20;
 
-- Result: Complete order history with user and product details
-- Execution: ~50ms in a well-indexed database

After decomposition, these tables live in different databases owned by different services:

User Service Database: users table
Order Service Database: orders, order_items tables
Product Service Database: products table

The same query now requires data from three services. Simply running the SQL query is impossible—there's no single database containing all this data.

The Naive Approach (and why it fails):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// DON'T DO THIS - inefficient and slow
async function getOrderHistory(userId: string) {
  // Get user details
  const user = await userService.getUser(userId);
  
  // Get orders
  const orders = await orderService.getOrdersByUser(userId);
  
  // For each order, get each product (N+1 problem!)
  for (const order of orders) {
    for (const item of order.items) {
      item.product = await productService.getProduct(item.productId);
    }
  }
  
  return { user, orders };
}
 
// Problems:
// 1. Sequential calls - slow (each await waits for previous)
// 2. N+1 query pattern - 1 user + N orders + M product calls
// 3. For 20 orders with 3 items each, that's 1 + 1 + 60 = 62 API calls!
// 4. Network latency dominates: 62 calls × 20ms = 1.24 seconds minimum

Performance Reality Check

Cross-service queries will never be as fast as a single database JOIN. The goal is to minimize the performance gap through smart patterns while maintaining the benefits of service independence. Accept that some latency increase is the price of architectural flexibility.

API Composition Pattern

API Composition is the most straightforward pattern for cross-service queries. An API Composer (or Aggregator) service collects data from multiple services and combines them into a unified response.

Architecture:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
┌─────────────────────────────────────────────────────────────────────┐
│                            Client                                    │
│                               │                                      │
│                               ▼                                      │
│                      ┌───────────────┐                               │
│                      │ API Gateway / │                               │
│                      │ Composer      │                               │
│                      └───────┬───────┘                               │
│                              │                                       │
│           ┌──────────────────┼──────────────────┐                   │
│           │                  │                  │                   │
│           ▼                  ▼                  ▼                   │
│    ┌────────────┐    ┌────────────┐    ┌────────────┐              │
│    │   User     │    │   Order    │    │  Product   │              │
│    │  Service   │    │  Service   │    │  Service   │              │
│    └─────┬──────┘    └─────┬──────┘    └─────┬──────┘              │
│          │                 │                 │                      │
│          ▼                 ▼                 ▼                      │
│    ┌────────────┐    ┌────────────┐    ┌────────────┐              │
│    │  User DB   │    │ Order DB   │    │ Product DB │              │
│    └────────────┘    └────────────┘    └────────────┘              │
└─────────────────────────────────────────────────────────────────────┘
 
1. Client requests order history
2. Composer calls User, Order, and Product services in parallel
3. Composer joins the responses in memory
4. Composer returns unified response to client

Optimized Implementation:

The key to performant API composition is parallelization and batching:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
class OrderHistoryComposer {
  async getOrderHistory(userId: string): Promise<OrderHistoryResponse> {
    // Step 1: Fetch orders and user in parallel
    const [orders, user] = await Promise.all([
      this.orderService.getOrdersByUser(userId),
      this.userService.getUser(userId),
    ]);
    
    // Step 2: Collect all unique product IDs from all orders
    const productIds = new Set<string>();
    for (const order of orders) {
      for (const item of order.items) {
        productIds.add(item.productId);
      }
    }
    
    // Step 3: Batch fetch all products in a single call
    const products = await this.productService.getProducts(
      Array.from(productIds)
    );
    
    // Build lookup map for O(1) access
    const productMap = new Map(products.map(p => [p.id, p]));
    
    // Step 4: Compose the response
    const enrichedOrders = orders.map(order => ({
      ...order,
      items: order.items.map(item => ({
        ...item,
        product: productMap.get(item.productId),
      })),
    }));
    
    return {
      user: { name: user.name, email: user.email },
      orders: enrichedOrders,
    };
  }
}
 
// Performance improvement:
// Before: 62 sequential calls = 62 × 20ms = 1,240ms
// After: 3 parallel calls = max(40ms, 30ms, 50ms) = ~50ms + composition time
// Result: ~25x faster

Best Practices for API Composition:

API Composition Best Practices

•Parallelize independent calls — Use Promise.all() or equivalent for requests that don't depend on each other.
•Batch related calls — Instead of fetching products one by one, fetch all needed products in a single batch call.
•Design batch APIs — Services should expose batch endpoints: GET /products?ids=1,2,3,4,5 not just GET /products/:id.
•Handle partial failures — If one service is down, decide whether to fail the entire request or return partial data.
•Add caching layers — Cache frequently accessed data (like product details) to reduce service calls.
•Set aggressive timeouts — Don't let one slow service hurt the entire response. Fail fast with fallbacks.

Where to Put the Composer

The composer can live in the API Gateway (BFF pattern), in a dedicated aggregation service, or in the service that 'owns' the primary entity (e.g., Order Service composes order details). Choose based on team ownership and performance requirements.

Strategic Data Denormalization

Instead of fetching data from other services at query time, denormalization stores copies of needed data locally within the consuming service. This trades storage space and write complexity for faster reads.

The Denormalization Tradeoff:

Benefits of Denormalization

•Single database query for reads
•No cross-service latency
•Works when other services are down
•Predictable, consistent performance
•Simpler read logic

Costs of Denormalization

•Data duplication across services
•Write complexity to keep copies in sync
•Eventual consistency (stale data possible)
•More storage usage
•Complexity in handling updates

Example: Order Service Denormalizing User and Product Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
// Order Service stores copies of data it needs from other services
interface Order {
  id: string;
  createdAt: Date;
  status: OrderStatus;
  
  // Denormalized from User Service (copied at order creation)
  userId: string;
  customerName: string;        // Copied, not fetched
  customerEmail: string;       // Copied, not fetched
  shippingAddress: Address;    // Snapshot at order time
  
  items: OrderItem[];
}
 
interface OrderItem {
  productId: string;
  
  // Denormalized from Product Service (copied at order creation)
  productName: string;         // Copied, not fetched
  productImageUrl: string;     // Copied, not fetched
  priceAtPurchase: Money;      // Snapshot at order time
  
  quantity: number;
}
 
// Order history query now needs only the Order database
async function getOrderHistory(userId: string): Promise<Order[]> {
  return orderDb.orders.findMany({
    where: { userId },
    orderBy: { createdAt: 'desc' },
    take: 20,
  });
  // No API calls needed - all data is local!
}

When to Denormalize:

Denormalization makes sense when:

The data is snapshots — Order addresses, prices at purchase time, etc. These should reflect the state at the time of the transaction, not current state.
The data changes rarely — Product names and images change infrequently. Caching with occasional updates is practical.
Cross-service queries are very frequent — If 90% of your traffic needs this data, amortizing the sync cost over many reads makes sense.
The source service may be unavailable — If orders must be displayable even when User Service is down.

Keeping Denormalized Data in Sync:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// User Service publishes events when user data changes
class UserService {
  async updateUserProfile(userId: string, updates: UserUpdate) {
    const user = await this.userDb.users.update(userId, updates);
    
    // Publish event for interested services
    await this.eventBus.publish('user.profile_updated', {
      userId: user.id,
      name: user.name,
      email: user.email,
      updatedAt: new Date(),
    });
    
    return user;
  }
}
 
// Order Service subscribes and updates its denormalized copies
class OrderServiceUserSubscriber {
  @Subscribe('user.profile_updated')
  async handleUserProfileUpdated(event: UserProfileUpdatedEvent) {
    // Update all orders with this user's info
    // (or decide updates only apply to future orders)
    
    await this.orderDb.orders.updateMany({
      where: { userId: event.userId },
      data: {
        customerName: event.name,
        customerEmail: event.email,
        customerInfoUpdatedAt: event.updatedAt,
      },
    });
    
    this.logger.info(`Updated denormalized user data for user ${event.userId}`);
  }
}

Snapshot vs. Live Data

For orders, you often want snapshots—the data as it was at the time of the order. The customer's address might have changed since they placed the order, but the shipping label should show the old address. Design carefully: which fields should be snapshots (never update) vs. sync'd (update on changes)?

CQRS: Command Query Responsibility Segregation

CQRS takes denormalization further by completely separating the write model from the read model. Write operations go to the authoritative service databases, while queries are served from dedicated read models optimized for specific query patterns.

The CQRS Architecture:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
┌────────────────────────────────────────────────────────────────────────┐
│                                                                        │
│   WRITE SIDE (Commands)              READ SIDE (Queries)              │
│   ═══════════════════════            ════════════════════             │
│                                                                        │
│   Client                             Client                           │
│     │                                  │                              │
│     ▼                                  ▼                              │
│   Create Order                       Get Order History                │
│     │                                  │                              │
│     ▼                                  │                              │
│   ┌─────────────┐                      │                              │
│   │ Order       │                      │                              │
│   │ Service     │─────Event────────────┴──────────┐                  │
│   └─────────────┘                                 │                  │
│         │                                         ▼                  │
│         ▼                               ┌─────────────────────┐      │
│   ┌─────────────┐                       │  Read Model Builder │      │
│   │ Order DB    │                       │  (Event Processor)  │      │
│   │ (Write)     │                       └──────────┬──────────┘      │
│   └─────────────┘                                  │                 │
│                                                    ▼                 │
│   ┌─────────────┐ ───Event────────────────► ┌─────────────┐          │
│   │ User        │                           │ Order       │          │
│   │ Service     │                           │ History     │          │
│   └─────────────┘                           │ Read Model  │          │
│                                             └─────────────┘          │
│   ┌─────────────┐ ───Event───────────────────────┘                   │
│   │ Product     │                                                    │
│   │ Service     │                                                    │
│   └─────────────┘                                                    │
│                                                                       │
└────────────────────────────────────────────────────────────────────────┘
 
Write Side: Services own their data, publish events
Read Side: Dedicated read models consume events, build query-optimized views

Building a CQRS Read Model:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
// Read model optimized for order history queries
interface OrderHistoryReadModel {
  orderId: string;
  orderCreatedAt: Date;
  orderStatus: string;
  orderTotal: Money;
  customerName: string;
  customerEmail: string;
  items: {
    productId: string;
    productName: string;
    productImageUrl: string;
    quantity: number;
    price: Money;
  }[];
}
 
// Event processor builds and maintains the read model
class OrderHistoryReadModelBuilder {
  @Subscribe('order.created')
  async handleOrderCreated(event: OrderCreatedEvent) {
    await this.readModelDb.orderHistory.create({
      orderId: event.orderId,
      orderCreatedAt: event.createdAt,
      orderStatus: 'pending',
      orderTotal: event.total,
      customerId: event.userId,
      // Will be enriched when we receive user event
      customerName: null,
      customerEmail: null,
      items: event.items,
    });
    
    // Request current user data if we don't have it cached
    await this.requestUserEnrichment(event.userId);
  }
 
  @Subscribe('user.profile_updated')
  async handleUserProfileUpdated(event: UserProfileUpdatedEvent) {
    // Update all read models for this user
    await this.readModelDb.orderHistory.updateMany({
      where: { customerId: event.userId },
      data: {
        customerName: event.name,
        customerEmail: event.email,
      },
    });
  }
 
  @Subscribe('product.details_updated')
  async handleProductUpdated(event: ProductUpdatedEvent) {
    // Update product details in order items
    await this.readModelDb.orderHistory.updateItemsByProductId(
      event.productId,
      { productName: event.name, productImageUrl: event.imageUrl }
    );
  }
}
 
// Query handler serves directly from read model
class OrderHistoryQueryHandler {
  async getOrderHistory(userId: string): Promise<OrderHistoryReadModel[]> {
    return this.readModelDb.orderHistory.findMany({
      where: { customerId: userId },
      orderBy: { orderCreatedAt: 'desc' },
      take: 20,
    });
    // Single query, all data pre-joined, no API calls
  }
}

CQRS Tradeoffs
Aspect	Benefit	Cost
Query performance	Optimized for specific query patterns	Must build and maintain read models
Scalability	Read and write scale independently	More moving parts to operate
Flexibility	Each read model optimized differently	Multiple models = more storage
Consistency	N/A	Eventual consistency between write and read
Development	Query handlers are simple	Event processors add complexity

When to Use CQRS

CQRS shines when you have: (1) significant read/write ratio disparity, (2) complex queries spanning multiple domains, (3) different scaling needs for reads vs writes, or (4) the need for multiple query-optimized views of the same data. Don't use CQRS for simple CRUD applications—the overhead isn't worth it.

Materialized Views for Cross-Service Data

Materialized views are pre-computed query results stored as tables. In a microservices context, you can create materialized views that aggregate data from multiple services for efficient querying.

Approaches to Building Materialized Views:

Materialized View Strategies

•Event-Driven Updates — Views are updated incrementally as events arrive from source services. Near real-time, but complex to implement.
•Periodic Batch Refresh — A scheduled job queries all source services and rebuilds the view. Simpler but introduces staleness.
•Hybrid Approach — Batch rebuild provides baseline; events provide incremental updates for freshness.

Implementation: Event-Driven Materialized View

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
// Materialized view for dashboard: orders with revenue by product category
interface ProductCategoryRevenueView {
  categoryId: string;
  categoryName: string;
  totalOrders: number;
  totalRevenue: Money;
  lastOrderAt: Date;
}
 
class ProductCategoryRevenueBuilder {
  // Initialize view from batch data
  async rebuildView() {
    // Fetch all data from services (batch operation)
    const [orders, products, categories] = await Promise.all([
      this.orderService.getAllOrders({ status: 'completed' }),
      this.productService.getAllProducts(),
      this.productService.getAllCategories(),
    ]);
 
    // Build aggregations
    const aggregates = new Map<string, ProductCategoryRevenueView>();
    
    for (const order of orders) {
      for (const item of order.items) {
        const product = products.find(p => p.id === item.productId);
        const category = categories.find(c => c.id === product?.categoryId);
        
        if (!category) continue;
        
        if (!aggregates.has(category.id)) {
          aggregates.set(category.id, {
            categoryId: category.id,
            categoryName: category.name,
            totalOrders: 0,
            totalRevenue: Money.zero(),
            lastOrderAt: new Date(0),
          });
        }
        
        const agg = aggregates.get(category.id)!;
        agg.totalOrders++;
        agg.totalRevenue = agg.totalRevenue.add(item.total);
        if (order.createdAt > agg.lastOrderAt) {
          agg.lastOrderAt = order.createdAt;
        }
      }
    }
 
    // Store materialized view
    await this.viewDb.productCategoryRevenue.truncate();
    await this.viewDb.productCategoryRevenue.insertMany(
      Array.from(aggregates.values())
    );
  }
 
  // Incremental update from events
  @Subscribe('order.completed')
  async handleOrderCompleted(event: OrderCompletedEvent) {
    for (const item of event.items) {
      // Get category for this product
      const product = await this.productService.getProduct(item.productId);
      
      // Increment aggregates
      await this.viewDb.productCategoryRevenue.upsert({
        where: { categoryId: product.categoryId },
        update: {
          totalOrders: { increment: 1 },
          totalRevenue: { increment: item.total },
          lastOrderAt: event.completedAt,
        },
        create: {
          categoryId: product.categoryId,
          categoryName: product.categoryName,
          totalOrders: 1,
          totalRevenue: item.total,
          lastOrderAt: event.completedAt,
        },
      });
    }
  }
}

Periodic Refresh for Simplicity:

For less critical views, a simple periodic refresh may be sufficient:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Scheduled job runs every 5 minutes
@Cron('*/5 * * * *')
async refreshDashboardView() {
  const startTime = Date.now();
  
  try {
    await this.viewBuilder.rebuildView();
    
    this.metrics.gauge('view_age_seconds', 0);
    this.logger.info(`View refreshed in ${Date.now() - startTime}ms`);
  } catch (error) {
    this.logger.error('View refresh failed', { error });
    // View remains stale but available with previous data
  }
}
 
// Queries hit the materialized view directly
async getDashboardData(): Promise<DashboardData> {
  const [categoryRevenue, viewMeta] = await Promise.all([
    this.viewDb.productCategoryRevenue.findMany(),
    this.viewDb.viewMetadata.findOne({ name: 'productCategoryRevenue' }),
  ]);
  
  return {
    categoryRevenue,
    dataAsOf: viewMeta.lastRefreshAt,  // Let client know data freshness
  };
}

View Freshness Communication

Always communicate data freshness to consumers. Include a 'dataAsOf' timestamp in responses so clients know how stale the data might be. For dashboards and analytics, users typically accept data that's minutes old—but they should know.

GraphQL Federation

GraphQL Federation provides a unified query interface over multiple services, allowing clients to write queries that span services while the federation layer handles the composition automatically.

How Federation Works:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
┌─────────────────────────────────────────────────────────────────────┐
│                            Client                                    │
│                               │                                      │
│    query {                    │                                      │
│      order(id: "123") {       │                                      │
│        id                     │                                      │
│        total                  │                                      │
│        customer {             ◄── Single query spanning services    │
│          name                 │                                      │
│          email                │                                      │
│        }                      │                                      │
│        items {                │                                      │
│          product {            │                                      │
│            name               │                                      │
│            imageUrl           │                                      │
│          }                    │                                      │
│        }                      │                                      │
│      }                        │                                      │
│    }                          │                                      │
│                               │                                      │
│                               ▼                                      │
│                    ┌─────────────────────┐                          │
│                    │  Gateway (Router)   │                          │
│                    │  Apollo Federation  │                          │
│                    └─────────┬───────────┘                          │
│                              │                                       │
│         ┌────────────────────┼────────────────────┐                 │
│         │                    │                    │                 │
│         ▼                    ▼                    ▼                 │
│   ┌───────────┐       ┌───────────┐       ┌───────────┐            │
│   │  Orders   │       │   Users   │       │ Products  │            │
│   │ Subgraph  │       │ Subgraph  │       │ Subgraph  │            │
│   └───────────┘       └───────────┘       └───────────┘            │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Implementing Federation with Entity References:

Each service defines its own types and extends other services' types:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# Order Subgraph (Order Service)
type Order @key(fields: "id") {
  id: ID!
  createdAt: DateTime!
  status: OrderStatus!
  total: Money!
  customerId: ID!
  items: [OrderItem!]!
}
 
type OrderItem {
  productId: ID!
  quantity: Int!
  priceAtPurchase: Money!
  product: Product! # Reference to Product from another service
}
 
# Extend User type to add orders
extend type User @key(fields: "id") {
  id: ID! @external
  orders: [Order!]!  # Resolved by Order Service
}
 
# User Subgraph (User Service)
type User @key(fields: "id") {
  id: ID!
  name: String!
  email: String!
}
 
# Product Subgraph (Product Service)
type Product @key(fields: "id") {
  id: ID!
  name: String!
  imageUrl: String!
  price: Money!
  category: Category!
}
 
# Extend OrderItem to resolve product
extend type OrderItem {
  product: Product! @requires(fields: "productId")
}

Resolver Implementation:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Order Service resolvers
const resolvers = {
  Query: {
    order: (_, { id }) => orderDb.orders.findById(id),
    orders: (_, { userId }) => orderDb.orders.findByUser(userId),
  },
  
  Order: {
    // Reference resolver - how to load Order by ID from external reference
    __resolveReference: (reference) => orderDb.orders.findById(reference.id),
  },
  
  // Extend User type with orders field
  User: {
    orders: (user) => orderDb.orders.findByUser(user.id),
  },
};
 
// Product Service resolvers
const resolvers = {
  Product: {
    // Reference resolver - federation calls this to resolve Product references
    __resolveReference: async (reference, context) => {
      // Batched: DataLoader collects all productIds and fetches in one query
      return context.dataloaders.products.load(reference.id);
    },
  },
};

DataLoader for N+1 Prevention

GraphQL Federation naturally leads to N+1 problems when resolving references. Use DataLoader to batch and deduplicate reference resolutions. When the gateway needs to resolve 50 product references, DataLoader collects them and makes a single batch request to the Product Service.

GraphQL Federation Tradeoffs
Aspect	Benefit	Cost
Developer experience	Single unified schema; intuitive queries	Requires GraphQL expertise
Client flexibility	Clients request exactly what they need	Query complexity can spiral
Service independence	Services own their types and extensions	Schema coordination needed
Performance	Gateway can optimize query execution	Multiple service roundtrips possible
Caching	CDN/cache-friendly with persisted queries	Cache invalidation complex

Choosing the Right Pattern

Different patterns suit different scenarios. Here's a decision framework for choosing the right approach to cross-service queries:

Pattern Selection Matrix
Scenario	Recommended Pattern	Rationale
Simple aggregation, moderate traffic	API Composition	Straightforward, sufficient performance
High read volume, stable source data	Denormalization	Fast reads, tolerable sync overhead
Complex queries, heavy analytics	CQRS Read Models	Query-optimized, scales independently
Dashboard/reporting needs	Materialized Views	Pre-computed, predictable performance
Flexible client-driven queries	GraphQL Federation	Client flexibility, unified interface
Snapshot data (historical records)	Copy at Write Time	No sync needed, semantically correct

Key Decision Factors:

Questions to Guide Pattern Selection

•How fresh must the data be? Real-time needs favor API Composition. Tolerance for staleness enables caching, denormalization, or materialized views.
•How complex is the query? Simple lookups suit API Composition. Complex aggregations need CQRS or materialized views.
•What's the read/write ratio? Heavy reads justify the investment in read-optimized patterns like CQRS.
•Must queries work when services are down? Denormalization and local caches provide resilience against service failures.
•How many query patterns exist? Few patterns = specific materialized views. Many patterns = GraphQL Federation's flexibility.
•What's the team's expertise? Don't adopt GraphQL Federation if no one knows GraphQL. Start with simpler patterns.

Start Simple, Evolve as Needed

Begin with API Composition—it's the simplest pattern and often sufficient. Add caching if performance is insufficient. Move to denormalization or CQRS only when you have evidence that simpler patterns can't meet your requirements. Over-engineering cross-service queries is a common mistake.

Performance Optimization Techniques

Regardless of which pattern you choose, several optimization techniques apply universally to cross-service queries.

Caching at Multiple Layers:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
class OptimizedProductClient {
  constructor(
    private httpClient: HttpClient,
    private cache: CacheService,
    private localCache: Map<string, Product>,
  ) {}
 
  async getProduct(productId: string): Promise<Product> {
    // Layer 1: In-memory cache (per-request or short TTL)
    if (this.localCache.has(productId)) {
      return this.localCache.get(productId)!;
    }
 
    // Layer 2: Distributed cache (Redis)
    const cached = await this.cache.get(`product:${productId}`);
    if (cached) {
      this.localCache.set(productId, cached);
      return cached;
    }
 
    // Layer 3: Service call
    const product = await this.httpClient.get<Product>(
      `/products/${productId}`
    );
 
    // Populate caches
    await this.cache.set(`product:${productId}`, product, { ttl: 300 });
    this.localCache.set(productId, product);
 
    return product;
  }
 
  async getProducts(productIds: string[]): Promise<Product[]> {
    const results: Product[] = [];
    const uncachedIds: string[] = [];
 
    // Check caches first
    for (const id of productIds) {
      const cached = this.localCache.get(id) 
                     || await this.cache.get(`product:${id}`);
      if (cached) {
        results.push(cached);
      } else {
        uncachedIds.push(id);
      }
    }
 
    // Batch fetch uncached
    if (uncachedIds.length > 0) {
      const fetched = await this.httpClient.get<Product[]>(
        `/products?ids=${uncachedIds.join(',')}`
      );
      
      for (const product of fetched) {
        await this.cache.set(`product:${product.id}`, product, { ttl: 300 });
        this.localCache.set(product.id, product);
        results.push(product);
      }
    }
 
    return results;
  }
}

Request Coalescing:

When multiple concurrent requests need the same data, coalesce them into a single service call:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import DataLoader from 'dataloader';
 
// DataLoader batches and deduplicates requests within a tick
const productLoader = new DataLoader<string, Product>(
  async (productIds) => {
    // This function is called once with all requested IDs
    const products = await productService.getProducts(Array.from(productIds));
    
    // Return in same order as requested IDs
    const productMap = new Map(products.map(p => [p.id, p]));
    return productIds.map(id => productMap.get(id) || null);
  },
  { cache: true }  // Caches within request context
);
 
// Multiple concurrent calls...
await Promise.all([
  productLoader.load('product-1'),  // Batched together
  productLoader.load('product-2'),  // into single request
  productLoader.load('product-1'),  // Deduplicated - same as first
]);
// Result: One request to productService.getProducts(['product-1', 'product-2'])

Timeout and Fallback Strategies:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
async function getOrderWithFallbacks(orderId: string): Promise<EnrichedOrder> {
  const order = await orderService.getOrder(orderId);
  
  // Fetch enrichment data with timeouts and fallbacks
  const [user, products] = await Promise.all([
    withTimeout(
      userService.getUser(order.userId),
      { timeoutMs: 500, fallback: { name: 'Unknown User', email: null } }
    ),
    withTimeout(
      productService.getProducts(order.items.map(i => i.productId)),
      { timeoutMs: 500, fallback: [] }
    ),
  ]);
 
  // Compose response with whatever data we got
  return {
    ...order,
    customer: user,
    items: order.items.map(item => ({
      ...item,
      product: products.find(p => p.id === item.productId) || {
        name: `Product ${item.productId}`,
        imageUrl: '/placeholder.png',
      },
    })),
    _partial: user.email === null || products.length === 0,  // Flag if degraded
  };
}

Graceful Degradation

Cross-service queries should degrade gracefully. If the Product Service is slow, return orders with placeholder product data rather than failing entirely. Let the client know data is partial so it can display appropriately.

Anti-Patterns to Avoid

While solving the cross-service query problem, teams often fall into patterns that undermine the benefits of database decomposition.

Anti-Pattern: The Super-Service

•Description: A single service becomes the 'composer' for everything, fetching data from all other services.
•Problem: This super-service becomes a bottleneck and coupling point. It must know about all services, changes frequently, and becomes a team dependency.
•Solution: Distribute composition responsibility. Each service can compose data for its own primary use cases.

Anti-Pattern: Recreating the JOIN

•Description: Replicating all data everywhere so every service can 'JOIN' locally.
•Problem: Data duplication explodes, sync mechanisms become complex, and storage costs grow. You've recreated the shared database with extra steps.
•Solution: Denormalize strategically. Only replicate data that's frequently needed and changes rarely.

Anti-Pattern: Synchronous Chains

•Description: Service A calls Service B, which calls Service C, which calls Service D, all synchronously.
•Problem: Latency compounds (A's latency ≥ B + C + D). Any service in the chain failing fails the entire request.
•Solution: Parallelize where possible. Use caching. Consider async patterns. Flatten call hierarchies.

Anti-Pattern: No Ownership of Composed Views

•Description: Multiple services each have their own version of the 'order summary' view, built differently.
•Problem: Inconsistent data across different parts of the application. Maintenance burden multiplies.
•Solution: Assign clear ownership. One team owns the order summary view/API. Others consume it.

The Hidden Database

If your read models or materialized views grow to contain most of the data from most services, you've recreated a shared database under a different name. Be vigilant about creeping scope. Each read model should serve specific, bounded query needs.

Summary: Handling Joins Across Services

Cross-service queries are an inherent challenge in Database per Service architecture. Multiple patterns exist to address this challenge, each with specific tradeoffs suitable for different scenarios.

Key Takeaways

•The inability to JOIN is intentional — It enforces service independence. Embrace it as a feature, not a limitation.
•API Composition is the starting point — Parallelize and batch calls. Often sufficient for moderate traffic.
•Denormalization trades complexity for speed — Store copies of needed data locally. Use events to keep in sync.
•CQRS separates read and write concerns — Build query-optimized read models from domain events.
•Materialized views pre-compute aggregations — Ideal for dashboards and analytics with tolerance for staleness.
•GraphQL Federation unifies query interfaces — Provides client flexibility at the cost of infrastructure complexity.
•Start simple and evolve — Begin with API Composition. Add complexity only when evidence shows it's needed.
•Optimize with caching and batching — Apply caching at multiple layers. Use DataLoader to coalesce requests.
•Avoid anti-patterns — Don't create super-services, don't replicate everything everywhere, don't build synchronous chains.

What's next:

The final page in this module addresses Eventual Consistency—the reality that with separate databases, data across services cannot be immediately consistent. We'll explore how to design systems that embrace eventual consistency, communicate it to users, and handle the edge cases it introduces.

Page Complete

You now have a comprehensive toolkit for handling cross-service queries. From simple API composition to sophisticated CQRS patterns, you can choose the right approach for your specific requirements while avoiding common pitfalls.

4 / 5

Loading learning content...

System Design (HLD)Database Decomposition

Database Decomposition

LevelAdvanced

Duration90 mins

TopicDatabase Decomposition

4 / 5

Handling Joins Across Services

When You Can't Just JOIN Anymore

This page explores the patterns and techniques for handling cross-service data queries without sacrificing the benefits of database decomposition.

What You Will Learn

Understanding the Cross-Service Query Problem

Let's make the problem concrete with a typical e-commerce example. Before decomposition, you had a simple query:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Display order history page: one query, all data
SELECT 
  o.id AS order_id,
  o.created_at AS order_date,
  o.status,
  o.total,
  u.name AS customer_name,
  u.email AS customer_email,
  p.name AS product_name,
  p.image_url,
  oi.quantity,
  oi.price_at_purchase
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.user_id = '12345'
ORDER BY o.created_at DESC
LIMIT 20;
 
-- Result: Complete order history with user and product details
-- Execution: ~50ms in a well-indexed database

After decomposition, these tables live in different databases owned by different services:

User Service Database: users table
Order Service Database: orders, order_items tables
Product Service Database: products table

The same query now requires data from three services. Simply running the SQL query is impossible—there's no single database containing all this data.

The Naive Approach (and why it fails):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
// DON'T DO THIS - inefficient and slow
async function getOrderHistory(userId: string) {
  // Get user details
  const user = await userService.getUser(userId);
  
  // Get orders
  const orders = await orderService.getOrdersByUser(userId);
  
  // For each order, get each product (N+1 problem!)
  for (const order of orders) {
    for (const item of order.items) {
      item.product = await productService.getProduct(item.productId);
    }
  }
  
  return { user, orders };
}
 
// Problems:
// 1. Sequential calls - slow (each await waits for previous)
// 2. N+1 query pattern - 1 user + N orders + M product calls
// 3. For 20 orders with 3 items each, that's 1 + 1 + 60 = 62 API calls!
// 4. Network latency dominates: 62 calls × 20ms = 1.24 seconds minimum

Performance Reality Check

API Composition Pattern

Architecture:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
┌─────────────────────────────────────────────────────────────────────┐
│                            Client                                    │
│                               │                                      │
│                               ▼                                      │
│                      ┌───────────────┐                               │
│                      │ API Gateway / │                               │
│                      │ Composer      │                               │
│                      └───────┬───────┘                               │
│                              │                                       │
│           ┌──────────────────┼──────────────────┐                   │
│           │                  │                  │                   │
│           ▼                  ▼                  ▼                   │
│    ┌────────────┐    ┌────────────┐    ┌────────────┐              │
│    │   User     │    │   Order    │    │  Product   │              │
│    │  Service   │    │  Service   │    │  Service   │              │
│    └─────┬──────┘    └─────┬──────┘    └─────┬──────┘              │
│          │                 │                 │                      │
│          ▼                 ▼                 ▼                      │
│    ┌────────────┐    ┌────────────┐    ┌────────────┐              │
│    │  User DB   │    │ Order DB   │    │ Product DB │              │
│    └────────────┘    └────────────┘    └────────────┘              │
└─────────────────────────────────────────────────────────────────────┘
 
1. Client requests order history
2. Composer calls User, Order, and Product services in parallel
3. Composer joins the responses in memory
4. Composer returns unified response to client

Optimized Implementation:

The key to performant API composition is parallelization and batching:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
class OrderHistoryComposer {
  async getOrderHistory(userId: string): Promise<OrderHistoryResponse> {
    // Step 1: Fetch orders and user in parallel
    const [orders, user] = await Promise.all([
      this.orderService.getOrdersByUser(userId),
      this.userService.getUser(userId),
    ]);
    
    // Step 2: Collect all unique product IDs from all orders
    const productIds = new Set<string>();
    for (const order of orders) {
      for (const item of order.items) {
        productIds.add(item.productId);
      }
    }
    
    // Step 3: Batch fetch all products in a single call
    const products = await this.productService.getProducts(
      Array.from(productIds)
    );
    
    // Build lookup map for O(1) access
    const productMap = new Map(products.map(p => [p.id, p]));
    
    // Step 4: Compose the response
    const enrichedOrders = orders.map(order => ({
      ...order,
      items: order.items.map(item => ({
        ...item,
        product: productMap.get(item.productId),
      })),
    }));
    
    return {
      user: { name: user.name, email: user.email },
      orders: enrichedOrders,
    };
  }
}
 
// Performance improvement:
// Before: 62 sequential calls = 62 × 20ms = 1,240ms
// After: 3 parallel calls = max(40ms, 30ms, 50ms) = ~50ms + composition time
// Result: ~25x faster

Best Practices for API Composition:

API Composition Best Practices

•Parallelize independent calls — Use Promise.all() or equivalent for requests that don't depend on each other.
•Batch related calls — Instead of fetching products one by one, fetch all needed products in a single batch call.
•Design batch APIs — Services should expose batch endpoints: GET /products?ids=1,2,3,4,5 not just GET /products/:id.
•Handle partial failures — If one service is down, decide whether to fail the entire request or return partial data.
•Add caching layers — Cache frequently accessed data (like product details) to reduce service calls.
•Set aggressive timeouts — Don't let one slow service hurt the entire response. Fail fast with fallbacks.

Where to Put the Composer

Strategic Data Denormalization

The Denormalization Tradeoff:

Benefits of Denormalization

•Single database query for reads
•No cross-service latency
•Works when other services are down
•Predictable, consistent performance
•Simpler read logic

Costs of Denormalization

•Data duplication across services
•Write complexity to keep copies in sync
•Eventual consistency (stale data possible)
•More storage usage
•Complexity in handling updates

Example: Order Service Denormalizing User and Product Data

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
// Order Service stores copies of data it needs from other services
interface Order {
  id: string;
  createdAt: Date;
  status: OrderStatus;
  
  // Denormalized from User Service (copied at order creation)
  userId: string;
  customerName: string;        // Copied, not fetched
  customerEmail: string;       // Copied, not fetched
  shippingAddress: Address;    // Snapshot at order time
  
  items: OrderItem[];
}
 
interface OrderItem {
  productId: string;
  
  // Denormalized from Product Service (copied at order creation)
  productName: string;         // Copied, not fetched
  productImageUrl: string;     // Copied, not fetched
  priceAtPurchase: Money;      // Snapshot at order time
  
  quantity: number;
}
 
// Order history query now needs only the Order database
async function getOrderHistory(userId: string): Promise<Order[]> {
  return orderDb.orders.findMany({
    where: { userId },
    orderBy: { createdAt: 'desc' },
    take: 20,
  });
  // No API calls needed - all data is local!
}

When to Denormalize:

Denormalization makes sense when:

The data is snapshots — Order addresses, prices at purchase time, etc. These should reflect the state at the time of the transaction, not current state.
The data changes rarely — Product names and images change infrequently. Caching with occasional updates is practical.
Cross-service queries are very frequent — If 90% of your traffic needs this data, amortizing the sync cost over many reads makes sense.
The source service may be unavailable — If orders must be displayable even when User Service is down.

Keeping Denormalized Data in Sync:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
// User Service publishes events when user data changes
class UserService {
  async updateUserProfile(userId: string, updates: UserUpdate) {
    const user = await this.userDb.users.update(userId, updates);
    
    // Publish event for interested services
    await this.eventBus.publish('user.profile_updated', {
      userId: user.id,
      name: user.name,
      email: user.email,
      updatedAt: new Date(),
    });
    
    return user;
  }
}
 
// Order Service subscribes and updates its denormalized copies
class OrderServiceUserSubscriber {
  @Subscribe('user.profile_updated')
  async handleUserProfileUpdated(event: UserProfileUpdatedEvent) {
    // Update all orders with this user's info
    // (or decide updates only apply to future orders)
    
    await this.orderDb.orders.updateMany({
      where: { userId: event.userId },
      data: {
        customerName: event.name,
        customerEmail: event.email,
        customerInfoUpdatedAt: event.updatedAt,
      },
    });
    
    this.logger.info(`Updated denormalized user data for user ${event.userId}`);
  }
}

Snapshot vs. Live Data

CQRS: Command Query Responsibility Segregation

The CQRS Architecture:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
┌────────────────────────────────────────────────────────────────────────┐
│                                                                        │
│   WRITE SIDE (Commands)              READ SIDE (Queries)              │
│   ═══════════════════════            ════════════════════             │
│                                                                        │
│   Client                             Client                           │
│     │                                  │                              │
│     ▼                                  ▼                              │
│   Create Order                       Get Order History                │
│     │                                  │                              │
│     ▼                                  │                              │
│   ┌─────────────┐                      │                              │
│   │ Order       │                      │                              │
│   │ Service     │─────Event────────────┴──────────┐                  │
│   └─────────────┘                                 │                  │
│         │                                         ▼                  │
│         ▼                               ┌─────────────────────┐      │
│   ┌─────────────┐                       │  Read Model Builder │      │
│   │ Order DB    │                       │  (Event Processor)  │      │
│   │ (Write)     │                       └──────────┬──────────┘      │
│   └─────────────┘                                  │                 │
│                                                    ▼                 │
│   ┌─────────────┐ ───Event────────────────► ┌─────────────┐          │
│   │ User        │                           │ Order       │          │
│   │ Service     │                           │ History     │          │
│   └─────────────┘                           │ Read Model  │          │
│                                             └─────────────┘          │
│   ┌─────────────┐ ───Event───────────────────────┘                   │
│   │ Product     │                                                    │
│   │ Service     │                                                    │
│   └─────────────┘                                                    │
│                                                                       │
└────────────────────────────────────────────────────────────────────────┘
 
Write Side: Services own their data, publish events
Read Side: Dedicated read models consume events, build query-optimized views

Building a CQRS Read Model:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
// Read model optimized for order history queries
interface OrderHistoryReadModel {
  orderId: string;
  orderCreatedAt: Date;
  orderStatus: string;
  orderTotal: Money;
  customerName: string;
  customerEmail: string;
  items: {
    productId: string;
    productName: string;
    productImageUrl: string;
    quantity: number;
    price: Money;
  }[];
}
 
// Event processor builds and maintains the read model
class OrderHistoryReadModelBuilder {
  @Subscribe('order.created')
  async handleOrderCreated(event: OrderCreatedEvent) {
    await this.readModelDb.orderHistory.create({
      orderId: event.orderId,
      orderCreatedAt: event.createdAt,
      orderStatus: 'pending',
      orderTotal: event.total,
      customerId: event.userId,
      // Will be enriched when we receive user event
      customerName: null,
      customerEmail: null,
      items: event.items,
    });
    
    // Request current user data if we don't have it cached
    await this.requestUserEnrichment(event.userId);
  }
 
  @Subscribe('user.profile_updated')
  async handleUserProfileUpdated(event: UserProfileUpdatedEvent) {
    // Update all read models for this user
    await this.readModelDb.orderHistory.updateMany({
      where: { customerId: event.userId },
      data: {
        customerName: event.name,
        customerEmail: event.email,
      },
    });
  }
 
  @Subscribe('product.details_updated')
  async handleProductUpdated(event: ProductUpdatedEvent) {
    // Update product details in order items
    await this.readModelDb.orderHistory.updateItemsByProductId(
      event.productId,
      { productName: event.name, productImageUrl: event.imageUrl }
    );
  }
}
 
// Query handler serves directly from read model
class OrderHistoryQueryHandler {
  async getOrderHistory(userId: string): Promise<OrderHistoryReadModel[]> {
    return this.readModelDb.orderHistory.findMany({
      where: { customerId: userId },
      orderBy: { orderCreatedAt: 'desc' },
      take: 20,
    });
    // Single query, all data pre-joined, no API calls
  }
}

CQRS Tradeoffs
Aspect	Benefit	Cost
Query performance	Optimized for specific query patterns	Must build and maintain read models
Scalability	Read and write scale independently	More moving parts to operate
Flexibility	Each read model optimized differently	Multiple models = more storage
Consistency	N/A	Eventual consistency between write and read
Development	Query handlers are simple	Event processors add complexity

When to Use CQRS

Materialized Views for Cross-Service Data

Approaches to Building Materialized Views:

Materialized View Strategies

•Event-Driven Updates — Views are updated incrementally as events arrive from source services. Near real-time, but complex to implement.
•Periodic Batch Refresh — A scheduled job queries all source services and rebuilds the view. Simpler but introduces staleness.
•Hybrid Approach — Batch rebuild provides baseline; events provide incremental updates for freshness.

Implementation: Event-Driven Materialized View

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
// Materialized view for dashboard: orders with revenue by product category
interface ProductCategoryRevenueView {
  categoryId: string;
  categoryName: string;
  totalOrders: number;
  totalRevenue: Money;
  lastOrderAt: Date;
}
 
class ProductCategoryRevenueBuilder {
  // Initialize view from batch data
  async rebuildView() {
    // Fetch all data from services (batch operation)
    const [orders, products, categories] = await Promise.all([
      this.orderService.getAllOrders({ status: 'completed' }),
      this.productService.getAllProducts(),
      this.productService.getAllCategories(),
    ]);
 
    // Build aggregations
    const aggregates = new Map<string, ProductCategoryRevenueView>();
    
    for (const order of orders) {
      for (const item of order.items) {
        const product = products.find(p => p.id === item.productId);
        const category = categories.find(c => c.id === product?.categoryId);
        
        if (!category) continue;
        
        if (!aggregates.has(category.id)) {
          aggregates.set(category.id, {
            categoryId: category.id,
            categoryName: category.name,
            totalOrders: 0,
            totalRevenue: Money.zero(),
            lastOrderAt: new Date(0),
          });
        }
        
        const agg = aggregates.get(category.id)!;
        agg.totalOrders++;
        agg.totalRevenue = agg.totalRevenue.add(item.total);
        if (order.createdAt > agg.lastOrderAt) {
          agg.lastOrderAt = order.createdAt;
        }
      }
    }
 
    // Store materialized view
    await this.viewDb.productCategoryRevenue.truncate();
    await this.viewDb.productCategoryRevenue.insertMany(
      Array.from(aggregates.values())
    );
  }
 
  // Incremental update from events
  @Subscribe('order.completed')
  async handleOrderCompleted(event: OrderCompletedEvent) {
    for (const item of event.items) {
      // Get category for this product
      const product = await this.productService.getProduct(item.productId);
      
      // Increment aggregates
      await this.viewDb.productCategoryRevenue.upsert({
        where: { categoryId: product.categoryId },
        update: {
          totalOrders: { increment: 1 },
          totalRevenue: { increment: item.total },
          lastOrderAt: event.completedAt,
        },
        create: {
          categoryId: product.categoryId,
          categoryName: product.categoryName,
          totalOrders: 1,
          totalRevenue: item.total,
          lastOrderAt: event.completedAt,
        },
      });
    }
  }
}

Periodic Refresh for Simplicity:

For less critical views, a simple periodic refresh may be sufficient:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Scheduled job runs every 5 minutes
@Cron('*/5 * * * *')
async refreshDashboardView() {
  const startTime = Date.now();
  
  try {
    await this.viewBuilder.rebuildView();
    
    this.metrics.gauge('view_age_seconds', 0);
    this.logger.info(`View refreshed in ${Date.now() - startTime}ms`);
  } catch (error) {
    this.logger.error('View refresh failed', { error });
    // View remains stale but available with previous data
  }
}
 
// Queries hit the materialized view directly
async getDashboardData(): Promise<DashboardData> {
  const [categoryRevenue, viewMeta] = await Promise.all([
    this.viewDb.productCategoryRevenue.findMany(),
    this.viewDb.viewMetadata.findOne({ name: 'productCategoryRevenue' }),
  ]);
  
  return {
    categoryRevenue,
    dataAsOf: viewMeta.lastRefreshAt,  // Let client know data freshness
  };
}

View Freshness Communication

GraphQL Federation

How Federation Works:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
┌─────────────────────────────────────────────────────────────────────┐
│                            Client                                    │
│                               │                                      │
│    query {                    │                                      │
│      order(id: "123") {       │                                      │
│        id                     │                                      │
│        total                  │                                      │
│        customer {             ◄── Single query spanning services    │
│          name                 │                                      │
│          email                │                                      │
│        }                      │                                      │
│        items {                │                                      │
│          product {            │                                      │
│            name               │                                      │
│            imageUrl           │                                      │
│          }                    │                                      │
│        }                      │                                      │
│      }                        │                                      │
│    }                          │                                      │
│                               │                                      │
│                               ▼                                      │
│                    ┌─────────────────────┐                          │
│                    │  Gateway (Router)   │                          │
│                    │  Apollo Federation  │                          │
│                    └─────────┬───────────┘                          │
│                              │                                       │
│         ┌────────────────────┼────────────────────┐                 │
│         │                    │                    │                 │
│         ▼                    ▼                    ▼                 │
│   ┌───────────┐       ┌───────────┐       ┌───────────┐            │
│   │  Orders   │       │   Users   │       │ Products  │            │
│   │ Subgraph  │       │ Subgraph  │       │ Subgraph  │            │
│   └───────────┘       └───────────┘       └───────────┘            │
│                                                                      │
└─────────────────────────────────────────────────────────────────────┘

Implementing Federation with Entity References:

Each service defines its own types and extends other services' types:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# Order Subgraph (Order Service)
type Order @key(fields: "id") {
  id: ID!
  createdAt: DateTime!
  status: OrderStatus!
  total: Money!
  customerId: ID!
  items: [OrderItem!]!
}
 
type OrderItem {
  productId: ID!
  quantity: Int!
  priceAtPurchase: Money!
  product: Product! # Reference to Product from another service
}
 
# Extend User type to add orders
extend type User @key(fields: "id") {
  id: ID! @external
  orders: [Order!]!  # Resolved by Order Service
}
 
# User Subgraph (User Service)
type User @key(fields: "id") {
  id: ID!
  name: String!
  email: String!
}
 
# Product Subgraph (Product Service)
type Product @key(fields: "id") {
  id: ID!
  name: String!
  imageUrl: String!
  price: Money!
  category: Category!
}
 
# Extend OrderItem to resolve product
extend type OrderItem {
  product: Product! @requires(fields: "productId")
}

Resolver Implementation:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
// Order Service resolvers
const resolvers = {
  Query: {
    order: (_, { id }) => orderDb.orders.findById(id),
    orders: (_, { userId }) => orderDb.orders.findByUser(userId),
  },
  
  Order: {
    // Reference resolver - how to load Order by ID from external reference
    __resolveReference: (reference) => orderDb.orders.findById(reference.id),
  },
  
  // Extend User type with orders field
  User: {
    orders: (user) => orderDb.orders.findByUser(user.id),
  },
};
 
// Product Service resolvers
const resolvers = {
  Product: {
    // Reference resolver - federation calls this to resolve Product references
    __resolveReference: async (reference, context) => {
      // Batched: DataLoader collects all productIds and fetches in one query
      return context.dataloaders.products.load(reference.id);
    },
  },
};

DataLoader for N+1 Prevention

GraphQL Federation Tradeoffs
Aspect	Benefit	Cost
Developer experience	Single unified schema; intuitive queries	Requires GraphQL expertise
Client flexibility	Clients request exactly what they need	Query complexity can spiral
Service independence	Services own their types and extensions	Schema coordination needed
Performance	Gateway can optimize query execution	Multiple service roundtrips possible
Caching	CDN/cache-friendly with persisted queries	Cache invalidation complex

Choosing the Right Pattern

Different patterns suit different scenarios. Here's a decision framework for choosing the right approach to cross-service queries:

Pattern Selection Matrix
Scenario	Recommended Pattern	Rationale
Simple aggregation, moderate traffic	API Composition	Straightforward, sufficient performance
High read volume, stable source data	Denormalization	Fast reads, tolerable sync overhead
Complex queries, heavy analytics	CQRS Read Models	Query-optimized, scales independently
Dashboard/reporting needs	Materialized Views	Pre-computed, predictable performance
Flexible client-driven queries	GraphQL Federation	Client flexibility, unified interface
Snapshot data (historical records)	Copy at Write Time	No sync needed, semantically correct

Key Decision Factors:

Questions to Guide Pattern Selection

•How fresh must the data be? Real-time needs favor API Composition. Tolerance for staleness enables caching, denormalization, or materialized views.
•How complex is the query? Simple lookups suit API Composition. Complex aggregations need CQRS or materialized views.
•What's the read/write ratio? Heavy reads justify the investment in read-optimized patterns like CQRS.
•Must queries work when services are down? Denormalization and local caches provide resilience against service failures.
•How many query patterns exist? Few patterns = specific materialized views. Many patterns = GraphQL Federation's flexibility.
•What's the team's expertise? Don't adopt GraphQL Federation if no one knows GraphQL. Start with simpler patterns.

Start Simple, Evolve as Needed

Performance Optimization Techniques

Regardless of which pattern you choose, several optimization techniques apply universally to cross-service queries.

Caching at Multiple Layers:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
class OptimizedProductClient {
  constructor(
    private httpClient: HttpClient,
    private cache: CacheService,
    private localCache: Map<string, Product>,
  ) {}
 
  async getProduct(productId: string): Promise<Product> {
    // Layer 1: In-memory cache (per-request or short TTL)
    if (this.localCache.has(productId)) {
      return this.localCache.get(productId)!;
    }
 
    // Layer 2: Distributed cache (Redis)
    const cached = await this.cache.get(`product:${productId}`);
    if (cached) {
      this.localCache.set(productId, cached);
      return cached;
    }
 
    // Layer 3: Service call
    const product = await this.httpClient.get<Product>(
      `/products/${productId}`
    );
 
    // Populate caches
    await this.cache.set(`product:${productId}`, product, { ttl: 300 });
    this.localCache.set(productId, product);
 
    return product;
  }
 
  async getProducts(productIds: string[]): Promise<Product[]> {
    const results: Product[] = [];
    const uncachedIds: string[] = [];
 
    // Check caches first
    for (const id of productIds) {
      const cached = this.localCache.get(id) 
                     || await this.cache.get(`product:${id}`);
      if (cached) {
        results.push(cached);
      } else {
        uncachedIds.push(id);
      }
    }
 
    // Batch fetch uncached
    if (uncachedIds.length > 0) {
      const fetched = await this.httpClient.get<Product[]>(
        `/products?ids=${uncachedIds.join(',')}`
      );
      
      for (const product of fetched) {
        await this.cache.set(`product:${product.id}`, product, { ttl: 300 });
        this.localCache.set(product.id, product);
        results.push(product);
      }
    }
 
    return results;
  }
}

Request Coalescing:

When multiple concurrent requests need the same data, coalesce them into a single service call:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
import DataLoader from 'dataloader';
 
// DataLoader batches and deduplicates requests within a tick
const productLoader = new DataLoader<string, Product>(
  async (productIds) => {
    // This function is called once with all requested IDs
    const products = await productService.getProducts(Array.from(productIds));
    
    // Return in same order as requested IDs
    const productMap = new Map(products.map(p => [p.id, p]));
    return productIds.map(id => productMap.get(id) || null);
  },
  { cache: true }  // Caches within request context
);
 
// Multiple concurrent calls...
await Promise.all([
  productLoader.load('product-1'),  // Batched together
  productLoader.load('product-2'),  // into single request
  productLoader.load('product-1'),  // Deduplicated - same as first
]);
// Result: One request to productService.getProducts(['product-1', 'product-2'])

Timeout and Fallback Strategies:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
async function getOrderWithFallbacks(orderId: string): Promise<EnrichedOrder> {
  const order = await orderService.getOrder(orderId);
  
  // Fetch enrichment data with timeouts and fallbacks
  const [user, products] = await Promise.all([
    withTimeout(
      userService.getUser(order.userId),
      { timeoutMs: 500, fallback: { name: 'Unknown User', email: null } }
    ),
    withTimeout(
      productService.getProducts(order.items.map(i => i.productId)),
      { timeoutMs: 500, fallback: [] }
    ),
  ]);
 
  // Compose response with whatever data we got
  return {
    ...order,
    customer: user,
    items: order.items.map(item => ({
      ...item,
      product: products.find(p => p.id === item.productId) || {
        name: `Product ${item.productId}`,
        imageUrl: '/placeholder.png',
      },
    })),
    _partial: user.email === null || products.length === 0,  // Flag if degraded
  };
}

Graceful Degradation

Anti-Patterns to Avoid

While solving the cross-service query problem, teams often fall into patterns that undermine the benefits of database decomposition.

Anti-Pattern: The Super-Service

•Description: A single service becomes the 'composer' for everything, fetching data from all other services.
•Problem: This super-service becomes a bottleneck and coupling point. It must know about all services, changes frequently, and becomes a team dependency.
•Solution: Distribute composition responsibility. Each service can compose data for its own primary use cases.

Anti-Pattern: Recreating the JOIN

•Description: Replicating all data everywhere so every service can 'JOIN' locally.
•Problem: Data duplication explodes, sync mechanisms become complex, and storage costs grow. You've recreated the shared database with extra steps.
•Solution: Denormalize strategically. Only replicate data that's frequently needed and changes rarely.

Anti-Pattern: Synchronous Chains

•Description: Service A calls Service B, which calls Service C, which calls Service D, all synchronously.
•Problem: Latency compounds (A's latency ≥ B + C + D). Any service in the chain failing fails the entire request.
•Solution: Parallelize where possible. Use caching. Consider async patterns. Flatten call hierarchies.

Anti-Pattern: No Ownership of Composed Views

•Description: Multiple services each have their own version of the 'order summary' view, built differently.
•Problem: Inconsistent data across different parts of the application. Maintenance burden multiplies.
•Solution: Assign clear ownership. One team owns the order summary view/API. Others consume it.

The Hidden Database

Summary: Handling Joins Across Services

Cross-service queries are an inherent challenge in Database per Service architecture. Multiple patterns exist to address this challenge, each with specific tradeoffs suitable for different scenarios.

Key Takeaways

•The inability to JOIN is intentional — It enforces service independence. Embrace it as a feature, not a limitation.
•API Composition is the starting point — Parallelize and batch calls. Often sufficient for moderate traffic.
•Denormalization trades complexity for speed — Store copies of needed data locally. Use events to keep in sync.
•CQRS separates read and write concerns — Build query-optimized read models from domain events.
•Materialized views pre-compute aggregations — Ideal for dashboards and analytics with tolerance for staleness.
•GraphQL Federation unifies query interfaces — Provides client flexibility at the cost of infrastructure complexity.
•Start simple and evolve — Begin with API Composition. Add complexity only when evidence shows it's needed.
•Optimize with caching and batching — Apply caching at multiple layers. Use DataLoader to coalesce requests.
•Avoid anti-patterns — Don't create super-services, don't replicate everything everywhere, don't build synchronous chains.

What's next:

Page Complete

4 / 5