Using INSERT on set data types can create tombstones

Summary

When adding records into existing rows of a given table that has collection set data types. Using the INSERT statement instead of the UPDATE statement can create unwanted tombstones.

Symptoms

Consider a table that has a collection set data type for a given column like so:

CREATE TABLE my_table ( 
id text, 
description text, 
data set<text>, 
PRIMARY KEY ((id), description) 

When records are inserted into the table like so 

INSERT INTO my_table (id, description, data) VALUES ('id_01', 'the first id', { 'new1', 'new2', 'new3' });

Rows are created. However, if an update is added like so:

INSERT INTO my_table (id, description, data) VALUES ('id_01', 'the first id', { 'new1', 'new2', 'new3', 'new4' });

This would actually create a tombstone since the whole collection set of values "new1", "new2", "new3", and "new4" is written, instead of the the "new4" element simply being inserted.

Cause

For collection set type columns, this is simply the way the insert / update works

Solution

To avoid tombstones, use the UPDATE statement with the "+" or "-" to add or remove collection set elements respectively, for example:

UPDATE my_table SET data = data + { 'new5', 'new6' } WHERE id = 'id_01';
UPDATE my_table SET data = data - { 'new1' } WHERE id = 'id_01';

Further reading

For more information and examples see the docs here:

https://docs.datastax.com/en/cql/3.1/cql/cql_using/use_list_t.html