CODEWITHSUNDEEP
cassandranosqlcql
Morysh
Morysh

Reputation: 71

Cassandra secondary vs extra table and read

I'm facing a dilemma that my small knowledge of Cassandra doesn't allow me to solve.
I have a index table used to retrieve data from an item (a notification) using an external id. However, the data contained in that table (in that case the status of the notification) is modified so I need to update the index table as well. Here is the tables design:

TABLE notification_by_external_id (
    external_id text,
    partition_key_date text,
    id uuid,
    status text,
    ...
    PRIMARY KEY (external_id, partition_key_date, id)
);

TABLE notification (
    partition_key_date text,
    status text,
    id uuid,
    ...
    PRIMARY KEY (partition_key_date, status, id)
);

The problem is that when I want to update the notification status (and hence the notification_by_external_id table), I don't have access to the external ID.
So far I came up to 2 solutions, none of which seems optimal, and I can't decide which one to go with.

Solution 1
Create an index on notification_by_external_id.id, but this will obviously be a high cardinality column. There can be several external IDs for each notifications, but we're talking about something around 5-10 to one top.

Solution 2
Create a table

TABLE external_id_notification (
    notification_id uuid,
    external_id text
    PRIMARY KEY (notification_id, external_id)
);

but that would mean making one extra read operation (and of course maintain another table) which I understood is also a bad practice.

Upvotes: 0

Views: 264

Answers (1)

Jan Dörrenhaus
Jan Dörrenhaus

Reputation: 6717

The thing to understand about secondary indexes is, that their scalability issue is not with the number of rows in the table, but with the amount of nodes in your cluster. A select on an index column means that every single node will have to process it and respond to it, just that it itself will be able to process the select efficiently.

Use secondary indexes for administrative purposes (i.e. you on cqlsh) only. Do not use it for productive purposes.

That being said. You could duplicate all the information into your external_id_notification table. That would alleviate the need for an extra read operation. I know that relational databases taught you, that duplicate data is bad (what if it differs?), and that you should always normalize. But you are not on a relational database. Denormalization is a thing, and on Cassandra, you should always go for that, unless you absolutely cannot.

Upvotes: 1

Related Questions