Index limited number of records based on "within <#> days"

SQL++
1

Hello! I’m in the process of converting a rather large scale application to CB. We currently process around 100-200 million “activity” records per month (and growing). To keep costs down, we’re proposing of only having a limit of how far back you can see your activity.
Is there a way to keep Couchbase indexing only based on a certain date? Say, only the past 90 days of activity or only newest 1,000 records (per customer)? The users will rarely even be able to scroll back 90 days, and we’d like to retain the data in potentially glacial storage or something that’s not regularly used (some of our clients are in health care and require years of storage, even if they don’t actively use it).
Each activity record look almost identical, we index the same fields, and have a “custom” object that contains all the non-index information (such as “cached” records of user name’s, page they were on, machine that made the request, etc.).
We use N1QL presently to filter on the index fields (such as the customer + project), and are currently using CB Server 4.6.

If there’s no easy way to do this, we can continue on our original plan of just adding more and more servers to the cluster as it grows to keep up with the growing index. I did some searching through scaling Couchbase and was unsuccessful finding answers to this. If anybody could help, it would be greatly appreciated. Thank you!

2

You can use partial index feature.
Example: CREATE INDEX ix1 ON default(k0,k1,k2) WHERE timestamp >= "2017-01-01T00:00:00.000-07:00"
SELECT … FROM default WHERE k0 IS NOT NULL … AND timestamp >= “2017-03-01T01:00:00.000-07:00”;

In ix1 keeps the items all 2017 and query is looking for items from 01st March 2017 so ix1 qualifies. If you want reduce index size later date drop and recreate with new timestamp

1 Like
3

This is great, thank you!
There’s no way to possibly limit it to a # instead of just a timestamp similarly to N1QL “limit”, would there be?
Is the speed of recreating this index once a month of any concern? I imagine I could be indexing it on a different shard, then after it’s created, delete the old one to prevent any perceived downtime.

4

There is no limit. There is no concern on how often you create index. You can create on same node or different node different index and later you can drop old index.

5

Thank for the quick responses! You have been extremely helpful!