Section 2: Data Modeling Fundamentals

Cassandra Wide Rows

Master the powerful wide partition pattern! Learn time-series optimization, denormalization strategies, performance characteristics with animations, production examples, and expert-level design patterns.

📖 The Story: The Spreadsheet Everyone Understands

Imagine organizing employee data. You have two choices:

📄 Approach 1: Multiple Narrow Spreadsheets (Traditional)

Employee ID: 12345

Spreadsheet 1: Personal Info

┌──────────┬──────────────┐

│ Column   │ Value        │

├──────────┼──────────────┤

│ Name     │ John Smith   │

│ Email    │ john@co.com  │

└──────────┴──────────────┘

Spreadsheet 2: Address

┌──────────┬──────────────┐

│ Column   │ Value        │

├──────────┼──────────────┤

│ Street   │ 123 Main St  │

│ City     │ NYC          │

└──────────┴──────────────┘

Spreadsheet 3: Phone Numbers

┌──────────┬──────────────┐

│ Column   │ Value        │

├──────────┼──────────────┤

│ Mobile   │ 555-1234     │

│ Home     │ 555-5678     │

└──────────┴──────────────┘

Critical Problems:

Need 3 Spreadsheets: One employee's data split across 3 files
Multiple Lookups: Must open and search 3 different files
No Complete Picture: Can't see all info at once
Slow: Time wasted switching between files

This is like traditional normalized databases!

📊 Approach 2: ONE WIDE Spreadsheet (Wide Rows!)

Single Wide Spreadsheet - ALL DATA IN ONE ROW:

┌─────────┬────────────┬─────────────┬─────────────┬──────┬──────────┬──────────┐

│ Emp ID  │ Name       │ Email       │ Street      │ City │ Mobile   │ Home     │

├─────────┼────────────┼─────────────┼─────────────┼──────┼──────────┼──────────┤

│ 12345   │ John Smith │ john@co.com │ 123 Main St │ NYC  │ 555-1234 │ 555-5678 │

└─────────┴────────────┴─────────────┴─────────────┴──────┴──────────┴──────────┘

          ↑ ONE ROW = ALL EMPLOYEE DATA ↑

Massive Benefits:

Everything in ONE Row: All data together
Single Lookup: Open one file, find everything
Complete Picture: See all info immediately
Lightning Fast: One read operation

This is EXACTLY how wide rows work in Cassandra!

📈 Approach 3: SUPER WIDE Spreadsheet (Time-Series Magic!)

Sensor ID: ABC-123

Temperature Sensor - THOUSANDS of readings in ONE row:

┌─────────┬──────────────────┬──────────────────┬──────────────────┬─────┬──────────────────┐

│ Sensor  │ 2024-01-15 10:00 │ 2024-01-15 10:01 │ 2024-01-15 10:02 │ ... │ 2024-01-15 23:59 │

├─────────┼──────────────────┼──────────────────┼──────────────────┼─────┼──────────────────┤

│ ABC-123 │ 72.3°F           │ 72.4°F           │ 72.5°F           │ ... │ 73.1°F           │

└─────────┴──────────────────┴──────────────────┴──────────────────┴─────┴──────────────────┘

          ↑ ONE PARTITION = THOUSANDS OF TIME-SERIES DATA POINTS! ↑

This ONE Row Contains:

86,400 readings per day (1 per second)
All stored together on same node
Physically sorted by timestamp
Query "10:00-11:00" = slice the row

💡 Wide Row Magic Explained

In Cassandra:
• sensor_id = Partition Key (which spreadsheet)
• Each timestamp = Clustering Column (which column)
• Each reading = Data value

Result: Lightning-fast time-series queries! ⚡

📊 What Are Wide Rows?

Understanding Cassandra's most powerful data organization pattern.

Complete Definition

Wide Row (Wide Partition): A Cassandra partition containing many rows with the SAME partition key but DIFFERENT clustering columns.

Think of it as:

One key (partition key) → Identifies the partition
Many values (clustering columns + data) → Stored in partition
Stored together on same node → Co-located
Read together in single query → Efficient

Example Schema:

PRIMARY KEY (user_id, timestamp)

             ↑             ↑

       Partition Key  Clustering Column

Wide Row for user_id='alice':

Partition: alice

├── alice, 2024-01-01 10:00, "login"

├── alice, 2024-01-01 10:15, "view_page"

├── alice, 2024-01-01 10:30, "add_to_cart"

├── alice, 2024-01-01 10:45, "checkout"

├── alice, 2024-01-01 11:00, "logout"

└── ... (thousands more rows!)

ALL these rows stored together = WIDE PARTITION

Narrow vs Wide Rows: Visual Comparison

📄

Narrow Row Pattern

PRIMARY KEY (order_id)
                                

Structure:

Each order = separate partition
Partition 1: Order-001
Partition 2: Order-002
Partition 3: Order-003

Characteristics:

Each partition: 1 row
Must query multiple partitions
Distributed across many nodes
More coordinator overhead

📊

Wide Row Pattern

PRIMARY KEY (user_id, order_date)
                                

Structure:

One user = one wide partition
alice, 2023-01-15
alice, 2023-03-20
alice, 2023-06-10

Characteristics:

One partition: many rows
Single query gets all orders
Stored together on one node
Minimal coordinator overhead

🔬 Anatomy of a Wide Row

Understanding how wide rows are physically stored and why they're so fast.

Physical Storage Deep Dive

What happens on disk when you write a wide row:

Step-by-Step Storage Process:

1. Partition Key → Hash → Token → Node

  user_id='alice' → Murmur3 hash → token: -3847293847

  → Maps to Node 2

2. All rows with same partition key → Same Node

  Every row with user_id='alice' goes to Node 2

3. Rows sorted by clustering column

  Timestamp ascending (or descending if specified)

4. Stored in ONE SSTable segment

  [Partition: alice]

  ├── 2024-01-01 10:00, "login"

  ├── 2024-01-01 10:15, "view"

  ├── 2024-01-01 10:30, "purchase"

  └── ... (physically sequential on disk!)

5. Sequential disk layout

  All bytes stored contiguously

  No fragmentation, no random seeks

Why This Makes Wide Rows SO Fast:

Single Disk Seek: Find partition once via hash lookup
Sequential Read: All rows read in one pass (not scattered)
No Random I/O: Disk head doesn't jump around
All Data Co-Located: No network hops between nodes
Cache Friendly: Sequential access optimizes OS cache

Performance Result: Querying 1,000 rows from a wide partition is 100x faster than querying 1,000 separate partitions!

⏰ Wide Rows for Time-Series Data

The killer use case that powers Netflix, Discord, and Apple.

The Perfect Pattern: IoT Sensor Data

CREATE TABLE sensor_readings (

  sensor_id UUID,

  reading_time TIMESTAMP,

  temperature DECIMAL,

  humidity DECIMAL,

  pressure DECIMAL,

  PRIMARY KEY (sensor_id, reading_time)

) WITH CLUSTERING ORDER BY (reading_time DESC);

                                          ↑ Newest first!

Wide Row Structure:

sensor_id='ABC-123' (ONE partition contains):

├── 2024-01-15 10:00:00, temp=72.3°F, humidity=45%, pressure=1013mb

├── 2024-01-15 10:00:01, temp=72.4°F, humidity=45%, pressure=1013mb

├── 2024-01-15 10:00:02, temp=72.5°F, humidity=46%, pressure=1013mb

├── 2024-01-15 10:00:03, temp=72.6°F, humidity=46%, pressure=1013mb

└── ... (86,400 readings per day for 1 reading/second!)

All stored together, sorted by time, on ONE node!

Query Examples:

-- Get last hour of readings:

SELECT * FROM sensor_readings

WHERE sensor_id = 'ABC-123'

AND reading_time > '2024-01-15 09:00:00'

AND reading_time < '2024-01-15 10:00:00';

Performance: 2-5ms for 3,600 readings! ⚡

-- Get latest 50 readings (DESC order!):

SELECT * FROM sensor_readings

WHERE sensor_id = 'ABC-123'

LIMIT 50;

Performance: 1-2ms! (reads first 50 rows) 🚀

Why So Fast:

Binary Search to Start: O(log n) to find first timestamp
Sequential Read of Range: O(k) where k = rows to read
All Data Same Node: No network latency
No Coordinator Hops: Direct node access
Sorted Storage: Leverages physical disk layout

Production Time-Series Examples

🎬

Netflix: Viewing History

PRIMARY KEY (user_id, view_timestamp)
                                

Wide Row Design:

user_id = partition key
Each view = clustering row
Sorted DESC (newest first)
"Continue Watching" = LIMIT 10

Scale:
• 200M users worldwide
• 1,000 views per user avg
• = 200 billion rows!

Query time: 2-3ms per user ⚡

💬

Discord: Message History

PRIMARY KEY (channel_id, message_id)
                                

Wide Row Design:

channel_id = partition key
Each message = clustering row
Sorted by message_id (TIMEUUID)
Load messages: sequential scan

Scale:
• 10M active channels
• 10K messages per channel avg
• = 100 billion rows!

Query time: 5-10ms per channel ⚡

📱

Apple: iCloud Photos

PRIMARY KEY (user_id, photo_timestamp)
                                

Wide Row Design:

user_id = partition key
Each photo = clustering row
Sorted DESC (newest first)
Timeline view = sequential scan

Scale:
• 1 billion iCloud users
• 10,000 photos per user avg
• = 10 trillion rows!

Query time: 2-3ms for 50 photos ⚡

🗂️ Wide Rows for Denormalization

Storing all related data together in one partition.

Pattern: Complete User Profile

Traditional Normalized Approach (BAD for Cassandra):

users table (id, name, email)
addresses table (user_id, street, city)
phones table (user_id, type, number)
preferences table (user_id, setting, value)

Problem: Need 4 queries across 4 partitions!

Wide Row Approach (GOOD for Cassandra):

CREATE TABLE user_attributes (

  user_id UUID,

  attribute_name TEXT,

  attribute_value TEXT,

  PRIMARY KEY (user_id, attribute_name)

);

Wide Row for user_id='12345':

12345, "name", "John Smith"

12345, "email", "john@example.com"

12345, "phone_mobile", "555-1234"

12345, "phone_home", "555-5678"

12345, "address_street", "123 Main St"

12345, "address_city", "NYC"

12345, "address_zip", "10001"

12345, "pref_theme", "dark"

12345, "pref_language", "en"

... (all user attributes in ONE partition!)

Benefits:

One Query: Get complete profile with single SELECT
Flexible Schema: Add new attributes without schema changes
All Co-Located: All data on same node
Fast Reads: Single partition scan
Easy Updates: Insert new attribute = new row

⚡ Performance Characteristics

Understanding the speed and trade-offs of wide rows.

📖

Read Performance

Scenario: Get 1,000 rows of data

Narrow Rows (1,000 partitions):
• 1,000 hash lookups
• Random disk seeks
• Coordinator overhead
• Multiple network hops
• Time: 500-1000ms

Wide Rows (1 partition):
• 1 hash lookup
• 1 disk seek
• Sequential read
• Single node
• Time: 5-10ms

100x FASTER! ⚡

✍️

Write Performance

Scenario: Write 1,000 rows of data

Narrow Rows:
• Distributed across nodes
• Parallel writes possible
• Better load distribution
• Best for write-heavy
• Time: 50ms

Wide Rows:
• All to same partition/node
• Sequential in memtable
• Potential bottleneck
• Single node writes
• Time: 20-30ms

Trade-off: Ultra-fast reads vs write distribution

When to Use Wide Rows

Wide rows excel when:

Read-Heavy Workload: 80%+ reads vs writes
Time-Series Data: Append-only or mostly append
Related Data: Logically belongs together
Range Queries: Need to query by time/sequence
Sequential Access: Often read consecutive rows

Avoid wide rows when:

Write-Heavy: More writes than reads
Random Updates: Frequently update random rows
Unbounded Growth: Partition grows forever
Hotspots: One partition gets all traffic

🌍 More Production Examples

How industry leaders leverage wide rows.

🎵 Spotify: Playlist History

CREATE TABLE playlist_tracks (

  playlist_id UUID,

  added_at TIMESTAMP,

  track_id UUID,

  added_by_user_id UUID,

  PRIMARY KEY (playlist_id, added_at)

) WITH CLUSTERING ORDER BY (added_at DESC);

Wide Row Structure:

playlist_id='discover-weekly-12345':

├── 2024-01-15 10:00, track="Song A", user="alice"

├── 2024-01-14 15:30, track="Song B", user="bob"

├── 2024-01-13 08:45, track="Song C", user="alice"

└── ... (all tracks ever added to this playlist!)

Query: Show playlist with newest songs first

SELECT * FROM playlist_tracks

WHERE playlist_id = ?

LIMIT 50;

Result: 50 most recent tracks in 2-3ms! ⚡

🐦 Twitter: User Timeline

CREATE TABLE user_timeline (

  user_id UUID,

  tweet_id TIMEUUID,

  tweet_text TEXT,

  author_id UUID,

  PRIMARY KEY (user_id, tweet_id)

) WITH CLUSTERING ORDER BY (tweet_id DESC);

Why DESC Order Matters:

Latest tweets at START of partition
Homepage shows newest tweets first
No need to scan entire partition
LIMIT 25 = read first 25 rows only
Infinitely fast regardless of timeline size!

💻 GitHub: Repository Commits

CREATE TABLE repo_commits (

  repo_id UUID,

  commit_timestamp TIMESTAMP,

  commit_hash TEXT,

  author TEXT,

  message TEXT,

  PRIMARY KEY (repo_id, commit_timestamp)

) WITH CLUSTERING ORDER BY (commit_timestamp DESC);

Benefits for GitHub:

All commits for repo in one partition
Commit history page loads instantly
Time-range queries super fast
DESC order shows recent commits first
Scales to repos with millions of commits

✅ Best Practices for Wide Rows

Production-tested guidelines for wide row design.

✅

DO This

Keep Partitions Under 100MB: Monitor sizes regularly
Use Time Bucketing: Prevent unbounded growth
Leverage DESC Order: For "latest first" queries
Use LIMIT: Always limit result sets
Monitor Sizes: Track partition metrics
Test with Real Data: Simulate production volumes
Plan for 3-5 Years: Growth projections
Use for Read-Heavy: 80%+ reads ideal

❌

DON'T Do This

Create Unbounded Partitions: Always have limits
Use for Mutable Data: Frequent updates problematic
Skip Size Calculations: Do the math first!
Query Without LIMIT: Can return millions of rows
Ignore Partition Warnings: Cassandra warns for a reason
Use for Write-Heavy: Consider narrow rows instead
Forget About Hotspots: Celebrity problem is real
Deploy Without Testing: Test at scale first

Pre-Deployment Checklist

Before going to production, verify:

☐ Partition Size Math: Calculated max partition size

☐ Growth Projection: Works for next 3-5 years

☐ Time Bucketing: Implemented if data is time-series

☐ Clustering Order: DESC for latest-first queries

☐ LIMIT Clauses: All queries have sensible limits

☐ Load Testing: Tested with production-scale data

☐ Monitoring: Alerts set up for large partitions

☐ TTL Strategy: Old data cleanup planned

⚠️ Wide Row Anti-Patterns

Critical mistakes to avoid.

❌ Anti-Pattern 1: Unbounded Partition Growth

-- BAD: No time bucketing

PRIMARY KEY (user_id, action_timestamp)

Problem After 5 Years:

Active user performs 200 actions/day:

• 200 actions × 365 days × 5 years = 365,000 actions

• Row size: 300 bytes

• Partition size: 365K × 300 = 110 MB!

Problems:

• Exceeds 100MB recommendation

• Queries get slower over time

• Eventually hits Cassandra limits

• Can't delete old data efficiently

✅ SOLUTION: Time Bucketing

-- GOOD: Monthly time buckets

PRIMARY KEY ((user_id, year_month), action_timestamp)

Now After 5 Years:

• 60 partitions (5 years × 12 months)

• Each partition: 6,083 actions

• Partition size: 6K × 300 = 1.8 MB ✓

Benefits:

• Perfect partition size!

• Queries stay fast forever

• Easy to drop old months

• Scalable indefinitely

❌ Anti-Pattern 2: Frequent Random Updates

-- BAD: Using wide rows for frequently updated data

PRIMARY KEY (product_id, attribute_name)

Problem:

E-commerce product with 50 attributes
Prices update every 5 minutes
Inventory updates every 1 minute
Result: Constant updates to same partition
Tombstones pile up, performance degrades

✅ SOLUTION: Use Narrow Rows for Mutable Data

-- GOOD: Separate tables or narrow rows

PRIMARY KEY (product_id)

Or use Redis/other cache for highly mutable data

❌ Anti-Pattern 3: Celebrity/Hotspot Problem

Problem: One partition gets all the traffic

Twitter celebrity with 100M followers:

PRIMARY KEY (user_id, follower_id)

Result:

• ONE partition with 100M rows

• Size: 1GB+ partition!

• ALL traffic to one node

• Node becomes bottleneck

• Queries time out

✅ SOLUTION: Bucket Large Partitions

-- Add bucket to partition key

PRIMARY KEY ((user_id, bucket), follower_id)

where bucket = follower_id % 100

Result:

• 100 partitions × 1M followers each

• Each partition: 10MB

• Distributed across 100 nodes

• Balanced load!

💼 Interview Questions & Expert Answers

Master wide rows for interviews!

1 What is a wide row and when should you use it?

Wide rows store many clustering columns in one partition, perfect for time-series and related data co-location.

▼

Answer:

A wide row (wide partition) is a Cassandra partition containing many rows with the SAME partition key but DIFFERENT clustering columns.

When to Use:

Time-Series Data: IoT sensors, logs, metrics
Related Data: User actions, messages, photos
Read-Heavy Workloads: 80%+ reads
Sequential Access: Range queries common
Latest-First Queries: Use DESC clustering order

Key Benefit: All data co-located on same node enables single disk seek + sequential read = 100x faster than querying multiple partitions!

2 What's the performance difference between wide rows and narrow rows?

Wide rows are 100x faster for reads due to single disk seek and sequential access vs multiple random seeks.

▼

Answer:

Read Performance Comparison:

Narrow Rows (1,000 partitions): 1,000 hash lookups, random seeks, coordinator overhead → 500-1000ms
Wide Rows (1 partition): 1 hash lookup, 1 disk seek, sequential read → 5-10ms
Result: 100x faster!

Why Wide Rows Are Faster:

O(log n) binary search to find start point
O(k) sequential read where k = rows needed
All data on same node (no network)
Cache-friendly sequential access

Trade-off: Write performance slightly worse (all writes to same node) but still fast!

3 How do you prevent unbounded partition growth in wide rows?

Use time bucketing by adding time period to partition key, creating multiple bounded partitions instead of one infinite partition.

▼

Answer:

Use time bucketing by adding a time component to the partition key.

-- Instead of:

PRIMARY KEY (sensor_id, timestamp)

-- Use:

PRIMARY KEY ((sensor_id, year_month), timestamp)

Benefits:

Creates new partition each month
Old partitions never grow
Easy to drop old data
Predictable partition sizes
Works indefinitely

Bucket Size Selection: Calculate writes_per_period × row_size < 100MB to choose hourly, daily, or monthly bucketing.

4 Why are wide rows perfect for time-series data?

Physical sort order matches query patterns, enabling ultra-fast range queries with sequential disk reads.

▼

Answer: Time-series data naturally aligns with wide row design because timestamps provide natural clustering order, data is append-only, range queries are common, and physical sort order matches query patterns. This enables binary search to start + sequential read = O(log n + k) query time!

5 What's the maximum recommended partition size for wide rows?

Ideal: 1-10MB, Acceptable: 10-50MB, Warning: 50-100MB, Maximum: 100MB before Cassandra warns.

▼

Answer: Ideal 1-10MB, acceptable 10-50MB, warning zone 50-100MB, maximum 100MB (Cassandra logs warnings), hard limit 2GB (may refuse). Larger partitions slow queries, compaction, and repair while consuming more memory.

6 How does DESC clustering order help with wide rows?

DESC order puts newest data at partition start, making "latest first" queries read only first few rows regardless of partition size.

▼

Answer: DESC clustering order stores newest data at the START of the partition. For "latest first" queries (feeds, timelines), Cassandra reads first N rows and stops - query time is constant regardless of partition size! This is why Netflix, Twitter, Apple use DESC order for user feeds.

🎓 Chapter Summary: Wide Row Mastery

You now master Cassandra's most powerful data organization pattern!

Key Concepts Mastered:

Wide Row Definition: Many rows with same partition key, different clustering columns
Time-Series Pattern: Perfect for IoT, logs, metrics, user activity
Performance Benefits: 100x faster reads through co-location and sequential access
Time Bucketing: Prevent unbounded growth with year_month partition keys
DESC Ordering: Latest-first queries read only start of partition

Production Patterns:

Netflix: Viewing history with DESC order
Discord: Message history per channel
Apple: iCloud photos timeline
Spotify: Playlist track history
Twitter: User timeline feeds
GitHub: Repository commit history

🚀 You can now design production-grade time-series schemas!

Responsive Ad