Advanced CQL Features

Materialized Views

Automate denormalization and enable flexible querying without manual table duplication!

📖 The Story: Emma's Denormalization Nightmare

Emma built a movie review platform with millions of reviews. Users want to view reviews by movie AND by user. She tried three approaches to handle this dual-access pattern...

❌ Attempt 1: Secondary Index (Too Slow!)

CREATE TABLE reviews (
    review_id UUID PRIMARY KEY,
    movie_id UUID,
    user_id UUID,
    rating INT,
    content TEXT
);

CREATE INDEX idx_movie ON reviews(movie_id);

SELECT * FROM reviews 
WHERE movie_id = 123;  -- Scatter-gather query!
                

Problems:

🔥 Coordinator contacts ALL 50 nodes
⏱️ Query takes 2-3 seconds under load
💥 Timeout errors during peak traffic
😡 Users complain about slow page loads

⚠️ Attempt 2: Manual Denormalization (Maintenance Hell!)

CREATE TABLE reviews_by_movie (
    movie_id UUID,
    review_id UUID,
    user_id UUID,
    rating INT,
    content TEXT,
    PRIMARY KEY (movie_id, review_id)
);

CREATE TABLE reviews_by_user (
    user_id UUID,
    review_id UUID,
    movie_id UUID,
    rating INT,
    content TEXT,
    PRIMARY KEY (user_id, review_id)
);

-- Every INSERT/UPDATE/DELETE needs to update BOTH tables!
BEGIN BATCH
  UPDATE reviews_by_movie SET rating = 5 WHERE ...;
  UPDATE reviews_by_user SET rating = 5 WHERE ...;
APPLY BATCH;
                

Problems:

🐛 Application code becomes complex (BATCH everywhere)
💥 Bugs! Forgot to update one table → data inconsistency!
📝 Every developer must remember to update both tables
⏱️ Code reviews become nightmares
🔥 Production incident: Data out of sync!

✅ The PERFECT Solution: Materialized Views!

-- Step 1: Create base table
CREATE TABLE reviews (
    review_id UUID,
    movie_id UUID,
    user_id UUID,
    rating INT,
    content TEXT,
    created_at TIMESTAMP,
    PRIMARY KEY (review_id)
);

-- Step 2: Create materialized view for movie queries
CREATE MATERIALIZED VIEW reviews_by_movie AS
    SELECT * FROM reviews
    WHERE movie_id IS NOT NULL 
      AND review_id IS NOT NULL
    PRIMARY KEY (movie_id, review_id);

-- Step 3: Create materialized view for user queries
CREATE MATERIALIZED VIEW reviews_by_user AS
    SELECT * FROM reviews
    WHERE user_id IS NOT NULL 
      AND review_id IS NOT NULL
    PRIMARY KEY (user_id, review_id);

-- That's it! Cassandra maintains views automatically!

-- Insert into base table only:
INSERT INTO reviews (review_id, movie_id, user_id, rating, content)
VALUES (uuid(), 123, 456, 5, 'Great movie!');

-- Views update automatically! No BATCH needed! 🎉

-- Query by movie (FAST! ⚡)
SELECT * FROM reviews_by_movie 
WHERE movie_id = 123;

-- Query by user (FAST! ⚡)
SELECT * FROM reviews_by_user 
WHERE user_id = 456;
                

Benefits:

✅ Automatic updates: Cassandra maintains views!
✅ Simple code: Write to base table only!
✅ Always consistent: No bugs from forgetting to update!
✅ Blazing fast: Both queries return in 5ms!
✅ No application complexity: Database handles it!

Emma's app is now fast, reliable, and easy to maintain! 🎉

🎬 What are Materialized Views?

Automatically maintained denormalized tables that stay in sync with the base table!

Simple Definition

Materialized View (MV): A read-only table that Cassandra automatically updates when the base table changes.

Think of Materialized Views as:

🪞 Magic Mirror: Automatically reflects changes from base table
🔄 Auto-Sync Tables: Cassandra handles all updates for you
📋 Smart Copy: Different primary key, always up-to-date
🎯 Query Shortcuts: Fast access patterns without code complexity

How Materialized Views Work

When to Use Materialized Views

✅

Perfect For

Multiple Access Patterns: Query same data by different keys
Read-Heavy Workloads: More reads than writes
Simple Denormalization: Just changing primary key
Automatic Consistency: Need guaranteed sync
Reduce Code Complexity: Avoid manual BATCH statements

❌

Avoid For

Write-Heavy Tables: High INSERT/UPDATE volume (slow!)
Many MVs on One Table: > 3 MVs = performance hit
Frequent Schema Changes: MVs harder to modify
Complex Transformations: Can't filter or aggregate
Large Base Tables: Initial MV build takes time

💡

Trade-offs

Write Performance: -20% to -40% on base table
Storage: Each MV doubles storage for that data
Consistency: Eventually consistent (slight delay)
Limitations: Can't filter rows, only reorganize
Build Time: Large tables take hours to populate

🔨 Creating Materialized Views

Master the syntax and rules for creating materialized views!

Basic Syntax

-- Basic MV syntax
CREATE MATERIALIZED VIEW view_name AS
    SELECT column1, column2, column3
    FROM base_table
    WHERE column1 IS NOT NULL 
      AND column2 IS NOT NULL
    PRIMARY KEY (column1, column2);

-- Real example: Query movies by genre
CREATE TABLE movies (
    movie_id UUID PRIMARY KEY,
    title TEXT,
    genre TEXT,
    release_year INT,
    rating DECIMAL
);

CREATE MATERIALIZED VIEW movies_by_genre AS
    SELECT *
    FROM movies
    WHERE genre IS NOT NULL 
      AND movie_id IS NOT NULL
    PRIMARY KEY (genre, movie_id);

-- Now you can query by genre!
SELECT * FROM movies_by_genre
WHERE genre = 'Sci-Fi';
    

Critical MV Rules

Include ALL Primary Key Columns: Base table PK must be in MV WHERE and PRIMARY KEY
IS NOT NULL Required: All PK columns must have IS NOT NULL in WHERE
Static Columns: Can only be included if partition key is the same
No Filtering: Can't use WHERE with conditions other than IS NOT NULL
No Aggregations: Can't use COUNT, SUM, AVG, etc.

Complete Example: E-Commerce Orders

-- Base Table: Orders by order_id
CREATE TABLE orders (
    order_id UUID,
    customer_id UUID,
    product_id UUID,
    order_date DATE,
    status TEXT,
    total DECIMAL,
    PRIMARY KEY (order_id)
);

-- MV 1: Query orders by customer
CREATE MATERIALIZED VIEW orders_by_customer AS
    SELECT *
    FROM orders
    WHERE customer_id IS NOT NULL 
      AND order_id IS NOT NULL
    PRIMARY KEY (customer_id, order_id);

-- MV 2: Query orders by product
CREATE MATERIALIZED VIEW orders_by_product AS
    SELECT *
    FROM orders
    WHERE product_id IS NOT NULL 
      AND order_id IS NOT NULL
    PRIMARY KEY (product_id, order_id);

-- MV 3: Query orders by status and date (compound partition key)
CREATE MATERIALIZED VIEW orders_by_status AS
    SELECT *
    FROM orders
    WHERE status IS NOT NULL 
      AND order_date IS NOT NULL
      AND order_id IS NOT NULL
    PRIMARY KEY ((status, order_date), order_id)
    WITH CLUSTERING ORDER BY (order_id DESC);

-----------------------------------
-- Usage Examples
-----------------------------------

-- Insert into base table ONLY
INSERT INTO orders (order_id, customer_id, product_id, order_date, status, total)
VALUES (uuid(), uuid(), uuid(), '2025-01-03', 'shipped', 99.99);

-- All MVs update automatically! ✨

-- Query by customer
SELECT * FROM orders_by_customer
WHERE customer_id = ?;

-- Query by product
SELECT * FROM orders_by_product
WHERE product_id = ?;

-- Query by status
SELECT * FROM orders_by_status
WHERE status = 'shipped' 
  AND order_date = '2025-01-03';
    

Selecting Specific Columns

-- You don't have to SELECT *
CREATE MATERIALIZED VIEW customer_order_summary AS
    SELECT customer_id, order_id, order_date, total
    FROM orders
    WHERE customer_id IS NOT NULL 
      AND order_id IS NOT NULL
    PRIMARY KEY (customer_id, order_date, order_id)
    WITH CLUSTERING ORDER BY (order_date DESC);

-- This MV only includes 4 columns (smaller, faster!)
    

Dropping Materialized Views

-- Drop a materialized view
DROP MATERIALIZED VIEW orders_by_customer;

-- Drop with IF EXISTS
DROP MATERIALIZED VIEW IF EXISTS orders_by_product;

-- Check existing MVs
DESCRIBE MATERIALIZED VIEWS;
    

🏗️ Real-World Materialized View Examples

Production-ready MV patterns from real applications!

Example 1: Social Media - Posts by User and Hashtag

-- Base Table: Posts by post_id
CREATE TABLE posts (
    post_id TIMEUUID,
    user_id UUID,
    content TEXT,
    hashtags SET<TEXT>,
    likes INT,
    created_at TIMESTAMP,
    PRIMARY KEY (post_id)
);

-- MV: Get user's timeline
CREATE MATERIALIZED VIEW posts_by_user AS
    SELECT *
    FROM posts
    WHERE user_id IS NOT NULL 
      AND post_id IS NOT NULL
    PRIMARY KEY (user_id, post_id)
    WITH CLUSTERING ORDER BY (post_id DESC);

-- Query: Get user's posts (newest first)
SELECT * FROM posts_by_user
WHERE user_id = ?
LIMIT 20;
                

Example 2: IoT Sensors - Data by Sensor and by Location

-- Base Table: Sensor readings by timestamp
CREATE TABLE sensor_data (
    sensor_id UUID,
    timestamp TIMESTAMP,
    location TEXT,
    temperature DECIMAL,
    humidity DECIMAL,
    battery INT,
    PRIMARY KEY ((sensor_id, DATE(timestamp)), timestamp)
) WITH CLUSTERING ORDER BY (timestamp DESC);

-- MV: Query by location
CREATE MATERIALIZED VIEW sensor_data_by_location AS
    SELECT *
    FROM sensor_data
    WHERE location IS NOT NULL 
      AND sensor_id IS NOT NULL
      AND timestamp IS NOT NULL
    PRIMARY KEY (location, timestamp, sensor_id)
    WITH CLUSTERING ORDER BY (timestamp DESC);

-- Query: All sensors in a building
SELECT * FROM sensor_data_by_location
WHERE location = 'Building-A'
  AND timestamp >= '2025-01-03';
                

Example 3: E-Learning - Courses by Instructor and Category

-- Base Table: Courses
CREATE TABLE courses (
    course_id UUID,
    instructor_id UUID,
    category TEXT,
    title TEXT,
    duration INT,
    price DECIMAL,
    rating DECIMAL,
    PRIMARY KEY (course_id)
);

-- MV: Courses by instructor
CREATE MATERIALIZED VIEW courses_by_instructor AS
    SELECT *
    FROM courses
    WHERE instructor_id IS NOT NULL 
      AND course_id IS NOT NULL
    PRIMARY KEY (instructor_id, rating, course_id)
    WITH CLUSTERING ORDER BY (rating DESC);

-- MV: Courses by category
CREATE MATERIALIZED VIEW courses_by_category AS
    SELECT *
    FROM courses
    WHERE category IS NOT NULL 
      AND course_id IS NOT NULL
    PRIMARY KEY (category, rating, course_id)
    WITH CLUSTERING ORDER BY (rating DESC);

-- Query: Instructor's top-rated courses
SELECT * FROM courses_by_instructor
WHERE instructor_id = ?
LIMIT 10;

-- Query: Top Python courses
SELECT * FROM courses_by_category
WHERE category = 'Python'
LIMIT 20;
                

Example 4: Event Tracking - Events by User and by Type

-- Base Table: Events
CREATE TABLE events (
    event_id TIMEUUID,
    user_id UUID,
    event_type TEXT,
    event_data TEXT,
    created_at TIMESTAMP,
    PRIMARY KEY (event_id)
);

-- MV: User activity timeline
CREATE MATERIALIZED VIEW events_by_user AS
    SELECT *
    FROM events
    WHERE user_id IS NOT NULL 
      AND event_id IS NOT NULL
    PRIMARY KEY (user_id, event_id)
    WITH CLUSTERING ORDER BY (event_id DESC);

-- MV: Events by type (for analytics)
CREATE MATERIALIZED VIEW events_by_type AS
    SELECT *
    FROM events
    WHERE event_type IS NOT NULL 
      AND event_id IS NOT NULL
    PRIMARY KEY (event_type, event_id)
    WITH CLUSTERING ORDER BY (event_id DESC);

-- Query: User's recent activity
SELECT * FROM events_by_user
WHERE user_id = ?
LIMIT 50;

-- Query: All login events
SELECT * FROM events_by_type
WHERE event_type = 'login';
                

⭐ Materialized View Best Practices & Limitations

Learn what works, what doesn't, and how to optimize MVs!

✅

DO's

Limit MVs per Table: Max 3-5 MVs (performance!)
Use for Read-Heavy: More reads than writes
Include Base PK: Always in WHERE and PRIMARY KEY
Monitor Build Progress: Large tables take time
Test Performance Impact: Measure write latency
Use Appropriate Replication: Same or less than base
Document Your MVs: Explain query patterns

❌

DON'Ts

Don't Create Too Many: > 5 MVs = slow writes
Don't Use for Writes: MVs are read-only!
Don't Filter Rows: Can't use WHERE col = value
Don't Aggregate: No COUNT, SUM, AVG
Don't Use on Write-Heavy: > 10K writes/sec
Don't Expect Instant: Eventually consistent
Don't Ignore Write Cost: -20% to -40% throughput

💡

Pro Tips

Build During Low Traffic: Initial population is heavy
Select Only Needed Columns: Smaller = faster
Use Compound Keys: (status, date) for bucketing
Monitor Repair: Ensure MVs stay in sync
Consider Manual Denorm: For write-heavy tables
Test on Production Load: Simulate real traffic
Plan for Rebuilds: DROP + CREATE if corrupt

Major Limitations

❌ Limitation #1: Cannot Filter Rows

You can only reorganize data, not filter it!

-- ❌ WRONG: Can't filter by value
CREATE MATERIALIZED VIEW active_users AS
    SELECT *
    FROM users
    WHERE status = 'active'  -- ERROR!
    PRIMARY KEY (user_id);

-- ✅ CORRECT: Only IS NOT NULL allowed
CREATE MATERIALIZED VIEW users_by_status AS
    SELECT *
    FROM users
    WHERE status IS NOT NULL
      AND user_id IS NOT NULL
    PRIMARY KEY (status, user_id);

-- Then filter in query:
SELECT * FROM users_by_status
WHERE status = 'active';
                

❌ Limitation #2: Cannot Aggregate Data

No COUNT, SUM, AVG, GROUP BY, or computed columns!

-- ❌ WRONG: Can't aggregate
CREATE MATERIALIZED VIEW order_totals AS
    SELECT customer_id, SUM(total) AS total_spent  -- ERROR!
    FROM orders
    GROUP BY customer_id;

-- ✅ WORKAROUND: Use counters in separate table
CREATE TABLE customer_totals (
    customer_id UUID PRIMARY KEY,
    total_spent COUNTER
);

-- Update in application when order created:
UPDATE customer_totals
SET total_spent = total_spent + ?
WHERE customer_id = ?;
                

❌ Limitation #3: Write Performance Impact

Each MV adds 20-40% overhead to write operations!

Performance Impact

1 MV: -20% write throughput
2 MVs: -35% write throughput
3 MVs: -50% write throughput
5+ MVs: -70%+ write throughput (not recommended!)

Solution: Limit to 3-5 MVs maximum per table.

❌ Limitation #4: Eventually Consistent

MVs update asynchronously - slight delay possible!

-- Write to base table
INSERT INTO orders (...) VALUES (...);

-- Immediately query MV
SELECT * FROM orders_by_customer WHERE customer_id = ?;
-- ⚠️ May not see the new order yet! (milliseconds delay)

-- In most cases, delay is < 10ms (acceptable!)
-- For critical consistency, query base table directly
                

❌ Limitation #5: Static Column Restrictions

Can only include static columns if partition key is the same!

CREATE TABLE user_posts (
    user_id UUID,
    post_id TIMEUUID,
    username TEXT STATIC,  -- Static column
    content TEXT,
    PRIMARY KEY (user_id, post_id)
);

-- ❌ WRONG: Can't include static if partition key changes
CREATE MATERIALIZED VIEW posts_by_id AS
    SELECT *
    FROM user_posts
    WHERE post_id IS NOT NULL 
      AND user_id IS NOT NULL
    PRIMARY KEY (post_id, user_id);  -- ERROR! Static column issue

-- ✅ CORRECT: Don't select static column
CREATE MATERIALIZED VIEW posts_by_id AS
    SELECT post_id, user_id, content  -- No username
    FROM user_posts
    WHERE post_id IS NOT NULL 
      AND user_id IS NOT NULL
    PRIMARY KEY (post_id, user_id);
                

⚡ Performance Considerations

Optimize MV performance for production workloads!

📊 Write Performance Impact

Every MV adds overhead because Cassandra must:

Parse the write: Determine which MVs are affected
Generate MV mutations: Create writes for each MV
Write to base table: Normal write operation
Write to MV tables: Additional writes (async but still overhead)
Maintain consistency: Track MV updates via batchlog

-- Example: Write with 3 MVs
INSERT INTO orders (...) VALUES (...);

-- Cassandra internally does:
-- 1. Write to orders (base table)
-- 2. Write to orders_by_customer (MV 1)
-- 3. Write to orders_by_product (MV 2)
-- 4. Write to orders_by_status (MV 3)
-- Total: 4 writes instead of 1!
        

Building MVs on Large Tables

MV Build Time Estimates

Table Size	Rows	Build Time
Small	< 1M rows	1-5 minutes
Medium	1M - 10M rows	10-60 minutes
Large	10M - 100M rows	1-6 hours
Huge	> 100M rows	6+ hours

During build:

High CPU usage across cluster
Increased disk I/O
MV is queryable but incomplete
Don't create during peak traffic!

Monitoring MV Health

-- Check MV build progress
nodetool viewbuildstatus keyspace_name view_name

-- Example output:
/*
orders_by_customer: SUCCESS (100%)
orders_by_product: BUILDING (45%)
*/

-- Check for MV repair issues
nodetool repair keyspace_name view_name

-- View MV statistics
SELECT * FROM system_views.views;

-- Check lag between base table and MV
-- Monitor write timestamps in both tables
    

Optimization Strategies

✅

Fast MVs

Limit to 3 MVs: Per table maximum
Select Only Needed: Fewer columns = faster
Appropriate CL: LOCAL_QUORUM for writes
Regular Repair: Keep MVs in sync
Monitor Lag: Alert if > 1 second behind

❌

Slow MVs

Too Many MVs: > 5 per table kills writes
SELECT *: Unnecessary data slows down
No Monitoring: Don't notice sync issues
Wrong CL: ALL consistency = very slow
No Repair: MVs drift out of sync

When NOT to Use MVs

Consider manual denormalization instead if:

Write throughput > 10,000 writes/second per table
Need more than 5 different access patterns
Write latency is critical (< 5ms required)
Table has frequent schema changes
Need to filter or aggregate data

🖥️ Interactive Materialized View Console

Practice creating materialized views in our simulator!

CQL Materialized View Playground

Enter CQL Command:

🚀 Materialized View Simulator Ready!
Try the examples or create your own MV...

Available Examples:
• Example 1: Basic MV creation
• Example 2: MV with clustering order
• Example 3: Multiple MVs on same table

💼 Interview Questions & Expert Answers

Master materialized views for your next Cassandra interview!

1 What is a materialized view and how is it different from a secondary index? ▼

Answer: Materialized views are automatically maintained denormalized tables, while secondary indexes are lookups that enable filtering on non-primary key columns.

Materialized View:

What: A complete table with different primary key
Data: Full copy of data, automatically synchronized
Queries: Fast, single-partition reads
Writes: Cassandra updates automatically
Performance: Read-optimized, write overhead 20-40%

Secondary Index:

What: Lookup structure (column_value → partition_key)
Data: No data duplication, just pointers
Queries: Slower, scatter-gather across all nodes
Writes: Automatic index updates
Performance: Write overhead ~10%, reads slower than MV

Feature	MV	Index
Read Speed	⚡ Fast (5ms)	🐌 Slower (50ms+)
Write Impact	-20% to -40%	-10%
Storage	2x (full copy)	+10-20%
Best For	Known query patterns	Ad-hoc queries

2 What are the main limitations of materialized views? ▼

Answer: MVs have five major limitations: no row filtering, no aggregations, write performance impact, eventual consistency, and static column restrictions.

1. Cannot Filter Rows

You can only use WHERE with IS NOT NULL, not actual filtering.

-- ❌ Can't do this:
WHERE status = 'active'

-- ✅ Only this:
WHERE status IS NOT NULL
                    

2. Cannot Aggregate Data

No COUNT, SUM, AVG, MAX, MIN, or GROUP BY allowed.

3. Write Performance Impact

Each MV adds 20-40% write overhead
3 MVs = 50% slower writes
Cassandra must update base table + all MVs

4. Eventually Consistent

MV updates are async (usually < 10ms delay)
Read immediately after write may miss data
Not suitable for strict consistency requirements

5. Static Column Restrictions

Can only include static columns if partition key remains the same in MV.

Workarounds:

For filtering: Reorganize then filter in query
For aggregations: Use counter tables or application-level
For consistency: Query base table directly
For heavy writes: Consider manual denormalization

3 How does Cassandra maintain materialized views internally? ▼

Answer: Cassandra uses a batchlog-based approach to ensure MVs are updated atomically with the base table.

The MV Update Process:

Write Received: Client writes to base table
MV Detection: Coordinator identifies which MVs are affected
Mutation Generation: Creates write mutations for each MV
Batchlog Entry: Writes to distributed batchlog for durability
Parallel Writes: Sends writes to base table + all MVs
Async Completion: MVs update asynchronously
Batchlog Cleanup: Removes batchlog entry when complete

-- Example: Write with 2 MVs
INSERT INTO reviews (review_id, movie_id, user_id, rating)
VALUES (uuid(), 101, 201, 5);

-- Internally Cassandra does:
-- 1. Write to batchlog: [reviews, reviews_by_movie, reviews_by_user]
-- 2. Write to reviews table (base)
-- 3. Write to reviews_by_movie (MV 1)
-- 4. Write to reviews_by_user (MV 2)
-- 5. Remove batchlog entry when all succeed
                    

Why Batchlog?

Crash Recovery: If node fails mid-write, batchlog ensures MV updates complete
Consistency: Prevents base table and MVs from getting out of sync
Replay: Other nodes replay batchlog if coordinator fails

Trade-off:

Batchlog adds overhead (extra write + disk space), which is why MVs impact write performance.

4 When should you use materialized views vs manual denormalization? ▼

Answer: Use MVs for read-heavy workloads with simple denormalization needs. Use manual denormalization for write-heavy workloads or when you need more control.

Use Materialized Views When:

✅ Read-Heavy: 80%+ reads, < 10K writes/sec
✅ Simple Reorganization: Just changing primary key structure
✅ Reduce Complexity: Don't want BATCH logic in application
✅ Few Access Patterns: 2-3 different query patterns max
✅ Automatic Sync: Need guaranteed consistency without code
✅ Development Speed: Faster to implement than manual

Use Manual Denormalization When:

✅ Write-Heavy: > 10K writes/sec
✅ Many Access Patterns: > 5 different ways to query
✅ Need Filtering: Want to filter rows (MVs can't)
✅ Need Aggregation: Want computed columns
✅ Custom Logic: Complex transformation rules
✅ Performance Critical: Can't afford 20-40% write overhead
✅ Batch Control: Want fine-grained control over when tables sync

-- Materialized View Approach (automatic)
CREATE MATERIALIZED VIEW reviews_by_movie AS
    SELECT * FROM reviews
    WHERE movie_id IS NOT NULL AND review_id IS NOT NULL
    PRIMARY KEY (movie_id, review_id);

INSERT INTO reviews (...) VALUES (...);  -- MV updates automatically!

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

-- Manual Denormalization (more control)
CREATE TABLE reviews_by_movie (...);

-- Application code handles sync:
BEGIN BATCH
  INSERT INTO reviews (...) VALUES (...);
  INSERT INTO reviews_by_movie (...) VALUES (...);
APPLY BATCH;
                    

Decision Matrix:

If (read_heavy AND simple_reorg AND < 5 patterns) → Use MV
Else → Use manual denormalization

5 How do you rebuild a materialized view that has become inconsistent? ▼

Answer: Drop and recreate the MV, or use nodetool repair for minor inconsistencies.

Option 1: Full Rebuild (Recommended for Major Issues)

-- Step 1: Drop the corrupted MV
DROP MATERIALIZED VIEW reviews_by_movie;

-- Step 2: Recreate with same definition
CREATE MATERIALIZED VIEW reviews_by_movie AS
    SELECT * FROM reviews
    WHERE movie_id IS NOT NULL 
      AND review_id IS NOT NULL
    PRIMARY KEY (movie_id, review_id);

-- Cassandra rebuilds from base table automatically
-- Monitor progress:
nodetool viewbuildstatus keyspace_name reviews_by_movie
                    

Option 2: Repair (For Minor Inconsistencies)

-- Run repair on the MV
nodetool repair keyspace_name reviews_by_movie

-- This syncs data across replicas
-- Use for: occasional missing rows, replica drift
                    

Common Causes of Inconsistency:

Node Failures: Node crashed during MV update
Network Partitions: Split brain scenarios
Disk Corruption: SSTable corruption on MV
Upgrade Issues: Bug in Cassandra version
No Regular Repair: Replicas drifted over time

Prevention Best Practices:

✅ Run nodetool repair weekly on MVs
✅ Monitor MV lag (base table vs MV timestamps)
✅ Use LOCAL_QUORUM consistency level
✅ Monitor batchlog size (shouldn't grow unbounded)
✅ Test MV queries after cluster maintenance

Important Notes

Rebuild Time: Large base tables take hours to rebuild MV
During Rebuild: MV is queryable but incomplete
Traffic Impact: Rebuild causes high CPU/disk I/O
Recommendation: Rebuild during low-traffic windows

🎓 Chapter Summary: Materialized View Mastery

Congratulations! You now understand Materialized Views at a production level!

Key Concepts Mastered:

Automatic Denormalization: Cassandra maintains MVs for you
Different Primary Key: Same data, organized differently
Eventually Consistent: Slight delay (usually < 10ms)
Write Overhead: 20-40% per MV
Read Performance: Fast as regular tables (5ms)

The Golden Rules:

Limit to 3-5 MVs: Per table maximum
Include Base PK: Always in WHERE and PRIMARY KEY
IS NOT NULL Only: Can't filter by value
Read-Heavy Workloads: Best use case
Monitor Write Impact: Track latency

When to Use What:

Scenario	Solution
Read-heavy, simple reorg	✅ Use Materialized View
Write-heavy (> 10K/sec)	❌ Manual Denormalization
Need to filter rows	❌ Manual Denormalization
Need aggregations	❌ Counter Tables
2-3 access patterns	✅ Use Materialized View
> 5 access patterns	❌ Manual Denormalization

Major Limitations to Remember:

❌ Cannot filter rows (only IS NOT NULL)
❌ Cannot aggregate data (no COUNT, SUM, AVG)
❌ Write overhead: 20-40% per MV
❌ Eventually consistent (slight delay)
❌ Static column restrictions
❌ Build time on large tables (hours)

Production Checklist:

✅ Base table PK included in MV WHERE and PRIMARY KEY
✅ All PK columns have IS NOT NULL
✅ Limited to 3-5 MVs per table
✅ Write performance tested (measured impact)
✅ MV build scheduled during low traffic
✅ Regular nodetool repair on MVs
✅ Monitoring for MV lag/inconsistencies
✅ Documentation of access patterns

Quick Command Reference:

-- Create MV
CREATE MATERIALIZED VIEW view_name AS
    SELECT * FROM base_table
    WHERE col1 IS NOT NULL AND col2 IS NOT NULL
    PRIMARY KEY (col1, col2);

-- Drop MV
DROP MATERIALIZED VIEW view_name;

-- Check build status
nodetool viewbuildstatus keyspace view_name

-- Repair MV
nodetool repair keyspace view_name
        

🚀 You're now ready to use Materialized Views in production!

Remember Emma's story: MVs = Automatic denormalization without code complexity! 🎉

Responsive Ad