Should we reconsider using materialized views in Cassandra?

Question

There is a bit of talk lately around the use of materialized views (MV) in Cassandra. As an ordinary user, it is difficult to know whether it is or it isn't a good idea to use MVs.

What is the recommendation for MVs and is there guidance available on the subject?

Answer 1

Background

Central to data modelling in Cassandra is denormalizing data into separate tables so that each application query maps to a table for optimized reads. Back in 2015, Cassandra 3.0 introduced materialized views (CASSANDRA-6477) as an automated way of denormalization so you didn’t have to design and maintain tables manually.

Example

Let’s look at a table comments_by_video that stores all the comments posted by users for each video. The table looks like this:

CREATE TABLE comments_by_video (
    videoid uuid,
    commentid timeuuid,
    userid uuid,
    comment text,
    PRIMARY KEY (videoid, commentid)
) WITH CLUSTERING ORDER BY (commentid DESC);

On the site, users can also list all the comments they have posted on various videos. The usual way of modelling for this query is to create a new table called comments_by_user:

CREATE TABLE comments_by_user (
    userid uuid,
    commentid timeuuid,
    videoid uuid,
    comment text,
    PRIMARY KEY (userid, commentid)
) WITH CLUSTERING ORDER BY (commentid DESC);

We would batch the updates to both tables to keep them synchronized.

Alternatively, we could create a materialized view based on the table comments_by_video so that new comments also get posted in the comments_by_user table automatically. Here is how we do that in CQL:

CREATE MATERIALIZED VIEW comments_by_user AS
    SELECT videoid, commentid, comment, userid
    FROM killrvideo.comments_by_video
    WHERE videoid IS NOT NULL AND commentid IS NOT NULL
    PRIMARY KEY (videoid, commentid)
    WITH CLUSTERING ORDER BY (commentid DESC);

Easy, right? Not quite so read on.

Caveat

There is a catch -- the updates take place asynchronously. In some instances, the view misses the updates and becomes out-of-sync with the base table.

In the last few years, we have come to understand that either (a) the design of materialized views, (b) the implementation, or (c) both may be flawed. There are conflicting views on the subject but one thing is certain: there is no quick fix if you run into this issue. The only workaround is to drop the view which means an outage to parts of your application until you recreate it.

For this reason, the Apache Cassandra project reinforced the "experimental" classification of the materialized view feature in Cassandra 4.0 (CASSANDRA-13959) where it is now disabled by default and an operator will need to explicitly set a flag to enable it.

Recommendation

If you are already using materialized views successfully then feel free to continue using it.

But if you’re just about to jump in, don’t. Stick with designing separate tables.

References

• Docs - Using materialized views
• Tutorial - Practical Application Data Modeling
• Blog - New in Cassandra 3.0: Materialized Views