Designing Database Sharding with Claude Code: Horizontal Partitioning, Routing, Cross-Shard Queries

Introduction

When a single DB server becomes a write bottleneck, sharding distributes data across multiple DB servers. However, cross-shard queries are unavailable, making shard key design the most critical decision. Generate designs with Claude Code.

---

CLAUDE.md Sharding Rules

## Database Sharding Design Rules

### Shard Key Selection Criteria
- High cardinality (user ID, tenant ID)
- Even distribution (prevent hot shards)
- Match query patterns (avoid queries without shard key)
- Never change (extremely difficult to change once set)

### Cross-Shard Constraints
- JOINs must be done in the application layer (no cross-shard JOINs)
- COUNT(*)/SUM aggregations use MapReduce pattern
- Global uniqueness guaranteed with ULID or UUIDv7

---

Generated Sharding Implementation

// src/sharding/shardRouter.ts
import crypto from 'crypto';

const SHARD_CONFIGS = [
  { id: 0, host: 'db-shard-0.internal', port: 5432, database: 'appdb_0' },
  { id: 1, host: 'db-shard-1.internal', port: 5432, database: 'appdb_1' },
  { id: 2, host: 'db-shard-2.internal', port: 5432, database: 'appdb_2' },
  { id: 3, host: 'db-shard-3.internal', port: 5432, database: 'appdb_3' },
];

export class ShardRouter {
  getShardId(tenantId: string): number {
    const hash = crypto.createHash('sha256').update(tenantId).digest('hex');
    return parseInt(hash.slice(0, 8), 16) % SHARD_CONFIGS.length;
  }

  getShardConfig(tenantId: string) {
    return SHARD_CONFIGS[this.getShardId(tenantId)];
  }
}

export const shardRouter = new ShardRouter();

// src/sharding/shardedPrisma.ts
const shardClients = new Map<number, PrismaClient>();

export function getPrismaForTenant(tenantId: string): PrismaClient {
  const config = shardRouter.getShardConfig(tenantId);
  if (!shardClients.has(config.id)) {
    const client = new PrismaClient({
      datasources: { db: { url: `postgresql://...@${config.host}:${config.port}/${config.database}` } },
    });
    shardClients.set(config.id, client);
  }
  return shardClients.get(config.id)!;
}

export class ShardedOrderRepository {
  async findByTenant(tenantId: string) {
    const prisma = getPrismaForTenant(tenantId);
    return prisma.order.findMany({ where: { tenantId }, orderBy: { createdAt: 'desc' } });
  }
}

---

Cross-Shard Aggregation (MapReduce)

export async function getGlobalOrderStats() {
  const shardResults = await Promise.all(
    SHARD_CONFIGS.map(async (shard) => {
      const prisma = shardClients.get(shard.id)!;
      const [count, revenue] = await Promise.all([
        prisma.order.count({ where: { status: 'completed' } }),
        prisma.order.aggregate({ where: { status: 'completed' }, _sum: { totalCents: true } }),
      ]);
      return { completedOrders: count, totalRevenueCents: revenue._sum.totalCents ?? 0 };
    })
  );

  return shardResults.reduce(
    (acc, r) => ({ completedOrders: acc.completedOrders + r.completedOrders, totalRevenueCents: acc.totalRevenueCents + r.totalRevenueCents }),
    { completedOrders: 0, totalRevenueCents: 0 }
  );
}

---

Summary

Design Database Sharding with Claude Code:

1. CLAUDE.md — shard key selection criteria, prohibit cross-shard JOINs, aggregation policy
2. Hash by tenant ID to determine shard number (consistent distribution)
3. Include shard key in all queries (force no cross-shard access)
4. Cross-shard aggregation uses MapReduce pattern; search offloaded to Elasticsearch

---

Review sharding designs with **Code Review Pack (¥980)* using /code-review at prompt-works.jp*

myouga (@myougatheaxo) — Axolotl VTuber.