Designing for Scale: Database Sharding, Read Replicas, and CQRS

Most SaaS products will never need sharding. But understanding these patterns helps you make better architecture decisions early.

When You Actually Need Them

• Read replicas: > 1000 read queries/second, or analytics queries slowing production
• Sharding: > 100GB of data that won't fit on one machine with acceptable query time
• CQRS: complex read patterns that don't map to your write model

For most SaaS at < $1M ARR: you don't need any of these. Optimize your queries first.

Read Replicas: The First Step

// Prisma with read/write split
const writeDb = new PrismaClient({ datasources: { db: { url: PRIMARY_URL } } });
const readDb = new PrismaClient({ datasources: { db: { url: READ_REPLICA_URL } } });

// Writes go to primary
await writeDb.orders.create({ data: orderData });

// Reads go to replica (may be slightly stale)
const orders = await readDb.orders.findMany({ where: { userId } });

CQRS: Separate Read and Write Models

// Write side: optimized for consistency
class OrderCommandHandler {
  async placeOrder(cmd: PlaceOrderCommand) {
    // Validate, check inventory, create order
    await this.db.orders.create({ data: { ... } });
    // Publish event for read model to consume
    await this.eventBus.publish('order.placed', orderData);
  }
}

// Read side: denormalized for query performance
class OrderEventHandler {
  async onOrderPlaced(event: OrderPlacedEvent) {
    // Update a pre-aggregated 'customer_order_summary' table
    await this.readDb.customerOrderSummary.upsert({
      where: { customerId: event.customerId },
      update: {
        totalOrders: { increment: 1 },
        totalSpend: { increment: event.amount },
      },
      create: { customerId: event.customerId, totalOrders: 1, totalSpend: event.amount },
    });
  }
}

When CQRS makes sense: dashboards with complex aggregations, analytics queries that would require expensive JOINs on the write model.

Horizontal Sharding (For When You Actually Need It)

// Route queries to the right shard
function getShardForUser(userId: string): PrismaClient {
  // Consistent hashing: same userId always hits same shard
  const shardIndex = hashUserId(userId) % shards.length;
  return shards[shardIndex];
}

// Queries within a shard work normally
const db = getShardForUser(userId);
const orders = await db.orders.findMany({ where: { userId } });

// Cross-shard queries are expensive — design to avoid them

The Right Optimization Order

1. Index your queries
2. Add Redis caching
3. Add a read replica
4. Upgrade your database instance
5. CQRS for specific query patterns
6. Sharding (rarely needed)

Architecture that scales from day one — without premature complexity — is what the AI SaaS Starter Kit is designed for.