Single-Table DynamoDB Design for Multi-Tenant SaaS

DynamoDB is incredibly powerful when used correctly, but its pricing model punishes poor design choices harshly. We've seen 100x cost reductions by switching from naive multi-table designs to optimized single-table patterns. This post shares the techniques that make this possible.

Why Single-Table Design?

Traditional relational thinking leads developers to create one DynamoDB table per entity type. This approach has several problems:

Multiple round trips: Loading related data requires multiple queries
No transactions across tables: Atomic operations limited to single table
Wasted provisioned capacity: Each table needs its own capacity allocation
Higher operational overhead: More tables to monitor and maintain

Single-table design stores all entity types in one table, using carefully designed keys to enable efficient access patterns.

Key Design Principles

1. Start with Access Patterns

Before designing your table, list every access pattern your application needs:

Access Patterns for Theatre Catalogue:
1. Get show by ID
2. List all shows for a venue
3. List all venues in a region
4. Get all performances for a show
5. List upcoming performances across all venues
6. Get show with all its reviews
7. List top-rated shows by genre

2. Design Keys for Access Patterns

Partition key (PK) and sort key (SK) should support your most critical queries:

# Key structure convention
PK: ENTITY#identifier
SK: METADATA | RELATED_ENTITY#identifier

# Examples
PK: SHOW#phantom-opera     SK: METADATA
PK: SHOW#phantom-opera     SK: VENUE#hm-theatre
PK: SHOW#phantom-opera     SK: PERFORMANCE#2026-03-15-19:30
PK: SHOW#phantom-opera     SK: REVIEW#rev-123

PK: VENUE#hm-theatre       SK: METADATA
PK: VENUE#hm-theatre       SK: SHOW#phantom-opera

PK: REGION#london          SK: VENUE#hm-theatre

3. Use Overloaded GSIs

Global Secondary Indexes enable additional access patterns:

# GSI1: Inverted index for reverse lookups
GSI1PK: VENUE#hm-theatre    GSI1SK: SHOW#phantom-opera
GSI1PK: GENRE#musical       GSI1SK: RATING#4.8#SHOW#phantom-opera

# GSI2: Time-based queries
GSI2PK: PERFORMANCES        GSI2SK: 2026-03-15T19:30:00Z#perf-123
GSI2PK: REVIEWS#2026-01     GSI2SK: 2026-01-15T10:30:00Z#rev-456

Multi-Tenant Isolation

For SaaS applications, tenant isolation is critical. Here's our pattern:

Tenant-Prefixed Keys

# Key structure with tenant isolation
PK: TENANT#acme#SHOW#show-123
SK: METADATA

# This ensures:
# 1. All tenant data is co-located
# 2. Cross-tenant access is impossible without explicit key
# 3. Easy to query all data for a tenant

Tenant Aggregation Queries

// Query all shows for a tenant
const params = {
  TableName: 'Catalogue',
  KeyConditionExpression: 'PK BEGINS_WITH :pk',
  ExpressionAttributeValues: {
    ':pk': 'TENANT#acme#SHOW#'
  }
};

// Query with filter for specific entity type
const params = {
  TableName: 'Catalogue',
  KeyConditionExpression: 'PK = :pk AND SK BEGINS_WITH :sk',
  ExpressionAttributeValues: {
    ':pk': 'TENANT#acme',
    ':sk': 'SHOW#'
  }
};

Efficient Query Patterns

Collection Pattern

Fetch an entity and its related items in a single query:

// Get show with all its performances and reviews
const params = {
  TableName: 'Catalogue',
  KeyConditionExpression: 'PK = :pk',
  ExpressionAttributeValues: {
    ':pk': 'SHOW#phantom-opera'
  }
};

// Result includes:
// - METADATA item (the show itself)
// - All PERFORMANCE# items
// - All REVIEW# items

// Parse results by SK prefix
const parseResults = (items) => ({
  show: items.find(i => i.SK === 'METADATA'),
  performances: items.filter(i => i.SK.startsWith('PERFORMANCE#')),
  reviews: items.filter(i => i.SK.startsWith('REVIEW#'))
});

Sparse GSI Pattern

Only index items that need to be queried via GSI:

// Only featured shows have GSI2PK
{
  PK: 'SHOW#phantom-opera',
  SK: 'METADATA',
  title: 'The Phantom of the Opera',
  featured: true,
  GSI2PK: 'FEATURED',      // Only set if featured
  GSI2SK: 'RATING#4.8'
}

{
  PK: 'SHOW#small-show',
  SK: 'METADATA',
  title: 'Small Production',
  featured: false
  // No GSI2PK - not indexed
}

// Query featured shows only
const params = {
  TableName: 'Catalogue',
  IndexName: 'GSI2',
  KeyConditionExpression: 'GSI2PK = :pk',
  ExpressionAttributeValues: {
    ':pk': 'FEATURED'
  }
};

Adjacency List Pattern

Model many-to-many relationships:

// Show belongs to multiple genres
PK: SHOW#phantom-opera    SK: GENRE#musical
PK: SHOW#phantom-opera    SK: GENRE#romance
PK: SHOW#phantom-opera    SK: GENRE#drama

// Genre has multiple shows (GSI)
GSI1PK: GENRE#musical     GSI1SK: SHOW#phantom-opera
GSI1PK: GENRE#musical     GSI1SK: SHOW#les-miserables
GSI1PK: GENRE#musical     GSI1SK: SHOW#wicked

// Query: All shows in a genre
const params = {
  TableName: 'Catalogue',
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :pk',
  ExpressionAttributeValues: {
    ':pk': 'GENRE#musical'
  }
};

Write Patterns for Consistency

Transaction for Related Writes

// Atomically create show and its venue relationship
const params = {
  TransactItems: [
    {
      Put: {
        TableName: 'Catalogue',
        Item: {
          PK: 'SHOW#new-show',
          SK: 'METADATA',
          title: 'New Show',
          venueId: 'venue-123'
        }
      }
    },
    {
      Put: {
        TableName: 'Catalogue',
        Item: {
          PK: 'SHOW#new-show',
          SK: 'VENUE#venue-123',
          GSI1PK: 'VENUE#venue-123',
          GSI1SK: 'SHOW#new-show'
        }
      }
    },
    {
      Update: {
        TableName: 'Catalogue',
        Key: { PK: 'VENUE#venue-123', SK: 'METADATA' },
        UpdateExpression: 'SET showCount = showCount + :inc',
        ExpressionAttributeValues: { ':inc': 1 }
      }
    }
  ]
};

await dynamodb.transactWrite(params);

Optimistic Locking

// Prevent lost updates with version numbers
const updateShow = async (showId, updates, expectedVersion) => {
  try {
    await dynamodb.update({
      TableName: 'Catalogue',
      Key: { PK: 'SHOW#' + showId, SK: 'METADATA' },
      UpdateExpression: 'SET title = :title, version = :newVersion',
      ConditionExpression: 'version = :expectedVersion',
      ExpressionAttributeValues: {
        ':title': updates.title,
        ':newVersion': expectedVersion + 1,
        ':expectedVersion': expectedVersion
      }
    });
  } catch (e) {
    if (e.name === 'ConditionalCheckFailedException') {
      throw new Error('Concurrent modification detected');
    }
    throw e;
  }
};

Cost Optimization Techniques

1. Right-Size Your Capacity

# Use on-demand for unpredictable workloads
BillingMode: PAY_PER_REQUEST

# Use provisioned with auto-scaling for predictable patterns
BillingMode: PROVISIONED
ProvisionedThroughput:
  ReadCapacityUnits: 100
  WriteCapacityUnits: 50

# Auto-scaling configuration
ScalingTarget:
  MinCapacity: 50
  MaxCapacity: 500
  TargetUtilization: 70

2. Minimize GSI Projections

# Project only needed attributes
GlobalSecondaryIndexes:
  - IndexName: GSI1
    Projection:
      ProjectionType: INCLUDE
      NonKeyAttributes:
        - title
        - rating
        # Don't include large attributes like 'description'

3. Use Batch Operations

// Batch reads - up to 100 items
const results = await dynamodb.batchGet({
  RequestItems: {
    'Catalogue': {
      Keys: showIds.map(id => ({
        PK: 'SHOW#' + id,
        SK: 'METADATA'
      }))
    }
  }
});

// Batch writes - up to 25 items
await dynamodb.batchWrite({
  RequestItems: {
    'Catalogue': items.map(item => ({
      PutRequest: { Item: item }
    }))
  }
});

4. Compress Large Attributes

import { gzip, gunzip } from 'zlib';
import { promisify } from 'util';

const gzipAsync = promisify(gzip);
const gunzipAsync = promisify(gunzip);

// Compress before write
const item = {
  PK: 'SHOW#phantom-opera',
  SK: 'METADATA',
  title: 'The Phantom of the Opera',
  description_compressed: await gzipAsync(
    Buffer.from(longDescription)
  )
};

// Decompress after read
const description = (await gunzipAsync(
  item.description_compressed
)).toString();

Monitoring and Debugging

Key Metrics to Watch

ConsumedReadCapacityUnits: Are you hitting capacity limits?
ThrottledRequests: Sign of under-provisioning
SuccessfulRequestLatency: P50 should be under 10ms
ReturnedItemCount vs ScannedCount: Inefficient queries scan more than they return

Query Analysis

// Enable query metrics
const result = await dynamodb.query({
  ...params,
  ReturnConsumedCapacity: 'INDEXES'
});

console.log('Consumed capacity:', result.ConsumedCapacity);
console.log('Scanned count:', result.ScannedCount);
console.log('Returned count:', result.Count);

// Warning signs:
// - ScannedCount >> Count (filtering in DynamoDB, not keys)
// - High ConsumedCapacity for small results (over-scanning)

Migration Strategy

Moving from multi-table to single-table:

Map access patterns: Document every query your application makes
Design new schema: Create PK/SK/GSI structure that supports all patterns
Dual-write period: Write to both old and new tables
Backfill: Migrate historical data with batch writes
Validate: Compare query results between old and new
Switch reads: Point application to new table
Decommission: Stop writes to old tables, then delete

Key Takeaways

Start with access patterns: Design keys for your queries, not your entities
Use composite keys: PK + SK enable powerful query patterns
Overload GSIs: One GSI can serve multiple access patterns
Think in collections: Fetch related data in single queries
Optimize projections: Only index and return what you need

Single-table design requires upfront investment in understanding your access patterns, but the payoff in performance and cost is substantial. We've seen 100x cost reductions and significant latency improvements by applying these patterns.