question

dtln812 avatar image
dtln812 asked Erick Ramirez edited

Is it better to do multiple partition reads vs storing rows in a large partition?

I want to keep track of unread messages for private chats for each user. I thought of two ways for doing this, both are pretty similar, the difference is the approach of select and partition key. So one user can have from 1 to 10.000+ contacts. When he's logging in he requests the count of unread messages for all his contacts.
First approach:

create table private_unread_counter (
    from_id text,
    to_id text,
    count counter,
    primary key ((from_id, to_id))
);

This approach means that I'll have to make a select for each contact that an user has. Something like that:

foreach (contact in requestingUserContacts)
{
    unreadMsgsFromContact = select count from private_unread_counter where from_id=contact and to_id=requestingUser
}

Second approach:

create table private_unread_counter (
    from_id text,
    to_id text,
    count counter,
    primary key (to_id)
);

I have one (potentially big) partition for user, and when I want to fetch how many unread messages a user has, I make one select only:

unreadMsgsFromContacts = select (from_id, count) from private_unread_counter where to_id=requestingUser

I'm not sure which one is more appropriate for this. Any answer / info would be appreciated.

data modeling
10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.

1 Answer

steve.lacerda avatar image
steve.lacerda answered

Hi! It all depends on the partition size that you think is possible. If the partition size gets into the GiB range, then I would avoid that because it's going to end up causing you issues with things like repair and read performance. If the partition size is 100MiB, then that will work for you and you can go that route. I've seen too many people get lost in that large partition to only have to go back and fix it, which is much more painful (full migration of data) than thinking it out properly at the start.

Share
10 |1000

Up to 8 attachments (including images) can be used with a maximum of 1.0 MiB each and 10.0 MiB total.