How to setup cassandra event store table structure correctly

Question

I'm beginner in case of Cassandra database. I have prepared example of event store table, which looks like the following:

CREATE TABLE IF NOT EXISTS eventstore.Event(
                                Id uuid, 
                                Data text, 
                                Version int,
                                AggregateId uuid,
                                EventIdentity uuid,
                                Date timestamp,
                                  PRIMARY KEY (AggregateId, Version)
                                ) WITH CLUSTERING ORDER BY (Version ASC)

Where:

Id -> Unique GUID for each event

Data -> JSON event data

Version -> int value of event version

AggregateId -> as named, Id of Aggregate

EventIdentity -> Id of Event type

Date -> timestamp when Event occurred

I am not sure if my Primary key is correct (AggregateId,Version) and also Clustering by Version. I'm wondering if my table will be partitioned correctly. Partition by AggregateId which contains all events for this aggregate ordered by Version.

In Cassandra, everything depends on the queries that you're planning to execute against this table. Can you update your question with examples of queries? — Alex Ott

Aaron Aaron · Accepted Answer · 2019-11-12T14:13:26

Partition by AggregateId which contains all events for this aggregate ordered by Version.

If that's your goal for partitioning, then you have configured your PRIMARY KEY correctly. The only add I'd make, would be around this statement:

Id -> Unique GUID for each event

If you really want to ensure uniqueness (and if it's possible that aggregate events could share versions) I would add your ID on the end of the PRIMARY KEY definition:

PRIMARY KEY (AggregateId, Version, Id)

Two follow up questions:

1) What is your anticipated query pattern? If you'll always have AggregateId and/or Version in your WHERE clause, you should be fine. If not, you might want to reconsider the PK definition.

2) What is the ratio of AggregateId:Version? You'll want to make sure that the partitions would not be subject to unbound growth, and be mindful of Cassandra's 2 billion cell-per-partition limit.

But overall, it looks like you're on the right track.

How to setup cassandra event store table structure correctly

1 Answers