Data modeling in Cassandra: design for your queries

A query that can't identify its partition must ask many nodes. Cassandra rejects such queries unless you add ALLOW FILTERING — treat that keyword as a red flag in production code.

Rule 2: one table per query

Suppose the app needs two views of user activity: "a user's recent actions" and "everyone who acted on an item". In SQL that's one table and two indexes. In Cassandra it's two tables:

CREATE TABLE activity_by_user (
  user_id uuid,
  ts      timeuuid,
  item_id uuid,
  action  text,
  PRIMARY KEY (user_id, ts)
) WITH CLUSTERING ORDER BY (ts DESC);

CREATE TABLE activity_by_item (
  item_id uuid,
  ts      timeuuid,
  user_id uuid,
  action  text,
  PRIMARY KEY (item_id, ts)
) WITH CLUSTERING ORDER BY (ts DESC);

The application writes each event to both. Denormalization is the normal case in Cassandra — disk is cheap, cross-partition reads are not. A logged BATCH can keep the two writes together when that matters.

Rule 3: keep partitions bounded

A partition key like sensor_id alone would grow forever as readings accumulate. Very large partitions slow down reads, repairs and compaction. Add a time bucket (day, week or month, depending on volume) to the partition key, as in the first example, so each partition stays a manageable size. A common guideline is to keep partitions well under about 100 MB and a few hundred thousand rows.

Rule 4: spread load evenly

A partition key with few distinct values — country, status — concentrates traffic on a few nodes ("hot partitions"). Choose keys with high cardinality and even access, or add a bucket to spread a hot key across several partitions.

Tunable consistency

Each read and write chooses a consistency level. With a replication factor of 3, using QUORUM for both reads and writes means a read always overlaps with the latest successful write (2 + 2 > 3). In multi-datacenter clusters, LOCAL_QUORUM keeps that guarantee within a data center without cross-DC latency.

A modeling checklist

1. List the application's queries first.
2. For each query, design a table whose partition key is in the WHERE clause.
3. Use clustering columns for the sort order the query needs.
4. Bucket partitions that grow over time.
5. Accept duplication; write to all the tables that need the data.

What is Cassandra

In this article you will learn about Cassandra and its Data Model.

#data-modeling#scalability