I have been working in a cassandra database to keep the real time data of an event/monitoring system that has quite a large number of events per hour(100k+) and I have hit a wall in a specific need: I need a way to get the data by event state(open,close) for all the events.

Each event has the following fields: id(this is generated by the event system), state, start_date,end_date,node, description.

My first aproach was to have a table with state as partition key and id as clustering:

CREATE TABLE alarms_by_state (
PRIMARY KEY((state),id))

This that didn't work, as it keeps 2 states for each id.

What I need is that for example we receive event with id = 1 and state=open and the application saves it into the database, later another event with id=1 and state=closed and replaces the previous record.

I know this could all be done in the application side, but I'm trying to keep that control to a minimum due to the number of events I receive per hour.

Thank you for your time

1 Answer 1


that didn't work, as it keeps 2 states for each id.

I like that you tried this. For anyone who sees this post in the future, it'll underscore the point that primary keys in Cassandra are unique.

Here's how I would do it.

CREATE TABLE alarms_by_date (
    id UUID,
    state TEXT,
    start_date DATE,
    end_date DATE,
    node TEXT, 
    PRIMARY KEY((day,hour,m15),start_date,id))

CREATE INDEX ON alarms_by_date(state);

What this does:

  • Stores your data partitioned in 15 minute intervals. With 100k/hour, that's too much to throw into a single partition. Breaking that into 4 parts should keep it from growing too large. This could be done with a single partition key, as long as the key was made up of those components (ex: 202102171045).
  • Applying a clustering index of start_date and id. Not sure which of the two dates (start/end) are more important to your business case. But this way, the data will be in descending order by time. Also, I like using id columns as the last clustering key, to help ensure uniqueness.
  • Applying a secondary index on status is really the way to go here. Yes, there's a lot wrong with secondary indexes in Cassandra. But, the main problem with them, is that they can result in scans of multiple nodes. By pre-empting queries using that index with the date/partition, queries will be restricted to only a single node. So I think that'd be the way to go.

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.