Efficiently managing time to live and soft delete rows
RANGE COLUMNS is highly useful in managing time to live and soft delete rows. Suppose you have a application which specifies the expiry time of row (row to be deleted after it crosses the expiry time) and the expiry is varying.
Suppose the application can do the following types of inserts:
- Insert persistent data
- Insert with expiry
If the expiry is constant i.e all the rows inserted will be deleted after certain time, we can go with RANGE partitioning. But if the expiry is varying i.e some rows will be deleted in a week, some in a month, some in a year and some have no expiry, it is not possible to create partitions. In that case, you can use the RANGE COLUMNS partitioning explained below.
How it works...
We introduce a column called soft_delete which will be set by trigger. The soft_delete column will be part of range column partitioning.
Partitioning will be like (soft_delete, expires). The soft_delete and expires together controls which partition...
