Cassandra: List recent coversations for a user

Question

we are using cassandra to store chat data. we have to maintain all the users conversations and sort them based on the messages received.

Conversation: Is a stream of messages between 2 or more users. Conversations does not have an endtime and are continuous.

We are maintaining a table which has "user to conversations" based on the conversation create time. But we would like to sort it based on the messages received.

Solutions

• Solution -1:

I have looked at a similar question Cassandra: List 10 most recently modified records

which suggests to use materialized view. Is this approach fine for frequently updated records like messages.

• Solution -2: Use another table to maintain user_recentconversations in a list. Update the list with when the messages are received/sent to/by the user.

Considering the usecase please suggest what would be better solution.

Answer 1

What you actually want is to sort all the messages globally. Indeed, sorting conversations by the most recent message means sort all the messages as well.

An easy way to solve your problem is to create another table where you store each arrived message for the purpose of ordering the conversations:

CREATE TABLE conversations_by_most_recent_message (
    conversation_id int,
    ts timestamp,
    message text,
    PRIMARY KEY (conversation_id, ts)
) WITH CLUSTERING ORDER BY (ts DESC);

Whenever a message arrives you store it there. In this table you will have all the messages pertaining each conversation reverse-ordered chronologically. Getting data from this table will help you to know the timestamp of the last message on per-conversation basis.

The second step you need to do is getting the most recent record from each partition, and this can be easily solved exploiting the PER PARTITION LIMIT clause introduced in Cassandra 3.6:

SELECT * FROM conversations_by_most_recent_message PER PARTITION LIMIT 1;

This will fetch only the first record from each partition, that is only the most recent message from each conversation.

The final step is to sort (by timestamp desc) the rows retrieved at application level.