Order by on mutable columns in cassandra

Question

I have a table with schema below :

create table xx(
 bucket_id int,
 like_count int,
 photo_id int,
 username text,
 PRIMARY KEY(bucket_id,like_count,photo_id)
) WITH CLUSTERING ORDER BY (like_count DESC)

Here i can fetch all records in in descending order of like_count. But i need to update like_count at some point in my app, which i am not able to do because its part of primary key.

If i remove it from primary key, i can not get sorted results based on like_count. What would be correct way to tackle this problem in cassandra?

yurgis yurgis · Accepted Answer · 2016-04-26T17:26:15

I am afraid Cassandra is not a good fit for dealing with mutable orders. (Consider Redis Sorted Sets instead) With that said, you can actually achieve this using CAS-like semantics (compare-and-set) and light-weight transactions which will make your update around 20x slower.

You will also need an additional table that will serve as a lookup for current like_count per bucket_id/photo_id.

create table yy (
  bucket_id int,
  photo_id int,
  like_count int,
  PRIMARY KEY((bucket_id,photo_id))
)

Then do a light-weight-transactional delete from xx followed (if success) by an re-insert into xx and update to yy: Some pseudo code:

//CAS loop (supposedly in a function of args: bucket_id, photo_id, username, new_score)
for (;;) {

  //read current score (the assumption here is that the bucket_id/photo_id entry already exists in both xx and yy)
  ResultSet rs1 = select like_count from yy where bucket_id = ? and photo_id = ?
  int old_score = rs1.one().getInt(0)

  //same score don't do anything
  if (new_score == old_score) break;

  //attempt to delete using light-weight transaction (note usage of IF EXISTS)
  ResultSet r2 = delete from xx where bucket_id = ? and photo_id = ? and like_count = old_score IF EXISTS
  if (rs2.one().getBool(0)) {

    //if delete was successful, reinsert with the new score
    insert bucket_id, photo_id, photo_id, username, like_count into xx values (?, ?, ?, new_score)

    //update lookup table
    update yy set like_count = new_score where bucket_id = ? and photo_id = ?

    //we are done!
    break;
  }

  //delete was not successful, someone already updated the score
  //try again in a next CAS iteration
}

Order by on mutable columns in cassandra

2 Answers