cassandra - Supporting logical delete for an existing feed table -

I want to apply logical delete to a news-feed record to support a later version.
The system is in production, so any solution should support existing data.
Inserting an archive in the feed is idempotent, thus putting the already deleted record (the same primary key) should not be undelete it.
To retrieve any solution, the query should support a page of existing or deleted records.

Feed table:

  Press my table with my_feed (tenant_id int, item_id int, created_at timestamp, feed_data text, primary key (tenant_id, created_it, feed_id)) with compression = {'Sstable_compression': 'LZ4 Compressor'} and Clustering Order By (created_at DESC);  

My thoughts have two approaches, but there are serious disadvantages of these two: 1. Delete deleted records in a different table. The questions are trivial and no migration is necessary, but idempotent is difficult to insert (read only before inserting?).
2. Add Add_deleted column Create a secondary index to support the query for that column. It seems easy to support idmotent inserts (light transaction or update operation) The main disadvantage is that the old records have zero value, thus requiring data migration.

Is there a third more elegant attitude? Do you support one of the tips above?

If you maintain a separate table for deleted records, you can use your "move" operation Use the CQL's batch to run, but that table is the only record of extinction, you have to check it first if you want to repeat the records that are not about your behavior. Before reading, there is usually an anti-pattern, etc. To use a is_deleted column may be some migration work is required, as you mention, but possibly more The serious problem can be that it is usually very inefficient to create an index on very low cardinal columns. With the Boolean field, I think there are only two lines in your index. If you do not destroy too many times, then this means that your "false" line will be very broad and therefore

If you avoid creating a secondary index for the is_deleted column, and you indicate both the null and the false to indicate the active record Allow , whereas only the obvious true indicates deleted people, you may not need to migrate anything (do you really know who during the migration? Will the existing records be deleted from?) Then you can delete the client's archived records Filters give up, who will be in charge of you already have some paging behavior. For the sake of this design, you may have to ask EN Records for EN, which have not been deleted!

I hope that according to the question and tell the address as you have said. I would be curious to know why the records already deleted have the need to be protected to bring you back, but I can imagine a situation where you can have a special feed (and CAS problems) ) But there are many actors.

On some unrelated notes, you would like to consider using timeuuid instead of timestamp for your created_at field You can. The CCL supports a dateOf () function to get that date if it is an obstacle (it may be impossible to get a conflict within your tenant_id partitions) In this case, you can safely ignore me.)