Data Partitioning

Why This Comes Up in Interviews

Partitioning is more fundamental than sharding — it's the STRATEGY, while sharding is one implementation. It appears when:

• "How do you handle 1B+ rows efficiently?" → Partition
• "How do you design DynamoDB's key schema?" → Partition key + sort key
• "How do you avoid hot partitions in Kafka?" → Partition key design
• "How do you optimize time-series queries?" → Time-based partitioning

Key distinction: Partitioning = dividing data by some criteria. Sharding = partitioning across MACHINES. You can partition within one database.

Partitioning vs Sharding vs Table Partitioning

Why it matters: Table partitioning is free optimization (DB handles it). Sharding is an architectural decision with major trade-offs.

Three Dimensions of Partitioning

Horizontal Partitioning (Most Common)

Split by rows: Different rows in different partitions. Same schema everywhere.

Vertical Partitioning

Split by columns: Frequently-accessed columns separated from rarely-accessed ones.

Why: If 95% of queries only need 4 columns, why load 40 columns from disk?

Functional Partitioning

Split by feature/domain: Each bounded context gets its own database.

This is essentially the microservices "database per service" pattern.

Partition Key Design — The DynamoDB Mental Model

DynamoDB's key model is the best framework for thinking about partition design in interviews:

Partition Key (PK): Determines WHICH partition. Must be high-cardinality, evenly distributed. Sort Key (SK): Determines ORDER within a partition. Enables range queries.

Design Patterns

The golden rule: Design so that 95%+ of queries hit a SINGLE partition.

Anti-Patterns (What NOT to Do)

The Hot Partition Problem — Deep Dive

What makes a partition hot: - Celebrity account: one partition key gets 1000x traffic (Justin Bieber's timeline) - Temporal hotspot: "today" partition gets all writes in time-series data - Category skew: "electronics" category has 10x items vs "garden supplies"

Back-of-envelope: - DynamoDB partition limit: ~3000 RCU / 1000 WCU per partition (baseline planning numbers — since 2018-2019, adaptive capacity and burst capacity allow individual partitions to temporarily exceed these limits, so they are no longer hard ceilings) - Celebrity with 100M followers: follower fanout = 1M writes/sec during a post - Single partition handles 1000 WCU → needs 1000 partitions for that ONE user

Solutions by Pattern

Instagram's approach: For celebrity accounts, partition key = user_id + random(0-9). Writes spread across 10 partitions. Reads: 10 parallel queries → merge → return. Works because read fanout (10 queries) is cheap, write hotspot (10,000 WCU on one partition) is impossible.

Secondary Indexes with Partitioned Data

The core problem: Primary data is partitioned by entity_id. But you also need to query by email, timestamp, or category.

DynamoDB Example

• Table: PK = user_id, SK = order_timestamp
• LSI: Sort by order_total (same partition, different sort order)
• GSI: PK = product_id, SK = order_timestamp (query: "all orders for product X")

GSI write amplification: Every write to main table → also writes to GSI partitions. 3 GSIs = 4x write cost.

Partitioning Strategies in Real Systems

PostgreSQL Table Partitioning (Free Performance)

When: Single table > 10M rows with identifiable access patterns (usually time-based).

Common patterns:

Back-of-envelope benefit: - 1B row table, query needs last 7 days - Without partitioning: index scan on 1B rows → seconds - With daily partitions: only 7 partitions scanned, each ~3M rows → milliseconds - Speedup: ~50x for time-bounded queries

Partition Rebalancing

When needed: Partition grows too large, or load becomes uneven.

DynamoDB auto-split: - Partition gets > 10GB or > 3000 RCU → automatically splits into two - No downtime, no application awareness needed - But: too many splits = too many partitions = metadata overhead

Interview Framework

When designing a data model:

Step 1 — Identify access patterns: "The primary queries are [per-user lookups / time-range scans / category filtering]. This tells me the partition key should be [entity that 95%+ queries filter by]."

Step 2 — Choose partition key: "I'd partition by [user_id/device_id/conversation_id] because it's [high cardinality, immutable, matches access pattern]. Sort key would be [timestamp] for range queries within partition."

Step 3 — Hot partition mitigation: "If any entity becomes disproportionately hot, I'd use write sharding — append random suffix to PK, reads do parallel scatter-gather across suffixes."

Step 4 — Secondary access: "For queries not aligned with PK (e.g., 'all orders for product X'), I'd add a [GSI / Elasticsearch index / denormalized table]."

Step 5 — Rebalancing: "I'd use [DynamoDB auto-split / logical partition indirection / over-provisioned partition count] so growth doesn't require manual intervention."

Quick Recall