Cassandra Materialized Views

A materialized view is an automatically maintained copy of a base table, organized around a different primary key. When you write data to the base table, Cassandra automatically propagates the update to the materialized view. This lets you query the same data using a different partition key without duplicating your application write logic.

The Photo Album Analogy

Imagine you organize your photo library in two ways simultaneously: one album sorted by date taken, and another sorted by location. Every time you add a photo, both albums update automatically. A Cassandra materialized view works the same way — you write once to the base table, and Cassandra maintains one or more alternative arrangements of that data for you.

Why Materialized Views Exist

Cassandra recommends designing one table per query pattern. Often you need to query the same data in different ways. Without materialized views, you would manually write to multiple tables from your application code. Materialized views automate that duplication inside Cassandra.

Base table: orders_by_customer
  PRIMARY KEY (customer_id, order_date)
  → Fast query: "all orders for customer X"

Materialized view: orders_by_status
  PRIMARY KEY (status, order_date, customer_id)
  → Fast query: "all pending orders this week"

You write only to orders_by_customer.
Cassandra updates orders_by_status automatically.

Creating a Materialized View

-- Base table:
CREATE TABLE users (
  user_id   UUID PRIMARY KEY,
  username  TEXT,
  email     TEXT,
  country   TEXT,
  joined    TIMESTAMP
);

-- Materialized view: query users by country
CREATE MATERIALIZED VIEW users_by_country AS
  SELECT user_id, username, email, country, joined
  FROM users
  WHERE country IS NOT NULL
    AND user_id IS NOT NULL
  PRIMARY KEY (country, user_id);

Rules for the WHERE Clause in a Materialized View

Every column in the materialized view's primary key must appear in the WHERE clause with an IS NOT NULL condition. This ensures Cassandra can always determine which partition of the view to update when a base table row changes.

Querying a Materialized View

-- Query the base table (by user_id):
SELECT * FROM users WHERE user_id = [uuid];

-- Query the materialized view (by country):
SELECT * FROM users_by_country WHERE country = 'Germany';

What Cassandra Does on a Base Table Write

INSERT INTO users (user_id, username, email, country)
VALUES (uuid(), 'alice', 'alice@example.com', 'Germany');

Cassandra internally:
  Step 1: Write row to base table users
  Step 2: Compute materialized view partition key (country='Germany')
  Step 3: Write row to users_by_country on the node owning
          partition 'Germany'
  Step 4: Acknowledge write to client

Materialized View Restrictions

Restriction                            Reason
──────────────────────────────────────────────────────────────────
View PK must include base table PK     Cassandra needs to locate the
  columns                              view row when base row changes
WHERE clause must cover view PK        Prevents null partition keys
  columns with IS NOT NULL             in the view
Only one new column can be added       View PK = (new col) + base PK
  to the view's partition key          columns; adding more breaks this
Cannot UPDATE view directly            Views are read-only; write to
                                       base table only
Cannot use collection columns          LIST, SET, MAP cannot be in
  in view PRIMARY KEY                  view partition or clustering key

Multiple Materialized Views on One Table

-- Base table: products
CREATE TABLE products (
  product_id UUID PRIMARY KEY,
  name       TEXT,
  category   TEXT,
  price      DECIMAL,
  in_stock   BOOLEAN
);

-- View 1: products by category
CREATE MATERIALIZED VIEW products_by_category AS
  SELECT * FROM products
  WHERE category IS NOT NULL AND product_id IS NOT NULL
  PRIMARY KEY (category, product_id);

-- View 2: products by stock status
CREATE MATERIALIZED VIEW products_by_stock AS
  SELECT * FROM products
  WHERE in_stock IS NOT NULL AND product_id IS NOT NULL
  PRIMARY KEY (in_stock, product_id);

Write Overhead of Materialized Views

Each materialized view adds a write to every INSERT and UPDATE on the base table. With three views, one base table write becomes four writes (one base + three views). This increases write latency and cluster load. Keep the number of views per table small and monitor write latency after adding views.

Write amplification:
  0 views → 1 write (base table only)
  1 view  → 2 writes
  2 views → 3 writes
  3 views → 4 writes

Dropping a Materialized View

DROP MATERIALIZED VIEW IF EXISTS users_by_country;

Dropping a view does not affect the base table or its data.

Describing a Materialized View

DESCRIBE MATERIALIZED VIEW users_by_country;

Materialized Views vs Lookup Tables

Approach             Pros                          Cons
──────────────────────────────────────────────────────────────────
Materialized View    Automatic sync;               Write amplification;
                     less application code         experimental in
                                                   some versions
Lookup Table         Full control; faster reads;   Application must write
(manual duplication) no experimental risk          to both tables

For mission-critical production workloads, many teams prefer manual duplication (application-level dual writes) because it is more transparent and easier to debug. Materialized views are a convenient shortcut when the write overhead is acceptable.

Summary

Materialized views automatically maintain an alternative representation of a base table organized around a different primary key. You write once to the base table and query multiple views for different access patterns. Every column in the view's primary key must appear in the WHERE clause as IS NOT NULL. Materialized views add write amplification proportional to the number of views — keep the count small and monitor write latency in production.

Previous lesson

Back to course

Next lesson