Denormalization in Cassandra

Answer:

Denormalization is the intentional duplication of data across multiple tables to optimize read performance by avoiding JOINs.

Why Required in Cassandra:

• No JOINs Support: Cassandra doesn't support JOINs between tables - data would need to be gathered from multiple nodes
• Query-Driven Design: Each table is designed for a specific query pattern
• Fast Reads: All data needed for a query is in one partition - single read operation
• Horizontal Scalability: Each query hits one partition on one node - scales linearly
• Distributed Architecture: JOINs across distributed nodes would kill performance

Tradeoff: Accept write complexity and data duplication to achieve blazing-fast reads at web scale.

Answer:

Key Insight: SQL trades read complexity for write simplicity. Cassandra trades write complexity for read simplicity.

Answer:

Query-driven data modeling means designing your database schema based on how you plan to query the data, not on the structure of the entities themselves.

The Process:

• Step 1: List all queries your application needs to perform
• Step 2: For each query, design a table optimized for that specific access pattern
• Step 3: Structure the primary key to enable efficient lookup
• Step 4: Include all necessary data in each row (denormalize)

Example:

• Query: "Get user's orders" → orders_by_user table with user_id as partition key
• Query: "Get order by ID" → orders_by_id table with order_id as partition key
• Query: "Get pending orders" → orders_by_status table with status as partition key

Rule: If you can't answer "What queries will use this table?" you're doing it wrong!

Answer:

Use BATCH statements to write to multiple tables atomically:

BEGIN BATCH
  INSERT INTO orders_by_user (user_id, order_id, ...) VALUES (...);
  INSERT INTO orders_by_id (order_id, user_id, ...) VALUES (...);
  INSERT INTO orders_by_status (status, order_id, ...) VALUES (...);
APPLY BATCH;

How Batches Help:

• Atomicity: All writes succeed or all fail (within same partition)
• Isolation: Batch appears as single operation
• Timestamp Consistency: All writes get same timestamp

Important Caveats:

• Batches are NOT transactions (no rollback)
• Only use for writes to same partition or denormalized data
• Eventual consistency still applies across replicas
• Application must handle batch failures and retries

Alternative: Use application logic with retry mechanisms to ensure all tables updated.

Answer:

Main Downsides:

• Increased Storage: Same data stored multiple times - requires more disk space
• Write Complexity: Single logical change requires updating multiple tables
• Write Amplification: One user action = multiple database writes (performance cost)
• Consistency Challenges: Keeping duplicate data in sync is application's responsibility
• Eventual Consistency: Different tables may temporarily show different values
• No Referential Integrity: Database doesn't enforce consistency - application must
• Schema Changes: Updating structure requires changing multiple tables
• Data Anomalies: Risk of insert/update/delete anomalies if not handled carefully

When It's Worth It:

• Read-heavy workloads (90%+ reads)
• Need for consistent low latency at massive scale
• Known query patterns (not ad-hoc)
• Storage cost acceptable tradeoff for performance

Answer:

Decision Framework:

• Rule 1: Denormalize data needed together
  • If query needs user name + order details, store both in orders table
  • Avoid requiring secondary lookups
• Rule 2: Consider query frequency
  • Frequently accessed data → definitely denormalize
  • Rarely accessed → may not be worth duplication
• Rule 3: Analyze update frequency
  • Rarely changing data (product categories) → safe to denormalize
  • Frequently changing data (real-time prices) → be cautious
• Rule 4: Consider data size
  • Small data (names, IDs) → duplicate freely
  • Large data (images, files) → store references only

Example Decision:

Order table includes user_name (small, rarely changes, needed for display) but NOT user_profile_picture (large, store URL reference instead).

Answer:

"One table per query" means creating a separate table optimized for each distinct access pattern in your application.

The Principle:

• Each query pattern gets its own table
• Table's primary key matches the query's WHERE clause
• Table contains all data needed by that query
• Query can be satisfied with single partition read

Example - User Activity System:

• Query 1: "Get activities by user" → activities_by_user (PK: user_id)
• Query 2: "Get activities by type" → activities_by_type (PK: activity_type)
• Query 3: "Get today's activities" → activities_by_date (PK: date)

Why It Works:

• Each table optimized for its specific use case
• No need to compromise primary key for multiple access patterns
• Guaranteed fast reads for all queries
• Clear mapping: query → table

Cost: More tables, more writes, more storage. But reads are always fast!

Answer:

Scenario: Real-time stock trading platform

Problem:

• Stock prices change multiple times per second
• Price stored in: user_portfolios, order_history, price_alerts, analytics_table
• One price update → must update 4+ tables immediately
• Write amplification = 4x or more
• Risk of inconsistent prices across tables

Better Approach:

• Don't denormalize: Store only stock_id in other tables
• Separate price table: current_stock_prices with real-time updates
• Application joins: Fetch price separately when displaying
• Cache: Use Redis/Memcached for frequently accessed prices

Other "Don't Denormalize" Cases:

• Real-time sensor data (temperature, location)
• Live sports scores
• Inventory counts with high turnover
• Currency exchange rates

Rule: If data changes more frequently than it's read, reconsider denormalization!

Answer:

Write amplification occurs when a single logical update requires multiple physical writes to the database.

How Denormalization Causes It:

• Same data duplicated across multiple tables
• Changing one piece of data requires updating all copies
• 1 logical write → N physical writes (N = number of tables)

Example:

User changes their name:
1 logical update → must write to:
  - users_by_id
  - users_by_email  
  - orders_by_user (all user's orders)
  - comments_by_user (all user's comments)
  - reviews_by_user (all user's reviews)
  
Result: 1 name change = 100+ physical writes!

Impact:

• More I/O: Higher disk write load
• Higher Latency: Writes take longer
• Network Traffic: More data transferred to replicas
• Cost: More cloud storage I/O charges

Mitigation Strategies:

• Only denormalize rarely-changing data
• Use batches to write to multiple tables atomically
• Consider eventual consistency for non-critical updates
• Accept the tradeoff for read-heavy workloads

Answer:

Challenge: When data is duplicated, deletions must be propagated to all copies.

Strategies:

• Option 1: Batch Deletes

  BEGIN BATCH
    DELETE FROM orders_by_user WHERE user_id = X AND order_id = Y;
    DELETE FROM orders_by_id WHERE order_id = Y;
    DELETE FROM orders_by_status WHERE status = 'pending' AND order_id = Y;
  APPLY BATCH;

• Option 2: Soft Deletes (Recommended)
  • Add `deleted BOOLEAN` column to all tables
  • SET deleted = true instead of DELETE
  • Filter deleted rows in application
  • Periodically clean up with TTL or batch job
• Option 3: TTL-Based Cleanup
  • Set TTL when inserting data
  • Data auto-expires after time period
  • Good for time-series or temporary data

Best Practice: Use soft deletes for most cases - easier to handle, recoverable, and avoids deletion anomalies.

Answer:

Already covered in Question 7 - see above for complete answer about "One Table Per Query" principle.

Answer:

Increased Storage Requirements:

• Data Duplication: Same data stored N times (N = number of tables)
• Multiplication Factor: 3 tables with duplicated data = 3x storage
• Replication Factor: With RF=3, actual storage = 3 * N * data_size

Example Calculation:

Normalized SQL: 100GB data * RF=3 = 300GB total storage
Denormalized (3 tables): 100GB * 3 tables * RF=3 = 900GB total storage

Result: 3x more storage required!

Cost-Benefit Analysis:

• Storage Cost: ~$0.02-0.10/GB/month (cheap!)
• Compute Cost: Servers to handle slow queries (expensive!)
• Latency Cost: User drop-off from slow loads (very expensive!)

Reality Check:

• 900GB cloud storage = $9-90/month
• Additional servers for slow queries = $500-5000/month
• Lost revenue from poor performance = $$$$

Conclusion: Storage cost increase is trivial compared to performance gains. Modern storage is cheap!

Answer:

When to Use Each:

• Manual Denormalization: Complex business logic, need full control, different data in each table
• Materialized Views: Simple alternate primary key on same data, reduce boilerplate code

Best Practice: Start with materialized views for simplicity, switch to manual if you need more control.

Answer:

Challenge: In SQL, many-to-many requires junction table. In Cassandra, we denormalize based on query patterns.

Example: Students ↔ Courses (many students take many courses)

SQL Approach (3 tables):

students: student_id, name
courses: course_id, title
enrollments: student_id, course_id  -- junction table

Cassandra Approach (2+ tables based on queries):

• Query 1: "Get courses for student X"

CREATE TABLE courses_by_student (
    student_id UUID,
    course_id UUID,
    -- Denormalized course data
    course_title TEXT,
    course_instructor TEXT,
    enrollment_date TIMESTAMP,
    PRIMARY KEY (student_id, course_id)
);

• Query 2: "Get students in course Y"

CREATE TABLE students_by_course (
    course_id UUID,
    student_id UUID,
    -- Denormalized student data
    student_name TEXT,
    student_email TEXT,
    enrollment_date TIMESTAMP,
    PRIMARY KEY (course_id, student_id)
);

Writing:

BEGIN BATCH
  INSERT INTO courses_by_student (...) VALUES (...);
  INSERT INTO students_by_course (...) VALUES (...);
APPLY BATCH;

Key Point: Each direction of the relationship gets its own table!

Answer:

Strategies to Reduce Overhead:

• 1. Denormalize Selectively
  • Only duplicate data that's actually needed together
  • Don't denormalize "just in case"
  • Analyze which fields each query uses
• 2. Prefer Immutable Data
  • Denormalize data that rarely changes (categories, types)
  • Avoid denormalizing frequently updated data (prices, counts)
• 3. Use Batches Efficiently
  • Batch writes to multiple tables together
  • Reduces network roundtrips
  • Ensures atomic application of changes
• 4. Consider Materialized Views
  • Let Cassandra maintain simple denormalizations
  • Reduces application code complexity
  • Automatic consistency management
• 5. Asynchronous Updates
  • For non-critical data, update asynchronously
  • Use message queues for eventual consistency
  • Improves write latency
• 6. TTL-Based Cleanup
  • Set TTL on denormalized data that expires
  • Automatic deletion reduces maintenance
• 7. Limit Table Count
  • Don't create table for every possible query
  • Focus on high-frequency queries
  • Accept slower performance for rare queries

Golden Rule: Denormalization is a tradeoff. Optimize for your specific use case, not theoretical perfection!