DynamoDB Single-Table Design & Access Patterns Cheat Sheet

Complete engineering guide to Amazon DynamoDB Single-Table Design: mastering PK, SK, and GSI modeling, overloaded composite key item collections, and single-digit millisecond query access patterns.

---

1. The Core Primitives: PK, SK, and GSI

In Amazon DynamoDB, PK, SK, and GSI are the fundamental primitives used to model relational concepts into a single NoSQL table while maintaining single-digit millisecond query performance.

Interactive Architecture Diagram
100%Fit100%
Synthesizing vector architecture diagram...

1. PK (Partition Key)

• Definition: The primary attribute (hash attribute) that DynamoDB uses to determine the physical partition where an item is stored.
• How it works: DynamoDB hashes the PK value to distribute data evenly across underlying storage partitions.
• Single-Table Usage: Instead of using entity-specific names like UserId or OrderId, a generic PK column stores namespaced identifiers (e.g., USER#123 or ORDER#456). Items with the same PK are co-located on the same physical partition.

2. SK (Sort Key)

• Definition: The secondary attribute in a composite primary key (hash-and-range key), used in conjunction with the PK.
• How it works: All items sharing the same PK are physically sorted by their SK values in storage. This enables efficient range queries (BEGINS_WITH, BETWEEN, <, >).
• Single-Table Usage: A generic SK column stores entity subtypes or hierarchical data (e.g., PROFILE, ADDRESS#1, or timestamps like 2026-09-15T18:00:00Z), allowing you to fetch a parent item and its children in a single query.

3. GSI (Global Secondary Index)

• Definition: An auxiliary index that has its own independent Partition Key and Sort Key, separate from the base table's primary key.
• How it works: GSIs replicate and project data asynchronously into a separate index structure. Because they have unique keys, they allow you to query data using entirely different access patterns than the main table.
• Single-Table Usage: In Single-Table Design, generic GSI keys (such as GSI1PK and GSI1SK) are overloaded across multiple different entity types to support alternative lookup paths (e.g., looking up a user by their email address rather than their internal ID) with $O(1)$ single-partition efficiency.

---

2. Entity Overloading & Item Collections

By co-locating multiple relational entities (e.g., Users, Orders, and Order Items) within the same item collection (PK = USER#<id>), a client can query the user and all their recent orders in a single sub-5ms network round-trip.

---

3. High-Frequency Single-Table Access Patterns

Access Pattern 1: Fetch User Profile

typescriptCopy
const result = await dynamoClient.send(new GetItemCommand({
  TableName: "ProductionCoreTable",
  Key: {
    PK: { S: "USER#101" },
    SK: { S: "PROFILE" }
  }
}));

• Performance: Exact primary key lookup ($O(1)$) targeting a single partition.
• Cost: 1 RCU (Strongly Consistent) or 0.5 RCU (Eventually Consistent) for items $\le 4\text{ KB}$.

Access Pattern 2: Fetch User and All Recent Orders in 1 Round-Trip

typescriptCopy
const result = await dynamoClient.send(new QueryCommand({
  TableName: "ProductionCoreTable",
  KeyConditionExpression: "PK = :pk AND SK BEGINS_WITH(:prefix)",
  ExpressionAttributeValues: {
    ":pk": { S: "USER#101" },
    ":prefix": { S: "ORDER#2026" }
  }
}));

• Performance: Reads contiguous sorted rows from physical storage in a single sequential seek.
• Advantage: Zero SQL joins, zero relational locking, predictable single-digit millisecond latency.

Access Pattern 3: Look Up Order by Order ID (Inverted GSI Query)

typescriptCopy
const result = await dynamoClient.send(new QueryCommand({
  TableName: "ProductionCoreTable",
  IndexName: "GSI1",
  KeyConditionExpression: "GSI1PK = :orderId",
  ExpressionAttributeValues: {
    ":orderId": { S: "ORDER#9901" }
  }
}));

• Performance: Reverses the relationship without knowing the parent UserId. Asynchronous replication lag from base table to GSI is typically $< 10\text{ ms}$.

---

4. Operational Invariants & Scaling Rules

Core Architecture Rules:

1. Key Salting for High-Write Hotspots: For ultra-popular items (e.g. flash sales or celebrity accounts), append a deterministic pseudo-random salt suffix: PK = CELEB#101#salt_0..9.
2. Avoid High-Selectivity FilterExpression: FilterExpression runs after DynamoDB reads the data from the partition, consuming RCUs for discarded rows. Model alternate access patterns with dedicated GSIs instead.
3. Sparse Indexes for Efficient Queuing: Items that omit a GSI's partition key attribute are not indexed by DynamoDB. Use this to create sparse indexes (e.g. only indexing unfulfilled orders: UNFULFILLED#YES).
4. Sub-millisecond Reads with DAX: Front read-heavy DynamoDB tables with Amazon DynamoDB Accelerator (DAX) to serve item-cache queries in microsecond latency with zero WCU/RCU consumption.

---

5. Architectural Cross-References

• Database Sharding & Partition Keys Deep Dive
• Distributed Caching Patterns & DAX
• Change Data Capture (CDC) & DynamoDB Streams
• Cloud Database Decision Matrix & Trade-Offs