Which one is better to use TTL or Delete in Cassandra?

Question

I want to remove records from Cassandra cluster after a particular time. So what Should I use TTL or manually delete?

Erna

Answer 1

It depends on your data model. The fortunate answer, is that due to their predictable nature, you can build your data model to accommodate TTLs.

Let's say I build the following table to track user requests to a REST service, for example. Suppose that I really only care about the last week's worth of data, so I'll set a TTL of 604800 seconds (7 days). So the query I need to support is basically this (querying transactions for user 'Bob' for the prior 7 days):

SELECT * FROM rest_transactions_by_user 
  WHERE username='Bob' AND transaction_time > '2018-05-28 13:41';

To support that query, I'll build this table:

CREATE TABLE rest_transactions_by_user (
  username TEXT,
  transaction_time TIMESTAMP,
  service_name TEXT,
  HTTP_result BIGINT,
  PRIMARY KEY (username,transaction_time))
  WITH CLUSTERING ORDER BY (transaction_time DESC)
  AND gc_grace_seconds = 864000      
  AND default_time_to_live = 604800;

A few things to note:

• I am leaving gc_grace_seconds at the default of 864000 (ten days). This will ensure that the TTL tombstones will have adequate time to be propagated throughout the cluster.
• Rows will TTL at 7 days (as mentioned above). After that, they become tombstones for an additional 10 days.
• I am clustering by transaction_time in DESCending order. This puts the rows I care about (the ones that haven't TTL'd) at the "top" of my partition (sequentially).
• By querying for a transaction_time of the prior 7 days, I am ignoring anything older than that. As my TTL tombstones will exist for 10 days afterward, they will be at the "bottom" of my partition.

In this way, limiting my query to the last 7 days ensures that Cassandra will never have to deal with the tombstones, as my query will never find them. So in this case, I have built a data model where a TTL is "better" than a random delete.

Withdraw

Answer 2

The answer is "it depends". Deleting data in cassandra is never free.

If you have to "DELETE" you need always to issue those queries, with TTL it's done from the moment you write the data. But by using DELETE you have more control over data deletion.

On the operation side, you should try to get your tombstones in the same sstable so once gc_grace is expired the full sstable can be dropped. Because data is only actually deleted when the sstables are compacted, even if gc_grace has passed, and a compaction didn't happen with the sstable holding the tombstone, the tombstone will not be deleted from the harddrive. This also make relevant the choice of compaction strategy for your table.

If you're also using a lot of tombstones, you should always enable: "unchecked_tombstone_compaction" at table level. You can read more about that here: https://docs.datastax.com/en/cql/3.1/cql/cql_reference/compactSubprop.html

Cristobalcristobalite

Answer 3

Letting the record expire based on TTL is better. With TTL based delete, you can set the gc_grace_seconds to a much lower value (1 day or two) and you do not have to worry about tombstones lingering for a longer duration.

With manual delete, you need to make sure the tombstones do not increase beyond the warning and error threshold, as it impacts the query.

Epistaxis

While this technically works, I would caution you about lowering your gc_grace_seconds that much. That essentially means you'll need to run repair every day, or risk data inconsistency. Which, in the case of tombstones, means "ghosting." – Withdraw

As I understand, the issue with ghosting is due to a node being down and doesn't even realize that the record got deleted, and gc-ed as well in the other replica. With TTL, the record will also be expired on the node that was down and came back online now. Is my understanding correct? – Epistaxis