CODEWITHSUNDEEP
scyllatimeuuid
let4be
let4be

Reputation: 1081

How to guarantee monotonically increasing timeuuid when selecting from scylla

I have a table with timeuuid as a clustering key.

CREATE TABLE event (
    domain TEXT,        
    createdAt TIMEUUID,
    kind TEXT,
    PRIMARY KEY (domain, createdAt)
);

I wish to select the data in order of this clustering key with the following guarantee - if I selected something, there will be NO inserts before those records in the future(so I could iterate through records checking what's new happened without the risk of skipping any events)

SELECT kind FROM event WHERE domain = ? AND createdAt > lastCreatedAtWeAreAwareOf

If I generate timeuuid on client and use parallel insert to scylla it's technically possible that recent timeuuid will get inserted first before several older(say due to say some networking issue) and I might miss those records in my selects.

What are possible ways to resolve this?

I tried using currentTimeUUID function and it seems to work(monotonically increasing within the same partition key) but creates a lot of duplicates(20-40 duplicates per the same partition key), I.e I end up with lots of records with exactly the same currentTimeUUID(I would really like a way to avoid duplicates, it complicates the select process and consumes unnecessary resources)

I'm also curious is there a threat of backward clock jumps when using currentTimeUUID function?

Upvotes: 4

Views: 886

Answers (1)

haaawk
haaawk

Reputation: 330

EDITED

It seems that there's a bug in Scylla that currentTimeUUID always generates duplicates for writes done at the same time using the same coordinator. I created an issue here. Thanks for bringing this up.

PREVIOUS ANSWER BELOW

If I generate timeuuid on client and use parallel insert to scylla it's technically possible that recent timeuuid will get inserted first before several older(say due to say some networking issue) and I might miss those records in my selects.

Just to clarify, all writes will be stored in the right order. There will be a point in time when you will be able to read old enough writes in the right order. This means that one possible solution would be to make sure that select does not query too recent data. Thus leaving a window for 'late' writes to arrive and take their place in line. For example, you could use a select like this:

SELECT kind FROM event WHERE domain = ? AND createdAt > lastCreatedAtWeAreAwareOf AND createdAt < now() - 30s

I don't know whether it's ok for you to impose such delay though. This strategy won't give you a full certainty because all writes that got delayed by more than 30s will be missed.

I tried using currentTimeUUID function and it seems to work(monotonically increasing within the same partition key) but creates a lot of duplicates(20-40 duplicates per the same partition key), I.e I end up with lots of records with exactly the same currentTimeUUID(I would really like a way to avoid duplicates, it complicates the select process and consumes unnecessary resources)

You can reduce the chances of clustering key duplications by introducing additional clustering key column like:

CREATE TABLE event (
    domain TEXT,        
    createdAt TIMEUUID,
    randomBit UUID/int,
    kind TEXT,
    PRIMARY KEY (domain, createdAt, randomBit)
);

and generate value for it on the client in some good random way. Maybe there's some aspect of the record that you know is guaranteed to be unique and could be used as a clustering key column. It would work better than a random field.

Upvotes: 4

Related Questions