How are partition key, clustering key, and secondary index organized in Cassandra?

In Cassandra, data is organized considering the distributed nature of the database and high read/write speeds.

Partition Key — this is the key that determines on which node of the cluster the data will be stored. It is used for hashing and distributing data across partitions. All rows with the same partition key fall into one partition, ensuring data locality and query efficiency based on this key.

Clustering Key — this is an additional key that determines the order of rows within a partition. It allows efficient execution of queries with sorting and range filters within a single partition.

For example, if you have a table with partition key = user_id, and clustering key = timestamp, then all data for one user will be stored together and sorted by time.

Secondary Index — this is an index created on columns that are not part of the partition or clustering key. It allows queries on these columns but with limitations on performance and scalability. Secondary indexes in Cassandra are not always efficient with large volumes of data and often it is recommended to use materialized views or denormalization.

Example of table definition:

CREATE TABLE events (
  user_id uuid,
  event_time timestamp,
  event_type text,
  details text,
  PRIMARY KEY (user_id, event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

CREATE INDEX ON events(event_type);

Here, user_id is the partition key, event_time is the clustering key, and an index is created on the event_type column.