Solving Read Latency in Cassandra with Materialized Views

The Problem: The 'Wrong' Primary Key

In Apache Cassandra, you design your schema around your queries. If you have a table of orders partitioned by order_id, you can retrieve a specific order in milliseconds. However, if your business suddenly needs to query orders by customer_id or order_date, you face a dilemma: perform a full cluster scan (which is computationally expensive and often times out) or maintain a second table manually in your application code.

Managing manual secondary tables is error-prone. If your application crashes between writing to the base table and the secondary table, your data becomes inconsistent. Materialized Views (MVs) solve this by shifting the burden of data duplication from the application to the database engine.

How Materialized Views Work

A Materialized View is essentially a system-managed table that automatically synchronizes with a base table. When you write a row to the base table, Cassandra asynchronously propagates that change to the view. This allows you to effectively change the primary key of your data for specific query patterns without writing custom synchronization logic.

Unlike a standard index, which may require hitting multiple nodes to resolve a query, a Materialized View stores the data in a new partition layout. This ensures that reads remain local and fast, as the view is physically structured to support the new query pattern.

Worked Example: Querying Orders by Date

Assume you are using Cassandra 3.0 or later. You have a base table optimized for order lookups, but you need a way to fetch all orders for a specific day without scanning the entire cluster.

1. The Base Table

CREATE TABLE store.orders (
    order_id uuid PRIMARY KEY,
    customer_id uuid,
    order_date date,
    total_amount decimal
);

2. Creating the Materialized View

To query by date, create a view that uses order_date as the partition key. Run this command via cqlsh with keyspace-level permissions:

CREATE MATERIALIZED VIEW store.orders_by_date AS
    SELECT order_date, order_id, customer_id, total_amount
    FROM store.orders
    WHERE order_date IS NOT NULL AND order_id IS NOT NULL
    PRIMARY KEY (order_date, order_id);

Note: All columns in the view's primary key must be filtered for IS NOT NULL because Cassandra cannot partition null values.

3. Verifying the View

To confirm the view was created successfully, query the system schema:

SELECT * FROM system_schema.views WHERE keyspace_name='store';

Now, a query that would have previously required ALLOW FILTERING (and caused a performance hit) becomes a direct primary key lookup:

SELECT * FROM store.orders_by_date WHERE order_date = '2026-10-18';

The Trade-off: Write Amplification

Materialized Views are not a "free" performance boost. They introduce write amplification. Every single insert, update, or delete on the orders table triggers a corresponding write to the orders_by_date view.

• Latency: Write latency increases because the coordinator must manage the update propagation.
• Throughput: Under heavy write loads, this can lead to "update storms," where CPU usage spikes and compaction lags behind.
• Storage: You are effectively doubling the storage requirement for the columns included in the view.

Operational Guardrails

To avoid destabilizing your cluster, follow these practical constraints:

• Limit View Count: Avoid creating more than 2-3 views per base table. Each additional view multiplies the write load on your nodes.
• Selective Columns: Only include columns in the SELECT statement of the view that are absolutely necessary for the query.
• Version Check: Ensure your cluster is version 3.0+ by running nodetool status. Materialized Views are not supported in legacy versions.

How to verify the result

To measure the actual impact, use a tool like cassandra-stress or simple timing logs. Compare the latency of a filtered query on the base table versus a primary key query on the view. If the view query is significantly faster but your nodetool tpstats shows a buildup of pending mutations, you may need to reduce the number of views or scale your write capacity.