SSTables are never deleted on disk if table gets dropped

Question

SSTables are never deleted on disk if table gets deleted.

I had a a table whose tombstones count is >100000 due to which my read queries were throwing Tombstones error. I then dropped the table, but this didn't delete the SSTable files. I re-created the table, then I ran my select queries, I saw the tombstone error again. I don't understand why the old tombstone error has come up again? Also, when does the SSTable ever gets deleted on disk?

Answer 1

Truncate operation is safer than drop and recreate. Truncate may throw a timeout exception, do it again until it is completely done.

Answer 2

Truncating a table will not remove the SSTable(s) on disk. You need to run nodetool cleanup

Tombstones will disappear through compaction, but only once gc_grace_seconds has passed. The default is 10 days. Why so long? Its designed to be a bit longer than a week providing enough time to run repair on a cluster before deletes are discarded. This maximizes the opportunity for consistency across the nodes.

Answer 3

In order to have your tables deleted from disk you need to make sure that no hard-links are currently pointing at them. By default, a DROP command will create a snapshot of the CF. You need to set to false the auto_snapshot property in the YAML file:

# Whether or not a snapshot is taken of the data before keyspace truncation
# or dropping of column families. The STRONGLY advised default of true 
# should be used to provide data safety. If you set this flag to false, you will
# lose data on truncation or drop.
auto_snapshot: false

If you want err on the safe side (and a general procedure to recreate your keyspace), you could go for:

• DROP TABLE IF EXISTS mytable
• CREATE TABLE mytable (....)
• TRUNCATE mytable

I never had a single problem with this so far.