Range query with mutable column - SASI index usage and limitations?

Question

I have an issue with a use case implying a column holding an expiration date that has to be updated on a regular basis and filtered using a range/slice query.

Description

The table is used to maintain a list of elements to process. First, a list of candidate is retrieved (with an expiration date in the past) and then each element is updated with a new expiration date corresponding to the maximum processing time of the element. When the element processing is finished the expiration is updated to the maximum long value, i.e. it never expires. I know it looks like a queue and it is an anti-pattern in Cassandra. The full model is more complex but I have simplified to the range/slice query problem.

The table definition is below.

CREATE TABLE IF NOT EXISTS element_status (
partitionkey text,
elementid text,
lockexpirationinmillissinceepoch bigint,
PRIMARY KEY((partitionkey), requestid)) with default_time_to_live = 604800;

The initial insert.

INSERT INTO element_status (partitionkey, elementid,clientCallUuid, lockexpirationinmillissinceepoch) VALUES (‘mypartition’, ‘06e6668c-ebad-4e16-9329-a8854ebf1c32’, 123455);

The query to retrieve the candidates

SELECT * FROM element_status WHERE
    partitionKey='partitionKey' AND
    lockExpirationInMillisSinceEpoch < 123456
    LIMIT 123 ALLOW FILTERING;

The query to “lock” an element, i.e. update the expiration date

UPDATE element_status SET
    lockExpirationInMillisSinceEpoch=135456
  WHERE partitionKey='partitionKey' AND
    requestId='requestId';

The query to “finish” the element.

UPDATE keyspaceName.async_message_status SET
    lockExpirationInMillisSinceEpoch=9223372036854775807
  WHERE partitionKey='partitionKey' AND
    requestId='requestId';

For the range/slice query, a SASI index has been created with the following definition.

CREATE CUSTOM INDEX IF NOT EXISTS element_status_lock_expiration_in_millis_since_epoch_index ON element_status (lockExpirationInMillisSinceEpoch) USING 'org.apache.cassandra.index.sasi.SASIIndex';

The expected volume by partition is around 200k records by day and TTL is one week.

I use latest Cassandra version (3.11.1).

Questions

However, the usage of SASI index is not recommended for production. I would like to evaluate what are the problems that can occur with this SASI index.

• Can it occur that an element is never retrieved even if the initial insertion date is in the past?
• Same question after one or several updates and an expiration date in the past?
• Does any performance issue can occur?

The question was also asked on the Cassandra user list.