# DynamoDB — Part 2: Single-Table Design, Indexes and Streams

## 1. Start From Access Patterns

Relational design starts from entities and normalizes; DynamoDB design starts from the **list of queries** the application will run, then shapes keys so each query is a GetItem or Query.

Example — an e-commerce app:

| # | Access pattern | Key design |
|---|---|---|
| 1 | get customer profile | PK=`CUSTOMER#<id>`, SK=`PROFILE` |
| 2 | list a customer's orders, newest first | PK=`CUSTOMER#<id>`, SK begins_with `ORDER#` (SK=`ORDER#<iso-date>#<orderId>`) |
| 3 | get an order with its items | PK=`ORDER#<orderId>`, SK=`META` and SK=`ITEM#<n>` (one Query) |
| 4 | find an order by id alone | (3) covers it |
| 5 | list orders by status for the warehouse | **GSI1**: GSI1PK=`STATUS#<status>`, GSI1SK=`<iso-date>` |
| 6 | look up a customer by email | **GSI2**: GSI2PK=`EMAIL#<email>` |

## 2. Single-Table Design

Store **different entity types in one table** with generic key names (`PK`, `SK`, `GSI1PK`, …) and a `type` attribute. Items that are read together share a partition key, so one Query returns a whole "aggregate" — the DynamoDB equivalent of a join, precomputed at write time:

```
PK               SK                         type       attributes
CUSTOMER#42      PROFILE                    customer   name, email, GSI2PK=EMAIL#ana@x.io
CUSTOMER#42      ORDER#2025-06-12#o-9       orderref   total, status
ORDER#o-9        META                       order      customerId, status, GSI1PK=STATUS#PLACED, GSI1SK=2025-06-12
ORDER#o-9        ITEM#1                     item       sku, qty, price
ORDER#o-9        ITEM#2                     item       ...
```

**Pros**: fewest requests per page, one table to provision/monitor/back up. **Cons**: harder to read and evolve, requires knowing access patterns, analytics need export (S3/Athena) or streams. Many teams use a **pragmatic middle ground**: one table per bounded context/service, single-table inside it.

## 3. Secondary Indexes

- **GSI**: any attribute as partition/sort key; **sparse** — only items that have the GSI key attributes appear (great for "orders awaiting shipment": only those items carry `GSI1PK`).
- **Projections**: `KEYS_ONLY`, `INCLUDE` (chosen attributes) or `ALL` — every projected byte is written again (cost).
- GSIs are updated **asynchronously** (eventually consistent) and can **throttle the base table** if under-provisioned.
- Up to 20 GSIs per table; adding one later triggers a backfill.

## 4. Patterns

- **Adjacency list** for many-to-many (users ↔ groups): items `USER#1 / GROUP#7` and the inverse via a GSI.
- **Composite sort keys** for hierarchical queries: `SK = COUNTRY#DE#CITY#Berlin#STORE#12` → `begins_with` at any level.
- **Version history**: `SK = v0` (latest) + `v1, v2…` written in a transaction.
- **Idempotency / dedup** items with TTL.
- **Write sharding** for hot keys (Part 1 Under the Hood).

## 5. DynamoDB Streams

A **stream** records every item-level change (new/old images) for 24 hours, ordered per partition key. Consumers (Lambda event source mapping, Kinesis adapter) build:

- **materialized views / read models** in another table or OpenSearch (CQRS — `distributed-systems/message-brokers/11-event_driven_architecture`),
- **event publishing** to EventBridge (a native **outbox**: write once, the stream guarantees the event),
- **aggregations** (counters, leaderboards),
- **replication/audit** trails.

**Global tables** use streams internally for multi-region, multi-active replication with last-writer-wins conflict resolution (`databases/scaling/04-regional_partitioning`).

## 6. When DynamoDB Is the Wrong Choice

- Ad-hoc querying, reporting, complex joins → relational DB or analytics (export to S3 + Athena).
- Unknown or rapidly changing access patterns early in a product.
- Large items (> 400 KB) → store in S3, keep a pointer.
- Strong multi-item consistency across many entities beyond transactions' limits.

## Under the Hood: How Global Secondary Indexes Are Maintained

A GSI is physically another partitioned table. When you write an item, DynamoDB's request router first commits the write to the base table's partition (quorum across AZs), then **asynchronously propagates** the change to each GSI whose key attributes are present — inserting the new projection and deleting the old one if the GSI key changed. Propagation is usually milliseconds but not transactional, which is why GSI reads are always eventually consistent and why a read-after-write via a GSI can miss a just-written item. If a GSI's partition is throttled (hot GSI key, insufficient provisioned WCU), DynamoDB applies **back-pressure to the base table writes** so the index can't fall arbitrarily behind — a classic surprise where "the table" throttles because of one badly designed index.

![Single-table design stores customer profiles, order references, orders and order items under shared partition keys so one query returns an aggregate; sparse GSIs serve lookups by status and email; streams feed Lambda for read models and events](diagrams/02-single-table.svg)

## Common Pitfalls (Part 2)

1. **Designing tables like relational schemas** — one table per entity and "joins" in code.
2. **Scans + filters** in request paths.
3. **Low-cardinality partition keys** (`status`, `country`) — hot partitions.
4. **Projecting ALL into every GSI** — doubled write costs.
5. **Assuming GSI reads are consistent** right after writes.
6. **No plan for analytics** — use exports/streams, not table scans.

## Quick Reference (Part 2)

```
list access patterns → design PK/SK/GSIs so each is Get/Query
single-table: generic keys, type attr, co-locate data read together
GSI: sparse, projections, eventually consistent, can throttle base table
streams: 24 h change log → Lambda → read models / EventBridge / aggregates · global tables = multi-region