PRIMARY KEYS - The Complete Story

What is a Database?

Database = Organized collection of data

Think of it like:
• Filing cabinet with folders
• Library with books
• Warehouse with shelves

Purpose:
Store information so you can:
• Save it permanently
• Find it quickly
• Update it easily
• Delete when needed

Examples:
• Instagram: stores photos, users, likes
• Amazon: stores products, orders, customers
• Your phone: stores contacts, messages

What is a Table?

Table = Organized data in rows and columns

Like a spreadsheet:

What is a Key?

Key = Unique identifier for each row

Like:
• Your passport number (unique to you!)
• License plate (unique to car)
• Order number (unique to order)

Why needed?
To find specific row instantly!

Without key:
"Find Bob's info" = Check ALL rows ✗

With key:
"Get user_id = 123" = Direct lookup ✓

Keys make databases FAST! ⚡

Uniqueness

Unique = No duplicates allowed!

Example - Student IDs:
✅ Student 001 = Alice
✅ Student 002 = Bob
✗ Student 001 = Carol (CONFLICT!)

Why important?
• Can't have two people with same ID
• Database would be confused!
• "Get student 001" → Which one?? 😱

Keys MUST be unique!
This is the golden rule ✨

Distributed Database

Distributed = Data on multiple computers

Traditional:
• One computer (server)
• All data in one place
• Limited capacity (~1TB)

Distributed (Cassandra):
• Many computers (100+)
• Data split across them
• Unlimited capacity! ✓

Challenge:
How to find data quickly?
Answer: Smart primary keys! 🔑

Data Location

In distributed systems, WHERE matters!

Problem:
Data scattered across 100 computers
Need to find Alice's data fast!

Bad approach:
Check all 100 computers = SLOW! ✗

Good approach:
Use key to calculate location!
hash(key) → Computer #42
Direct lookup = FAST! ✓

This is what primary keys enable!

Sorting

Sorting = Arranging in order

Examples:
• Numbers: 1, 2, 3, 4, 5...
• Letters: A, B, C, D, E...
• Dates: Oldest → Newest

Why sort data?
• Find things faster
• Show in order (recent first)
• Range queries (last week)

In Cassandra:
Primary key controls sorting!
Data stored pre-sorted on disk ✓

Performance

Performance = Speed of operations

Measured in:
• Milliseconds (ms)
• 1 second = 1000ms

Database speeds:
• Excellent: 1-5ms ⚡
• Good: 10-50ms ✓
• Slow: 100-500ms ⚠️
• Very slow: 1000ms+ (1 sec) ✗

Goal:
Design keys for 1-5ms queries!
Primary keys are the secret! 🔑

Hash Function

Hash = Turn data into number

Like a magic formula:
• Input: "Alice"
• Hash function: 🎩✨
• Output: 42

Same input = Same number! ✓

In Cassandra:
hash(partition_key) → Node number

Example:
hash("user_123") = 42
→ Store on Computer #42

Enables O(1) instant lookups! ⚡

Formal Definition

Primary Key =
A column (or combination of columns) that uniquely identifies each row in a table.

In simple terms:
The "address" for finding specific data!

Rules:
• Must be unique
• Cannot be NULL
• Doesn't change
• Every table needs one

Without primary key = Chaos!
With primary key = Order! ✓

Purpose

Why primary keys exist:

1. Identify rows uniquely
No ambiguity, no confusion

2. Enable fast lookups
Find data in 1-5ms ⚡

3. Prevent duplicates
Can't insert same key twice

4. Control data location
(In distributed systems)

5. Enable sorting
Organized data access

Foundation of database efficiency!

In Cassandra

Cassandra's primary key has TWO parts!

PRIMARY KEY (partition_key, clustering_col)

Part 1: Partition Key
• WHERE data lives (which computer)
• Distributes data across nodes

Part 2: Clustering Columns
• HOW data is sorted within partition
• Organizes rows on disk

Together = Complete primary key!
This is Cassandra's superpower! 🚀

What is Partition Key?

Partition Key = Determines WHERE data lives in the cluster

Think of it as:
• Building address in a city
• Bookshelf number in a library
• Warehouse location in a company

How it works:
1. You specify partition key value (e.g., user_id = 12345)
2. Cassandra applies hash function: hash(12345) = 42
3. Result maps to node number: Node #42
4. All data with that key goes to Node #42 ✓

Example:
CREATE TABLE users (
  user_id UUID PRIMARY KEY, -- This is partition key
  name TEXT,
  email TEXT
);
Result: Each user's data stored on specific node! ⚡

What is Clustering Key?

Clustering Key = Determines HOW data is sorted within partition

Think of it as:
• Apartment numbers in a building
• Page numbers in a book
• Alphabetical order in a phonebook

How it works:
1. All rows with same partition key are grouped together
2. Within that group, rows are sorted by clustering key
3. Sorting happens at write time (once!)
4. Reads are instant - data already sorted! ✓

Example:
CREATE TABLE user_activity (
  user_id UUID,
  activity_time TIMESTAMP, -- Clustering key
  activity_type TEXT,
  PRIMARY KEY (user_id, activity_time)
) WITH CLUSTERING ORDER BY (activity_time DESC);
Result: Activities automatically sorted newest first! 🎯

Scenario 1: Netflix - Viewing History (250M+ Users)

Challenge: Store every show/movie watched by 250M users, enable "Continue Watching" feature

Table Design:
CREATE TABLE viewing_history (
  user_id UUID, -- PARTITION KEY
  watched_at TIMESTAMP, -- CLUSTERING COLUMN 1
  content_id UUID, -- CLUSTERING COLUMN 2
  content_title TEXT,
  content_type TEXT, -- 'movie' or 'series'
  season INT,
  episode INT,
  progress_seconds INT,
  total_duration_seconds INT,
  PRIMARY KEY (user_id, watched_at, content_id)
) WITH CLUSTERING ORDER BY (watched_at DESC, content_id ASC);
Why this design works perfectly:

1. Partition Key (user_id):
• All viewing history for one user stays together
• 250M users distributed evenly across nodes
• Average user has ~500 viewing records
• Partition size: ~50KB per user (manageable!)

2. Clustering Columns (watched_at DESC, content_id):
• watched_at DESC = Most recent watches first ✓
• content_id ensures uniqueness (same content watched multiple times)
• Pre-sorted = instant "Continue Watching" queries!

Query Patterns Enabled:
-- Get "Continue Watching" (last 10 items)
SELECT * FROM viewing_history
WHERE user_id = ?
LIMIT 10;
-- Result: 2ms ⚡

-- Get all watches from last 30 days
SELECT * FROM viewing_history
WHERE user_id = ?
AND watched_at > now() - 30d;
-- Result: 3-5ms ⚡

-- Check if user watched specific content
SELECT * FROM viewing_history
WHERE user_id = ?
AND watched_at > '2024-01-01'
AND content_id = ?;
-- Result: 2-3ms ⚡
Production Results:
• Query latency: P99 < 5ms (99% of queries under 5ms)
• Throughput: 500K+ queries/second globally
• Storage: ~12TB total (with compression)
• Availability: 99.99%+ uptime

Key Insight: Partition by user_id means each user's data is always together. No cross-node queries needed! 🚀

Scenario 2: Twitter - User Timeline (300M+ Active Users)

Challenge: Show personalized timeline, handle millions of tweets per day

Table Design:
CREATE TABLE user_timeline (
  user_id BIGINT, -- PARTITION KEY (viewer!)
  tweet_time TIMESTAMP, -- CLUSTERING COLUMN
  tweet_id BIGINT,
  author_id BIGINT,
  author_username TEXT,
  author_avatar TEXT,
  tweet_text TEXT,
  media_urls LIST<TEXT>,
  retweet_count INT,
  like_count INT,
  PRIMARY KEY (user_id, tweet_time)
) WITH CLUSTERING ORDER BY (tweet_time DESC);
Critical Design Decision - Partition Key is VIEWER, not AUTHOR!

Why user_id (viewer) as partition key?
• Each user sees their own personalized timeline
• All tweets in Bob's feed stored together on same node
• Loading timeline = single partition query ✓

What if we used author_id as partition key? ✗
To show Bob's timeline:
1. Get Bob's 1000 followers
2. Query each follower's tweets (1000 partitions!)
3. Merge and sort 1000 result sets
4. Show top 50
Result: 500ms-2s (SLOW!) ✗

With user_id as partition (current design):
SELECT * FROM user_timeline
WHERE user_id = ?
LIMIT 50;
Result: 2-5ms (INSTANT!) ⚡

The Trade-off (Fan-out on Write):
When Alice posts a tweet:
1. Alice has 50,000 followers
2. Write tweet to ALL 50,000 followers' timelines
3. Result: 50,000 writes!

But this is WORTH IT because:
• Writes: 50,000 × 1ms = 50ms total (acceptable!)
• Reads: Every user loads timeline in 2ms ⚡
• Users read 100x more than they post!

Production Results:
• Timeline load: P99 < 10ms
• Handles: 6,000+ tweets/second
• Scale: 300M+ active users
• Philosophy: "Optimize for reads, users scroll constantly!"

Scenario 3: Uber - Trip History (10M+ Drivers)

Challenge: Store every trip, enable driver earnings reports, rider trip history

Multiple Tables (Query-Driven Design):

Table 1: Trips by Driver
CREATE TABLE trips_by_driver (
  driver_id UUID, -- PARTITION KEY
  trip_start_time TIMESTAMP, -- CLUSTERING COLUMN
  trip_id UUID,
  rider_id UUID,
  pickup_location TEXT,
  dropoff_location TEXT,
  distance_km DOUBLE,
  duration_minutes INT,
  fare_amount DECIMAL,
  driver_earnings DECIMAL,
  status TEXT,
  PRIMARY KEY (driver_id, trip_start_time)
) WITH CLUSTERING ORDER BY (trip_start_time DESC);
Use Case: Driver daily earnings report
SELECT SUM(driver_earnings)
FROM trips_by_driver
WHERE driver_id = ?
AND trip_start_time >= '2024-12-29 00:00:00'
AND trip_start_time < '2024-12-30 00:00:00'; Result: 5-10ms ⚡

Table 2: Trips by Rider
CREATE TABLE trips_by_rider (
  rider_id UUID, -- PARTITION KEY
  trip_start_time TIMESTAMP, -- CLUSTERING COLUMN
  trip_id UUID,
  driver_id UUID,
  driver_name TEXT,
  driver_rating DOUBLE,
  pickup_location TEXT,
  dropoff_location TEXT,
  fare_amount DECIMAL,
  payment_method TEXT,
  PRIMARY KEY (rider_id, trip_start_time)
) WITH CLUSTERING ORDER BY (trip_start_time DESC);
Use Case: Rider "Your Trips" history
SELECT * FROM trips_by_rider
WHERE rider_id = ?
LIMIT 20; Result: 2-5ms ⚡

Key Design Principle: Duplicate Data for Different Access Patterns!
• Same trip stored in BOTH tables
• trips_by_driver: Optimized for driver queries
• trips_by_rider: Optimized for rider queries
• Storage cost: 2x
• Query performance: 100x faster! ✓

Production Results:
• Daily trips: 20M+
• Query latency: P95 < 10ms
• Storage: ~5TB (with both tables)
• Trade-off: 2x storage cost vs instant queries

Scenario 4: Spotify - Listen History (500M+ Users)

Challenge: Store every song played, enable "Recently Played", year-end stats

Problem with Simple Design:
PRIMARY KEY (user_id, played_at) Issue: Power users have 50,000+ plays = HUGE partition! ✗

Better Design with Time Bucketing:
CREATE TABLE listen_history (
  user_id UUID,
  month_bucket TEXT, -- 'YYYY-MM' format
  played_at TIMESTAMP,
  track_id UUID,
  track_name TEXT,
  artist_name TEXT,
  album_name TEXT,
  duration_ms INT,
  played_duration_ms INT,
  skip BOOLEAN,
  shuffle BOOLEAN,
  PRIMARY KEY ((user_id, month_bucket), played_at)
) WITH CLUSTERING ORDER BY (played_at DESC);
Composite Partition Key: (user_id, month_bucket)
• Each month = separate partition!
• Heavy user: ~3,000 plays/month = manageable partition size
• Prevents unbounded growth ✓

Query Patterns:
-- Recently Played (this month)
SELECT * FROM listen_history
WHERE user_id = ?
AND month_bucket = '2024-12'
LIMIT 50;
-- Result: 2-3ms ⚡

-- Year-end stats (12 partitions)
SELECT track_name, COUNT(*)
FROM listen_history
WHERE user_id = ?
AND month_bucket IN ('2024-01', '2024-02', ..., '2024-12')
GROUP BY track_name;
-- Result: 30-50ms (12 partitions) ✓
Production Results:
• Daily plays: 1 billion+
• Partition size: 2-5MB per user per month
• Query latency: P99 < 10ms
• TTL: 2 years (auto-delete old data)

Scenario 5: IoT - Temperature Sensors (1M+ Devices)

Challenge: Sensors report every 5 seconds, store readings, enable real-time monitoring

Disaster Design (DON'T DO THIS!):
PRIMARY KEY (sensor_id, reading_time) Problem:
• 5-second intervals = 17,280 readings/day
• After 1 year = 6.3M readings in ONE partition! 💥
• Partition size: 500MB+ (unmanageable!) ✗
• Queries become slower over time ✗

Production Design with Hour Bucketing:
CREATE TABLE sensor_readings (
  sensor_id UUID,
  hour_bucket TEXT, -- 'YYYY-MM-DD-HH'
  reading_time TIMESTAMP,
  temperature DOUBLE,
  humidity DOUBLE,
  pressure DOUBLE,
  battery_level INT,
  PRIMARY KEY ((sensor_id, hour_bucket), reading_time)
) WITH CLUSTERING ORDER BY (reading_time DESC)
AND default_time_to_live = 2592000; -- 30 days
Hour Bucketing Benefits:
• hour_bucket = '2024-12-29-14' (2pm on Dec 29)
• Each hour = separate partition
• Readings per hour: 720 (5-sec intervals)
• Partition size: ~50KB (perfect!) ✓

Query Patterns:
-- Latest reading (current hour)
SELECT * FROM sensor_readings
WHERE sensor_id = ?
AND hour_bucket = '2024-12-29-14'
LIMIT 1;
-- Result: 1-2ms ⚡

-- Last 24 hours (24 partitions)
SELECT * FROM sensor_readings
WHERE sensor_id = ?
AND hour_bucket IN ('2024-12-28-14', '2024-12-28-15', ..., '2024-12-29-14')
ORDER BY reading_time DESC;
-- Result: 20-30ms (24 partitions) ✓

-- Average temperature last hour
SELECT AVG(temperature)
FROM sensor_readings
WHERE sensor_id = ?
AND hour_bucket = '2024-12-29-14';
-- Result: 5-10ms ✓
Production Results:
• Sensors: 1M+ devices
• Daily readings: 17B+ (17 billion!)
• Ingestion rate: 200K writes/second
• Query latency: P99 < 10ms
• Storage: 5TB (with 30-day TTL)
• Key insight: Time bucketing prevents infinite partition growth! 🚀

Example 1: News Website - Articles by Category & Date

Requirements:
• Browse articles by category (Tech, Sports, Politics)
• Within category, show newest first
• Same date? Sort by title alphabetically

Table Design:
CREATE TABLE articles_by_category (
  site_id TEXT, -- PARTITION KEY (single column)
  category TEXT, -- CLUSTERING COLUMN 1
  publish_date DATE, -- CLUSTERING COLUMN 2
  title TEXT, -- CLUSTERING COLUMN 3
  article_id UUID,
  author TEXT,
  content TEXT,
  PRIMARY KEY (site_id, category, publish_date, title)
) WITH CLUSTERING ORDER BY
  (category ASC, publish_date DESC, title ASC);
Data Organization:

Example 2: Hotel Booking - Composite Partition Key

Challenge: Store bookings, prevent hot partitions (popular hotels get millions of bookings!)

Bad Design:
PRIMARY KEY (hotel_id, check_in_date) Problem: Popular hotel in NYC = 10M bookings = HOT PARTITION! 💥

Good Design with Composite Partition Key:
CREATE TABLE hotel_bookings (
  hotel_id UUID,
  month_bucket TEXT, -- 'YYYY-MM'
  check_in_date DATE,
  booking_id UUID,
  guest_name TEXT,
  guest_email TEXT,
  room_number INT,
  room_type TEXT,
  total_amount DECIMAL,
  status TEXT,
  PRIMARY KEY ((hotel_id, month_bucket), check_in_date, booking_id)
) WITH CLUSTERING ORDER BY (check_in_date ASC, booking_id ASC);
Composite Partition Key: (hotel_id, month_bucket)
• Each hotel-month combination = separate partition
• Hotel "Marriott-NYC" + "2024-12" = one partition
• Hotel "Marriott-NYC" + "2024-11" = different partition
• Result: Even distribution! ✓

Benefits:
• Popular hotel: 10M total bookings split into 120+ partitions (1 per month)
• Each partition: ~83K bookings (manageable!)
• Prevents hot partition ✓
• Natural for queries (usually search within month) ✓

Queries:
-- Get December bookings
SELECT * FROM hotel_bookings
WHERE hotel_id = ?
AND month_bucket = '2024-12';

-- Get bookings for specific date
SELECT * FROM hotel_bookings
WHERE hotel_id = ?
AND month_bucket = '2024-12'
AND check_in_date = '2024-12-25';

Example 3: Gaming - Player Achievements (Complex Composite)

Requirements: Multi-game platform, track achievements per game, show recent unlocks

CREATE TABLE player_achievements (
  player_id UUID,
  game_id UUID,
  unlock_time TIMESTAMP,
  achievement_id UUID,
  achievement_name TEXT,
  achievement_tier TEXT, -- 'Bronze', 'Silver', 'Gold'
  points INT,
  PRIMARY KEY ((player_id, game_id), unlock_time, achievement_id)
) WITH CLUSTERING ORDER BY (unlock_time DESC, achievement_id ASC);
Design Breakdown:
• Composite Partition: (player_id, game_id)
• Each player-game combo = separate partition
• Player Alice in "Fortnite" = partition 1
• Player Alice in "Minecraft" = partition 2
• Clustering: unlock_time DESC (recent first)

Benefits:
• Natural data grouping by game
• Prevents cross-game pollution
• Efficient "recent achievements in this game" queries

Query Examples:
-- Get Alice's Fortnite achievements
SELECT * FROM player_achievements
WHERE player_id = 'alice-uuid'
AND game_id = 'fortnite-uuid'
LIMIT 20;

-- Get recent unlocks (last 7 days)
SELECT * FROM player_achievements
WHERE player_id = 'alice-uuid'
AND game_id = 'fortnite-uuid'
AND unlock_time > now() - 7d;

1. Choose High-Cardinality Partition Keys

High-cardinality = many unique values

Good choices:
✅ user_id (millions of users)
✅ order_id (billions of orders)
✅ device_id (millions of devices)
✅ session_id (unique per session)

Bad choices:
✗ country (only ~200 values)
✗ status ('active'/'inactive')
✗ gender ('M'/'F'/'Other')
✗ boolean flags

Why: Even distribution = better performance!
Low cardinality = hot partitions = slow queries ✗

2. Always Include Partition Key in Queries

MANDATORY for performance!

✅ Good:
WHERE user_id = ?
Direct node lookup = 2ms ⚡

✗ Bad:
WHERE name = 'Alice'
Full cluster scan = 10-60s ✗

Rule: If you can't include partition key,
create new table with different partition key!

3. Use Time Bucketing for Time-Series

Prevent unbounded partition growth!

✗ Bad:
PRIMARY KEY (sensor_id, time)
Grows forever! ✗

✅ Good:
PRIMARY KEY
((sensor_id, day), time)
Bounded per day! ✓

Bucket sizes:
• Day: Normal frequency
• Hour: High frequency
• Month: Low frequency

4. Monitor Partition Sizes

Set alerts and monitor!

Recommended limits:
• Warning: 50MB
• Critical: 100MB
• Maximum: 2GB (hard limit)

Check with:
nodetool cfstats table_name

If too large:
Add time bucketing or
redesign partition key!

5. Design for Query Patterns

Know HOW you'll query!

Questions to ask:
• What queries are most common?
• Need recent data or historical?
• Range queries needed?
• Multiple access patterns?

Example:
"Show user's orders" →
PRIMARY KEY (user_id, order_date)

Different query = different table!
Query-driven design is key! 🔑

6. Use DESC for Time-Series

Most apps need recent data first!

✅ Recommended:
WITH CLUSTERING ORDER BY
(timestamp DESC)
Newest first! ⚡

Use cases:
• Activity feeds
• Chat messages
• Recent orders
• Latest logs

~90% of use cases need DESC!

7. Document Your Design

Explain WHY you chose that key!

In schema comments:
-- Partition by user_id:
-- groups user's data together
-- Cluster by timestamp DESC:
-- shows recent activity first

Document:
• Query patterns served
• Expected data volumes
• Why DESC vs ASC
• Time bucket rationale

Future you will thank you! 🙏

8. Test at Scale Early

Don't wait for production!

Load test with:
• Realistic data volumes
• Actual query patterns
• Expected growth (3-5 years)

Tools:
• cassandra-stress
• nosqlbench
• Custom scripts

Check:
• Partition sizes
• Query latencies (P99)
• Distribution (hot partitions?)

Better to redesign early! ✓

1. Using Low-Cardinality Partition Keys

DISASTER:
PRIMARY KEY (country, user_id)

Problem:
• Only ~200 countries worldwide
• USA partition = 100M users! 💥
• Hot partition, slow queries
• Uneven load distribution

FIX:
PRIMARY KEY (user_id, country)
Millions of users = even distribution! ✓

Rule: Partition key should have
thousands to millions of unique values!

2. Unbounded Time-Series Partitions

DISASTER:
PRIMARY KEY (sensor_id, timestamp)

Problem:
• Partition grows forever!
• After 1 year: 6M+ rows
• Partition size: 500MB+
• Queries slow over time 📉

FIX - Time Bucketing:
PRIMARY KEY
((sensor_id, day_bucket),
timestamp)
Each day = new partition! ✓
Bounded size: ~17K rows/day ✓

3. Querying Without Partition Key

DISASTER:
SELECT * FROM users
WHERE email = '[email protected]';

Problem:
• Scans ALL nodes! (100+)
• Checks ALL partitions
• 10-60 seconds or timeout ✗
• May crash cluster under load

FIX - Create Appropriate Table:
CREATE TABLE users_by_email (
  email TEXT PRIMARY KEY,
  ...
);

SELECT * FROM users_by_email
WHERE email = ?;
Direct lookup = 2ms! ⚡

4. Using ALLOW FILTERING

MAJOR RED FLAG:
SELECT * FROM users
WHERE age > 25
ALLOW FILTERING;

What it means:
"I know this is slow and dangerous,
do it anyway!" 🚨

Problem:
• Scans entire table
• Filters in memory
• Extremely slow (minutes!)
• Can OOM (out of memory)

FIX - Redesign Schema:
Create table with age as partition key
or part of composite key!

5. Wrong Clustering Order

MISTAKE:
Need recent data first, but:
PRIMARY KEY (user_id, timestamp)
-- No ORDER BY specified!
-- Default = ASC (oldest first)

Problem:
• Returns oldest data first
• Must reverse in application
• Extra processing overhead
• Wrong UX for users

FIX - Be Explicit:
WITH CLUSTERING ORDER BY
(timestamp DESC);
Always specify the order you need! ✓

6. Secondary Indexes (Misuse)

TEMPTING BUT DANGEROUS:
CREATE INDEX ON users(email);

Problem:
• Distributed index = queries all nodes!
• Slow with many matching rows
• Can timeout under load
• Not suitable for high-cardinality

When indexes fail:
• High-cardinality columns (email, user_id)
• Queries returning many rows
• Production load

FIX:
Create dedicated table with column as partition key!
CREATE TABLE users_by_email (
  email TEXT PRIMARY KEY,
  ...
);

Token-Aware Routing

What is token-aware routing?
When your application knows which node owns which token ranges, it can send queries directly to the correct node, avoiding the coordinator hop.

How it works:
1. Cassandra divides the token space (-2^63 to 2^63) into ranges
2. Each node owns specific token ranges
3. partition_key → hash → token → node owner
4. Driver maintains token map and routes directly

Performance benefit:
• Without token-aware: Client → Coordinator → Data node → Coordinator → Client (2 hops)
• With token-aware: Client → Data node → Client (1 hop)
• Latency reduction: 30-50%
• Lower coordinator load

Enable in drivers:
// Java driver
.withLoadBalancingPolicy(new TokenAwarePolicy(new DCAwareRoundRobinPolicy()))

// Python driver
cluster = Cluster(load_balancing_policy=TokenAwarePolicy(DCAwareRoundRobinPolicy()))
Why this matters for primary keys:
Your partition key choice directly impacts routing efficiency. High-cardinality keys with even distribution maximize token-aware routing benefits!

Partition Key Cardinality Analysis

Mathematical approach to choosing partition keys:

Formula for partition count:
Expected partitions = Total rows / Cardinality

Example 1: E-commerce orders
• Total rows: 100M orders
• Option A - country as partition key: ~200 countries
  → 100M / 200 = 500K rows per partition! ✗ Too large!

• Option B - customer_id as partition key: 5M customers
  → 100M / 5M = 20 rows per partition ✓ Perfect!

Example 2: IoT sensors (unbounded growth)
• Sensor reports: Every 5 seconds = 17,280/day
• Option A - sensor_id only: 10M sensors
  → After 1 year: 6.3M rows per sensor! ✗

• Option B - (sensor_id, day_bucket): 10M × 365
  → 17,280 rows per partition ✓ Manageable!

Target ranges:
• Ideal partition size: 10KB - 50MB
• Ideal rows per partition: 1 - 100K
• Minimum cardinality: 1,000+ unique values
• Optimal cardinality: 1M+ unique values

Check your cardinality:
SELECT COUNT(DISTINCT partition_key) FROM table;
-- Should return thousands to millions

Write Path & Partition Keys

Understanding how writes use partition keys:

Write process step-by-step:
1. Client sends write with partition key
2. Driver calculates token: hash(partition_key) → token value
3. Token-aware routing: Driver knows which node owns token
4. Coordinator receives: Node that owns the token range
5. Replication: Write to N replicas (based on RF)
6. Commit log: Durable write to disk
7. Memtable: In-memory sorted structure
8. Acknowledge: Return success to client

Why partition key matters for writes:
• Determines which node is coordinator
• Affects which replicas receive data
• Clustering columns determine sort order in memtable
• Pre-sorting at write = fast reads later!

Write performance considerations:
• Single partition write: 1-3ms typical
• Batch to same partition: 2-5ms (efficient!)
• Batch to different partitions: 10-30ms (slower)
• Hot partition writes: Can cause write timeouts

Batch write optimization:
-- GOOD: Batch to same partition
BEGIN BATCH
INSERT INTO users (user_id, ...) VALUES (?, ...);
INSERT INTO user_activity (user_id, ...) VALUES (?, ...);
APPLY BATCH;
-- Single coordinator, efficient!

-- BAD: Batch to many partitions
BEGIN BATCH
INSERT INTO users (user_id, ...) VALUES (user1, ...);
INSERT INTO users (user_id, ...) VALUES (user2, ...);
INSERT INTO users (user_id, ...) VALUES (user3, ...);
APPLY BATCH;
-- Multiple coordinators, slow!

Denormalization Patterns

Cassandra requires different thinking from SQL:

SQL Mindset (Normalized):
One truth, multiple views through JOINs
-- SQL approach
CREATE TABLE users (user_id INT, name TEXT);
CREATE TABLE orders (order_id INT, user_id INT);

-- Get user's orders
SELECT * FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.user_id = ?;
Cassandra Mindset (Denormalized):
Multiple truths, no JOINs, query-driven design
-- Cassandra approach
CREATE TABLE orders_by_user (
  user_id UUID, -- PARTITION KEY
  order_date TIMESTAMP, -- CLUSTERING
  order_id UUID,
  user_name TEXT, -- Denormalized!
  user_email TEXT, -- Denormalized!
  order_total DECIMAL,
  PRIMARY KEY (user_id, order_date)
);

-- Get user's orders (instant!)
SELECT * FROM orders_by_user
WHERE user_id = ?;
Denormalization strategies:

1. Duplicate data across tables:
Same logical entity in multiple tables with different partition keys
• orders_by_user (partition: user_id)
• orders_by_date (partition: order_date)
• orders_by_id (partition: order_id)

2. Embed related data:
Store user info in order table to avoid second query
• user_name, user_email in orders table
• product_name in order_items table

3. Use collections for one-to-many:
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  email_addresses SET<TEXT>,
  phone_numbers LIST<TEXT>,
  address MAP<TEXT, TEXT>
);
Trade-offs:
• Storage cost: 2-5x more (but storage is cheap!)
• Write complexity: Update multiple tables
• Read performance: 100x faster (no JOINs!)
• Consistency: Eventual consistency between tables

Golden rule: Optimize for reads, not writes. Users read 100x more than they write!

Partition Size Calculation

How to calculate expected partition size:

Formula:
Partition Size = (Average Row Size × Rows per Partition) + Overhead

Example: User activity logs
CREATE TABLE user_activity (
  user_id UUID, -- 16 bytes
  activity_time TIMESTAMP, -- 8 bytes
  activity_type TEXT, -- ~20 bytes
  activity_data TEXT, -- ~100 bytes
  ip_address TEXT, -- ~15 bytes
  PRIMARY KEY (user_id, activity_time)
);
Calculation:
1. Row size: 16 + 8 + 20 + 100 + 15 = 159 bytes per row
2. Overhead: ~20% for metadata = 159 × 1.2 = 191 bytes
3. Average user: 1,000 activities
4. Partition size: 191 × 1,000 = 191,000 bytes = ~186 KB ✓ Good!

Example: Sensor readings (unbounded)
• Row size: ~80 bytes
• Frequency: Every 5 seconds = 17,280 readings/day
• Without bucketing: 80 × 17,280 × 365 = 505 MB/year! ✗
• With day bucketing: 80 × 17,280 = 1.32 MB/partition ✓

Warning thresholds:
• < 10 MB: Excellent ✓
• 10-50 MB: Good ✓
• 50-100 MB: Warning ⚠️
• > 100 MB: Critical 🚨
• > 2 GB: Will fail 💥

Check actual partition sizes:
nodetool cfstats keyspace.table | grep "Partition Size"

Problem 1: Queries Taking 10+ Seconds

Symptoms:
• Queries that used to be fast are now slow
• Timeout errors appearing
• P99 latency > 1 second

Likely causes:
1. Querying without partition key (full cluster scan)
2. Large partition (> 100MB) causing slow reads
3. Too many tombstones in partition

Diagnosis steps:
-- Check if partition key included
TRACING ON;
SELECT * FROM table WHERE ...;
-- Look for "Sending request to X nodes" (bad if X > 1)

-- Check partition sizes
nodetool cfstats keyspace.table
nodetool tablehistograms keyspace.table

-- Check for tombstones
SELECT * FROM table WHERE partition_key = ?;
-- Enable metrics to see tombstone count
Solutions:
• Missing partition key: Redesign query to include it
• Large partition: Add time bucketing to schema
• Tombstones: Run repair, adjust gc_grace_seconds

Problem 2: Uneven Cluster Load

Symptoms:
• One node at 90% CPU, others at 10%
• Uneven disk usage across nodes
• Some nodes getting all the traffic

Likely causes:
1. Low-cardinality partition key (hot partitions)
2. Celebrity/popular entity problem
3. Poor token distribution

Diagnosis:
-- Check node load
nodetool status
nodetool ring

-- Check top partitions by size
nodetool toppartitions keyspace.table 10

-- Check cardinality
SELECT COUNT(DISTINCT partition_key) FROM table;

Solutions:
• Low cardinality: Choose different partition key with more unique values
• Celebrity problem: Add sharding column or use fan-out pattern
• Token distribution: Run nodetool rebuild, check vnodes configuration

Problem 3: "Partition Larger Than 100MB" Warnings

Symptoms:
• Warning in logs: "Writing large partition"
• Compactions taking hours
• Memory pressure on nodes

Root cause:
Unbounded partition growth, usually time-series without bucketing

Diagnosis:
nodetool cfstats keyspace.table | grep -A 10 "Partition Size"
nodetool toppartitions keyspace.table 10
Immediate mitigation:
1. Increase heap size temporarily
2. Adjust compaction strategy
3. Limit query result size (LIMIT clause)

Long-term fix:
Must redesign schema with time bucketing:
-- OLD (bad)
PRIMARY KEY (entity_id, timestamp)

-- NEW (fixed)
PRIMARY KEY ((entity_id, day_bucket), timestamp)

-- Migration required!

Problem 4: Write Timeouts

Symptoms:
• WriteTimeout exceptions
• Writes taking > 1 second
• Failed writes in application logs

Partition key related causes:
1. Writing to hot partition (overwhelmed node)
2. Large partition causing slow memtable flushes
3. Batch writes spanning many partitions

Solutions:
• Hot partition: Add composite partition key or sharding
• Large partition: Implement time bucketing
• Multi-partition batch: Break into single-partition batches
• Increase write timeout (temporary only!)

Problem 5: Cannot Query By Column X

Error message:
"Cannot execute this query as it might involve data filtering"

Cause:
Trying to query by column that's not partition key or clustering column

Example:
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  email TEXT,
  name TEXT
);

-- This will fail!
SELECT * FROM users WHERE email = 'alice@email.com';
-- Error: Must specify partition key
Solution options:

Option 1: Create new table (BEST)
CREATE TABLE users_by_email (
  email TEXT PRIMARY KEY,
  user_id UUID,
  name TEXT
);

SELECT * FROM users_by_email WHERE email = ?;
Option 2: Secondary index (RARELY GOOD)
Only for low-cardinality columns with limited result sets

Option 3: ALLOW FILTERING (NEVER DO THIS)
Extremely slow, defeats Cassandra's purpose

Zero-Downtime Migration Tips

• ✅ Use feature flags to control dual-write behavior
• ✅ Monitor write latency (should not increase significantly)
• ✅ Backfill in small batches (1000 rows at a time)
• ✅ Add checkpointing to resume failed backfills
• ✅ Verify data consistency before switching reads
• ✅ Keep dual writes for at least 1 week after read switch
• ✅ Have automated rollback scripts ready

Common Migration Mistakes

• ❌ Switching reads before backfill completes
• ❌ Dropping old table too quickly
• ❌ No rollback plan
• ❌ Backfilling during peak traffic
• ❌ Not testing on production-scale data
• ❌ Forgetting to update all application instances
• ❌ No monitoring during migration

Benchmark: Query Performance by Design

Read vs Write Optimization Patterns

Partition Size Impact on Performance

Optimization Checklist

🎯 The Essential Rules (Print This!)

⚡ Quick Decision Matrix

🔍 Monitoring Commands

📈 Target Metrics

• Query latency: P99 < 10ms
• Partition size: < 100MB
• Rows/partition: < 100K
• Write latency: < 5ms
• Compaction time: Minutes not hours
• Node CPU: Evenly distributed
• Cardinality: 1M+ unique values

🚨 Alert Thresholds

• Warning: Partition > 50MB
• Critical: Partition > 100MB
• Critical: P99 latency > 100ms
• Warning: Uneven node load (>20% diff)
• Critical: Compaction > 1 hour
• Warning: Tombstone ratio > 20%
• Critical: Write timeouts

Official Documentation

• Apache Cassandra Docs
• DataStax Academy (Free courses)
• CQL Reference Guide
• Data Modeling Guidelines

Recommended Books

• "Cassandra: The Definitive Guide"
• "Mastering Apache Cassandra"
• "Learning Apache Cassandra"
• "Cassandra Data Modeling"

Video Tutorials

• DataStax YouTube Channel
• ApacheCon Presentations
• Cassandra Summit Talks
• Tech conference recordings

Practice Tools

• Docker Cassandra images
• CCM (Cassandra Cluster Manager)
• DataStax Studio
• NoSQLBench (load testing)

Community

• Apache Cassandra Slack
• Stack Overflow #cassandra tag
• Reddit r/cassandra
• Local Cassandra meetups

Production Resources

• Netflix blog (Cassandra at scale)
• Uber engineering blog
• Discord engineering blog
• DataStax case studies

🔑 Primary Key Syntax Cheat Sheet

✅ Do's

• ✅ Use high-cardinality partition keys (millions of values)
• ✅ Always include partition key in WHERE clause
• ✅ Use time bucketing for time-series data
• ✅ Use DESC for recent-first queries (~90% of cases)
• ✅ Monitor partition sizes (<100MB ideal)
• ✅ Design for specific query patterns
• ✅ Duplicate data across tables for different queries
• ✅ Document your design decisions
• ✅ Test at scale before production
• ✅ Use TTL for time-series data cleanup

❌ Don'ts

• ❌ Don't use low-cardinality partition keys (country, status)
• ❌ Don't query without partition key
• ❌ Don't use ALLOW FILTERING (redesign instead)
• ❌ Don't let partitions grow unbounded
• ❌ Don't rely on default ASC (be explicit)
• ❌ Don't use secondary indexes for high-cardinality
• ❌ Don't skip clustering columns in WHERE clause
• ❌ Don't range query on partition key components
• ❌ Don't ignore partition size warnings
• ❌ Don't design like SQL (no JOINs!)

⚡ Performance Quick Wins

🎯 Decision Tree: Choosing Your Primary Key

📋 Common Use Case Patterns