Cassandra Materialized Views: Quick Reads, Hidden Costs, and When to Use Them

Why You Might Need a Different Primary Key

In Cassandra a query must match the table’s primary key exactly. If you need to look up data by a column that isn’t part of the key you either have to scan the entire table or maintain a second table that uses the desired column as the key. This is a common pain point when the read workload is heavy but writes are relatively light.

Materialized Views: Simple, but with Caveats

A materialized view (MV) is a special type of table that Cassandra automatically keeps in sync with a base table. When you insert, update, or delete a row in the base table, Cassandra propagates the change to the MV asynchronously. The MV has its own primary key layout, so you can query it directly for the read pattern you need.

Because the MV is stored as a separate table, writes incur extra I/O and storage overhead. Cassandra guarantees eventual consistency for the MV but does not perform read repair or anti‑entropy on it, so a client may read stale data until the view catches up.

How to Create and Use a Materialized View

1. Define the base table. The view inherits all columns from the base table, so you only need to specify the new primary key.
2. Create the view. Use the CREATE MATERIALIZED VIEW statement. The view’s primary key must be a subset of the base table’s primary key plus any columns you want to filter on.
3. Write to the base table. All inserts, updates, and deletes go only to the base table. Cassandra takes care of updating the view.
4. Read from the view. Query the view as you would any table, but use the view’s primary key in the WHERE clause.

Concrete Example

Assume you have a keyspace ecommerce and you want to look up orders by customer_id instead of by the default order_id primary key.

-- 1. Create base table
CREATE TABLE ecommerce.orders (
    order_id UUID PRIMARY KEY,
    customer_id UUID,
    order_date timestamp,
    total decimal
);

-- 2. Create materialized view for customer lookup
CREATE MATERIALIZED VIEW ecommerce.orders_by_customer AS
    SELECT * FROM ecommerce.orders
    WHERE customer_id IS NOT NULL
    PRIMARY KEY (customer_id, order_id);

Now you can read all orders for a customer with:

SELECT * FROM ecommerce.orders_by_customer
WHERE customer_id = 0x1234abcd;

Notice that the MV uses customer_id as the partition key and order_id as the clustering column. This gives you efficient range queries per customer.

Trade‑offs & Limitations

• Write Amplification: Each write to the base table triggers an additional write to the MV. For high‑write workloads this can double disk I/O and increase latency.
• Eventual Consistency: The MV may lag behind the base table by a few milliseconds, especially under heavy load or network partitions. If your application requires strict consistency, a MV may not be suitable.
• No Read Repair: Cassandra does not perform read repair on MVs. A node that falls behind can serve stale data until it catches up.
• Limited Schema Flexibility: The MV cannot add new columns that aren’t in the base table, and altering the base table’s primary key can break the view.
• Replica Factor & Consistency: The MV inherits the replica factor of the keyspace but can be queried at a different consistency level. Mixing CLs can lead to confusing read results.

Practical Checklist for Adoption

1. Is the read pattern read‑heavy and write‑light? If yes, a MV can reduce client‑side filtering.
2. Do you tolerate a few milliseconds of eventual consistency? If your business logic requires strict atomicity, consider a different approach.
3. Can your cluster handle the extra write I/O? Monitor disk usage and write latency after enabling the MV.
4. Will you need to change the base table’s schema in the future? Remember that MV changes are automatic but can impact performance.
5. Do you have a backup strategy that covers both the base table and the MV? MVs are not backed up automatically with snapshot tools.

Actionable Takeaway

Materialized views are a quick way to add a new query pattern without duplicating application logic. Use them for low‑write, read‑heavy workloads where eventual consistency is acceptable. For high‑write or consistency‑critical scenarios, consider maintaining a separate table manually or redesigning your data model.

Before deploying to production, perform a small‑scale test: insert a few hundred rows, measure write latency and disk usage, and confirm that the MV eventually reflects all changes. Keep an eye on system_views.write_latency and system_views.disk_space_used to spot write amplification early.