Known limitations of materialized views

As of writing, the following limitations are known for materialized views.

As of writing, the following limitations are known for materialized views:

  • There is not way to automatically detect and fix permanent inconsistency between the base and the view (CASSANDRA-10346)
  • Incremental repair is not supported on base tables with materialized views (CASSANDRA-12888)
  • Cannot filter materialized views by non-primary key columns (CASSANDRA-13798)
  • Deleting individual columns from a base table not selected in a materialized view can potentially prevent updates with lower timestamps (from repair or hints) from being applied (CASSANDRA-13826).

    To illustrate this limitation, consider the following statement, which creates a base table with primary key columns base_pk1 and base_pk2.
    CREATE TABLE base_table (
    base_pk1 int,
    base_pk2 int,
    view_pk int,
    unselected int)
    PRIMARY KEY (base_pk1, base_pk2);
    A materialized view is created, including the base table primary keys base_pk1 and base_pk2, plus an additional column view_pk on its primary key. The unselected column from the base table is not included in the view definition.
    CREATE MATERIALIZED VIEW mv AS SELECT
    base_pk1,
    base_pk2,
    view_pk FROM base_table
    PRIMARY KEY (base_pk2, base_pk1, view_pk);
    A DELETE statement is issued to the unselected column of the base table.
    DELETE unselected from base_table 
    WHERE base_pk1=1 AND base_pk2=1 
    USING TIMESTAMP 500;
    Later, an update with a lower timestamp arrives from a repair or hint.
    INSERT INTO base_table (
    base_pk1,
    base_pk2,
    view_pk)
    VALUES (1, 1, 1) USING TIMESTAMP 100;
    After this update, the following SELECT statement will not return the row previously inserted.
    SELECT * FROM mv WHERE base_pk1 = 1 AND base_pk2 = 1 and view_pk =1;

    To overcome this limitation, always include columns that might be individually deleted in the materialized view definition when the primary key contains a column not present in the primary key of the base table. Alternatively, avoid performing individual column deletions on materialized views with these attributes.

  • Full repairs on base tables must go through the write path to generate view updates, which can cause higher load and increased repair time (compared to a table without materialized views) when many changes are present.