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.

Synthesizing vector architecture diagram...

Start in the "Amazon DynamoDB Base Table" panel. DynamoDB hashes each item's PK to choose a physical partition: every USER#101 item (profile and both orders) lands in Partition A, stored sorted by SK, while USER#102 hashes to Partition B. One query with PK = USER#101 therefore returns the profile and all orders from a single partition. The dotted arrows show the order items being copied asynchronously into the "Global Secondary Index" panel under a flipped key (GSI1PK = ORDER#…), which answers "which user placed order 9901?" with one lookup. You design the keys around your queries: the base table serves "everything for this user", the GSI serves "find by order", and GSI reads may lag the base table slightly.

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. Microsecond Reads with DAX: Front read-heavy DynamoDB tables with Amazon DynamoDB Accelerator (DAX). A cache hit is served in microseconds and consumes no RCU; a cache miss reads from the table and consumes RCU as usual. Writes pass through DAX to the table and consume WCU as usual. DAX serves eventually consistent reads only (strongly consistent reads go straight to the table), and writes that don't go through DAX (for example replicated writes from another Region) aren't reflected until the cached item expires.

---

5. Capacity Math Drills

Two rules cover every drill below:

• 1 RCU = one strongly consistent read per second of an item up to 4 KB. An eventually consistent read costs half as much. Item size is rounded up to the next 4 KB.
• 1 WCU = one standard write per second of an item up to 1 KB. Item size is rounded up to the next 1 KB.

Drill 1: Eventually Consistent Reads

Drill 2: Standard Writes

---

6. 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