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.henne...@arhs-developments.com<mailto:nicolas.henne...@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/>
