17.04.2024 Problem:
The issue revolves around an unexpected change in the TTL for our Cassandra cluster. There are approximately 28 tables, six of which contain around 2 million records each. The requirement is to update the TTL for these tables to three years. However, TTL is configured at the row level.
The client seeks alternative methods to update the TTL without resorting to reinserting all the data, which would be both time-consuming and lengthy.
More specifically, the client is not seeking steps to take after the TTL update. Instead, he is looking for methods to update the TTL on tables containing over 2 million records without needing to reinsert all the records. The request is to assist in exploring possible methods to update the TTL at the column level rather than at the entire table level.
Solution:
The old data in Cassandra should be either reinserted or updated with TTL.
It is necessary to update “users” USING TTL 30 SET “location” = ‘Vancouver’ WHERE “username” = ‘bob’;
Then run nodetool commands to free up disk space.
The TTL is additional 8 bytes in a row. So it should be added for each row. The default TTL can be also added to the table using ALTER TABLE syntax.
ALTER TABLE table_name WITH default_time_to_live= xxxx Seconds.
This will apply to new data.
Conclusion:
In summary, updating the Time to Live (TTL) for a Cassandra cluster, especially for tables with millions of records, is complex. The proposed solution involves using CQL commands like “UPDATE” with TTL and “ALTER TABLE” to manage TTL settings. However, careful consideration of performance and resource implications is crucial. The client should test the solution and consult experts or documentation for guidance.