DynamoDB Single-Table Design: Access Patterns and GSI Strategies

DynamoDB Thinking

DynamoDB requires modeling data around access patterns upfront. Unlike SQL, you can't just query anything.

Core Concepts

• Partition Key (PK): Determines which partition stores the item
• Sort Key (SK): Allows range queries within a partition
• GSI: Global Secondary Index — different PK/SK combination
• LSI: Local Secondary Index — same PK, different SK

Single-Table Design

// All entities in ONE table
// PK and SK encode entity type and relationships

// User entity
{ PK: 'USER#alice', SK: 'PROFILE', name: 'Alice', email: 'alice@example.com' }

// User's orders (query PK = 'USER#alice', SK begins_with 'ORDER#')
{ PK: 'USER#alice', SK: 'ORDER#2026-05-15#abc123', status: 'shipped', total: 89.99 }

// Order lookup by ID (query PK = 'ORDER#abc123')
{ PK: 'ORDER#abc123', SK: 'DETAILS', userId: 'alice', total: 89.99 }

// Product
{ PK: 'PRODUCT#prod456', SK: 'DETAILS', name: 'Headphones', price: 79.99 }

// GSI1 for email lookups
{ PK: 'USER#alice', SK: 'PROFILE', GSI1PK: 'EMAIL#alice@example.com', GSI1SK: 'USER#alice' }

Table Design

const { DynamoDBClient } = require('@aws-sdk/client-dynamodb')
const { DynamoDBDocumentClient, PutCommand, QueryCommand, GetCommand } = require('@aws-sdk/lib-dynamodb')

const ddb = DynamoDBDocumentClient.from(new DynamoDBClient({}))

// Get user by ID
async function getUser(userId) {
  const { Item } = await ddb.send(new GetCommand({
    TableName: 'MainTable',
    Key: { PK: `USER#${userId}`, SK: 'PROFILE' },
  }))
  return Item
}

// Get user's orders (sorted by date desc)
async function getUserOrders(userId, lastKey) {
  const { Items, LastEvaluatedKey } = await ddb.send(new QueryCommand({
    TableName: 'MainTable',
    KeyConditionExpression: 'PK = :pk AND begins_with(SK, :prefix)',
    ExpressionAttributeValues: {
      ':pk': `USER#${userId}`,
      ':prefix': 'ORDER#',
    },
    ScanIndexForward: false,  // Descending order
    Limit: 20,
    ExclusiveStartKey: lastKey,
  }))
  return { orders: Items, nextKey: LastEvaluatedKey }
}

// Get user by email (via GSI)
async function getUserByEmail(email) {
  const { Items } = await ddb.send(new QueryCommand({
    TableName: 'MainTable',
    IndexName: 'GSI1',
    KeyConditionExpression: 'GSI1PK = :email',
    ExpressionAttributeValues: { ':email': `EMAIL#${email}` },
  }))
  return Items?.[0]
}

Write Operations

// Create user (with uniqueness check)
async function createUser(userId, email, name) {
  // Conditional write — fails if PK/SK already exists
  await ddb.send(new PutCommand({
    TableName: 'MainTable',
    Item: {
      PK: `USER#${userId}`,
      SK: 'PROFILE',
      GSI1PK: `EMAIL#${email}`,
      GSI1SK: `USER#${userId}`,
      name,
      email,
      createdAt: new Date().toISOString(),
      entityType: 'USER',
    },
    ConditionExpression: 'attribute_not_exists(PK)',
  }))
}

// Transactional write (atomic multi-item)
const { TransactWriteCommand } = require('@aws-sdk/lib-dynamodb')

await ddb.send(new TransactWriteCommand({
  TransactItems: [
    {
      Put: {
        TableName: 'MainTable',
        Item: { PK: `ORDER#${orderId}`, SK: 'DETAILS', ...orderData },
        ConditionExpression: 'attribute_not_exists(PK)',
      },
    },
    {
      Update: {
        TableName: 'MainTable',
        Key: { PK: `USER#${userId}`, SK: 'PROFILE' },
        UpdateExpression: 'SET orderCount = orderCount + :one',
        ExpressionAttributeValues: { ':one': 1 },
      },
    },
  ],
}))

DynamoDB Streams + Lambda

// Process changes in real-time
export const handler = async (event) => {
  for (const record of event.Records) {
    if (record.eventName === 'INSERT') {
      const newItem = unmarshall(record.dynamodb.NewImage)
      if (newItem.entityType === 'ORDER') {
        await processNewOrder(newItem)
      }
    }
  }
}

Capacity Planning

// On-demand mode: pay per request (good for variable traffic)
// Provisioned mode: pre-allocate for predictable traffic

// Monitor consumed capacity
const { ConsumedCapacity } = await ddb.send(new QueryCommand({
  TableName: 'MainTable',
  ReturnConsumedCapacity: 'TOTAL',
  // ...
}))
console.log('RCUs consumed:', ConsumedCapacity.CapacityUnits)