DynamoDB Single-Table Design: Advanced Patterns From a 4TB Production Table

Single-table design in DynamoDB is the most misunderstood pattern in cloud architecture. Done well, it delivers sub-10ms reads at any scale. Done poorly, it creates an unmaintainable mess that even its authors cannot reason about six months later.

After four years of operating a 4TB single-table design handling 180K RCU at peak, I have learned where the pattern shines and where it should never be applied.

The Problem We Solved

Our order management system needed to support these access patterns with single-digit millisecond latency:

1. Get order by ID
2. Get all orders for a customer (sorted by date)
3. Get all orders in a status (PENDING, PROCESSING, SHIPPED)
4. Get order items for an order
5. Get customer profile
6. Get orders by fulfillment center (sorted by priority)
7. Get daily order count by status (analytics)

In a relational model, this requires 4+ tables and multiple JOINs. In DynamoDB single-table design, it requires one table and 3 GSIs.

The Table Schema

// Entity key patterns
interface KeyPatterns {
  // Orders
  order: { PK: `CUSTOMER#${customerId}`, SK: `ORDER#${orderId}` };
  // Order Items
  orderItem: { PK: `ORDER#${orderId}`, SK: `ITEM#${itemId}` };
  // Customer Profile
  customer: { PK: `CUSTOMER#${customerId}`, SK: `PROFILE` };
  // Order Status (for GSI1)
  orderByStatus: { GSI1PK: `STATUS#${status}`, GSI1SK: `${createdAt}#${orderId}` };
  // Fulfillment Center (for GSI2)
  orderByFC: { GSI2PK: `FC#${fcId}`, GSI2SK: `${priority}#${orderId}` };
  // Daily Analytics (for GSI3)
  dailyCount: { GSI3PK: `DATE#${date}`, GSI3SK: `STATUS#${status}` };
}

The full table design:

Entity	PK	SK	GSI1PK	GSI1SK	GSI2PK	GSI2SK
Customer	CUSTOMER#123	PROFILE	-	-	-	-
Order	CUSTOMER#123	ORDER#abc	STATUS#PENDING	2025-01-15T10:30:00#abc	FC#east-1	1#abc
Order Item	ORDER#abc	ITEM#001	-	-	-	-

Access Pattern Implementation

Pattern 1: Get Customer Orders (Paginated)

import { DynamoDBDocumentClient, QueryCommand } from '@aws-sdk/lib-dynamodb';

async function getCustomerOrders(
  client: DynamoDBDocumentClient,
  customerId: string,
  options: { limit?: number; lastKey?: Record<string, unknown> } = {}
) {
  const result = await client.send(new QueryCommand({
    TableName: 'orders-table',
    KeyConditionExpression: 'PK = :pk AND begins_with(SK, :skPrefix)',
    ExpressionAttributeValues: {
      ':pk': `CUSTOMER#${customerId}`,
      ':skPrefix': 'ORDER#',
    },
    ScanIndexForward: false, // newest first
    Limit: options.limit ?? 25,
    ExclusiveStartKey: options.lastKey,
  }));

  return {
    orders: result.Items,
    nextKey: result.LastEvaluatedKey,
  };
}

Pattern 2: Get Orders by Status (Cross-Customer)

async function getOrdersByStatus(
  client: DynamoDBDocumentClient,
  status: string,
  options: { since?: string; limit?: number } = {}
) {
  const result = await client.send(new QueryCommand({
    TableName: 'orders-table',
    IndexName: 'GSI1',
    KeyConditionExpression: options.since
      ? 'GSI1PK = :pk AND GSI1SK > :since'
      : 'GSI1PK = :pk',
    ExpressionAttributeValues: {
      ':pk': `STATUS#${status}`,
      ...(options.since && { ':since': options.since }),
    },
    ScanIndexForward: true, // oldest first (FIFO processing)
    Limit: options.limit ?? 100,
  }));

  return result.Items;
}

Pattern 3: Transactional Order Creation

import { TransactWriteCommand } from '@aws-sdk/lib-dynamodb';

async function createOrder(
  client: DynamoDBDocumentClient,
  order: Order,
  items: OrderItem[]
) {
  const transactItems = [
    // Write order record
    {
      Put: {
        TableName: 'orders-table',
        Item: {
          PK: `CUSTOMER#${order.customerId}`,
          SK: `ORDER#${order.id}`,
          GSI1PK: `STATUS#PENDING`,
          GSI1SK: `${order.createdAt}#${order.id}`,
          GSI2PK: `FC#${order.fulfillmentCenter}`,
          GSI2SK: `${order.priority}#${order.id}`,
          ...order,
          entityType: 'ORDER',
        },
        ConditionExpression: 'attribute_not_exists(PK)',
      },
    },
    // Write each order item
    ...items.map(item => ({
      Put: {
        TableName: 'orders-table',
        Item: {
          PK: `ORDER#${order.id}`,
          SK: `ITEM#${item.id}`,
          ...item,
          entityType: 'ORDER_ITEM',
        },
      },
    })),
    // Update daily counter
    {
      Update: {
        TableName: 'orders-table',
        Key: {
          PK: `ANALYTICS`,
          SK: `DATE#${order.createdAt.split('T')[0]}#STATUS#PENDING`,
        },
        UpdateExpression: 'ADD orderCount :inc',
        ExpressionAttributeValues: { ':inc': 1 },
      },
    },
  ];

  await client.send(new TransactWriteCommand({ TransactItems: transactItems }));
}

GSI Strategy: The Three-GSI Rule

After experimenting with up to 8 GSIs on a table, we settled on a rule: no more than 3 GSIs per table. Each GSI:

• Doubles your write costs (every write is replicated)
• Adds replication lag (typically 20-50ms, occasionally seconds)
• Consumes shared provisioned capacity
• Increases table storage by 30-100%

Our GSI utilization data:

GSI	Purpose	Read Capacity	Write Amplification	Replication Lag (p99)
GSI1	Status lookup	24K RCU peak	1.0x	45ms
GSI2	FC routing	8K RCU peak	1.0x	38ms
GSI3	Analytics	2K RCU peak	1.0x	62ms

Hot Partition Mitigation

At 180K RCU, hot partitions are inevitable. Our STATUS#PENDING partition received 60% of all GSI1 reads. We implemented write sharding:

// Shard the hot partition across N shards
const SHARD_COUNT = 10;

function getShardedStatusKey(status: string): string {
  const shard = Math.floor(Math.random() * SHARD_COUNT);
  return `STATUS#${status}#SHARD${shard}`;
}

// Reading requires scatter-gather across all shards
async function getOrdersByStatusSharded(
  client: DynamoDBDocumentClient,
  status: string,
  limit: number
): Promise<Order[]> {
  const queries = Array.from({ length: SHARD_COUNT }, (_, i) =>
    client.send(new QueryCommand({
      TableName: 'orders-table',
      IndexName: 'GSI1',
      KeyConditionExpression: 'GSI1PK = :pk',
      ExpressionAttributeValues: {
        ':pk': `STATUS#${status}#SHARD${i}`,
      },
      Limit: limit,
      ScanIndexForward: true,
    }))
  );

  const results = await Promise.all(queries);
  return results
    .flatMap(r => r.Items as Order[])
    .sort((a, b) => a.createdAt.localeCompare(b.createdAt))
    .slice(0, limit);
}

The tradeoff: scatter-gather reads add latency (parallel queries take the latency of the slowest shard) but eliminate throttling. In practice, 10 parallel queries at 3ms each is better than 1 query that gets throttled for 200ms.

Performance at Scale

Production metrics from our 4TB table:

Metric	Value
Table size	4.1TB
Item count	2.8 billion
Peak RCU	180,000
Peak WCU	42,000
Average read latency (p50)	3.2ms
Average read latency (p99)	8.7ms
TransactWrite latency (p50)	14ms
TransactWrite latency (p99)	38ms
Monthly cost	$28,400
Throttle events/day	<50 (post-sharding)

When NOT to Use Single-Table Design

After four years, here is my honest assessment of when to avoid this pattern:

1. Rapidly evolving access patterns: If you do not know your queries upfront, single-table design paints you into a corner.
2. Teams without DynamoDB expertise: The learning curve is steep. A poorly designed single table is worse than multiple simple tables.
3. Heavy analytics requirements: If you need ad-hoc queries, use DynamoDB as the operational store and export to Athena/Redshift for analytics.
4. Small datasets (<10GB): The complexity is not justified. A simple table per entity with on-demand pricing works fine.

Key Takeaways

1. Design from access patterns, not entities: List every query your application needs before designing the schema. This is backwards from relational thinking.
2. Three GSIs maximum: Each additional GSI adds write amplification and operational complexity. If you need more, reconsider your data model.
3. Shard hot partitions proactively: Do not wait for throttle events. If you know a partition key will be hot, shard it from day one.
4. Transactions are expensive but worth it: TransactWrite costs 2x a normal write but guarantees consistency across entities. Use them for cross-entity operations.
5. Single-table is not always single-table: We use a separate table for high-throughput event streams to avoid write contention with our transactional data.

The pattern works exceptionally well when your access patterns are stable and well-defined. It fails when teams try to force relational thinking into a key-value store. Know your queries first, then design the table.