Question for the Yugabyte team, we are using GSIs in our tables, hence the table needs to be transactional=true, due to which we cannot use the ttl functionality. Do you’ll have any suggestions we can implement deleting records older than a certain time, can we use the “USING TIMESTAMP” clause on insertions and then delete as a part of a background job ? Performance ?
Yes - doing a background job to scan all rows to find out the rows to delete can have some impact performance. Depends on how many rows are in the table. If it is not a very large table, having a periodic (like nightly job) to do the purging would work fine.
An alternate option would be to maintain two tables (instead of using the index). Suppose Table A is the regular table. And Table B keeps the reverse mapping (that normally you would have used an index for). The app would have to explicitly insert to both tables – but it can do the insert with USING TTL <n> clause on both so that the rows are automatically garbage collected by the system at the right time.
Hi Kannan, regarding option 2, maintaining 2 tables, we would need to insert records into 2 tables within a transaction, which means that the tables have to be declared as transactional, we end up with the same problem. Is that correct ?
Yes - mixing multi-shard/multi-table txns with time-to-live TTL isn’t supported. So if that (strict consistency) is needed, then, the doing explicit delete jobs in the background for expired data would be the approach to take.