0

I'm in the process of designing a Cassandra database expected to handle messages on several clusters. The data model is primarily structured with a partitioning strategy based on a key locationId using a modulo 100 approach (so a total of 100 partitions) . While this model suits most clusters with a varied distribution of keys, I'm facing a challenge with a particular cluster that predominantly has data under a single key value, which means all messages in this cluster belong to one location(id).

Design Details and Challenge:

Partitioning Strategy: key-based partitioning based on locationId modulo 100.

Query Pattern: Always has locationId, and may include others like deviceId, start and end timestamps, message type. We also plan to have secondary indexes on these identifiers for facilitating searches. Search can also be for a single message based on the id ( along with the locationId)

Clustering Column: startTimestamp is used as a clustering column, primarily for sorting purposes

Challenge Cluster: Has only one locationIdlocationId, leading to a large partition.

Seeking Insights On:

  • How can I devise an effective partitioning strategy for a cluster with limited key diversity?
  • Would a different data model or partitioning approach be advisable for this particular cluster?
  • Strategies for optimizing read/write performance in a scenario with a skewed distribution of key values.

I would appreciate any insights or experiences on handling such scenarios in Cassandra.

2 Answers 2

0

In short, you need to have a bucket in the partition to break down the size of the partition. It is recommended you keep the partition size <=10MB if you are anticipating hypergrowth to these table(s). Also, you may have to leverage these free browser-based tutorials to deeply understand data modeling nuances and efficient strategies,

0

When modelling data in Cassandra, the primary objective is to design tables which exactly match the application queries. This means that you need to start the modelling process by listing all the application queries so you can design the tables accordingly.

From the details in your post, it seems you are more concerned about how the data will be stored in the tables rather than focusing on the app queries. For example, you pointed out that there is only one location so there is no reason to partition by location. In this scenario it makes no sense for the app to query by location.

You haven't provided details of your use case so I'll make a few assumptions in order to come up with something tangible for you.

If the app needs to query by device, here is a table partitioned by device ID:

CREATE TABLE messages_by_deviceid (
    deviceid text,
    receivedtime timestamp,
    messageid text,
    message text,
    PRIMARY KEY (deviceid, receivedtime)
) WITH CLUSTERING ORDER BY (receivedtime DESC)

To retrieve all messages by device:

SELECT * FROM messages_by_deviceid
  WHERE deviceid = ?

To retrieve the last 10 messages:

SELECT * FROM messages_by_deviceid
  WHERE deviceid = ?
  LIMIT 10

To retrieve messages from a period of time:

SELECT * FROM messages_by_deviceid
  WHERE deviceid = ?
  AND receivedtime >= ?
  AND receivedtime < ?

Hopefully these examples will help you move forward with your design. Cheers!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.