Create a Global Secondary Index which include non-key attributes

Question

I am trying to create a GSI on a table Scylla cluster. According to documentation I can create CREATE INDEX ON ks.users (email); which will include this field and the primary key in the Materialized view it creates. Is there a way I can project other non-key attribute to the MV while creating this GSI?

Thanks for the help.

Answer 1

This kind of projection is not possible due to the constraints that materialized views have - only up to one non-key column can be incorporated into the materialized view's key. Scylla's GSI creates an underlying materialized view and puts the indexed column in the key, so only one non-key column can be used.

The only workarounds I can come up with are:

• changing the base table schema by creating a single column that stores the combined values of both attributes as a blob - then the index can be created over this blob;
• changing the base table schema so that one of the columns is part of the primary key.

Also, currently the only option to customize projections in Scylla's indexing are local indexes, where one can ensure that the base partition key is also used as a partition key in the underlying view: CREATE INDEX ON ks.users ((userid), email). One day it would be nice to implement generic indexing, which would allow customizing the underlying materialized view in a more flexible way - contributions are always welcome :) Still, the constraints of materialized views will apply, so projecting more non-key columns for indexing could only be allowed once the same is allowed for materialized views.