Advanced CQL Features

CQL Practice in Cassandra

Roll up your sleeves and start coding! CQL practice helps you learn how to create tables, insert data, and run powerful queries — like learning the language your database speaks.

📖 The Story: Tom's Production Nightmare

Tom launched his social media app with 1 million users. Everything worked in development. But on launch day, the entire system CRASHED within 2 hours. Here's what went wrong...

❌ The Disasters: What Tom Did Wrong

Mistake #1: No Partition Key Strategy

CREATE TABLE user_posts (
    user_id UUID PRIMARY KEY,  -- ❌ WRONG!
    post_id TIMEUUID,
    content TEXT
);

-- Problem: One partition per user!
-- Influencer with 100K posts = 100K rows in ONE partition!
-- Result: MASSIVE hot partitions, nodes crash! 💥
                

Mistake #2: SELECT * Everywhere

SELECT * FROM user_posts;  -- ❌ Scans ENTIRE table!
-- Reads 1 million rows across all nodes
-- Timeout after 90 seconds! ⏱️
                

Mistake #3: ALLOW FILTERING on Everything

SELECT * FROM users 
WHERE age > 18 ALLOW FILTERING;  -- ❌ Full table scan!
-- Query takes 45 seconds on 1M users
-- Users rage quit! 😡
                

The Result:

💥 System crashes every 2 hours (OOM errors)
⏱️ Query timeouts everywhere (30-90 seconds)
🔥 Hot partitions cause node failures
📉 App rating drops from 4.8 to 1.2 stars
💸 AWS bill skyrockets to $50,000/month

✅ The Fix: Following Best Practices!

Fix #1: Proper Partition Key Design

CREATE TABLE user_posts (
    user_id UUID,
    post_date DATE,           -- ✅ Bucket by date!
    post_id TIMEUUID,
    content TEXT,
    PRIMARY KEY ((user_id, post_date), post_id)
) WITH CLUSTERING ORDER BY (post_id DESC);

-- Now: Max ~100 posts per partition (manageable!)
-- Even influencers have small, fast partitions ⚡
                

Fix #2: Always Specify Partition Key

SELECT * FROM user_posts
WHERE user_id = 123 
  AND post_date = '2025-01-03'
LIMIT 20;

-- ⚡ Returns in 5ms! Single partition read!
                

Fix #3: Query-Driven Design

-- Create table per query pattern
CREATE TABLE users_by_age (
    age INT,
    user_id UUID,
    name TEXT,
    PRIMARY KEY (age, user_id)
);

SELECT * FROM users_by_age
WHERE age = 25 LIMIT 50;  -- ⚡ 3ms!
                

The Results:

✅ Zero crashes: 99.99% uptime for 6 months!
✅ Blazing fast: All queries < 10ms
✅ Scalable: Handles 10M users easily
✅ Happy users: Rating back to 4.9 stars!
✅ Cost savings: AWS bill down to $8,000/month

Tom learned: Following best practices = Production success! 🎉

🔍 Query Best Practices

Write fast, efficient queries that scale to billions of rows!

✅

DO's

Always Use Partition Key: WHERE pk = value
Use LIMIT: Prevent massive result sets
Select Specific Columns: SELECT a, b, c (not *)
Use Prepared Statements: Better performance + security
Batch Wisely: Only same partition, max 100 statements
Use Paging: fetchSize for large results
TTL for Temporary Data: Auto-cleanup

❌

DON'Ts

Never SELECT *: Without partition key
Avoid ALLOW FILTERING: Full table scan!
Don't Use IN Heavily: Max 10-20 values
No Cross-Partition Batches: Kills performance
Don't Query All Partitions: Use token ranges if needed
Avoid Large Collections: Max 100-1000 elements
No Range Scans on PK: Use clustering columns

💡

Pro Tips

Use Token Function: For parallel scans
TRACING ON: See query execution path
Async Queries: Better throughput
Retry Logic: Handle timeouts gracefully
Consistency Tuning: ONE/LOCAL_ONE for reads
Lightweight Transactions: Use sparingly (Paxos)
Monitor Slow Queries: Log queries > 100ms

Query Examples: Good vs Bad

-- ❌ BAD: No partition key (scans entire cluster!)
SELECT * FROM users 
WHERE age > 25 ALLOW FILTERING;  
-- Scans 100M rows across 50 nodes! Takes 2 minutes! 😱

-- ✅ GOOD: Always specify partition key
SELECT name, email FROM users
WHERE user_id = 123;
-- Reads 1 partition, 1 node! Returns in 2ms! ⚡

-----------------------------------

-- ❌ BAD: SELECT * returns ALL columns
SELECT * FROM products 
WHERE category = 'Electronics';
-- Returns 50 columns including 5MB images! 💥

-- ✅ GOOD: Select only what you need
SELECT product_id, name, price 
FROM products
WHERE category = 'Electronics'
LIMIT 100;
-- Returns only 3 columns, max 100 rows! ⚡

-----------------------------------

-- ❌ BAD: Large IN clause
SELECT * FROM users
WHERE user_id IN (1, 2, 3, ..., 500);  -- 500 IDs!
-- Coordinator becomes bottleneck! 🐌

-- ✅ GOOD: Limit IN to 10-20 values
SELECT * FROM users
WHERE user_id IN (1, 2, 3, 4, 5);
-- Or use async queries in parallel

-----------------------------------

-- ❌ BAD: Cross-partition BATCH
BEGIN BATCH
  INSERT INTO users (user_id, ...) VALUES (1, ...);
  INSERT INTO users (user_id, ...) VALUES (2, ...);
  INSERT INTO users (user_id, ...) VALUES (3, ...);
APPLY BATCH;
-- Coordinator must coordinate across partitions! Slow! 🐌

-- ✅ GOOD: BATCH only for same partition
BEGIN BATCH
  INSERT INTO posts_by_user (user_id, post_id, ...) VALUES (1, 101, ...);
  INSERT INTO posts_by_hashtag (hashtag, post_id, ...) VALUES ('#tech', 101, ...);
APPLY BATCH;
-- Use for maintaining denormalized data consistency
    

🎨 Data Modeling Best Practices

The foundation of performance: Model your data right from day one!

1. Query-Driven Design (Not Entity-Driven!)

Design tables around queries, not entities. One table per query pattern!

-- ❌ BAD: Entity-driven (like SQL)
CREATE TABLE users (...);
CREATE TABLE posts (...);
CREATE TABLE comments (...);
-- Then try to JOIN (Cassandra doesn't support JOINs!)

-- ✅ GOOD: Query-driven design
-- Query 1: Get user's posts
CREATE TABLE posts_by_user (
    user_id UUID,
    post_id TIMEUUID,
    content TEXT,
    PRIMARY KEY (user_id, post_id)
);

-- Query 2: Get posts by hashtag
CREATE TABLE posts_by_hashtag (
    hashtag TEXT,
    post_id TIMEUUID,
    user_id UUID,
    content TEXT,
    PRIMARY KEY (hashtag, post_id)
);

-- Denormalization is your friend!
                

2. Keep Partitions Small (< 100MB)

Large partitions cause hot spots, slow reads, and memory issues!

-- ❌ BAD: Unbounded partition (can grow forever!)
CREATE TABLE sensor_data (
    sensor_id UUID PRIMARY KEY,  -- ❌ All data for one sensor!
    timestamp TIMESTAMP,
    temperature DECIMAL
);
-- After 1 year: 31M rows in ONE partition! 💥

-- ✅ GOOD: Bucket by time (bounded partitions)
CREATE TABLE sensor_data (
    sensor_id UUID,
    date_bucket DATE,        -- ✅ Partition per day!
    timestamp TIMESTAMP,
    temperature DECIMAL,
    PRIMARY KEY ((sensor_id, date_bucket), timestamp)
);
-- Now: Max ~86K rows per partition (1 reading/second/day)
                

Partition Size Limits

Soft Limit: 100MB per partition
Row Limit: 100,000 rows recommended
Hard Limit: 2GB (but you'll have problems before this!)

3. Choose the Right Partition Key

The partition key determines data distribution - choose wisely!

-- ❌ BAD: Low cardinality partition key
CREATE TABLE users (
    country TEXT,  -- ❌ Only ~200 unique values!
    user_id UUID,
    PRIMARY KEY (country, user_id)
);
-- USA partition: 150M users! MASSIVE hot partition! 🔥

-- ✅ GOOD: High cardinality partition key
CREATE TABLE users (
    user_id UUID,     -- ✅ Millions of unique values!
    country TEXT,
    name TEXT,
    PRIMARY KEY (user_id)
);

-- Need to query by country? Create separate table!
CREATE TABLE users_by_country (
    country TEXT,
    user_id UUID,
    name TEXT,
    PRIMARY KEY (country, user_id)
);
                

4. Use Clustering Columns for Sorting

Clustering columns provide automatic sorting - use them!

-- ✅ GOOD: Clustering columns for ordering
CREATE TABLE messages (
    conversation_id UUID,
    message_time TIMESTAMP,    -- Clustering column
    message_id TIMEUUID,       -- Clustering column
    sender_id UUID,
    content TEXT,
    PRIMARY KEY (conversation_id, message_time, message_id)
) WITH CLUSTERING ORDER BY (message_time DESC, message_id DESC);

-- Query: Get latest 20 messages (pre-sorted!)
SELECT * FROM messages
WHERE conversation_id = 123
LIMIT 20;  -- ⚡ Already sorted DESC!
                

5. Denormalize for Performance

Duplicate data across tables - storage is cheap, JOINs are impossible!

-- Denormalize user info into posts table
CREATE TABLE posts_by_user (
    user_id UUID,
    post_id TIMEUUID,
    -- Denormalized user data ✅
    username TEXT,
    user_avatar TEXT,
    -- Post data
    content TEXT,
    likes INT,
    PRIMARY KEY (user_id, post_id)
);

-- No need to fetch from users table separately!
-- Trade-off: Must update username in multiple tables if it changes
                

🏗️ Schema Design Best Practices

Design schemas that are maintainable, scalable, and performant!

1. Use Meaningful Naming Conventions

-- ❌ BAD: Cryptic names
CREATE TABLE t1 (
    pk UUID,
    c1 TEXT,
    c2 INT,
    PRIMARY KEY (pk)
);

-- ✅ GOOD: Clear, descriptive names
CREATE TABLE orders_by_customer (
    customer_id UUID,
    order_date DATE,
    order_id TIMEUUID,
    total_amount DECIMAL,
    status TEXT,
    PRIMARY KEY ((customer_id, order_date), order_id)
) WITH CLUSTERING ORDER BY (order_id DESC);

-- Naming convention:
-- Table: {entity}_by_{query_pattern}
-- Example: users_by_email, posts_by_hashtag
                

2. Choose Appropriate Data Types

-- ❌ BAD: Wrong data types
CREATE TABLE events (
    event_id TEXT,           -- ❌ Should be UUID!
    timestamp BIGINT,        -- ❌ Should be TIMESTAMP!
    price FLOAT,             -- ❌ Should be DECIMAL!
    is_active TEXT,          -- ❌ Should be BOOLEAN!
    PRIMARY KEY (event_id)
);

-- ✅ GOOD: Correct data types
CREATE TABLE events (
    event_id UUID,           -- ✅ Proper UUID
    timestamp TIMESTAMP,     -- ✅ Native timestamp
    price DECIMAL,           -- ✅ Exact decimal (no rounding)
    is_active BOOLEAN,       -- ✅ True/False
    metadata MAP<TEXT, TEXT>, -- ✅ For flexible key-value data
    PRIMARY KEY (event_id)
);
                

3. Use TTL for Temporary Data

-- ✅ GOOD: TTL for session data (auto-cleanup!)
CREATE TABLE user_sessions (
    session_id UUID PRIMARY KEY,
    user_id UUID,
    login_time TIMESTAMP,
    ip_address TEXT
) WITH default_time_to_live = 86400;  -- 24 hours

-- Or set TTL per row:
INSERT INTO user_sessions (...) VALUES (...)
USING TTL 3600;  -- 1 hour

-- Perfect for: sessions, caches, temporary tokens, OTPs
                

4. Set Compaction Strategy Wisely

-- Time-series data? Use TimeWindowCompactionStrategy
CREATE TABLE sensor_readings (
    sensor_id UUID,
    reading_time TIMESTAMP,
    temperature DECIMAL,
    PRIMARY KEY (sensor_id, reading_time)
) WITH compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_unit': 'DAYS',
    'compaction_window_size': 1
};

-- Write-heavy, few updates? Use LeveledCompactionStrategy
CREATE TABLE logs (
    log_id TIMEUUID PRIMARY KEY,
    message TEXT
) WITH compaction = {
    'class': 'LeveledCompactionStrategy'
};

-- General purpose? Use SizeTieredCompactionStrategy (default)
                

5. Configure Table Properties

CREATE TABLE important_data (
    id UUID PRIMARY KEY,
    data TEXT
) WITH
    comment = 'User profile data - DO NOT DROP!'
    AND gc_grace_seconds = 864000      -- 10 days
    AND bloom_filter_fp_chance = 0.01  -- 1% false positive
    AND caching = {
        'keys': 'ALL',
        'rows_per_partition': '100'
    }
    AND compression = {
        'class': 'LZ4Compressor',
        'chunk_length_in_kb': 64
    };
                

✍️ Write & Read Best Practices

Optimize your writes and reads for maximum performance!

✍️

Write Best Practices

Use Prepared Statements: 10x faster + prevents injection
Batch Same Partition: Atomic updates to denormalized data
Async Writes: Better throughput for bulk operations
Set Consistency Wisely: LOCAL_ONE for writes (fast!)
Use Lightweight Transactions Sparingly: 4x slower (Paxos)
Avoid Hot Partitions: Distribute writes evenly
Use TTL: Auto-expire temporary data

📖

Read Best Practices

Always Use PK: Single-partition reads are fastest
Use LIMIT: Prevent accidentally reading millions of rows
Select Specific Columns: Don't SELECT * unnecessarily
Use Paging: fetchSize for large result sets
Consistency ONE/LOCAL_ONE: Fastest reads
Cache Hot Data: Application-level or row cache
Avoid ALLOW FILTERING: Full table scan = slow

Write Examples: Best Practices

-- ✅ GOOD: Prepared statement (faster + secure)
PREPARE insert_user AS
    INSERT INTO users (user_id, name, email)
    VALUES (?, ?, ?);

EXECUTE insert_user (uuid(), 'John Doe', 'john@example.com');

-----------------------------------

-- ✅ GOOD: BATCH for denormalized data (same partition!)
BEGIN BATCH
  -- Update post in posts_by_user table
  UPDATE posts_by_user
  SET content = 'Updated content'
  WHERE user_id = 123 AND post_id = 456;
  
  -- Update same post in posts_by_hashtag table
  UPDATE posts_by_hashtag
  SET content = 'Updated content'
  WHERE hashtag = '#cassandra' AND post_id = 456;
APPLY BATCH;

-- Ensures both tables updated atomically!

-----------------------------------

-- ✅ GOOD: TTL for temporary data
INSERT INTO password_reset_tokens (token, user_id, created_at)
VALUES ('abc123', uuid(), toTimestamp(now()))
USING TTL 3600;  -- Expires in 1 hour automatically!

-----------------------------------

-- ❌ BAD: Lightweight transaction for every write
INSERT INTO users (user_id, email)
VALUES (uuid(), 'test@example.com')
IF NOT EXISTS;  -- ❌ Uses Paxos (slow!)

-- ✅ GOOD: Use LWT only when truly needed (uniqueness)
INSERT INTO usernames (username, user_id)
VALUES ('john_doe', uuid())
IF NOT EXISTS;  -- ✅ OK for ensuring unique usernames
    

Read Examples: Best Practices

-- ✅ GOOD: Partition key + LIMIT
SELECT post_id, content, created_at
FROM posts_by_user
WHERE user_id = 123
LIMIT 20;
-- Fast! Single partition, limited results

-----------------------------------

-- ✅ GOOD: Paging for large results
-- Application code (Python example):
/*
session.default_fetch_size = 100  # Fetch 100 rows at a time
for row in session.execute(query):
    process(row)  # Automatically pages through all results
*/

-----------------------------------

-- ✅ GOOD: Consistency level for fast reads
SELECT * FROM products
WHERE product_id = 456;
-- Set consistency to ONE or LOCAL_ONE in driver
-- Reads from closest replica (fastest!)

-----------------------------------

-- ❌ BAD: Reading without partition key
SELECT * FROM users 
WHERE age = 25 ALLOW FILTERING;
-- Scans ENTIRE table! Takes minutes on large datasets!

-- ✅ GOOD: Create table for this query pattern
CREATE TABLE users_by_age (
    age INT,
    user_id UUID,
    name TEXT,
    PRIMARY KEY (age, user_id)
);

SELECT * FROM users_by_age
WHERE age = 25
LIMIT 100;
-- Now it's fast! ⚡
    

⚡ Performance Optimization Best Practices

Tune your cluster and queries for maximum performance!

1. Choose Right Consistency Level

Consistency Level Guide

LOCAL_ONE (Fastest): Reads/writes to closest node. Use for non-critical data, caching.
LOCAL_QUORUM (Recommended): Majority in local datacenter. Best balance of performance and consistency.
QUORUM (Strong Consistency): Majority across all datacenters. Slower but ensures consistency.
ALL (Slowest): All replicas. Use only for critical data. Fails if any node is down!

-- Application code (Python example):
/*
# Fast reads (sessions, caching)
session.execute(query, consistency_level=ConsistencyLevel.LOCAL_ONE)

# Balanced (most use cases)
session.execute(query, consistency_level=ConsistencyLevel.LOCAL_QUORUM)

# Strong consistency (financial transactions)
session.execute(query, consistency_level=ConsistencyLevel.QUORUM)
*/
                    

2. Monitor and Tune Compaction

-- Check compaction stats
nodetool compactionstats

-- Trigger manual compaction if needed
nodetool compact keyspace_name table_name

-- Choose right compaction strategy per table
ALTER TABLE time_series_data
WITH compaction = {
    'class': 'TimeWindowCompactionStrategy',
    'compaction_window_unit': 'HOURS',
    'compaction_window_size': 1
};
                

3. Use Caching Strategically

-- Enable row cache for hot data
ALTER TABLE user_profiles
WITH caching = {
    'keys': 'ALL',
    'rows_per_partition': '100'
};

-- Enable key cache (default, usually good)
ALTER TABLE products
WITH caching = {
    'keys': 'ALL'
};

-- Disable caching for rarely-read data
ALTER TABLE archived_logs
WITH caching = {'keys': 'NONE'};
                

4. Optimize Compression

-- LZ4: Fast compression (default, recommended)
ALTER TABLE high_throughput_table
WITH compression = {
    'class': 'LZ4Compressor'
};

-- Snappy: Good balance (use for general purpose)
ALTER TABLE general_data
WITH compression = {
    'class': 'SnappyCompressor'
};

-- Deflate: Best compression ratio (slower, for cold data)
ALTER TABLE archived_data
WITH compression = {
    'class': 'DeflateCompressor'
};
                

5. Monitor Query Performance

-- Enable tracing to see query execution
TRACING ON;

SELECT * FROM users WHERE user_id = 123;

-- Shows:
-- - Which nodes were contacted
-- - Response times from each node
-- - Total query time
-- - Whether indexes were used

TRACING OFF;

-----------------------------------

-- Check slow queries in logs
-- cassandra.yaml:
/*
slow_query_log_timeout_in_ms: 500
*/

-- Monitor with nodetool
nodetool tablestats keyspace_name
nodetool tablehistograms keyspace_name table_name
                

Performance Checklist

✅ Partitions < 100MB each
✅ Queries always use partition key
✅ No ALLOW FILTERING in production
✅ Consistency level: LOCAL_QUORUM or LOCAL_ONE
✅ Prepared statements for all queries
✅ Compaction strategy matches workload
✅ Caching enabled for hot data
✅ Slow query monitoring enabled
✅ Regular nodetool repair scheduled
✅ Replication factor ≥ 3

🚫 Anti-Patterns to Avoid

Learn from common mistakes that break production systems!

❌ Anti-Pattern #1: The "God Partition"

Storing unbounded data in a single partition.

-- ❌ BAD: All events for an entity in one partition
CREATE TABLE user_activity (
    user_id UUID PRIMARY KEY,
    event_time TIMESTAMP,
    event_type TEXT
);
-- After 5 years: Active user has 10M events in ONE partition!
-- Partition size: 2GB! Node crashes! 💥

-- ✅ GOOD: Bucket by time
CREATE TABLE user_activity (
    user_id UUID,
    year_month TEXT,    -- '2025-01', '2025-02'
    event_time TIMESTAMP,
    event_type TEXT,
    PRIMARY KEY ((user_id, year_month), event_time)
);
-- Now: Max ~4M events per partition (assuming 100/day)
                

❌ Anti-Pattern #2: Using Collections Like Arrays

Storing thousands of items in a single collection.

-- ❌ BAD: Unbounded collection
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    friend_ids SET<UUID>  -- ❌ Can grow to 100K+ friends!
);
-- Popular user with 50K friends = massive collection!
-- Reading entire row = slow! Updating = slower!

-- ✅ GOOD: Separate table for relationships
CREATE TABLE friendships (
    user_id UUID,
    friend_id UUID,
    friendship_date TIMESTAMP,
    PRIMARY KEY (user_id, friend_id)
);
-- Now scalable! Can page through results!
                

❌ Anti-Pattern #3: Using Cassandra Like SQL

Trying to JOIN, GROUP BY, or aggregate in CQL.

-- ❌ BAD: Trying to do SQL-style operations
SELECT COUNT(*) FROM users;  -- ❌ Scans entire table!

SELECT AVG(price) FROM products;  -- ❌ Not supported!

SELECT * FROM users JOIN orders...  -- ❌ No JOINs!

-- ✅ GOOD: Do aggregations in application layer
-- Maintain counts/aggregates separately
CREATE TABLE user_count (
    dummy TEXT PRIMARY KEY,
    count COUNTER
);

UPDATE user_count SET count = count + 1 
WHERE dummy = 'total';

-- For JOINs: Denormalize data into single table
                

❌ Anti-Pattern #4: Reading Before Writing

Checking if data exists before inserting (unnecessary in Cassandra).

-- ❌ BAD: Read-before-write pattern
// Application code:
/*
result = SELECT * FROM users WHERE user_id = ?
if result.empty:
    INSERT INTO users (user_id, ...) VALUES (?, ...)
*/
-- Doubles latency! Unnecessary in Cassandra!

-- ✅ GOOD: Just write (upsert is built-in!)
INSERT INTO users (user_id, name, email)
VALUES (?, ?, ?);
-- Cassandra automatically upserts! No need to check first!

-- Only use IF NOT EXISTS when uniqueness is critical:
INSERT INTO usernames (username, user_id)
VALUES ('john_doe', uuid())
IF NOT EXISTS;  -- OK for ensuring unique usernames
                

❌ Anti-Pattern #5: Logged Batches for Performance

Using BATCH for bulk inserts (actually slower!).

-- ❌ BAD: BATCH for bulk inserts (SLOWER!)
BEGIN BATCH
  INSERT INTO users (...) VALUES (...);  -- user_id = 1
  INSERT INTO users (...) VALUES (...);  -- user_id = 2
  INSERT INTO users (...) VALUES (...);  -- user_id = 3
APPLY BATCH;
-- Coordinator becomes bottleneck! Slower than individual writes!

-- ✅ GOOD: Async parallel inserts
// Application code (Python):
/*
from cassandra.concurrent import execute_concurrent_with_args

prepared = session.prepare("INSERT INTO users (...) VALUES (?, ?, ?)")
execute_concurrent_with_args(session, prepared, parameters_list, concurrency=50)
*/
-- Much faster! Parallel writes to different nodes!

-- ONLY use BATCH for denormalized data consistency
                

🖥️ Interactive Best Practices Console

Test CQL best practices in our simulator!

CQL Best Practices Validator

Enter CQL Query:

🎯 Best Practices Validator Ready!
Enter a CQL query and I'll analyze it for best practices...

Available Examples:
• Good Example: Optimized query
• Bad Example: Anti-pattern query
• Anti-Pattern: Common mistakes

💼 Interview Questions & Expert Answers

Master CQL best practices for your next interview!

1 What are the top 3 most important CQL best practices for production systems? ▼

Answer: Query-driven design, partition key management, and avoiding ALLOW FILTERING.

1. Query-Driven Data Modeling

Design tables around queries, not entities. One table per query pattern.

Why: Cassandra is optimized for fast reads when you know the partition key
Example: Need to query users by email? Create users_by_email table
Trade-off: Data duplication (acceptable - storage is cheap!)

2. Keep Partitions Small (< 100MB)

Use bucketing strategies to prevent unbounded partition growth.

Why: Large partitions cause hot spots, slow reads, memory issues
Example: Bucket time-series data by date: (sensor_id, date_bucket)
Rule: Max 100,000 rows or 100MB per partition

-- ❌ BAD: Unbounded partition
PRIMARY KEY (user_id)

-- ✅ GOOD: Bounded partition with bucketing
PRIMARY KEY ((user_id, year_month), timestamp)
                    

3. Never Use ALLOW FILTERING in Production

ALLOW FILTERING scans the entire table - creates massive performance problems.

Why: Coordinator reads from ALL nodes, filters millions of rows
Solution: Create denormalized table or use secondary index (carefully)
Exception: OK for small tables in dev/testing only

2 When should you use BATCH statements and when should you avoid them? ▼

Answer: Use BATCH only for maintaining consistency across denormalized tables in the same partition. Avoid for bulk inserts.

✅ GOOD Use Cases for BATCH:

Denormalized Data Consistency: Updating the same data in multiple tables atomically
Same Partition Writes: Multiple updates to same partition
Transactional Semantics: All-or-nothing writes for related data

-- ✅ GOOD: BATCH for denormalized data
BEGIN BATCH
  -- Update post in posts_by_user table
  UPDATE posts_by_user 
  SET title = 'Updated Title'
  WHERE user_id = 123 AND post_id = 456;
  
  -- Update same post in posts_by_tag table
  UPDATE posts_by_tag
  SET title = 'Updated Title'
  WHERE tag = '#tech' AND post_id = 456;
APPLY BATCH;
-- Ensures both tables stay in sync!
                    

❌ BAD Use Cases for BATCH:

Bulk Inserts: Actually SLOWER than individual async inserts!
Cross-Partition Batches: Coordinator becomes bottleneck
Performance Optimization: BATCH is NOT for speed

-- ❌ BAD: BATCH for bulk inserts (SLOWER!)
BEGIN BATCH
  INSERT INTO users (...) VALUES (1, ...);
  INSERT INTO users (...) VALUES (2, ...);
  INSERT INTO users (...) VALUES (3, ...);
APPLY BATCH;

-- ✅ GOOD: Async parallel inserts
// Use driver's async API to insert in parallel
// 10x faster than BATCH for bulk operations!
                    

Key Principle: BATCH is for atomicity, NOT performance. For bulk operations, use async parallel writes instead.

3 Explain the difference between denormalization and using secondary indexes. When would you choose each? ▼

Answer: Denormalization duplicates data in multiple tables for different query patterns. Secondary indexes allow querying non-PK columns but have performance limitations.

Denormalization:

What: Create separate tables for each query pattern
Pros: Fastest reads, predictable performance, scales infinitely
Cons: Data duplication, write complexity, storage overhead
When: Known query patterns, write-heavy workloads, best performance needed

-- Denormalization Example
CREATE TABLE users_by_id (
    user_id UUID PRIMARY KEY,
    email TEXT, name TEXT
);

CREATE TABLE users_by_email (
    email TEXT PRIMARY KEY,
    user_id UUID, name TEXT
);

-- Query by ID: Fast! ⚡
SELECT * FROM users_by_id WHERE user_id = ?;

-- Query by email: Fast! ⚡
SELECT * FROM users_by_email WHERE email = ?;
                    

Secondary Indexes:

What: Index on non-PK column, allows WHERE on that column
Pros: Simple to create, flexible for ad-hoc queries, no data duplication
Cons: Scatter-gather query (contacts ALL nodes), slower than denormalization
When: High cardinality, read-heavy, unpredictable queries, selective results

-- Secondary Index Example
CREATE TABLE users (
    user_id UUID PRIMARY KEY,
    email TEXT, name TEXT
);

CREATE INDEX idx_email ON users(email);

-- Query by email: Works, but slower (contacts all nodes)
SELECT * FROM users WHERE email = ?;
                    

Decision Matrix:

Factor	Denormalization	Secondary Index
Performance	⚡ Fastest	🐌 Slower
Cardinality	Any	High only
Storage	More (duplicated)	Less
Write Complexity	Higher	Lower
Best For	Known queries	Ad-hoc queries

4 What is partition key cardinality and why does it matter? ▼

Answer: Cardinality is the number of unique values. High cardinality ensures even data distribution; low cardinality causes hot partitions.

What is Cardinality?

The number of distinct values a column can have.

High Cardinality: Millions of unique values (user_id, email, UUID)
Low Cardinality: Few unique values (gender, status, country)

Why It Matters:

Cassandra distributes data across nodes based on partition key hash. Low cardinality = uneven distribution!

-- ❌ BAD: Low cardinality partition key
CREATE TABLE orders (
    status TEXT,  -- Only 5 values: pending/paid/shipped/delivered/cancelled
    order_id UUID,
    PRIMARY KEY (status, order_id)
);

-- Problem: 80% of orders are "delivered"
-- Result: 80% of data on just a few nodes!
-- Hot partition! Nodes crash under load! 💥

-- ✅ GOOD: High cardinality partition key
CREATE TABLE orders (
    order_id UUID,  -- Millions of unique values!
    customer_id UUID,
    status TEXT,
    PRIMARY KEY (order_id)
);

-- Now: Data evenly distributed across all nodes! ✅

-- Need to query by status? Create separate table!
CREATE TABLE orders_by_status (
    status TEXT,
    order_date DATE,  -- Add bucketing!
    order_id UUID,
    PRIMARY KEY ((status, order_date), order_id)
);
                    

Cardinality Examples:

✅ High (Good): user_id, email, UUID, timestamp, product_sku
⚠️ Medium: country (~200), zip_code (~40K in US), date
❌ Low (Bad): gender (2-3), boolean (2), status (3-5)

Golden Rule: Partition key should have thousands or millions of unique values for even distribution.

5 What are the best practices for consistency level selection in production? ▼

Answer: Choose consistency levels based on your application's availability vs consistency requirements. Most production systems use LOCAL_QUORUM for writes and LOCAL_ONE or LOCAL_QUORUM for reads.

Common Consistency Levels:

1. LOCAL_QUORUM (Recommended for Writes)

What: Majority of replicas in local datacenter must acknowledge
Formula: (replication_factor / 2) + 1
Example: RF=3 → requires 2 nodes
Use: Best balance of consistency and availability

2. LOCAL_ONE (Fastest Reads)

What: Only one replica in local datacenter responds
Speed: Fastest possible reads
Use: Non-critical data, caching, session data
Trade-off: May read stale data (eventual consistency)

3. QUORUM (Cross-DC Consistency)

What: Majority across ALL datacenters
Use: Critical data requiring strong consistency
Trade-off: Slower (cross-datacenter latency)

-- Application code example (Python driver):

# Fast reads (sessions, caching)
session.execute(
    query,
    consistency_level=ConsistencyLevel.LOCAL_ONE
)

# Balanced (most production use cases)
session.execute(
    query,
    consistency_level=ConsistencyLevel.LOCAL_QUORUM
)

# Strong consistency (financial, critical data)
session.execute(
    query,
    consistency_level=ConsistencyLevel.QUORUM
)

# Maximum consistency (rarely needed!)
session.execute(
    query,
    consistency_level=ConsistencyLevel.ALL  # Fails if any node down!
)
                    

Production Recommendations:

Use Case	Write CL	Read CL
Session data, caching	LOCAL_ONE	LOCAL_ONE
General application (default)	LOCAL_QUORUM	LOCAL_QUORUM
Financial transactions	QUORUM	QUORUM
Analytics (read-heavy)	LOCAL_QUORUM	LOCAL_ONE

Golden Rule: Write at LOCAL_QUORUM, read at LOCAL_ONE for best performance with reasonable consistency. Increase for critical data.

🎓 Chapter Summary: CQL Best Practices Mastery

Congratulations! You now know how to write production-grade CQL!

The 10 Commandments of CQL:

Query-Driven Design: Model tables around queries, not entities
Keep Partitions Small: < 100MB, use bucketing strategies
Always Use Partition Key: No queries without WHERE partition_key = ?
Never ALLOW FILTERING: Create proper tables or indexes instead
High Cardinality Keys: Millions of unique values for even distribution
Denormalize Fearlessly: Duplicate data for query patterns
Use LIMIT Always: Prevent accidentally reading millions of rows
BATCH for Atomicity: Not for performance! Use async for bulk
Prepared Statements: 10x faster + prevents SQL injection
LOCAL_QUORUM Default: Best consistency/performance balance

Critical Anti-Patterns to Avoid:

❌ The "God Partition" - unbounded partitions
❌ Using collections like arrays (100K+ items)
❌ Trying to use Cassandra like SQL (JOINs, aggregations)
❌ Read-before-write patterns (unnecessary!)
❌ BATCH for bulk inserts (actually slower!)
❌ SELECT * without partition key
❌ Low cardinality partition keys

Performance Checklist:

✅ All queries use partition key
✅ Partitions < 100MB / 100K rows
✅ Prepared statements for all queries
✅ Consistency level: LOCAL_QUORUM or LOCAL_ONE
✅ TTL for temporary data
✅ Proper compaction strategy per table
✅ No ALLOW FILTERING in production
✅ LIMIT on all queries
✅ High cardinality partition keys
✅ Monitoring and slow query logging enabled

🚀 You're now ready to build production-grade Cassandra applications!

Remember Tom's story: Following these best practices = Happy users, 99.99% uptime, and low AWS bills! 🎉

Responsive Ad