Database / Apache Cassandra Intermediate and Advanced interview questions
What is the difference between a secondary index and a materialized view?
Both let you query on something other than the base table's partition key, but they solve the problem in very different ways.
| Secondary Index | Materialized View |
| Indexes an existing column in place on the base table. | Creates a separate physical table with its own partition key. |
| Query still fans out across nodes at read time. | Query hits a single partition directly, like any normal table. |
| Cheap to create, no extra storage for a new table. | Uses extra storage and write bandwidth to maintain the copy. |
| Best for occasional, low-traffic lookups. | Best for well-defined, frequent query patterns. |
In practice, a secondary index is a quick, low-commitment way to support an ad-hoc query, while a materialized view (or its hand-rolled equivalent, a manually maintained query table) is the right tool when a query pattern is a core, high-traffic part of the application and needs single-partition read performance.
Which approach gives single-partition read performance for an alternate query pattern?
Which approach is generally more appropriate for an occasional, low-traffic lookup?
More Related questions...
