Who is Siddhant Deval?

Siddhant Deval is a Senior Full-Stack Engineer with 4+ years of experience building production-grade SaaS platforms — including a workforce management system scaled to 10k+ users with 1k+ daily active users, a centralised React SDK powering 3 flagship 3D design platforms, and cross-border HRIS portals for international health NGOs.

What technologies does he specialize in?

He specialises in React, Next.js, TypeScript, Node.js, Express, PostgreSQL, Redis, GraphQL, and AWS. He also has deep expertise in frontend performance (bundle optimisation, SSR/ISR, Core Web Vitals), observability (Sentry), containerisation (Docker), and CI/CD pipelines (GitHub Actions, CircleCI).

What kind of web development services does he offer?

He offers end-to-end full-stack engineering — from high-performance React & Next.js frontends with SSR/ISR and Core Web Vitals optimisation, to scalable backend APIs with PostgreSQL, Redis, and GraphQL, to cloud infrastructure on AWS and DigitalOcean with full CI/CD automation.

He is based in New Delhi, India, and works remotely with clients globally — including international health NGOs in Zambia and Nepal, a Singapore-based 3D design platform, and a Caribbean resort workforce system.

Back/

Siddhant Deval· Sep 11, 2026

backendAdvanced25 min read

DynamoDB Data Modeling: Access-Pattern-First Design

Engineers who model DynamoDB entity-first always retrofit key schemas and pay the migration tax — a full table scan and rewrite to change key design after data exists in production. This article covers access-pattern enumeration methodology, generic key overloading, composite sort keys, Adjacency List for many-to-many relationships, overloaded GSIs, sparse indexes, GSI vs. LSI tradeoffs (and the 10 GB item collection trap), and DynamoDB's RCU/WCU cost taxonomy.

Series·Part 7 of 13

AWS Serverless Engineering: Lambda to Production

Previous ChapterAppSync Advanced: Pipeline Resolvers, Subscriptions & Multi-Auth

Next ChapterDynamoDB at Scale: Partition Internals, GSI Backpressure & ACID Transactions

DynamoDB Data Modeling: Access-Pattern-First Design

Every AWS primitive is a tradeoff surface, not a feature toggle. DynamoDB's partition key is not a primary key in the relational sense — it is a routing decision that determines which storage node handles your request, and changing it after data exists means rewriting every item in the table. Engineers who model entities first and derive access patterns second always discover a query they forgot three months into production — and pay the migration tax: a full table scan, a rewrite script, and a production freeze window to swap key schemas. This article covers the discipline that eliminates that tax: enumerate every query before writing any key schema.

Prerequisite: This article builds on Document & NoSQL Databases: MongoDB & DynamoDB in Production, which covers the embed vs reference decision, basic single-table concepts, and DynamoDB Streams introduction. Those foundations are not repeated here.

1. Access-Pattern Enumeration — Design Entry Point

The correct entry point for DynamoDB schema design is the API contract, not the data model. Before writing a single attribute name, you must enumerate every read operation the system will perform — including the ones that come from reporting, audit, admin tooling, and mobile clients with different query needs.

1.1 The Enumeration Method

Step 1: List every entity in the system
  - User, Order, OrderItem, Product, Category, Review, Shipment

Step 2: For each entity, write every query in plain English
  - Get user by userId
  - Get all orders for a userId
  - Get order detail by orderId
  - Get all items in an order
  - Get all orders in PENDING status (for the fulfillment queue)
  - Get all orders placed in the last 24 hours (for the dashboard)
  - Get all reviews for a productId
  - Get the 10 most recent reviews for a productId

Step 3: Assign key slot to each query
  - "Get user by userId" → PK: USER#userId, SK: PROFILE
  - "Get all orders for a userId" → PK: USER#userId, SK: begins_with ORDER#
  - "Get order detail by orderId" → PK: ORDER#orderId, SK: METADATA
  - "Get all items in an order" → PK: ORDER#orderId, SK: begins_with ITEM#
  - "Get all orders in PENDING status" → GSI1: PK: STATUS#pending, SK: orderId
  - "Get all reviews for a productId" → PK: PRODUCT#productId, SK: begins_with REVIEW#

Step 4: Add entities to the key assignments
  - New query cannot be served by existing key slots → revise the schema
  - New query can be served by adding a sparse GSI → add carefully (write amplification cost)
  - New query requires a full table scan → acceptable only for infrequent admin/batch use

1.2 The Broken Pattern — Entity-First Modeling

TYPESCRIPT

// ❌ Entity-first modeling — three months of naive GSI accumulation
// Table design after modeling entities independently:
//   users:   PK = USER#userId,    SK = USER#userId
//   orders:  PK = ORDER#ordId,    SK = ORDER#ordId

// "Need orders by userId" → add GSI1: userId-index
// "Need orders by status" → add GSI2: status-index
// "Need open orders sorted by created date" → add GSI3: status-date-index
// "Need orders by region" → add GSI4: region-index
// Six months: 5 GSIs
//   Write amplification: 1 base table + 5 GSIs = 6× WCU per order write
//   Bill at 10K orders/day: 60K WCU-writes/day just for write amplification
//   Redesign migration: 4M existing records × full-table-scan + rewrite

// ✅ Access-pattern-first — 1 table, 1 GSI, all queries served

Architectural Note

Cost Nuance Hint: Why does 1 order write suddenly consume 6× write capacity? In DynamoDB, each secondary index operates as an independent physical storage partition. Every write to the base table replicates asynchronously to every GSI projecting that item, billing separate WCUs per 1 KB block per index. If the fundamental unit mechanics (RCU/WCU thresholds, 4 KB block rounding, or On-Demand vs. Provisioned pricing) are unfamiliar, reference Section 8: DynamoDB Cost Taxonomy at the end of this guide.

2. Generic Key Overloading — One Table, Many Entity Types

DynamoDB's single-table design stores multiple entity types in one table by using generic key attribute names (PK, SK) that hold different semantic values for different entity types.

2.1 The Key Schema

TYPESCRIPT

// All entity types share the same PK/SK attribute names
// The value prefix encodes the entity type and enables sort key range queries

// Key schema (TypeScript DynamoDB Document Client types):
interface BaseItem {
  PK: string    // Partition key — entity type + identifier (e.g., "USER#u42")
  SK: string    // Sort key — entity type or relationship (e.g., "PROFILE" or "ORDER#2026-01-15#o99")
  // Optional GSI keys (set only on items that should appear in that GSI)
  GSI1PK?: string
  GSI1SK?: string
  entityType: string  // Always set — enables filtering by entity type within a partition
}

// User profile item
// Query: "Get user by userId" → GetItem(PK=USER#u42, SK=PROFILE)
const userItem = {
  PK: 'USER#u42',
  SK: 'PROFILE',
  entityType: 'USER',
  email: 'alice@example.com',
  name: 'Alice',
  tier: 'PRO',
  createdAt: '2026-01-10T12:00:00Z',
}

// Order item — stored under user's partition
// Query: "Get all orders for userId" → Query(PK=USER#u42, SK begins_with ORDER#)
const orderUnderUser = {
  PK: 'USER#u42',
  SK: 'ORDER#2026-01-15#o99',  // ISO date prefix enables chronological sort
  entityType: 'ORDER',
  orderId: 'o99',
  status: 'PENDING',
  total: 149.99,
}

// Order item — also stored under its own partition for order-direct access
// Query: "Get order detail by orderId" → GetItem(PK=ORDER#o99, SK=METADATA)
const orderDirect = {
  PK: 'ORDER#o99',
  SK: 'METADATA',
  entityType: 'ORDER',
  userId: 'u42',
  status: 'PENDING',
  total: 149.99,
  // GSI key: enables "Get all orders in PENDING status" query
  GSI1PK: 'STATUS#PENDING',
  GSI1SK: '2026-01-15T12:00:00Z#o99', // ISO timestamp + orderId for chronological sort
}

2.2 Composite Sort Keys for Hierarchical Queries

TYPESCRIPT

// Sort key design with date prefix enables time-range queries without a GSI
// Query: "Get orders for user in January 2026"
//   → Query(PK=USER#u42, SK between ORDER#2026-01 and ORDER#2026-02)

const jan15Order = {
  PK: 'USER#u42',
  SK: 'ORDER#2026-01-15#o99', // ISO date → lexicographic sort = chronological sort
}
const jan22Order = {
  PK: 'USER#u42',
  SK: 'ORDER#2026-01-22#o103',
}
const feb01Order = {
  PK: 'USER#u42',
  SK: 'ORDER#2026-02-01#o108',
}

// KeyConditionExpression: SK between 'ORDER#2026-01' and 'ORDER#2026-02'
// Returns jan15Order and jan22Order — feb01Order excluded without a filter expression

3. One-to-Many and One-to-One Patterns

TYPESCRIPT

// One-to-one: same PK, different SK discriminator
// User profile and user settings live in the same partition → single-round-trip fetch with BatchGet
const profile = { PK: 'USER#u42', SK: 'PROFILE', entityType: 'USER', name: 'Alice' }
const settings = { PK: 'USER#u42', SK: 'SETTINGS', entityType: 'USER_SETTINGS', theme: 'dark', locale: 'en-US' }
const billingInfo = { PK: 'USER#u42', SK: 'BILLING', entityType: 'USER_BILLING', stripeId: 'cus_abc' }

// Fetch all three with one TransactGetItems call:
// [GetItem(USER#u42/PROFILE), GetItem(USER#u42/SETTINGS), GetItem(USER#u42/BILLING)]

// One-to-many: parent PK, children prefixed under same partition
// Query: "Get all items in order o99" → Query(PK=ORDER#o99, SK begins_with ITEM#)
const orderMeta = { PK: 'ORDER#o99', SK: 'METADATA', entityType: 'ORDER', total: 149.99 }
const orderItem1 = { PK: 'ORDER#o99', SK: 'ITEM#prod-1', entityType: 'ORDER_ITEM', quantity: 2, unitPrice: 49.99 }
const orderItem2 = { PK: 'ORDER#o99', SK: 'ITEM#prod-2', entityType: 'ORDER_ITEM', quantity: 1, unitPrice: 49.99 }
// One Query call returns metadata + all line items — no joins, no second round trip

Pro Tip & Optimization

Cost Nuance Hint (RCU Aggregation): Co-locating parent and children under the same partition key delivers a massive economic dividend. A single Query sums the sizes of all matched items in the partition before rounding up to the nearest 4 KB boundary (e.g., ten 300-byte items = 3 KB total = 1 RCU). Fetching those same 10 items via separate GetItem or BatchGetItem calls rounds each individual item to 4 KB (costing 10 RCUs). Co-location yields a 90% read discount! See Section 8.1 for the exact boundary math.

4. Adjacency List — Many-to-Many Relationships

The Adjacency List pattern represents many-to-many relationships by storing each relationship as two items — one in each direction.

TYPESCRIPT

// Use case: User ↔ Group membership (many-to-many)
// Query 1: "Get all groups that user u42 belongs to"
//   → Query(PK=USER#u42, SK begins_with GROUP#)
// Query 2: "Get all members of group g7"
//   → Query(GSI1, GSI1PK=GROUP#g7, GSI1SK begins_with USER#)

// Item direction A: user → group (in user's partition)
const membershipA = {
  PK: 'USER#u42',      // User's partition
  SK: 'GROUP#g7',      // Sort key encodes the group
  entityType: 'MEMBERSHIP',
  joinedAt: '2026-01-10T12:00:00Z',
  role: 'member',
}

// Item direction B: group → user (in group's "partition" via GSI)
const membershipB = {
  PK: 'GROUP#g7',      // ← Different from item A: group's partition
  SK: 'USER#u42',      // ← Inverted: user is now the sort key
  entityType: 'MEMBERSHIP',
  joinedAt: '2026-01-10T12:00:00Z',
  role: 'member',
  // GSI key — enables query by group
  GSI1PK: 'GROUP#g7',
  GSI1SK: 'USER#u42',
}

// Write: both items written atomically with TransactWriteItems
// Note: TransactWriteItems incurs a 2× WCU multiplier (two-phase commit) — writing both 1 KB items consumes 4 WCU minimum (see Section 8.2)
await docClient.send(new TransactWriteCommand({
  TransactItems: [
    { Put: { TableName: 'AppTable', Item: membershipA } },
    { Put: { TableName: 'AppTable', Item: membershipB } },
  ]
}))

// Query 1: all groups for user u42
const userGroups = await docClient.send(new QueryCommand({
  TableName: 'AppTable',
  KeyConditionExpression: 'PK = :pk AND begins_with(SK, :prefix)',
  ExpressionAttributeValues: { ':pk': 'USER#u42', ':prefix': 'GROUP#' },
}))

// Query 2: all members of group g7 (via GSI)
const groupMembers = await docClient.send(new QueryCommand({
  TableName: 'AppTable',
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :pk AND begins_with(GSI1SK, :prefix)',
  ExpressionAttributeValues: { ':pk': 'GROUP#g7', ':prefix': 'USER#' },
}))

Adjacency List many-to-many (User ↔ Group): base table items with PK/SK values labeled per direction + GSI1 inverted items enabling the Group→Members query; query direction arrows show both query paths — Adjacency List many-to-many (User ↔ Group): base table items with PK/SK values labeled per direction + GSI1 inverted items enabling the Group→Members query;…

5. Sparse Indexes — Entity-Scoped GSIs Without Write Amplification

A sparse index only indexes items that carry the GSI key attributes. Items without the GSI key attributes are not indexed — they are completely invisible to the GSI and pay zero write amplification.

TYPESCRIPT

// Use case: "Get all orders in PENDING status"
// Only PENDING orders carry GSI1PK — other statuses do not → sparse index on status

// PENDING order → GSI1PK set → appears in GSI → findable via "pending orders" query
const pendingOrder = {
  PK: 'ORDER#o99',
  SK: 'METADATA',
  status: 'PENDING',
  GSI1PK: 'STATUS#PENDING', // ← set for PENDING orders only
  GSI1SK: '2026-01-15T12:00:00Z#o99',
}

// DELIVERED order → GSI1PK NOT set → does NOT appear in GSI → no write amplification
const deliveredOrder = {
  PK: 'ORDER#o88',
  SK: 'METADATA',
  status: 'DELIVERED',
  // GSI1PK and GSI1SK intentionally absent
  // This item is invisible to GSI1 and costs 0 additional WCU for GSI writes
}

// Query: "Get all pending orders, sorted by date"
const pendingOrders = await docClient.send(new QueryCommand({
  TableName: 'AppTable',
  IndexName: 'GSI1',
  KeyConditionExpression: 'GSI1PK = :status',
  ExpressionAttributeValues: { ':status': 'STATUS#PENDING' },
  ScanIndexForward: true, // chronological order via GSI1SK
}))

Pro Tip & Optimization

Sparse indexes are ideal for "find all items of type X that are in state Y" queries — where only a small fraction of items match state Y at any given time. Fulfilled orders, resolved support tickets, and expired sessions are all poor sparse index candidates (too many items qualify). Active/pending/open state items are perfect candidates — low cardinality, high query frequency, low write amplification.

DynamoDB design process flow: Enumerate queries (correct entry point, cyan) → Assign entities to key slots → Identify GSI access patterns → Assign GSI keys; entity-first "wrong entry point" shown in red leading to GSI sprawl — DynamoDB design process flow: Enumerate queries (correct entry point, cyan) → Assign entities to key slots → Identify GSI access patterns → Assign GSI keys;…

6. GSI Overloading — One GSI Serving Multiple Entity Types

An overloaded GSI uses polymorphic GSI1PK/GSI1SK values to serve multiple entity types with different query patterns — reducing the total GSI count and its associated write amplification.

TYPESCRIPT

// GSI1 serves TWO completely different queries:
// Query A: "Get all reviews for a productId"
// Query B: "Get all orders in PENDING status"

// Review item — uses GSI1 for product review listing
const review = {
  PK: 'USER#u42',
  SK: 'REVIEW#prod-1#2026-01-15',
  entityType: 'REVIEW',
  rating: 5,
  text: 'Excellent product',
  GSI1PK: 'PRODUCT#prod-1',    // ← Route this entity type through GSI1
  GSI1SK: 'REVIEW#2026-01-15T12:00:00Z',
}

// Order item — also uses GSI1 but with different key semantics
const order = {
  PK: 'ORDER#o99',
  SK: 'METADATA',
  entityType: 'ORDER',
  status: 'PENDING',
  GSI1PK: 'STATUS#PENDING',    // ← Same GSI1, different prefix
  GSI1SK: '2026-01-15T12:00:00Z#o99',
}

// Both queries served by one GSI — not two:
// Reviews:  Query(GSI1, GSI1PK=PRODUCT#prod-1)
// Orders:   Query(GSI1, GSI1PK=STATUS#PENDING)
// FilterExpression: entityType = 'REVIEW' or 'ORDER' if mixed results are a concern

Crucial Requirement

Write amplification scales with GSI count: every item write to the base table triggers one additional write per GSI that indexes that item. At 5 GSIs, an item that appears in all 5 costs 6× WCU per write (1 base + 5 GSI). Model the WCU impact before adding each GSI: WCU_per_write × write_rate × (1 + GSI_count) × item_size_KB.

Example Calculation: Writing 2 KB items at 250 writes/sec to a table with 4 GSIs indexing the items:

Inputs: item_size_KB = 2 (2 WCU/write), write_rate = 250 writes/sec, GSI_count = 4
Calculation: 2 WCU × 250 writes/sec × (1 + 4) = 2,500 WCU/sec
Breakdown: 500 WCU for the base table + 2,000 WCU across the 4 GSIs (80% of total write budget spent on secondary indexes alone).

(For attribute projection impacts like ALL vs. KEYS_ONLY and sparse index savings, see Section 8.3: Secondary Index Multipliers & Projection Tax.)

7. Secondary Index Selection — GSI vs. LSI & The 10 GB Trap

In previous sections, we leveraged Global Secondary Indexes (GSIs) to invert relationship keys (Section 4), filter sparse entities (Section 5), and overload generic attributes for diverse queries (Section 6). However, DynamoDB provides two distinct secondary indexing primitives: Global Secondary Indexes (GSI) and Local Secondary Indexes (LSI).

Choosing between them is not simply a question of query syntax—it is an irreversible architectural decision with critical ramifications for partition scalability, consistency, and table lifecycle.

7.1 Architectural Comparison: GSI vs. LSI

Architectural Dimension	Global Secondary Index (GSI)	Local Secondary Index (LSI)
Partition Key (PK)	Can define any attribute as PK (can differ from base table PK)	Must be identical to base table PK
Sort Key (SK)	Optional; can use any attribute as alternate SK or omit entirely	Mandatory; defines an alternate SK under the same base PK
Creation Lifecycle	Dynamic: Can be created, updated, or deleted online anytime via `UpdateTable`	Immutable: Must be defined at `CreateTable` time; cannot be added or deleted later
Physical Data Placement	Stored on independent, dedicated storage partitions across the fleet	Physically co-located on the same partition storage node as the base item
Read Consistency	Eventually Consistent only (asynchronously replicated from storage nodes)	Supports both Strongly Consistent (`ConsistentRead: true`) and Eventually Consistent
Capacity & Throttling	Independent provisioned RCU/WCU (or On-Demand units); subject to GSI backpressure	Shares base table RCU/WCU pool; no separate capacity provisioning
Item Collection Limit	No limit: Base partition can scale across multiple storage nodes indefinitely	Strict 10 GB ceiling on the sum of all items sharing the same partition key
Index Quota	Up to 20 GSIs per table (soft limit, can be increased via AWS Service Quotas)	Up to 5 LSIs per table (hard limit, cannot be increased)

7.2 The 10 GB Item Collection Trap

The defining architectural hazard of an LSI is the Item Collection Size Limit.

When an LSI is defined on a table, DynamoDB groups all items that share the same partition key value into an Item Collection. This collection includes the base table items plus the projected index items across all LSIs defined on that table.

Storage Layer	Content	Shared Limit
Base Table Items under `PK: USER#u42`	PROFILE, ORDER#2026-001, ORDER#2026-002, … (accumulating)	Yes — counts toward 10 GB
LSI Projections (co-located on same node)	LSI1-SK: 2026-01-10#ORDER (projected copy), LSI1-SK: 2026-01-15#ORDER, …	Yes — counts toward 10 GB
Hard Limit	Total Combined Item Collection Size	MAX 10 GB
Breach consequence	`ItemCollectionSizeLimitExceededException` — writes rejected; partition splits physically blocked for this PK	—

Why the 10 GB Limit is Fatal in Single-Table Design:

Partition Splits Are Prohibited: In a standard DynamoDB table (without LSIs), when a partition key's items exceed 10 GB or traffic exceeds partition throughput thresholds, DynamoDB transparently splits the partition across multiple storage nodes. When an LSI is present, DynamoDB cannot split an item collection across nodes because LSI data must remain strictly co-located on the same node to support strong consistency.
Permanent Write Failure: Once the cumulative size of items under a single PK exceeds 10 GB, DynamoDB immediately fails all subsequent PutItem, UpdateItem, and BatchWriteItem requests for that partition key with:
```
ValidationException / ItemCollectionSizeLimitExceededException:
Item collection size limit exceeded.
```
No In-Place Recovery: You cannot drop the LSI to relieve the table. Because LSIs are immutable, the only recovery path is provisioning a brand-new table without the LSI, migrating the entire dataset via AWS DMS, S3 export/import, or custom pipelines, and cutting over DNS/application configuration.

[!CAUTION] If your application design stores append-only logs, customer order histories, IoT telemetry, or high-volume children under a single entity PK (e.g., PK: TENANT#t1 or PK: ACCOUNT#a12), an LSI is an existential architectural time bomb. The table will function flawlessly in development and staging, only to permanently reject writes for your largest, most valuable enterprise tenants once their historical data accumulates to 10 GB.

7.3 Decision Heuristic: When (If Ever) to Use an LSI

Because of the 10 GB collection limit and the inability to add or remove indexes post-creation, Global Secondary Indexes are the default recommendation for 99% of modern DynamoDB designs.

An LSI should ONLY be chosen if all three of the following conditions are simultaneously true:

Strongly Consistent Reads on an Alternate Sort Key Are Mandatory: Your access pattern cannot tolerate the 10–100ms asynchronous replication lag of a GSI (e.g., real-time inventory ledger reservation where reading stale state causes immediate financial double-allocation).
The Partition Key Has a Mathematically Proven Size Bound: The domain model strictly prevents the item collection from approaching 10 GB. For example, a user profile partition that only ever stores at most 5 setting records totaling under 50 KB throughout the account lifecycle.
The Table Key Schema Will Never Need Index Evolution: You can guarantee that the access pattern will never be retired, renamed, or restructured during the lifespan of the service.

Need alternate query dimension?
  │
```mermaid
flowchart TD
    Q1["Requires Strongly Consistent Read\non alternate key?"]
    Q2["Is item collection under PK\nstrictly bounded < 10 GB forever?"]
    GSI["Use GSI\n(online lifecycle, unlimited partition size)"]
    LSI["LSI is acceptable\n⚠ Warning: permanent table binding"]
    RETHINK["Redesign PK or\naccept eventual consistency with GSI"]

    Q1 -->|NO| GSI
    Q1 -->|YES| Q2
    Q2 -->|YES| LSI
    Q2 -->|NO| RETHINK


> [!TIP]
> If you do operate an LSI in production, monitor collection growth proactively by including `ReturnItemCollectionMetrics: 'SIZE_ESTIMATE'` on all write commands. Configure CloudWatch Alarms on item collection size estimates to detect partitions approaching limits before `ItemCollectionSizeLimitExceededException` crashes your ingestion pipeline.

---

## 8. DynamoDB Cost Taxonomy

Every key modeling choice in single-table design directly maps to DynamoDB's billing dimensions. Understanding the exact arithmetic behind capacity units prevents architectural decisions that look clean on paper but cause explosive cloud bills in production.

> [!NOTE]
> **Pricing Baseline & Regional Variance:** Cost numbers quoted below (e.g., $1.25/M WRU, $0.25/M RRU, $0.00065/WCU-hour) reflect AWS US-East (N. Virginia) standard rates at the time of writing. AWS rates vary by region (typically 20%–30% higher in regions like `ap-south-1` or `eu-central-1`) and are subject to change by AWS. Use these figures as structural architectural equations rather than fixed price quotes, and verify live rates on the official [AWS DynamoDB Pricing](https://aws.amazon.com/dynamodb/pricing/) page when budgeting production systems.

### 8.1 Read Capacity Units (RCU) and Aggregation Rules

DynamoDB measures read throughput in **Read Capacity Units (RCU)** (or Read Request Units in On-Demand mode):
- **1 RCU** = 1 strongly consistent read per second for an item up to **4 KB**.
- **0.5 RCU** = 1 eventually consistent read per second for an item up to **4 KB** (the default for `GetItem`, `Query`, and `Scan`).
- **2 RCU** = 1 transactional read (`TransactGetItems`) per second for an item up to **4 KB**.

| Item Size | Eventual Read (Default) | Strongly Consistent Read | Transactional Read (`TransactGet`) |
| :--- | :--- | :--- | :--- |
| **0.1 KB – 4.0 KB** | 0.5 RCU | 1.0 RCU | 2.0 RCU |
| **4.1 KB – 8.0 KB** | 1.0 RCU | 2.0 RCU | 4.0 RCU |
| **8.1 KB – 12.0 KB** | 1.5 RCU | 3.0 RCU | 6.0 RCU |
| **12.1 KB – 16.0 KB** | 2.0 RCU | 4.0 RCU | 8.0 RCU |

> [!IMPORTANT]
> **The `Query` vs. `GetItem` Aggregation Rule:**
> When issuing `GetItem` or `BatchGetItem`, DynamoDB rounds each item's size up to the next 4 KB boundary individually. When issuing a `Query`, DynamoDB **aggregates the size of all items returned in the result set first**, and then rounds the combined total to the next 4 KB boundary.
>
> *Example:* Fetching 8 order-item rows (500 bytes each):
> - **Via 8 separate `GetItem` calls:** `8 × ceil(0.5 KB / 4 KB) = 8 × 1 = 8 RCU` (eventual: 4 RCU).
> - **Via 1 single-partition `Query`:** `ceil((8 × 0.5 KB) / 4 KB) = ceil(4.0 KB / 4 KB) = 1 RCU` (eventual: 0.5 RCU).
> - **Result:** Single-table partition co-location reduces read capacity consumption by **87.5%**.

### 8.2 Write Capacity Units (WCU) and Transaction Multipliers

DynamoDB measures write throughput in **Write Capacity Units (WCU)** (or Write Request Units in On-Demand mode):
- **1 WCU** = 1 standard write per second (`PutItem`, `UpdateItem`, `DeleteItem`) for an item up to **1 KB**.
- **2 WCU** = 1 transactional write (`TransactWriteItems`) per second for an item up to **1 KB**.

| Item Size | Standard Write (`Put`/`Update`/`Delete`) | Transactional Write (`TransactWrite`) |
| :--- | :--- | :--- |
| **0.1 KB – 1.0 KB** | 1 WCU | 2 WCU |
| **1.1 KB – 2.0 KB** | 2 WCU | 4 WCU |
| **2.1 KB – 3.0 KB** | 3 WCU | 6 WCU |
| **3.1 KB – 4.0 KB** | 4 WCU | 8 WCU |

- **Two-Phase Commit Tax:** `TransactWriteItems` performs two internal operations per item: a prepare/lock step and a commit step. This is why atomic multi-item writes (such as writing both sides of an Adjacency List relationship) inherently cost `2 × items × ceil(size_KB)` in WCUs.
- **Failed Condition Expression Cost:** If a write includes a `ConditionExpression` (e.g., `attribute_not_exists(PK)`) and the condition evaluates to false, DynamoDB **still consumes the full write capacity units** of the attempted write.

### 8.3 Secondary Index Multipliers and Projection Tax

Every Global Secondary Index (GSI) is a physically separate storage table managed by storage nodes downstream from the base table:

Total Consumed WCU = Base Table WCU + Σ (GSI_i WCU for all matching indexes)


1. **Write Replication Multiplier:** When an item is written or updated on the base table, DynamoDB checks every GSI. If the item contains the GSI partition key, DynamoDB replicates the mutation to that GSI asynchronously, charging the corresponding WCU against the GSI's capacity.
2. **The Projection Tax (`ProjectionType`):**
   - `KEYS_ONLY`: Only indexes PK and SK. The projected item is tiny (~100–200 bytes), virtually always consuming only 1 WCU.
   - `INCLUDE`: Indexes PK, SK, and specific named attributes. Consumes WCUs based on the combined size of the projected attributes.
   - `ALL`: Replicates the **entire base item payload** into the GSI. If your base table item is 3.2 KB, writing it consumes 4 WCU on the base table **and 4 WCU on every GSI projecting `ALL`**!
3. **The Sparse Index Dividend:** If an item omits the GSI partition key attribute entirely, DynamoDB bypasses the GSI write replication pipeline. The write to that GSI consumes **0 WCU**. This is why sparse indexes (Section 5) are an essential cost-control mechanism.

### 8.4 Capacity Modes: On-Demand vs. Provisioned Break-Even

| Dimension | On-Demand (`PAY_PER_REQUEST`) | Provisioned Mode (`PROVISIONED`) |
| :--- | :--- | :--- |
| **Pricing Baseline** | ~$1.25 / million write units<br>~$0.25 / million read units | ~$0.00065 / WCU-hr (~$0.47 / WCU-month)<br>~$0.00013 / RCU-hr (~$0.09 / RCU-month) |
| **Idle Cost** | $0.00 | Fixed hourly rate per provisioned capacity unit |
| **Throttling Risk** | Near-zero (auto-scales up to 2× previous peak immediately) | High if traffic spikes exceed provisioned limit + burst pool |
| **Break-Even Threshold** | Best when average utilization is **< 14%** | Cheaper (up to **5×–7×**) when sustained utilization is **> 18%** |

> [!TIP]
> **Production Rule of Thumb:** Start new services on **On-Demand** to baseline actual traffic patterns without guessing capacity or risking throttling. Once traffic profiles stabilize and daily average utilization exceeds 15–20% of peak, switch to **Provisioned Mode with Application Auto Scaling** (targeting 70% utilization) to slash the DynamoDB bill by 60%–75%.

### 8.5 Table Classes: Standard vs. Standard-IA

- **DynamoDB Standard:** Default storage class ($0.25/GB-month storage). Ideal for active workloads where read/write throughput dominates total cost.
- **DynamoDB Standard-IA (Infrequent Access):** $0.10/GB-month storage (**60% cheaper storage**), but read and write requests cost **25% more**.
- **Decision Rule:** Standard-IA reaches break-even when **storage accounts for more than 50% of the total monthly DynamoDB bill** (typical in audit logs, historical order archives, and compliance snapshots).

---

## Summary

| Concept | Rule |
| :--- | :--- |
| Design entry point | Enumerate queries first — every unanswered query revises the key design before adding a GSI |
| Composite sort keys | ISO date prefix enables chronological range queries without a GSI |
| One-to-many | Parent PK + child prefix under same partition → single Query for parent + children |
| Adjacency List | Two items per relationship (inverted PK/SK) + GSI for inverse direction |
| Sparse index | Only indexed items carry GSI key attributes — absent key = zero GSI write cost |
| GSI write amplification | (1 + GSI_count) × WCU per write — model before adding each GSI |
| LSI vs. GSI | Default to GSI; avoid LSI unless strongly consistent queries on alternate SK are mandatory AND partition size is guaranteed <10 GB |
| Cost taxonomy | 1 RCU = 4 KB read; 1 WCU = 1 KB write; Transactions = 2×; Co-located Queries aggregate item sizes |
| Capacity break-even | On-Demand for spiky/idle workloads (<14% avg); Provisioned + Auto Scaling for steady throughput (>18%) |

## What's Next

> In [Part 8: DynamoDB at Scale — Partition Internals, GSI Backpressure & ACID Transactions](/blog/aws-dynamodb-partition-mechanics-indexing-transactions), we go below the key schema to the physical storage model: why per-partition hard limits make table-level capacity scaling ineffective for hot partitions, how an under-provisioned GSI throttles base table writes upstream, and when ACID transactions are worth their 2× WCU cost.

Research & Synthesis Note

This article was developed with AI-assisted deep search, specification cross-referencing, and technical research synthesis.

#DynamoDB#Single-Table Design#Access Patterns#GSI#LSI#Adjacency List#Cost Optimization#AWS#NoSQL

Written by Siddhant Deval

Senior Full-Stack Engineer building high-scale architectures, browser performance engineering systems, and SaaS platforms.

LinkedIn·GitHub·Twitter

Series·Part 7 of 13

AWS Serverless Engineering: Lambda to Production

Curriculum Roadmap

01Lambda Internals: Execution Model, Context Reuse & Cold-Start Root Causes

18 min read

02Lambda at Scale: Concurrency Architecture, ESM & Advanced Runtimes

20 min read

03API Gateway Essentials: Routing, Integrations & Authorization

17 min read

04API Gateway Advanced: VTL Templates, Direct Service Integrations & WAF

20 min read

05AppSync Foundations: Schema, Unit Resolvers & Direct Data Sources

17 min read

06AppSync Advanced: Pipeline Resolvers, Subscriptions & Multi-Auth

20 min read

07DynamoDB Data Modeling: Access-Pattern-First Design

Reading

08DynamoDB at Scale: Partition Internals, GSI Backpressure & ACID Transactions

25 min read

09Messaging Foundations: SQS, SNS & Fan-Out Patterns

17 min read

10EventBridge Architecture: Event Mesh, Pipes & Failure Routing

21 min read

11Serverless Security: IAM Least Privilege, VPC Networking & Secrets Governance

19 min read

12Serverless Observability & FinOps: CloudWatch, X-Ray & Cost Modeling

20 min read

13From Mid-Level to Senior: Production Readiness Checklist for Serverless Systems

18 min read

Previous ChapterAppSync Advanced: Pipeline Resolvers, Subscriptions & Multi-Auth

Next ChapterDynamoDB at Scale: Partition Internals, GSI Backpressure & ACID Transactions

AppSync Foundations: Schema, Unit Resolvers & Direct Data Sources

DynamoDB at Scale: Partition Internals, GSI Backpressure & ACID Transactions

Series·Part 7 of 13

AWS Serverless Engineering: Lambda to Production

Previous ChapterAppSync Advanced: Pipeline Resolvers, Subscriptions & Multi-Auth

Next ChapterDynamoDB at Scale: Partition Internals, GSI Backpressure & ACID Transactions

Revision Takeaways

0/5

DynamoDB schema design starts with queries, not entities — every query that cannot be answered by an existing key pattern revises the key design; a new GSI is the last resort, not the first response.
The Adjacency List stores many-to-many relationships in both directions as separate items — for User-Group membership, create both {PK: USER#x, SK: GROUP#y} and {PK: GROUP#y, SK: USER#x, GSI1PK: GROUP#y} to support inverse queries.
Sparse indexes only index items that carry the GSI attribute — other entity types sharing the base table do not appear in the index and pay no write amplification for writes that omit the GSI key.
Every GSI adds write amplification: N GSIs means (1+N)× WCU per item write; this cost compounds with write throughput and must be modeled before adding GSIs to avoid silent billing surprises.
Avoid Local Secondary Indexes (LSIs) in single-table design unless strongly consistent reads on alternate sort keys are mandatory; LSIs enforce an immutable 10 GB item collection ceiling per partition key that halts writes and cannot be split.