SASI index usage and limitation

[Nicolas Henneaux <Ni...@arhs-developments.com>]

Hi,

For a project I am working on, I have a use case implying a column holding an expiration date that has to be updated on a regular basis and filtered using a slice query.

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 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 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';

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 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 expected volume by partition is around 200k records by day and TTL is one week.

Thank you for your support,

Best regards,

Nicolas HENNEAUX
IT Architect

Email:

Nicolas.Henneaux@arhs-developments.com<ma...@arhs-developments.com>

Tel.:

+32 2 774 01 49

Fax:

+32 2 774 88 31


[id:image001.png@01D32882.048263F0]


Woluwedal 30
B-1932 Zaventem
www.arhs-dev-be.com<http://www.arhs-dev-be.com/>