Best Practice for Many bins with different TTL

1

I am looking for database solutions to solve an existing problem with our current dataset.

I have 90 million users, each user has a collection of attributes, there’s 1000+ separate attributes, but each user probably has 40-80 assigned. Each attribute has a set TTL some for hours, some for months. I’m looking for a solution to provide me all attributes for a given user, factoring out expired data.

It seems that the ideal pattern would be to create a record for each user, and attach the attributes as bins. However, TTL is only on a record level, so this method will not work. I see two options:

  1. Create a unique set for each user, with one record for each attribute, each record having a single bin with the name of the attribute. This would create 90 million sets with 40-80 records each, about 4.5 billion rows.

  2. Create records with two bins, a unique userID and a single attribute. Then place a secondary index on the userID bin. This would create one set and an index with 4.5 billion rows.

What’s better for performance? I cannot find any details on query performance when specifying a set alone. Or if having something like 90 million sets would cripple the system. Or if an index of 4.5 billion rows is too big.

Some background if it helps: We are currently using a 5-node Cassandra cluster, and we do have it working, sorta, but the queries to get the data are complicated, and our biggest issue is that to get it to work we needed to translate everything to a string, so we lost all typing on the data.

2

Aerospike only supports up to 1023 sets.

Sets, in Aerospike, do not create separate trees to search, they are still inserted into the partition’s trees. Sets offer certain configuration options and metrics about the data within the set.

If you stored the TTL for a bin within the record, you periodically scan all records and delete the expired bins using a background scan and predexp. Currently you would need to use two bins for the attribute TTL and the attribute.

3

This is commonly done in audience segmentation use cases in ad-tech for example. Using records level TTL is not a great option, due to some of the challenges mentioned above.

How I would model the data is to use the power of the Aerospike Maps API. If you store the data per user in a map with the key of the map being the attribute and the value being the TTL, you would have something like:

userid -> Bin( "map", {
    attribute1: TTL1,
    attribute2: TTL2,
    ....
})

The record level TTL would be the longest TTL of all the attributes so the records would eventually expire if not updated. To get the attributes associated with the user which have not expired, you can use MapOperation.getByValueRange:

// We only care about records whose TTL is in the future:
long now = Value.get(new Date().getTime());
Record results = client.operate(null, 
    new Key(namespaceName, setName, userId), 
    MapOperation.getByValueRange(MAP_BIN_NAME, now, null, MapReturnType.KEY));

When you insert data, you would need to use the corresponding MapOperation.put() to insert the value into the map. But some of the entries in the map might have expired already and we’re already updating the record so we might as well trim these off at the same time. Luckily the operate() API call is flexible enough to allow us to do both of these at the same time:

// Assume a 30 day TTL for this segment
long now = new Date().getTime();
long expiry = now + TimeUnit.DAYS.convert(30, TimeUnit.MILLISECONDS));

Operation insertMapValue = MapOperation.put(new MapPolicy(MapOrder.KEY_ORDERED, 0), MAP_BIN_NAME, Value.get(userid), Value.get(expiry));
Operation trimExpiredValues = MapOperation.removeByValueRange(MAP_BIN_NAME, null, Value.get(now), MapReturnType.NONE);

client.operate(writePolicy, 
    new Key(namespaceName, setName, userId), 
    insertMapValue, trimExpiredValues);

If you have users which are only ever read, you may have some stale attributes which have effectively TTLd out hanging around the database, but these will be removed when the record TTL’s out. (I’m assuming you’ve set up the expiration in the write policy as well as retries). If these become an issue, you could do a background scan periodically running the same removeByValueRange operation.

2 other things to note:

  1. I would definitely set up your map as a K_ORDERED or KV_ORDERED map
  2. If you don’t need high resolution on your TTL you can save space in the database by not storing the attribute level TTL as milliseconds since 1970, but something with smaller numbers like number of minutes since 1/1/2020 for example. Aerospike stores the data in MSGPACK format internally so smaller integral numbers take less space.
3 Likes