Cassandra duplicate tables for different partition keys?

user_917467 0
Viewed 48

I have the following table, called inbox_items:

USE zwoop_chat
CREATE TABLE IF NOT EXISTS inbox_items (
    postId text,
    userId text,
    partnerId text,
    fromUserId text,
    fromNickName text,
    fromAvatar text,
    toUserId text,
    toNickName text,
    toAvatar text,
    unread int static,
    lastMessage text,
    lastMessageDate timestamp,
    PRIMARY KEY ((postId, userId), lastMessageDate)
) WITH CLUSTERING ORDER BY (lastMessageDate DESC);

The problem with this table is that I want to query it, both by postId and userId, as well as by userId only.

In other words, I have an inbox per post, but I have an inbox per user as well.

Afaik there is no good way to achieve this because:

  1. The partition key(s) uniquely determine the node where the data is stored. I.e. all partition keys corresponding the where clause should be present.
  2. Secondary index is no good fit for keys with high cardinality (in this case, postId has high cardinality)

The solution I currently see is to duplicate the table with different keys.
This feels like such an overkill though.

Is there a better solution I'm missing?

cassandra
1 Answers

Assuming partitioning by userid alone would not generate partitions that are too large, you partition by userid, and have postid in the clustering key. You specified that you would query by :

The problem with this table is that I want to query it, both by postId and userId, as well as by userId only.

So in this instance, you do not need postid within the partition key, but within the clustering key. The only issue is if you intend to query by postid alone as well - but that was not mentioned.

If the partition by userid will result in partitions that are too large, there is additional bucketing techniques available.

user_1111240
Related