Section 2: Data Modeling Fundamentals

Cassandra Composite Keys

Master compound partition keys and composite clustering! Learn time bucketing, multi-tenant patterns, hotspot prevention, and production-grade composite key design with real-world examples.

📖 The Story: The Apartment Building Mail System

Imagine you're designing a mail delivery system for a massive housing complex. How do you organize addresses?

🏢 System 1: One Giant Building (Simple Key)

The Monolithic Approach: Single building with 10,000 apartments

The Megabuilding:

┌─────────────────────────────────┐

│  MEGABUILDING                   │

│  10,000 Apartments              │

│                                 │

│  Mail to: "Apartment 523"       │

│                                 │

│  Single Mail Room (Overwhelmed!)│

└─────────────────────────────────┘

Delivering to Apartment 523:

1. Go to THE building

2. Find THE mail room

3. Sort through 10,000 mailboxes

4. Time: 30 minutes 🐌

Critical Problems:

Unbounded Growth: Building keeps getting taller (no limit!)
Single Bottleneck: One mail room for 10,000 apartments
Slow Delivery: Finding one apartment takes forever
Maintenance Nightmare: Entire building on one foundation
Can't Scale: What happens at 50,000 apartments?

This is Cassandra with simple partition keys!

🏘️ System 2: Multiple Buildings (Composite Key)

The Smart Approach: 10 buildings with 1,000 apartments each

The Housing Complex:

Building A: 1,000 apartments | Mail Room A

Building B: 1,000 apartments | Mail Room B

Building C: 1,000 apartments | Mail Room C

Building D: 1,000 apartments | Mail Room D

Building E: 1,000 apartments | Mail Room E

... (10 buildings total)

Mail to: "(Building-E, Apartment-523)"

           ↑           ↑

    First key   Second key

Delivering to Building-E, Apt-523:

1. Identify building: Building E

2. Go to Building E's mail room

3. Sort through 1,000 mailboxes (not 10,000!)

4. Time: 3 minutes ⚡

10x FASTER!

Massive Benefits:

Controlled Size: Each building limited to 1,000 apartments
Distributed Load: 10 mail rooms working in parallel
Fast Delivery: Search only 1,000 mailboxes, not 10,000
Easy Scaling: Just add more buildings!
Isolation: Problem in Building A doesn't affect Building B
Maintenance: Can repair one building at a time

This is Cassandra with composite partition keys!

🎯 This is EXACTLY How Composite Keys Work!

🏢 Simple Key

Building = Partition
Apartment = Row
One huge building = One huge partition
Mail room = Node storage
Limited scalability

🏘️ Composite Key

Building + Apartment = Composite partition
Multiple buildings = Multiple partitions
Smaller buildings = Smaller partitions
Distributed mail rooms = Distributed nodes
Unlimited scalability!

❌ SIMPLE KEY (One Building):

PRIMARY KEY (apartment_number)

Result after 1 year:

• One partition with 10,000 rows

• Partition size: 100MB (TOO BIG!)

• Queries slow down over time

• Eventually hits size limits

✅ COMPOSITE KEY (Multiple Buildings):

PRIMARY KEY ((building_id, floor_number))

                ↑ Both columns together = partition ↑

Result after 1 year:

• 100 partitions (10 buildings × 10 floors)

• Each partition: 100 rows

• Partition size: 1MB (PERFECT!)

• Queries stay fast forever!

• Can add more buildings anytime

100x Better Distribution!

💡 Key Insight

Apartments: Multiple buildings prevent one from getting too big
Cassandra: Multiple partition key columns prevent one partition from getting too big

Both achieve scalability through controlled subdivision!

🔗 What are Composite Keys?

Understanding the two types of composite keys in Cassandra.

Complete Definition

Composite Keys: Using multiple columns together as part of your primary key. There are TWO types:

Type 1: Compound Partition Key

PRIMARY KEY ((col1, col2), clustering)

              ↑ Compound partition ↑

Both col1 AND col2 are HASHED TOGETHER

Result: ONE token, determines ONE node

Purpose: Prevent partition from growing too large

Type 2: Composite Clustering Key

PRIMARY KEY (partition, (cluster1, cluster2))

                         ↑ Composite clustering ↑

Multiple clustering columns for SORTING

Sorted by: cluster1 (first), then cluster2

Purpose: Hierarchical sorting within partition

Type 3: BOTH Combined!

PRIMARY KEY ((part1, part2), (cluster1, cluster2))

              ↑ Compound ↑    ↑ Composite clustering ↑

Maximum flexibility and control!

Compound partition: Prevents huge partitions

Composite clustering: Hierarchical sorting

Visual Comparison

1️⃣

Simple Key

PRIMARY KEY (user_id)
                                

Single column partition
No clustering columns
Simple but limited
Risk of huge partitions

2️⃣

Compound Partition

PRIMARY KEY ((user_id, year))
                                

Multiple columns partition
Both columns hashed together
Prevents unbounded growth
Time bucketing pattern

3️⃣

Composite Clustering

PRIMARY KEY (user_id, (year, month))
                                

Single partition key
Multiple clustering columns
Hierarchical sorting
Year → then month order

⚡

Both Combined

PRIMARY KEY ((user_id, region), (year, month))
                                

Compound partition key
Composite clustering key
Maximum control
Production-grade design!

🔑 Compound Partition Keys: Deep Dive

How multiple columns work together to determine data distribution.

The Problem: Unbounded Partition Growth

Real Scenario: IoT sensor data collection

❌ BAD DESIGN: Simple Partition Key

CREATE TABLE sensor_readings (

  sensor_id UUID,

  timestamp TIMESTAMP,

  temperature DECIMAL,

  humidity DECIMAL,

  PRIMARY KEY (sensor_id, timestamp)

               ↑

       Only partition key = DISASTER!

);

What Happens After 1 Year:

Sensor reads every 10 seconds:

• 6 readings/minute

• 360 readings/hour

• 8,640 readings/day

• 3,153,600 readings/year!

Each row: ~100 bytes

Partition size: 3,153,600 × 100 = 315 MB!

Problems:

• Far exceeds 100MB recommendation

• Slow queries (scan millions of rows)

• Eventually hits Cassandra partition limit

• Performance degrades over time

• Can't delete old data efficiently

✅ GOOD DESIGN: Compound Partition Key

CREATE TABLE sensor_readings (

  sensor_id UUID,

  year_month TEXT,  -- "2024-01", "2024-02", etc.

  timestamp TIMESTAMP,

  temperature DECIMAL,

  humidity DECIMAL,

  PRIMARY KEY ((sensor_id, year_month), timestamp)

               ↑ Compound partition! ↑

);

What Happens After 1 Year:

Same sensor, but bucketed by month:

• 12 partitions (one per month)

• Each partition: 262,800 readings

• Partition size: 262,800 × 100 = 26 MB ✓

Benefits:

• Well within 100MB recommendation

• Fast queries (smaller data sets)

• Never hits partition limit

• Performance stays consistent

• Easy to TTL or archive old months!

12x SMALLER partitions!

How Compound Keys Are Hashed

Understanding the hashing mechanism:

Step-by-Step Process:

PRIMARY KEY ((sensor_id, year_month), timestamp)

1. Insert arrives:

  sensor_id = '550e8400-e29b-41d4-a716-446655440000'

  year_month = '2024-01'

2. Concatenate partition key columns:

  combined = sensor_id + delimiter + year_month

  combined = '550e8400...|2024-01'

3. Apply Murmur3 hash:

  token = Murmur3(combined)

  token = -3847293847562938476

4. Map to token ring:

  Node 2 owns range: [-4000... to -2000...]

  Token -3847... falls in Node 2

5. Store on Node 2

CRITICAL INSIGHT:

('sensor-A', '2024-01') → Different partition than

('sensor-A', '2024-02') → Completely different partition!

Each unique combination = New partition

Why This Matters:

Same sensor_id but different year_month = different partitions
Each partition is independently sized and managed
Natural data rotation by time buckets
Easy to drop old data (just drop old partitions)

📅 Time Bucketing Pattern

The most common and powerful composite key pattern.

⏰

Hourly Bucketing

PRIMARY KEY (

  (device_id, year_month_day_hour),

  timestamp

);

Best For:

Very High Write Volume: 1000+ writes/sec
Real-Time Metrics: Live dashboards, monitoring
Short Retention: Days to weeks
Example: API request logs, click streams

Example Calculation:

100 writes/sec

× 3,600 sec/hour

= 360,000 rows/partition

If row = 200 bytes:

360K × 200 = 72 MB ✓

📆

Daily Bucketing

PRIMARY KEY (

  (user_id, date),

  timestamp

);

Best For:

Medium Write Volume: 10-1000 writes/sec
Daily Analytics: Activity tracking, events
Medium Retention: Months to a year
Example: User activity, transactions, orders

Example Calculation:

1 user action/minute

× 1,440 min/day

= 1,440 rows/partition

If row = 500 bytes:

1,440 × 500 = 720 KB ✓

📅

Monthly Bucketing

PRIMARY KEY (

  (customer_id, year_month),

  order_date

);

Best For:

Lower Write Volume: < 10 writes/sec
Historical Data: Archival, reporting
Long Retention: Years
Example: Customer orders, invoices, billing

Example Calculation:

5 orders/day

× 30 days/month

= 150 rows/partition

If row = 1 KB:

150 × 1KB = 150 KB ✓

Choosing the Right Bucket Size

Use this formula to determine your bucket size:

Target: 1-10 MB per partition (ideal)

Maximum: 100 MB per partition (hard limit)

Formula:

partition_size = writes_per_period × avg_row_size

Example: IoT Sensor

• Write rate: 10 readings/second

• Row size: 500 bytes

Hourly bucket:

10 × 3,600 × 500 = 18 MB (acceptable)

Daily bucket:

10 × 86,400 × 500 = 432 MB (TOO BIG!)

Decision: Use HOURLY bucketing ✓

Pro Tips:

Start Smaller: Better to have more, smaller partitions than fewer, larger ones
Consider Growth: Plan for 2-3x data growth over time
Monitor Size: Track partition sizes in production
Easy to Coarsen: Can always merge data later, but hard to split

🏢 Multi-Tenant Pattern

Isolating tenant data with composite partition keys.

Real Example: SaaS Application with 1,000 Tenants

❌ WITHOUT Multi-Tenant Key

CREATE TABLE users (

  user_id UUID,

  tenant_id UUID,

  name TEXT,

  PRIMARY KEY (user_id)

);

Problems:

Can't query "all users for tenant X" efficiently
One giant tenant can dominate partitions
No tenant data isolation
Cross-tenant queries required

✅ WITH Multi-Tenant Key

CREATE TABLE users (

  tenant_id UUID,

  user_id UUID,

  name TEXT,

  PRIMARY KEY ((tenant_id, user_id))

               ↑ Compound partition! ↑

);

Benefits:

Tenant Isolation: Each tenant's data in separate partitions
Fair Distribution: No single tenant dominates
Fast Tenant Queries: "Get all users for tenant" = fast
Easy Per-Tenant Operations: Export, delete, backup by tenant

Query Examples:

-- Get specific user:

SELECT * FROM users

WHERE tenant_id = 'acme-corp'

AND user_id = 'john-doe';

-- Get all users for tenant:

SELECT * FROM users

WHERE tenant_id = 'acme-corp';

-- Fast and efficient! ⚡

🔥 Hotspot Prevention with Composite Keys

Solving the celebrity problem and uneven data distribution.

❌

The Celebrity Problem

PRIMARY KEY (user_id)
                                

Scenario:

Celebrity: 50M followers
Normal user: 200 followers
All celebrity followers in ONE partition
Partition size: 500MB+ (HUGE!)
Node hosting celebrity = overloaded
Queries slow for celebrity data

✅

Bucket Solution

PRIMARY KEY (

  (user_id, bucket),

  follower_id

);

Solution:

Split followers across 100 buckets
Celebrity: 100 partitions × 500K followers
Each partition: 5MB (PERFECT!)
Distributed across 100 nodes
Balanced load
Fast queries!

Bucket Assignment:

bucket = follower_id % 100

Query all followers:

FOR bucket IN 0..99:

  SELECT * FROM followers

  WHERE user_id=?

  AND bucket=?;

🌍 Production Examples from Major Companies

How industry leaders use composite keys.

🎵 Spotify: User Listening History

CREATE TABLE listening_history (

  user_id UUID,

  year_month TEXT,  -- "2024-01"

  played_at TIMESTAMP,

  track_id UUID,

  duration_ms INT,

  PRIMARY KEY ((user_id, year_month), played_at)

) WITH CLUSTERING ORDER BY (played_at DESC);

Why This Design:

Monthly Buckets: Prevents unbounded growth per user
DESC Order: Most recent plays first (for "Recently Played")
Easy TTL: Drop old months after 2 years
Scale: 500M users × 12 months = 6B partitions (distributed!)

Common Queries:

-- Recently Played (homepage):

SELECT * FROM listening_history

WHERE user_id = ? AND year_month = '2024-12'

LIMIT 50;

-- This Month's Top Tracks:

SELECT track_id, COUNT(*)

FROM listening_history

WHERE user_id = ? AND year_month = '2024-12'

GROUP BY track_id;

💬 Discord: Message Storage

CREATE TABLE messages (

  channel_id UUID,

  bucket INT,        -- Time bucket (day)

  message_id TIMEUUID,

  author_id UUID,

  content TEXT,

  PRIMARY KEY ((channel_id, bucket), message_id)

) WITH CLUSTERING ORDER BY (message_id DESC);

Why This Design:

Daily Buckets: Popular channels = lots of messages, need small buckets
TIMEUUID: Natural chronological ordering + uniqueness
DESC Order: Latest messages first (scroll down to load older)
Balance: Even popular channels stay under 100MB/partition

💳 Stripe: Transaction Records

CREATE TABLE transactions (

  merchant_id UUID,

  year_month TEXT,   -- "2024-01"

  transaction_time TIMESTAMP,

  transaction_id UUID,

  amount DECIMAL,

  currency TEXT,

  PRIMARY KEY ((merchant_id, year_month), transaction_time)

);

Why This Design:

Monthly Buckets: Aligns with billing cycles
Per-Merchant: Easy to generate monthly statements
Time Ordered: Chronological transaction history
Compliance: Easy to archive old months for regulations

📋 Composite Clustering Keys: Hierarchical Sorting

Multiple clustering columns create powerful hierarchical sort orders.

The Filing Cabinet Analogy

Imagine organizing files in a cabinet:

📁 Single Clustering Column

PRIMARY KEY (employee_id, document_date)

Filing Order:

├── 2024-01-15

├── 2024-01-20

├── 2024-02-01

├── 2024-03-05

└── 2024-12-30

Just chronological - simple but limited!

📁 Composite Clustering Columns

PRIMARY KEY (employee_id, (year, month, day))

Filing Order (Hierarchical!):

├── 2023/

│   ├── 12/

│   │   ├── Day 30

│   │   └── Day 31

├── 2024/

│   ├── 01/

│   │   ├── Day 05

│   │   ├── Day 15

│   │   └── Day 20

│   ├── 02/

│   │   └── Day 01

│   └── 03/

│       └── Day 05

Hierarchical - powerful queries!

Why This is Better:

Can query by year only: "All 2024 documents"
Can query by year + month: "January 2024 documents"
Can query by year + month + day range: "Jan 15-20"
Natural grouping for analytics

How Composite Clustering Works Internally

The clustering columns create a composite sort key:

PRIMARY KEY (user_id, (year, month, day))

Internal Storage:

Row 1: user_id='alice', year=2023, month=12, day=30

→ Composite Key: "2023:12:30"

Row 2: user_id='alice', year=2024, month=01, day=15

→ Composite Key: "2024:01:15"

Row 3: user_id='alice', year=2024, month=01, day=20

→ Composite Key: "2024:01:20"

Physical Sort (Byte Comparison):

"2023:12:30" < "2024:01:15" < "2024:01:20"

This is why you MUST query left-to-right!

The composite key is ONE sorted value.

The Left-to-Right Rule:

Must specify clustering columns in ORDER
Can't skip a column in the middle
Range queries only on the LAST specified column
This is due to composite key byte ordering

✅

Valid Queries

PRIMARY KEY (user_id, (year, month, day))

-- Query by year only:

WHERE user_id=? AND year=2024

-- Query year + month:

WHERE user_id=? AND year=2024

AND month=1

-- Query year + month + day:

WHERE user_id=? AND year=2024

AND month=1 AND day=15

-- Range on LAST specified:

WHERE user_id=? AND year=2024

AND month=1 AND day>10 AND day<20

❌

Invalid Queries

PRIMARY KEY (user_id, (year, month, day))

-- Skipped year:

WHERE user_id=? AND month=1

❌ Missing year!

-- Skipped month:

WHERE user_id=? AND year=2024

AND day=15

❌ Missing month!

-- Range on non-last:

WHERE user_id=? AND year>2023

AND month=1

❌ Range must be on LAST column!

🔍 Query Patterns with Composite Keys

Master efficient querying strategies for composite keys.

Pattern 1: Time-Range Queries

CREATE TABLE sensor_data (

  sensor_id UUID,

  year_month TEXT,

  timestamp TIMESTAMP,

  temperature DECIMAL,

  PRIMARY KEY ((sensor_id, year_month), timestamp)

);

Efficient Time-Range Queries:

-- Single month range:

SELECT * FROM sensor_data

WHERE sensor_id = ?

AND year_month = '2024-01'

AND timestamp > '2024-01-15 00:00:00'

AND timestamp < '2024-01-20 23:59:59';

-- ✓ Single partition scan

-- Multi-month range (requires multiple queries):

SELECT * FROM sensor_data

WHERE sensor_id = ?

AND year_month IN ('2024-01', '2024-02', '2024-03')

AND timestamp > '2024-01-15 00:00:00';

-- ✓ 3 partition scans (still efficient)

Performance Characteristics:

Single Partition: O(log n) + O(k) - very fast
Multiple Partitions: O(p × (log n + k)) where p = partitions
Key Insight: Each partition scanned independently and in parallel

Pattern 2: Multi-Tenant Queries

CREATE TABLE tenant_events (

  tenant_id UUID,

  user_id UUID,

  event_time TIMESTAMP,

  event_type TEXT,

  PRIMARY KEY ((tenant_id, user_id), event_time)

) WITH CLUSTERING ORDER BY (event_time DESC);

Efficient Multi-Tenant Queries:

-- Recent events for specific user:

SELECT * FROM tenant_events

WHERE tenant_id = ?

AND user_id = ?

LIMIT 50;

-- ✓ Single partition, DESC order, super fast!

-- All users' recent events (requires app-level aggregation):

FOR EACH user IN tenant_users:

  SELECT * FROM tenant_events

  WHERE tenant_id = ?

  AND user_id = user.id

  LIMIT 10;

-- ✓ Parallel queries, then merge in app

Why This Pattern Works:

Each (tenant, user) combination = separate partition
No tenant can dominate a single partition
Queries stay fast even with millions of users
Easy to implement per-user rate limiting

Pattern 3: Hierarchical Data Queries

CREATE TABLE product_metrics (

  product_id UUID,

  region TEXT,

  year INT,

  month INT,

  day INT,

  sales DECIMAL,

  PRIMARY KEY ((product_id, region), year, month, day)

);

Hierarchical Query Examples:

-- Yearly total for region:

SELECT SUM(sales) FROM product_metrics

WHERE product_id = ?

AND region = 'US-WEST'

AND year = 2024;

-- Monthly total:

SELECT SUM(sales) FROM product_metrics

WHERE product_id = ?

AND region = 'US-WEST'

AND year = 2024

AND month = 1;

-- Daily range:

SELECT * FROM product_metrics

WHERE product_id = ?

AND region = 'US-WEST'

AND year = 2024

AND month = 1

AND day >= 15 AND day <= 20;

Aggregation Strategy:

Year-level aggregation: Fast (scans all days in year)
Month-level: Faster (scans fewer days)
Day-level: Fastest (pinpoint query)
Consider pre-aggregated tables for common queries

Query Performance Tips

DO:

Use LIMIT: Always limit result sets to prevent huge scans
Specify All Partition Columns: Essential for routing to correct node
Leverage Clustering Order: Use DESC for "latest first" queries
Batch Related Queries: Fetch multiple partitions in parallel

DON'T:

Use ALLOW FILTERING: Scans entire table - extremely slow!
Skip Partition Columns: Results in full cluster scan
Query Without Limits: Can return millions of rows
Use Inequality on Partition Key: Not supported

🖥️ Interactive Composite Key Design Tool

Calculate optimal composite key structure based on your requirements.

Composite Key Designer

Write Rate (per second):

Average Row Size (bytes):

Data Type:

⚠️ Composite Key Anti-Patterns

Common mistakes to avoid in production.

❌ Anti-Pattern 1: Using High Cardinality Second Column

-- BAD: UUID as second partition column

PRIMARY KEY ((tenant_id, user_id))

Problem:

• Each (tenant, user) = separate partition

• 1M users = 1M partitions

• Too much fragmentation!

• Coordinator overhead

• Can't query "all users in tenant"

✅ BETTER: Use user_id as clustering

-- GOOD: User as clustering column

PRIMARY KEY (tenant_id, user_id)

Benefits:

• All tenant data in one partition

• Can query "all users"

• Better for analytics

• Less coordinator overhead

When High Cardinality IS Appropriate:

Time buckets (year-month) - prevents unbounded growth
Region/shard IDs - distribute load
When isolation is more important than aggregation

❌ Anti-Pattern 2: Too Many Partition Columns

-- BAD: 4+ columns in partition key

PRIMARY KEY ((tenant_id, region, year, month))

Problems:

• Must specify ALL 4 in every query

• Extremely fragmented data

• Hard to aggregate across dimensions

• Complex application logic

✅ BETTER: 2-3 columns max

-- GOOD: Simplified partition key

PRIMARY KEY ((tenant_id, year_month), region)

                                  ↑clustering

Benefits:

• Only 2 required in WHERE clause

• Can query all regions for month

• Simpler queries

• Better flexibility

Rule of Thumb: Keep compound partition keys to 2-3 columns maximum!

❌ Anti-Pattern 3: Low Cardinality First Column

-- BAD: Boolean/enum as first column

PRIMARY KEY ((is_active, user_id))

               ↑ Only 2 values!

Problems:

• Only 2 partitions (true/false)

• MASSIVE partitions

• Hotspots (all activity on 2 nodes)

• Defeats composite key purpose!

✅ BETTER: High cardinality first

-- GOOD: User ID first

PRIMARY KEY (user_id, is_active)

                        ↑clustering

Benefits:

• Each user = separate partition

• Even distribution

• Can filter by is_active WITHIN user

• Scalable!

Guideline: First partition column should have high cardinality (100K+ unique values)!

❌ Anti-Pattern 4: Buckets Too Large

-- BAD: Yearly buckets for high-volume data

PRIMARY KEY ((sensor_id, year))

With 10 writes/sec:

• 10 × 31,536,000 sec/year = 315M rows/partition

• Partition size: 31.5 GB!

• WAY TOO BIG!

• Queries become slower and slower

✅ BETTER: Right-sized buckets

-- GOOD: Hourly or daily buckets

PRIMARY KEY ((sensor_id, year_month_day_hour))

With 10 writes/sec:

• 10 × 3,600 sec/hour = 36K rows/partition

• Partition size: 3.6 MB ✓

• Perfect size!

• Consistent performance

Always Calculate: Use the formula writes_per_period × row_size to verify!

❌ Anti-Pattern 5: Mutable Partition Columns

-- BAD: Using status/category in partition

PRIMARY KEY ((user_id, subscription_tier))

                           ↑ Changes!

Problems:

• User upgrades: "free" → "premium"

• Can't UPDATE partition key

• Must DELETE + INSERT (expensive!)

• Data moves to different partition

• Can cause temporary duplicates

✅ BETTER: Immutable partition columns

-- GOOD: Use immutable data

PRIMARY KEY (user_id, subscription_tier)

                          ↑clustering only

Benefits:

• Can UPDATE subscription_tier

• Row stays in same partition

• Simple update operation

• No data movement

Golden Rule: Partition key columns must be immutable or change very rarely!

✅ Production Best Practices

Battle-tested guidelines for composite key design.

✅

ALWAYS Do This

Calculate Partition Size: Use formula writes × period × row_size before deploying
Start with Smaller Buckets: Better 100 small partitions than 10 large ones
Use Time Buckets for Time-Series: Natural data lifecycle management
Test with Production Load: Simulate real write rates in staging
Monitor Partition Sizes: Set up alerts for > 50MB partitions
Document Your Decisions: Explain why you chose specific bucket sizes
Plan for Growth: Design for 3-5x data volume increase
Use Immutable Columns: Partition keys should never change

❌

NEVER Do This

Skip Size Calculations: Guessing leads to production issues!
Use Year-Only Buckets: For high-volume data - partitions explode
Put Low Cardinality First: Boolean/enum first = hotspots
Exceed 3 Partition Columns: Makes queries complex and brittle
Use Mutable Data in Keys: Updates require delete + insert
Forget About TTL: Old data piles up without cleanup
Deploy Without Load Testing: Production is not the place to discover issues
Ignore Partition Warnings: Cassandra logs large partition warnings for a reason!

Pre-Deployment Checklist

Before going to production, verify:

☐ Partition Size Math: Calculated and under 100MB

☐ Query Patterns Tested: All queries use partition key correctly

☐ Growth Plan: Bucket size works for 3 years

☐ Monitoring Setup: Alerts for partition size warnings

☐ TTL Strategy: Old data cleanup plan in place

☐ Load Testing: Tested with production-level write rates

☐ Documentation: Schema design rationale documented

☐ Backup Plan: Migration strategy if design needs changes

🎯 Choosing Between Patterns

Use Compound Partition When:

Preventing unbounded partition growth
Time-series data with long retention
Multi-tenant isolation needed
High write volume per entity
Natural bucketing exists (region, category)

Use Composite Clustering When:

Need hierarchical sorting
Multiple sort dimensions
Year → Month → Day queries
Partition size already controlled
Need flexible query granularity

Pro Tip: Often you'll use BOTH together!

PRIMARY KEY ((sensor_id, year_month), day, hour)

              ↑ Compound partition ↑   ↑ Composite clustering ↑

💼 Complete Interview Preparation (15 Questions)

Master composite keys for technical interviews!

6 What's the left-to-right rule for composite clustering keys?

Must query clustering columns left-to-right; can only use ranges on the last specified column.

▼

Answer:

When querying composite clustering keys, you must specify columns in order from left to right, and range queries can only be on the last specified column.

PRIMARY KEY (user_id, (year, month, day))

✅ Valid:

WHERE user_id=? AND year=2024

WHERE user_id=? AND year=2024 AND month=1

WHERE user_id=? AND year=2024 AND month=1 AND day>15

❌ Invalid:

WHERE user_id=? AND month=1  (missing year!)

WHERE user_id=? AND day=15   (missing year and month!)

WHERE user_id=? AND year>2023 AND month=1 (range not on last!)

Why? Clustering columns form a composite sort key. Cassandra can only efficiently filter following the sort order!

7 Can you update a composite partition key column?

No - partition keys are immutable; changing them requires DELETE old row + INSERT new row.

▼

Answer:

No! Partition key columns (including all columns in a compound key) are immutable and cannot be updated.

Why Not?

Partition key determines which node stores the data (via hashing)
Changing it would require moving data to a different partition/node
This would be extremely expensive
Would break the physical storage structure

Workaround: DELETE old row, then INSERT new row

-- Want to move user from 2023 bucket to 2024 bucket

DELETE FROM table WHERE user_id=? AND year=2023;

INSERT INTO table (...) VALUES (?, 2024, ...);

Best Practice: Only use immutable data in partition keys!

8 How do you query across multiple time buckets?

Use IN clause for 3-10 buckets, or make parallel queries and merge results in application code.

▼

Answer:

Use the IN clause to query multiple partitions, or make multiple queries in parallel and merge results in your application.

-- Query 3 months at once:

SELECT * FROM sensor_data

WHERE sensor_id = ?

AND year_month IN ('2024-01', '2024-02', '2024-03');

-- Alternative: Parallel queries in app

async function getMultiMonthData(sensorId, months) {

  const promises = months.map(month =>

    query("SELECT * WHERE sensor_id=? AND year_month=?",

      [sensorId, month])

  );

  return Promise.all(promises);

}

Performance: IN clause with 3-10 values is efficient. Beyond that, parallel queries may be better.

9 What's the maximum recommended partition size?

Ideal: 1-10MB, Acceptable: 10-50MB, Warning: 50-100MB, Maximum: 100MB, Hard limit: 2GB.

▼

Answer:

Ideal: 1-10 MB per partition
Acceptable: 10-50 MB per partition
Warning Zone: 50-100 MB per partition
Maximum: 100 MB (Cassandra warns beyond this)
Hard Limit: 2 GB (Cassandra may refuse)

Why It Matters:

Large partitions slow down queries (must scan more data)
Compaction becomes more expensive
Repair operations take longer
Memory pressure on coordinators
Risk of timeouts

Monitoring: Track max_partition_size and avg_partition_size metrics!

10 How do composite keys help with TTL strategies?

Time-bucketed keys enable efficient deletion of entire partitions instead of individual row TTLs.

▼

Answer:

Time-bucketed composite keys enable efficient deletion of old data by dropping entire partitions instead of individual rows.

PRIMARY KEY ((sensor_id, year_month), timestamp)

-- Delete all January 2023 data:

DELETE FROM sensor_data

WHERE sensor_id = ? AND year_month = '2023-01';

-- This drops ENTIRE partition efficiently!

-- No row-by-row deletion needed

Benefits:

Fast Deletion: Drop partition vs delete millions of rows
No Tombstones: Partition deletion is cleaner
Predictable: Know exactly which buckets to delete
Automatable: Scheduled cleanup jobs

Pro Pattern: Combine with Time Window Compaction Strategy (TWCS) for automatic partition expiry!

11 When should you use buckets for non-time-series data?

Use buckets for high-cardinality relationships like followers to prevent celebrity/hotspot problems.

▼

Answer:

Use buckets when you have high-cardinality relationships that could create unbounded partitions. Classic example: social graph (followers).

-- Celebrity with 50M followers

PRIMARY KEY ((user_id, bucket), follower_id)

Bucket Assignment:

bucket = follower_id % 100

Result:

• 100 partitions × 500K followers

• Each partition: manageable size

• Distributed across many nodes

Other Use Cases:

Message Threads: Popular threads split across buckets
Product Reviews: Popular products split
Event Attendees: Large events bucketed
Log Files: High-volume services bucketed

Trade-off: Must query all buckets to get complete data, but each query is fast!

12 How does compound key affect write performance?

Minimal overhead (<1%); actually improves performance by distributing writes and preventing hotspots.

▼

Answer:

Compound keys have minimal write overhead - just additional hashing computation. The benefits far outweigh the small cost.

Performance Impact:

Hash Computation: Minimal (~microseconds to hash 2-3 columns)
Write Path: Identical to simple keys after hashing
Overall Impact: < 1% overhead

Performance GAINS:

Better Distribution: Writes spread across more nodes
Smaller Memtables: Per-partition memtables stay small
Faster Compaction: Smaller partitions compact faster
Reduced Hotspots: More even node utilization

Bottom Line: Compound keys IMPROVE write performance in practice by preventing large partition issues!

13 Can you use composite keys with secondary indexes?

Yes, but you still must specify the full partition key; indexes only filter within partitions.

▼

Answer:

Yes, but with caution! Secondary indexes on composite key columns work, but you still need to specify the full partition key in queries.

CREATE TABLE events (

  user_id UUID,

  year_month TEXT,

  event_type TEXT,

  timestamp TIMESTAMP,

  PRIMARY KEY ((user_id, year_month), timestamp)

);

CREATE INDEX ON events(event_type);

-- This query STILL requires partition key:

SELECT * FROM events

WHERE user_id = ? AND year_month = ?

AND event_type = 'login';  -- Index helps here

-- This is VERY expensive (full cluster scan):

SELECT * FROM events

WHERE event_type = 'login';  -- ❌ No partition key!

Best Practice: Secondary indexes should filter WITHIN partitions, not across the entire cluster!

14 How do you migrate from simple to composite keys?

Create new table with composite key, dual-write to both, backfill old data, switch reads, drop old table.

▼

Answer:

Migration requires creating a new table and copying data. There's no in-place schema change for partition keys.

-- Step 1: Create new table with composite key

CREATE TABLE events_v2 (

  user_id UUID,

  year_month TEXT,  -- New!

  event_time TIMESTAMP,

  event_data TEXT,

  PRIMARY KEY ((user_id, year_month), event_time)

);

-- Step 2: Dual-write to both tables

INSERT INTO events (user_id, event_time, event_data)

VALUES (?, ?, ?);

INSERT INTO events_v2 (user_id, year_month, event_time, event_data)

VALUES (?, calculateYearMonth(?), ?, ?);

-- Step 3: Backfill old data

spark-submit backfill_events.py  -- Batch job

-- Step 4: Switch reads to new table

-- Step 5: Stop writing to old table

-- Step 6: Drop old table

Migration Timeline: Typically 1-4 weeks depending on data volume

15 What's the difference between using multiple partition columns vs multiple clustering columns?

Multiple partition columns create separate partitions; multiple clustering columns create hierarchical sorting within one partition.

▼

Answer:

This is about data distribution vs data organization within partitions.

Multiple Partition Columns (Compound):

PRIMARY KEY ((user_id, year), month)

Effect:

• Each (user_id, year) = DIFFERENT partition

• Data distributed across many partitions

• Prevents unbounded partition growth

• Must specify BOTH in WHERE clause

Multiple Clustering Columns (Composite):

PRIMARY KEY (user_id, (year, month))

Effect:

• All user data in ONE partition

• Sorted by year, then month

• Enables hierarchical queries

• Can query by year only OR year + month

When to Use Each:

Compound Partition: Partition getting too large
Composite Clustering: Need flexible query granularity
Both Together: Large data + hierarchical queries

🎓 Chapter Summary: Composite Key Mastery

You now master Cassandra's most powerful partition control mechanism!

Key Concepts Mastered:

Compound Partition Keys: Multiple columns hashed together for distribution
Time Bucketing: Prevent unbounded partition growth
Multi-Tenant Patterns: Isolate tenant data effectively
Hotspot Prevention: Balance load across nodes
Production Patterns: Real examples from Spotify, Discord, Stripe

The Apartment Analogy Recap:

Remember: Single building (simple key) vs multiple buildings (composite key). Smaller buildings = smaller partitions = better performance!

Production Checklist:

✅ Calculate expected partition sizes
✅ Choose appropriate time bucket (hourly/daily/monthly)
✅ Use compound keys for time-series data
✅ Implement multi-tenant isolation where needed
✅ Monitor partition sizes in production

🚀 You can now design scalable Cassandra schemas!

Responsive Ad

Cassandra Composite Keys

📖 The Story: The Apartment Building Mail System

🏢 System 1: One Giant Building (Simple Key)

Critical Problems:

🏘️ System 2: Multiple Buildings (Composite Key)

Massive Benefits:

🎯 This is EXACTLY How Composite Keys Work!

🏢 Simple Key

🏘️ Composite Key

💡 Key Insight

🔗 What are Composite Keys?

Complete Definition

Visual Comparison

Simple Key

Compound Partition

Composite Clustering

Both Combined

🔑 Compound Partition Keys: Deep Dive

The Problem: Unbounded Partition Growth

❌ BAD DESIGN: Simple Partition Key

✅ GOOD DESIGN: Compound Partition Key

How Compound Keys Are Hashed

📅 Time Bucketing Pattern

Hourly Bucketing

Daily Bucketing

Monthly Bucketing

Choosing the Right Bucket Size

🏢 Multi-Tenant Pattern

Real Example: SaaS Application with 1,000 Tenants

❌ WITHOUT Multi-Tenant Key

✅ WITH Multi-Tenant Key

🔥 Hotspot Prevention with Composite Keys

The Celebrity Problem

Bucket Solution

🌍 Production Examples from Major Companies

🎵 Spotify: User Listening History

💬 Discord: Message Storage

💳 Stripe: Transaction Records

📋 Composite Clustering Keys: Hierarchical Sorting

The Filing Cabinet Analogy

📁 Single Clustering Column

📁 Composite Clustering Columns

How Composite Clustering Works Internally

Valid Queries

Invalid Queries

🔍 Query Patterns with Composite Keys

Pattern 1: Time-Range Queries

Pattern 2: Multi-Tenant Queries

Pattern 3: Hierarchical Data Queries

Query Performance Tips

🖥️ Interactive Composite Key Design Tool

📊 Recommended Design

⚠️ Composite Key Anti-Patterns

❌ Anti-Pattern 1: Using High Cardinality Second Column

✅ BETTER: Use user_id as clustering

❌ Anti-Pattern 2: Too Many Partition Columns

✅ BETTER: 2-3 columns max

❌ Anti-Pattern 3: Low Cardinality First Column

✅ BETTER: High cardinality first

❌ Anti-Pattern 4: Buckets Too Large

✅ BETTER: Right-sized buckets

❌ Anti-Pattern 5: Mutable Partition Columns

✅ BETTER: Immutable partition columns

✅ Production Best Practices

ALWAYS Do This

NEVER Do This

Pre-Deployment Checklist

🎯 Choosing Between Patterns

Use Compound Partition When:

Use Composite Clustering When:

💼 Complete Interview Preparation (15 Questions)

🎓 Chapter Summary: Composite Key Mastery

Key Concepts Mastered:

The Apartment Analogy Recap:

Production Checklist: