Storage Patterns & Data Modeling

Relational modeling normalizes to reduce redundancy. At scale, joins across shards are expensive or impossible. The alternative: denormalize deliberately, duplicate data across tables optimized for specific queries, and accept eventual consistency between copies.

Read:write ratio drives architecture

Query-first table design (NoSQL)

For DynamoDB/Cassandra, list every access pattern before creating a table:

1. Get user profile by user_id
2. List orders for user_id, sorted by date DESC
3. Get order by order_id
4. Lookup orders by status for admin dashboard

Pattern 1 and 2 might share a table with PK=user_id, SK=order#date. Pattern 3 needs a GSI or separate table with PK=order_id. Pattern 4 might use a GSI on status or export to Elasticsearch for flexible admin search.

# DynamoDB — one table, two access patterns via composite keys
# Pattern: get user profile
#   PK = USER#123, SK = PROFILE
# Pattern: list user orders newest first
#   PK = USER#123, SK = ORDER#2024-06-07T10:00:00#order-456
# Pattern: get order by ID (GSI)
#   GSI1PK = ORDER#456 → full item

item = {
    "PK": f"USER#{user_id}",
    "SK": f"ORDER#{timestamp}#{order_id}",
    "amount": 99.99,
    "status": "shipped",
    "GSI1PK": f"ORDER#{order_id}"  # inverted index for lookup by order_id
}

Fan-out on write (push model)

When a user tweets, write the tweet ID into every follower's timeline cache/table. Read is O(1)—fetch precomputed timeline. Write is O(followers)—expensive for celebrities.

flowchart LR
  Post[User posts tweet] --> TW[Tweet store]
  Post --> F1[Follower 1 timeline]
  Post --> F2[Follower 2 timeline]
  Post --> FN[Follower N timeline]
  Read[User reads feed] --> F1

Write cost: Katy Perry with 100M followers → 100M writes per tweet. At 1 ms/write, that's 27+ hours serially—impossible. Even at 100K writes/sec parallel, 1000 seconds per tweet.

Fan-out on read (pull model)

Store tweets only in the author's outbox. On timeline read, fetch tweets from all followed users, merge-sort by time. Write is O(1); read is O(following) — expensive for users who follow 5,000 accounts.

flowchart LR
  Post[User posts tweet] --> TW[Author outbox]
  Read[User reads feed] --> Q[Query 500 followed users]
  Q --> TW
  Q --> Merge[Merge-sort by timestamp]
  Merge --> Timeline[Rendered timeline]

Hybrid model (Twitter's approach)

Regular users (< celebrity threshold): fan-out on write into Redis timeline sorted sets. Celebrities (> threshold, e.g. 1M followers): skip push; followers merge celebrity tweets at read time.

Celebrity threshold calculation

Define threshold T where push cost exceeds pull cost. If average user follows F accounts and average post rate is P tweets/sec system-wide:

• Push cost for user with F_followers: F_followers writes/post
• Pull cost per read: F_following tweet lookups
• Break-even roughly when F_followers × post_rate ≈ F_following × read_rate

Lambda architecture

Three layers: batch layer (Hadoop/Spark on full dataset, high latency, accurate), speed layer (Storm/Flink on recent data, low latency, approximate), serving layer (merges batch + speed views for queries).

flowchart TB
  Events[Incoming events] --> Batch[Batch layer\nSpark daily job]
  Events --> Speed[Speed layer\nFlink streaming]
  Batch --> Serving[Serving layer\nmerged views]
  Speed --> Serving
  Query[Queries] --> Serving

Pros: batch recompute corrects speed layer errors; mature tooling for both paths.

Cons: duplicate business logic (batch + stream codebases diverge); operational complexity; merge logic in serving layer is fragile.

Kappa architecture

Single stream processing layer (Kafka + Flink/Kafka Streams). Historical reprocessing = replay the log from offset 0 with updated code. No separate batch pipeline.

flowchart TB
  Events[Incoming events] --> Log[Immutable event log\nKafka]
  Log --> Stream[Stream processor\nFlink job v2]
  Stream --> Views[Materialized views\nDB / cache]
  Query[Queries] --> Views
  Log -.->|replay on code change| Stream

Pros: one codebase; replay for bug fixes and new features; simpler mental model.

Cons: replay time proportional to log retention; complex aggregations over years of data may still need batch export to warehouse; stream processor must handle full history volume on redeploy.

Traditional CRUD uses one schema for reads and writes. CQRS splits them: command side validates and persists events/state; query side serves from purpose-built read stores updated asynchronously.

flowchart LR
  Client -->|command| API[Command API]
  API --> WDB[(Write DB\nnormalized)]
  WDB -->|CDC / events| Projector[Projection workers]
  Projector --> RDB1[(Read DB — list view)]
  Projector --> RDB2[(Elasticsearch — search)]
  Client -->|query| QAPI[Query API]
  QAPI --> RDB1
  QAPI --> RDB2

When CQRS makes sense

• Read and write patterns diverge radically (complex writes, many read shapes)
• Read QPS >> write QPS and read models need heavy denormalization
• Different scaling requirements (scale read replicas/Elasticsearch independently)
• Combined with event sourcing for audit and temporal queries

When CQRS is overkill

• Simple CRUD app with similar read/write shapes
• Team < 5 engineers without streaming/CDC experience
• Strong consistency required on reads immediately after writes (CQRS is usually eventual)

// Command side — validate and persist
public OrderId placeOrder(PlaceOrderCommand cmd) {
    Order order = Order.create(cmd.items(), cmd.userId());
    orderRepo.save(order);                    // normalized write DB
    eventBus.publish(new OrderPlaced(order)); // triggers projections
    return order.id();
}

// Query side — denormalized read model (updated by projector)
// Table: orders_by_user — PK user_id, SK created_at, denormalized items + totals
public List<OrderSummary> getOrdersForUser(UserId userId) {
    return readRepo.queryByUser(userId);     // no joins — precomputed view
}

Traditional: UPDATE accounts SET balance = 150 WHERE id = 1 — previous balance lost. Event sourcing: append MoneyDeposited($50), MoneyWithdrawn($20) — full history preserved; current balance = fold(events).

flowchart LR
  Cmd[Command] --> Agg[Aggregate]
  Agg -->|append| ES[(Event Store)]
  ES --> E1[Event 1: AccountOpened]
  ES --> E2[Event 2: MoneyDeposited]
  ES --> E3[Event 3: MoneyWithdrawn]
  ES --> Proj[Projector]
  Proj --> View[(Current state view)]
  ES -->|replay| Agg

Core components

• Event store — append-only log (Kafka topic, EventStoreDB, custom Postgres table)
• Aggregate — domain object rebuilt by folding events; enforces invariants on commands
• Snapshots — periodic state checkpoints to avoid replaying 10K events on every load
• Projections — read models built from event stream (often paired with CQRS)

# Rebuild account state from events
def apply_events(events):
    account = Account.empty()
    for event in events:
        match event.type:
            case "AccountOpened":
                account = Account.open(event.user_id)
            case "MoneyDeposited":
                account = account.deposit(event.amount)
            case "MoneyWithdrawn":
                account = account.withdraw(event.amount)
    return account

# Snapshot every 100 events — load snapshot + events since
def load_account(account_id):
    snapshot, version = snapshot_store.get(account_id)
    new_events = event_store.get_since(account_id, version)
    return apply_events([snapshot] + new_events)

Benefits

• Audit trail — complete history for compliance (finance, healthcare)
• Temporal queries — "what was balance on March 1?"
• Debugging — replay production events in staging
• Multiple projections — build new read models from existing event log

Costs

• Event schema evolution (upcasting, versioning) is non-trivial
• Queries on current state require projections—can't SQL JOIN the event log efficiently
• Storage grows forever unless compaction/archival policy
• Team must think in events, not CRUD—steep learning curve

Horizontal partitioning (sharding)

Split rows across nodes by a shard key. Most common scaling approach for OLTP at billion-row scale.

• Hash partitioning — shard = hash(user_id) % N; even distribution; range queries cross all shards
• Range partitioning — user_id 1–1M on shard 1; efficient range scans; hot spots on latest range
• Consistent hashing — minimal key movement when adding nodes; used in DynamoDB, Cassandra, Redis Cluster

flowchart TB
  Router[Shard router] --> S1[Shard 1\nuser_id hash 0-33%]
  Router --> S2[Shard 2\nuser_id hash 34-66%]
  Router --> S3[Shard 3\nuser_id hash 67-100%]

Vertical partitioning

Split columns or tables by access pattern—hot columns separate from cold BLOBs. Example: users table (id, name, email) + user_profiles (id, bio, avatar_blob). Reads for user list skip heavy BLOB columns.

Functional partitioning

Split by domain/service—users DB, orders DB, inventory DB. Microservices default. Cross-domain queries require API composition or read-side CQRS joins. Independent scaling: orders shard differently than user profiles.

Temporal partitioning

Partition by time—monthly tables, date-keyed S3 prefixes, TimescaleDB hypertable chunks. Ideal for append-only time-series, logs, metrics. Drop old partitions for retention (cheap delete).

-- Temporal partitioning — Postgres declarative partitions
CREATE TABLE events (
    id          BIGSERIAL,
    created_at  TIMESTAMPTZ NOT NULL,
    payload     JSONB
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_06 PARTITION OF events
    FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');

-- Drop retention: detach and drop old partition (instant vs DELETE millions of rows)
DROP TABLE events_2023_01;

Composite partitioning

Combine strategies—e.g. functional (orders service) + horizontal (hash by order_id) + temporal (archive to S3 after 90 days). DynamoDB: PK for entity type + SK for time enables composite access patterns on one table.

Shard key selection checklist

1. High cardinality — avoid skew (don't shard by country if 80% users in US)
2. Co-locate related data — orders and order_items share user_id or order_id shard key
3. Match hottest queries — if 95% queries filter by tenant_id, shard by tenant
4. Plan for growth — consistent hashing or virtual nodes for adding shards without full reshuffle