DynamoDB Single-Table Design: A Comprehensive Modeling Guide

Thinking in terms of relational tables when using DynamoDB causes more problems than it solves. The typical approach (separate tables for Users, Orders, and Products) leads to multiple round-trips, complex application logic, and unpredictable costs.

Single-table design stores multiple entity types in one table using generic partition and sort keys. Instead of fetching a customer from one table and their orders from another, you retrieve everything in a single request. Make it the default when entities are read together, and keep separate tables when they are not. The pattern pays for itself through data locality, not through a lower table count.

Core Principles

1. Access patterns first: Document every query before designing the schema
2. Data locality: Store related data together using the same partition key
3. Generic keys: Use PK and SK instead of entity-specific names
4. Item collections: Group related items with shared partition keys
5. Attribute overloading: Same attributes serve different purposes across entity types

When to Use Single-Table Design

Single-table design excels when you need to retrieve related data together. Here are the conditions where it works well:

Good Use Cases:

• E-commerce systems where you fetch customers with their orders
• Social platforms retrieving posts with comments and likes
• Multi-tenant SaaS applications with tenant isolation
• Content management systems with hierarchical data
• IoT platforms collecting sensor readings by device

AWS’s NoSQL design guidance puts the rule simply: data that is accessed together should be stored together. Don’t force unrelated data into a single table just to follow a pattern.

Partition Key and Sort Key Strategies

The foundation of single-table design lies in understanding how to structure your keys.

Partition Key Patterns

// Entity type prefix - most common pattern
PK: "CUSTOMER#123"
PK: "ORDER#456"
PK: "PRODUCT#789"

// Composite partition key for multi-tenant
PK: "TENANT#acme#USER#123"

// High-cardinality user-specific key
PK: "USER#${userId}" // Each user gets unique partition

Anti-Pattern to Avoid:

// Low-cardinality status key - creates hot partitions
PK: "STATUS#active" // All active users in ONE partition
// This will throttle when you hit 3,000 RCU limit per partition

Sort Key Patterns

Sort keys enable range queries and hierarchical organization:

// Hierarchical sort key - enables prefix queries
SK: "US#CA#SanFrancisco#94102"
// Query: begins_with(SK, "US#CA") returns all California items

// Timestamp-based chronological ordering
SK: "2024-01-15T10:30:00#EVENT#123"

// Version control pattern
SK: "v0_item123" // Current version
SK: "v1_item123" // Previous version
SK: "v2_item123" // Older version

// Composite for relationships
SK: "ORDERITEM#PRODUCT#789#2024-01-15"

Best Practices:

• Use high-cardinality partition keys (userId, orderId, productId)
• Design sort keys to support range queries with begins_with and BETWEEN
• Include timestamps for chronological ordering
• Maintain consistent prefixes across entity types

Modeling Relationships

Each relationship type maps to a specific access pattern. Here are working examples for each.

One-to-One Relationships

Store related data in the same item collection with different sort keys:

interface User {
  PK: string;
  SK: string;
  EntityType: "User";
  email: string;
  name: string;
}

interface UserPreferences {
  PK: string;
  SK: string;
  EntityType: "UserPreferences";
  theme: "dark" | "light";
  language: string;
}

// Stored as:
{
  PK: "USER#123",
  SK: "METADATA",
  EntityType: "User",
  email: "[email protected]",
  name: "John Doe"
}
{
  PK: "USER#123",
  SK: "PREFERENCES",
  EntityType: "UserPreferences",
  theme: "dark",
  language: "en"
}

// Single query retrieves both
const params = {
  TableName: 'MainTable',
  KeyConditionExpression: 'PK = :pk',
  ExpressionAttributeValues: {
    ':pk': 'USER#123'
  }
};

One-to-Many Relationships

Item collections make one-to-many relationships straightforward:

interface Customer {
  PK: string;
  SK: string;
  EntityType: "Customer";
  name: string;
  email: string;
}

interface Order {
  PK: string; // Same as customer PK
  SK: string; // Chronological sort key
  EntityType: "Order";
  orderId: string;
  total: number;
  status: string;
  orderDate: string;
}

// Customer
{
  PK: "CUSTOMER#123",
  SK: "METADATA",
  EntityType: "Customer",
  name: "John Doe",
  email: "[email protected]"
}

// Orders for this customer
{
  PK: "CUSTOMER#123",
  SK: "ORDER#2024-01-15#456",
  EntityType: "Order",
  orderId: "456",
  total: 99.99,
  status: "delivered",
  orderDate: "2024-01-15"
}
{
  PK: "CUSTOMER#123",
  SK: "ORDER#2024-01-20#457",
  EntityType: "Order",
  orderId: "457",
  total: 149.99,
  status: "pending",
  orderDate: "2024-01-20"
}

// Get customer and ALL orders in one query
const getCustomerWithOrders = async (customerId: string) => {
  const result = await dynamodb.query({
    TableName: 'MainTable',
    KeyConditionExpression: 'PK = :pk',
    ExpressionAttributeValues: {
      ':pk': `CUSTOMER#${customerId}`
    }
  });

  return {
    customer: result.Items?.find(item => item.SK === 'METADATA'),
    orders: result.Items?.filter(item => item.SK.startsWith('ORDER#'))
  };
};

// Get only pending orders
const getPendingOrders = async (customerId: string) => {
  const result = await dynamodb.query({
    TableName: 'MainTable',
    KeyConditionExpression: 'PK = :pk AND begins_with(SK, :sk)',
    FilterExpression: '#status = :status',
    ExpressionAttributeNames: {
      '#status': 'status'
    },
    ExpressionAttributeValues: {
      ':pk': `CUSTOMER#${customerId}`,
      ':sk': 'ORDER#',
      ':status': 'pending'
    }
  });

  return result.Items;
};

Many-to-Many Relationships

Use the adjacency list pattern to model many-to-many relationships:

interface ProductCategory {
  PK: string;
  SK: string;
  EntityType: "ProductCategory";
  productName?: string;
  categoryName?: string;
}

// Product belongs to multiple categories
// Forward relationships
{
  PK: "PRODUCT#789",
  SK: "CATEGORY#Electronics",
  EntityType: "ProductCategory",
  categoryName: "Electronics"
}
{
  PK: "PRODUCT#789",
  SK: "CATEGORY#Gadgets",
  EntityType: "ProductCategory",
  categoryName: "Gadgets"
}

// Reverse relationships (write both for bidirectional queries)
{
  PK: "CATEGORY#Electronics",
  SK: "PRODUCT#789",
  EntityType: "CategoryProduct",
  productName: "Wireless Headphones"
}
{
  PK: "CATEGORY#Gadgets",
  SK: "PRODUCT#789",
  EntityType: "CategoryProduct",
  productName: "Wireless Headphones"
}

// Query all categories for a product
const getProductCategories = async (productId: string) => {
  const result = await dynamodb.query({
    TableName: 'MainTable',
    KeyConditionExpression: 'PK = :pk AND begins_with(SK, :sk)',
    ExpressionAttributeValues: {
      ':pk': `PRODUCT#${productId}`,
      ':sk': 'CATEGORY#'
    }
  });

  return result.Items;
};

// Query all products in a category
const getCategoryProducts = async (categoryName: string) => {
  const result = await dynamodb.query({
    TableName: 'MainTable',
    KeyConditionExpression: 'PK = :pk AND begins_with(SK, :sk)',
    ExpressionAttributeValues: {
      ':pk': `CATEGORY#${categoryName}`,
      ':sk': 'PRODUCT#'
    }
  });

  return result.Items;
};

Trade-off: Writing both directions doubles write operations but enables efficient queries in both directions without Scan operations.

Denormalization Pattern

Sometimes you need frequently accessed data without extra queries:

interface Order {
  PK: string;
  SK: string;
  EntityType: "Order";
  orderId: string;
  customerId: string;
  // Denormalized customer data
  customerName: string;
  customerEmail: string;
  total: number;
}

{
  PK: "ORDER#456",
  SK: "METADATA",
  EntityType: "Order",
  orderId: "456",
  customerId: "123",
  customerName: "John Doe",  // Copied from customer record
  customerEmail: "[email protected]", // Copied from customer record
  total: 99.99
}

Trade-off Analysis:

• Faster reads: No need to fetch customer details separately
• More complex writes: Update customer name requires updating all their orders
• Storage overhead: Customer data duplicated across orders
• Eventual consistency: Updates to customer data require background job to update orders

Use denormalization when read frequency significantly exceeds write frequency and data rarely changes.

GSI vs LSI: Making the Right Choice

Understanding the differences between Global Secondary Indexes and Local Secondary Indexes is critical for efficient access patterns.

Local Secondary Index (LSI)

LSI shares the partition key with the base table but uses a different sort key:

// Table structure
interface Order {
  PK: string;  // Partition key
  SK: string;  // Sort key (order date)
  orderId: string;
  status: string;
  total: number;
}

// Base table queries by date
{
  PK: "CUSTOMER#123",
  SK: "ORDER#2024-01-15#456",
  orderId: "456",
  status: "delivered",
  total: 99.99
}

// LSI enables querying by status with strong consistency
const lsiDefinition = {
  IndexName: "LSI-Status",
  KeySchema: [
    { AttributeName: "PK", KeyType: "HASH" },  // Same as table
    { AttributeName: "status", KeyType: "RANGE" } // Different sort key
  ],
  Projection: {
    ProjectionType: "ALL"
  }
};

// Query customer's pending orders with strong consistency
const params = {
  TableName: 'Orders',
  IndexName: 'LSI-Status',
  KeyConditionExpression: 'PK = :pk AND #status = :status',
  ExpressionAttributeNames: {
    '#status': 'status'
  },
  ExpressionAttributeValues: {
    ':pk': 'CUSTOMER#123',
    ':status': 'pending'
  },
  ConsistentRead: true // Only possible with LSI
};

LSI Characteristics:

• Must be defined at table creation (cannot add later)
• Shares partition key with base table
• Supports strongly consistent reads
• Shares throughput capacity with base table
• 10GB limit per partition key value
• Maximum 5 LSIs per table
• No additional capacity planning needed

Global Secondary Index (GSI)

GSI uses different partition and sort keys, enabling completely new access patterns:

// Table structure
interface Order {
  PK: string;
  SK: string;
  EntityType: string;
  orderId: string;
  orderDate: string;
  status: string;
  GSI1PK: string;  // For date-based queries
  GSI1SK: string;
}

{
  PK: "CUSTOMER#123",
  SK: "ORDER#456",
  EntityType: "Order",
  orderId: "456",
  orderDate: "2024-01-15",
  status: "delivered",
  GSI1PK: "2024-01-15",  // Group by date
  GSI1SK: "ORDER#456"
}

// GSI Definition
const gsiDefinition = {
  IndexName: "GSI1",
  KeySchema: [
    { AttributeName: "GSI1PK", KeyType: "HASH" },
    { AttributeName: "GSI1SK", KeyType: "RANGE" }
  ],
  Projection: {
    ProjectionType: "INCLUDE",
    NonKeyAttributes: ["orderId", "status", "total"]
  }
};

// Query ALL orders by date (across all customers)
const getOrdersByDate = async (date: string) => {
  const result = await dynamodb.query({
    TableName: 'MainTable',
    IndexName: 'GSI1',
    KeyConditionExpression: 'GSI1PK = :date',
    ExpressionAttributeValues: {
      ':date': date
    }
  });

  return result.Items;
};

GSI Characteristics:

• Can be added or removed after table creation
• Different partition and sort keys from base table
• Eventually consistent only (no strong consistency)
• Independent throughput in provisioned mode
• No size limits
• Maximum 20 GSIs per table (increased from 5)
• Enables cross-partition queries

Decision Matrix

Requirement	LSI	GSI
Strong consistency needed	Yes	No
Different partition key needed	No	Yes
Create after table exists	No	Yes
Same partition, different sort order	Yes	Yes
Independent capacity planning	No	Yes (provisioned)
Cross-partition queries	No	Yes
More than 10GB of data per partition	No	Yes

When to Use LSI:

• Strong consistency is required
• Querying same partition with alternative sort order
• Small datasets (< 10GB per partition)
• Access patterns known at table creation time

When to Use GSI:

• Need different partition key for access pattern
• Cross-partition queries required
• Adding new access patterns to existing table
• Eventually consistent reads are acceptable
• Large datasets

Sparse Index Pattern

Only items with GSI attributes are included in the index, reducing storage costs:

interface User {
  PK: string;
  SK: string;
  EntityType: "User";
  status: "active" | "inactive";
  GSI1PK?: string; // Only set for active users
  GSI1SK?: string;
}

// Active user - indexed
{
  PK: "USER#123",
  SK: "METADATA",
  EntityType: "User",
  status: "active",
  email: "[email protected]",
  GSI1PK: "ACTIVE_USERS",  // Included in GSI
  GSI1SK: "USER#123"
}

// Inactive user - NOT indexed
{
  PK: "USER#456",
  SK: "METADATA",
  EntityType: "User",
  status: "inactive",
  email: "[email protected]"
  // No GSI1PK/GSI1SK - not in index, saves storage
}

// Query only active users
const getActiveUsers = async () => {
  const result = await dynamodb.query({
    TableName: 'MainTable',
    IndexName: 'GSI1',
    KeyConditionExpression: 'GSI1PK = :type',
    ExpressionAttributeValues: {
      ':type': 'ACTIVE_USERS'
    }
  });

  return result.Items;
};

Cost Savings: If only 10% of users are active, sparse indexing reduces GSI storage by 90%.

DynamoDB Accelerator (DAX) Integration

DAX provides microsecond response times for read-heavy workloads through in-memory caching.

When to Use DAX

// Scenario 1: Read-heavy workload
// E-commerce product catalog
// - 95% reads, 5% writes
// - Expected cache hit rate: 90%+
// - Benefit: 10x latency improvement + reduced costs

// Scenario 2: Hot key pattern
// Flash sale - single product receives 10,000 reads/second
// Without DAX: Potential throttling, high costs
// With DAX: Offloads reads from DynamoDB

// Scenario 3: Repeated reads
// Analytics dashboard querying same data repeatedly
// DAX caches entire working set in memory

When to Avoid DAX

// Anti-pattern 1: Write-heavy workload
// Real-time analytics with frequent updates
// DAX adds overhead without benefit

// Anti-pattern 2: Strong consistency required
// Financial transactions needing immediate consistency
// DAX provides eventual consistency only

// Anti-pattern 3: Low cache hit rate
// Ad-hoc queries with random access patterns
// Cache hit rate < 50% means poor ROI

// Anti-pattern 4: Low traffic
// Application with < 100 requests/second
// DAX cost exceeds savings

DAX Implementation

import { DynamoDB } from '@aws-sdk/client-dynamodb';
import { DynamoDBDocument } from '@aws-sdk/lib-dynamodb';
import AmazonDaxClient from 'amazon-dax-client';

// Without DAX - direct DynamoDB
const directClient = new DynamoDB({
  region: 'us-east-1'
});
const dynamodb = DynamoDBDocument.from(directClient);

// With DAX
const daxClient = new AmazonDaxClient({
  endpoints: ['my-cluster.dax.us-east-1.amazonaws.com:8111'],
  region: 'us-east-1'
});
const dax = DynamoDBDocument.from(daxClient);

// Same API - drop-in replacement
const getProduct = async (productId: string) => {
  const params = {
    TableName: 'Products',
    Key: {
      PK: `PRODUCT#${productId}`,
      SK: 'METADATA'
    }
  };

  // First call: DynamoDB query (5ms)
  // Subsequent calls: DAX cache (500μs)
  const result = await dax.get(params);
  return result.Item;
};

// Query operations also cached
const getProductReviews = async (productId: string) => {
  const params = {
    TableName: 'Products',
    KeyConditionExpression: 'PK = :pk AND begins_with(SK, :sk)',
    ExpressionAttributeValues: {
      ':pk': `PRODUCT#${productId}`,
      ':sk': 'REVIEW#'
    }
  };

  const result = await dax.query(params);
  return result.Items;
};

DAX Latency and Cost Profile

const latencyAndCost = {
  dynamodb: {
    getItem: '3-5ms',
    query: '5-10ms',
    cost: '$0.155 per million reads'
  },
  dax: {
    getItem: '200-500μs (cache hit)',
    query: '300-700μs (cache hit)',
    cacheMiss: '4-6ms (DynamoDB + overhead)',
    cost: '$0.11/hour per t3.small node'
  }
};

Cost Analysis

// Scenario: 1,000 req/sec with 95% cache hit rate

// Without DAX:
// - Requests: 2.59B/month
// - DynamoDB cost: $401/month (on-demand at $0.155/million)

// With DAX (3-node t3.medium cluster):
// - DAX cluster: $482/month
// - Cache hits (95%): 2.46B reads (served by DAX)
// - Cache misses (5%): 130M reads from DynamoDB
// - DynamoDB cost: $20/month
// - Total: $502/month
// - Savings: Minimal at this scale; main benefit is 10x latency improvement

// Cost break-even at this hit rate: ~1,250 req/sec
// Under that volume, the case for DAX is latency alone

Query Optimization Techniques

The difference between Query and Scan operations dramatically impacts performance and cost.

Query vs Scan

// ANTI-PATTERN: Scan operation
const findUserByEmail = async (email: string) => {
  const result = await dynamodb.scan({
    TableName: 'Users',
    FilterExpression: 'email = :email',
    ExpressionAttributeValues: {
      ':email': email
    }
  });

  return result.Items?.[0];
};
// - Reads entire table, filters in application
// - Capacity scales with table size, not with matching items
// - Latency: seconds, and it grows with the table

// BEST PRACTICE: Query with GSI
const findUserByEmailOptimized = async (email: string) => {
  const result = await dynamodb.query({
    TableName: 'Users',
    IndexName: 'GSI-Email',
    KeyConditionExpression: 'GSI1PK = :email',
    ExpressionAttributeValues: {
      ':email': `EMAIL#${email}`
    }
  });

  return result.Items?.[0];
};
// - Direct partition access
// - 1 item = 0.5 RCU (eventually consistent)
// - Latency: 5-10ms
// - Capacity per lookup stays flat as the table grows

In a table with a million users, the Scan reads every item to return one; the GSI query reads one. That gap is the whole reason to spend a GSI on the email lookup.

Projection Optimization

// ANTI-PATTERN: Project all attributes
const gsiAll = {
  IndexName: 'GSI1',
  Projection: {
    ProjectionType: 'ALL' // Duplicates entire item
  }
};
// Storage: 2x table size (table + full GSI copy)
// Cost: High

// BEST PRACTICE: Project only needed attributes
const gsiInclude = {
  IndexName: 'GSI1',
  Projection: {
    ProjectionType: 'INCLUDE',
    NonKeyAttributes: ['name', 'email', 'status']
  }
};
// Storage: Minimal (keys + specified attributes)
// Cost: Optimized

// OPTIMAL: Keys only when fetching full item anyway
const gsiKeys = {
  IndexName: 'GSI1',
  Projection: {
    ProjectionType: 'KEYS_ONLY'
  }
};
// Use pattern: Query GSI for IDs, then BatchGetItem for details

Batch Operations

// ANTI-PATTERN: Sequential GetItem calls
const getUsersSequential = async (userIds: string[]) => {
  const users = [];

  for (const userId of userIds) {
    const result = await dynamodb.get({
      TableName: 'Users',
      Key: {
        PK: `USER#${userId}`,
        SK: 'METADATA'
      }
    });
    users.push(result.Item);
  }

  return users;
};
// 100 users = 100 round trips = 500-1000ms

// BEST PRACTICE: BatchGetItem
import { BatchGetCommand } from '@aws-sdk/lib-dynamodb';

const getUsersBatch = async (userIds: string[]) => {
  const command = new BatchGetCommand({
    RequestItems: {
      Users: {
        Keys: userIds.map(id => ({
          PK: `USER#${id}`,
          SK: 'METADATA'
        }))
      }
    }
  });

  const result = await dynamodb.send(command);
  return result.Responses?.Users || [];
};
// 100 users = 1 request (up to 100 items) = 50-100ms
// 5-10x faster

Composite Sort Key for Filtering

// ANTI-PATTERN: Query + FilterExpression
const getPendingOrdersWrong = async (customerId: string) => {
  const result = await dynamodb.query({
    TableName: 'Orders',
    KeyConditionExpression: 'PK = :pk',
    FilterExpression: '#status = :status',
    ExpressionAttributeNames: {
      '#status': 'status'
    },
    ExpressionAttributeValues: {
      ':pk': `CUSTOMER#${customerId}`,
      ':status': 'pending'
    }
  });

  return result.Items;
};
// Reads ALL orders, filters after
// Consumes RCUs for all orders, returns only pending

// BEST PRACTICE: Composite sort key
// SK format: "STATUS#pending#ORDER#456"
const getPendingOrdersOptimized = async (customerId: string) => {
  const result = await dynamodb.query({
    TableName: 'Orders',
    KeyConditionExpression: 'PK = :pk AND begins_with(SK, :status)',
    ExpressionAttributeValues: {
      ':pk': `CUSTOMER#${customerId}`,
      ':status': 'STATUS#pending'
    }
  });

  return result.Items;
};
// Reads ONLY pending orders
// Consumes RCUs only for matching items

Preventing Hot Partitions

Each DynamoDB partition supports 3,000 RCUs and 1,000 WCUs. Exceeding these limits causes throttling.

Hot Partition Scenarios

// ANTI-PATTERN 1: Low-cardinality partition key
{
  PK: "STATUS#active",  // Only 2-3 unique values
  SK: "USER#123"
}
// All active users in ONE partition
// Easily exceeds 3,000 RCU limit

// ANTI-PATTERN 2: Celebrity problem
{
  PK: "USER#celebrity",
  SK: "FOLLOWER#456"
}
// Millions of followers in one partition
// Exceeds 10GB and throughput limits

// ANTI-PATTERN 3: Time-based key without sharding
{
  PK: "2024-01-15",  // All today's data
  SK: "EVENT#123"
}
// Hot partition during peak hours

Prevention Strategy 1: Write Sharding

// Add random suffix to distribute writes
const SHARD_COUNT = 10;

const writeWithSharding = async (userId: string, data: any) => {
  const shardId = Math.floor(Math.random() * SHARD_COUNT);

  await dynamodb.put({
    TableName: 'Users',
    Item: {
      PK: `STATUS#active#${shardId}`, // Distributes across 10 partitions
      SK: `USER#${userId}`,
      ...data
    }
  });
};

// Reading requires querying all shards
const getActiveUsers = async () => {
  const promises = [];

  for (let i = 0; i < SHARD_COUNT; i++) {
    promises.push(
      dynamodb.query({
        TableName: 'Users',
        KeyConditionExpression: 'PK = :pk',
        ExpressionAttributeValues: {
          ':pk': `STATUS#active#${i}`
        }
      })
    );
  }

  const results = await Promise.all(promises);
  return results.flatMap(r => r.Items || []);
};

// Distributes writes across 10 partitions
// Throughput: 10,000 WCUs (1,000 * 10)

Prevention Strategy 2: Composite High-Cardinality Keys

// Combine low-cardinality with high-cardinality
{
  PK: `STATUS#active#USER#${userId}`, // Unique per user
  SK: "METADATA"
}

// Or use GSI for low-cardinality queries
{
  PK: `USER#${userId}`,
  SK: "METADATA",
  status: "active",
  GSI1PK: "STATUS#active",  // GSI for status queries
  GSI1SK: `USER#${userId}`
}

Prevention Strategy 3: Deterministic Sharding

import crypto from 'crypto';

const getShardId = (entityId: string, shardCount: number): number => {
  const hash = crypto.createHash('md5').update(entityId).digest('hex');
  return parseInt(hash.substring(0, 8), 16) % shardCount;
};

const shardId = getShardId(userId, 10);
const partitionKey = `USERS#${shardId}`;

This approach ensures the same entity always goes to the same shard, enabling consistent reads without querying all shards.

Cost Optimization Strategies

Provisioned vs On-Demand

const pricing = {
  provisioned: {
    rcu: '$0.00013 per hour',
    wcu: '$0.00065 per hour',
    storage: '$0.25 per GB-month'
  },
  onDemand: {
    readRequest: '$0.155 per million reads',
    writeRequest: '$0.78 per million writes',
    storage: '$0.25 per GB-month'
  }
};

// Example: 100M reads/month steady traffic
// Provisioned: ~38.5 RCU * $0.00013 * 730 hours = $3.65/month
// On-Demand: 100M / 1M * $0.155 = $15.50/month
// Difference: 4.25x more expensive with on-demand

When to Use Provisioned:

• Predictable traffic patterns
• High volume (>1M requests/day)
• 24/7 production applications
• Budget-conscious scenarios
• Can commit to reserved capacity for a further discount on 1-year or 3-year terms

When to Use On-Demand:

• Unpredictable traffic
• New applications with unknown load
• Development/testing environments
• Spiky workloads (10x variance)
• Small-scale applications (less than 1M requests/day)

Sparse Index Savings

// Without sparse index: All 1M users indexed
// Table: 10GB
// GSI with ALL projection: +10GB
// Total: 20GB * $0.25 = $5/month storage

// With sparse index: Only 100K active users indexed
// Table: 10GB
// GSI with sparse index: +1GB (10% of users)
// Total: 11GB * $0.25 = $2.75/month storage
// Savings: 45%

Single-Table Cost Benefits

// Multi-table approach:
// - Users: 5 RCU, 5 WCU
// - Orders: 10 RCU, 10 WCU
// - Products: 15 RCU, 5 WCU
// - OrderItems: 20 RCU, 20 WCU
// Total: 50 RCU, 40 WCU = $23.73/month

// Single-table approach:
// - MainTable: 30 RCU, 25 WCU
// Total: $14.71/month
// Savings: 38%, because each separate table needs its own headroom

Common Pitfalls and Solutions

Pitfall 1: Not Documenting Access Patterns First

Designing tables before the queries are understood leads to a redesign later:

// WRONG approach:
// 1. Create tables based on entities
// 2. Add GSIs when queries don't work
// 3. End up with 5+ GSIs, still inefficient

// RIGHT approach:
const accessPatterns = [
  'Get customer and all their orders',
  'Get order and all its items',
  'Get all orders by date range',
  'Get customer by email',
  'Get product and all reviews',
  'Get customer reviews'
];

// Design table and indexes to support ALL patterns efficiently

Pitfall 2: Missing Error Handling for Throttling

// Common gap: no retry logic when a traffic spike causes throttling
// Result: ProvisionedThroughputExceededException reaches the user

// Solution: Exponential backoff
import { DynamoDBClient } from '@aws-sdk/client-dynamodb';

const client = new DynamoDBClient({
  region: 'us-east-1',
  maxAttempts: 3,
  retryMode: 'adaptive' // Built-in exponential backoff
});

// Better: Implement circuit breaker pattern
// Best: Design to avoid hot partitions

Pitfall 3: Ignoring Item Size Limits

// Problem: 400KB limit per item
// An order with hundreds of line items exceeds it as a single item

// Solution 1: Pagination pattern
{
  PK: "ORDER#456",
  SK: "ITEMS#PAGE#1",
  items: [...] // First 100 items
}
{
  PK: "ORDER#456",
  SK: "ITEMS#PAGE#2",
  items: [...] // Next 100 items
}

// Solution 2: Individual items (preferred)
{
  PK: "ORDER#456",
  SK: "ITEM#1",
  productId: "789",
  quantity: 2
}
// Each line item as separate DynamoDB item

Pitfall 4: Not Planning LSIs at Table Creation

You cannot add LSIs after table creation. This requires data migration if you need them later:

// Plan LSIs upfront, even if not immediately needed
const tableDefinition = {
  TableName: 'Orders',
  KeySchema: [
    { AttributeName: 'PK', KeyType: 'HASH' },
    { AttributeName: 'SK', KeyType: 'RANGE' }
  ],
  LocalSecondaryIndexes: [
    {
      IndexName: 'LSI-Status',
      KeySchema: [
        { AttributeName: 'PK', KeyType: 'HASH' },
        { AttributeName: 'status', KeyType: 'RANGE' }
      ],
      Projection: { ProjectionType: 'ALL' }
    }
  ]
};

// GSIs can be added later, LSIs cannot

Pitfall 5: Incorrect Capacity Mode

// Steady traffic: 50 read/sec + 20 write/sec, 24/7
// 129.6M reads + 51.84M writes per month

// On-demand (eventually consistent reads bill 0.5 RRU each)
// 64.8M RRU * $0.155/M + 51.84M WRU * $0.78/M = ~$50/month

// Provisioned with auto-scaling, headroom included
// 100 RCU * $0.00013 * 730 + 40 WCU * $0.00065 * 730 = $28.47/month
// Difference: ~43% at this traffic shape

// Predictable traffic favors provisioned capacity
// Spiky, unpredictable traffic favors on-demand

When to Avoid Single-Table Design

Separate tables are the better choice in these cases:

• Different entity types have vastly different consistency requirements
• Team lacks DynamoDB expertise (learning curve impacts velocity)
• Simple CRUD with minimal relationships (overhead not justified)
• Ad-hoc reporting needs (data warehouse patterns better suited)
• Service boundaries in microservices (separate tables per service)
• Different entities have no access pattern overlap

Two boundaries are worth stating plainly: keep configuration data out of the operational table, and don’t stretch one table across service boundaries.

Type-Safe Implementation Libraries

When implementing single-table design patterns with TypeScript, using type-safe libraries instead of raw AWS SDK increases development velocity and prevents runtime errors. Two popular choices:

DynamoDB Toolbox

DynamoDB Toolbox is a modern TypeScript library compatible with AWS SDK v3:

• Type Safety: Automatic TypeScript types from entity definitions
• Schema Validation: Runtime data validation
• Query Builder: Type-safe query and update expressions
• Single-Table Support: Built-in support for GSI and composite key patterns
• AWS SDK v3: Full compatibility with the latest SDK version

Instead of complex AttributeValue mappings with raw AWS SDK, you can use clean, maintainable entity definitions. Check out the DynamoDB Toolbox guide for detailed implementation examples and production best practices.

OneTable

OneTable is an alternative library specifically designed for single-table design:

• Schema-Driven: Model definition with JSON schema
• Migration Support: Built-in schema migration support
• TypeScript Generation: Automatic type generation from schemas
• Developer Experience: Minimal boilerplate, intuitive API
• Validation: Robust validation with JSON Schema standard

OneTable provides powerful tools for schema evolution and migration needs, especially in large and complex single-table designs.

Which to Choose?

Choose DynamoDB Toolbox if:

• You’re migrating to AWS SDK v3 or starting fresh
• You want tighter integration with AWS ecosystem
• You’ll use more AWS-native patterns

Choose OneTable if:

• You perform schema migrations frequently
• You prefer JSON Schema standard
• You want more abstraction and convention over configuration
• You’re doing rapid prototyping

Both libraries are production-ready and actively maintained. The choice depends on team preference and project requirements.

Related Topics

Working with DynamoDB connects to several other areas worth exploring:

• TypeScript Development with DynamoDB Toolbox - Type-safe entity definitions and schema validation for single-table design
• AWS Lambda Guide 101: Cold Start Optimization - Efficient DynamoDB queries in serverless
• Serverless to CDK Migration Guide - Managing DynamoDB tables with CDK
• Serverless Architecture Patterns - DynamoDB in event-driven systems

The default holds when entities are read together and the access patterns are known before the table exists: one table, generic keys, and a small number of overloaded indexes. Override it when entity types belong to different services, when reporting is ad-hoc, or when the team has no room for the modeling learning curve. Separate tables cost extra round-trips, and that is a cheaper problem than a key schema nobody on the team can change.

References

• Best Practices for Designing and Architecting with DynamoDB - Official AWS guidance on key design, single-table modeling, and performance best practices.
• NoSQL Design for DynamoDB - AWS documentation on the fundamental shift from relational to NoSQL design thinking, starting from access patterns.
• Using Global Secondary Indexes in DynamoDB - Official reference for GSI concepts, provisioned throughput, and query patterns.
• Overloading Global Secondary Indexes - AWS guide to the sparse index and index overloading technique used in single-table design.
• The What, Why, and When of Single-Table Design with DynamoDB - Alex DeBrie’s canonical reference post explaining the rationale and trade-offs of single-table design, by an AWS Data Hero.
• Data Modeling Foundations in DynamoDB - AWS documentation on core data modeling concepts: partitions, keys, and item collections.
• Single-Table vs. Multi-Table Design in Amazon DynamoDB - Official AWS blog post comparing both approaches with concrete trade-off analysis.
• Amazon DynamoDB Pricing - Current on-demand and provisioned rates per Region; the cost examples here use us-east-1 list prices and change over time.