Query-first table design, partition and clustering keys, bucketing, consistency levels and tombstones.
Published September 24, 2026
Cassandra (and its relatives ScyllaDB, HBase and Bigtable) can absorb enormous write volumes across many machines, and stay available when nodes fail. It gets there by giving up things relational databases make easy: joins, ad-hoc queries and flexible secondary access paths. Working with it successfully comes down to one mindset shift:
Design tables around the queries you will run, not around the entities you have.
In a relational design, you normalize entities first and write queries later. In Cassandra, you list the queries first, and create one table per query, even if that means storing the same data several times.
Every Cassandra table has a primary key made of two parts:
CREATE TABLE messages_by_conversation (
conversation_id uuid,
sent_at timeuuid,
sender_id uuid,
body text,
PRIMARY KEY ((conversation_id), sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);
conversation_id): hashed to decide which nodes store the row. All rows with the same partition key live together, in one partition, on the same replicas.sent_at): decide the sort order within the partition. Rows are stored physically sorted, so reading "the latest 50 messages" is a single sequential read.Queries that are efficient in Cassandra:
WHERE conversation_id = ?.WHERE conversation_id = ? AND sent_at > ? LIMIT 50.Queries that aren't:
WHERE sender_id = ?). That would have to ask every node, and Cassandra refuses it unless you add ALLOW FILTERING, which is a red flag in production.Say a messaging feature needs three access patterns:
Each gets its own table:
-- Q1: messages in a conversation
CREATE TABLE messages_by_conversation (
conversation_id uuid, sent_at timeuuid, message_id uuid, sender_id uuid, body text,
PRIMARY KEY ((conversation_id), sent_at)
) WITH CLUSTERING ORDER BY (sent_at DESC);
-- Q2: a user's inbox, most recently active first
CREATE TABLE conversations_by_user (
user_id uuid, last_activity timestamp, conversation_id uuid, title text, last_snippet text,
PRIMARY KEY ((user_id), last_activity, conversation_id)
) WITH CLUSTERING ORDER BY (last_activity DESC, conversation_id ASC);
-- Q3: direct lookup
CREATE TABLE messages_by_id (
message_id uuid PRIMARY KEY, conversation_id uuid, sent_at timeuuid, body text
);
Sending a message writes to all three tables. Writes are cheap in Cassandra, because they're sequential appends to a commit log and an in-memory table, which is exactly why this duplication is acceptable. Use a logged batch when the writes must eventually all apply together. It guarantees they will all be applied eventually, but it's not an isolated transaction.
One subtlety in Q2: last_activity is part of the clustering key, and clustering columns can't be updated in place. Moving a conversation to the top of the inbox means deleting the old row and inserting a new one. Designs like this are common, and they're why modeling in Cassandra rewards thinking through update paths, not just reads.
A partition must stay bounded. A good rule of thumb is to keep partitions under about 100 MB, and under roughly 100,000 rows. Very large partitions cause slow reads, heavy compaction, memory pressure and repair problems. They also create hot spots, because one partition lives on only a few replicas.
A conversation between two people might stay small forever. A sensor writing every second, or a huge public chat channel, won't. The fix is bucketing: add a time component to the partition key.
CREATE TABLE readings_by_sensor_day (
sensor_id uuid, day date, ts timestamp, value double,
PRIMARY KEY ((sensor_id, day), ts)
) WITH CLUSTERING ORDER BY (ts DESC);
Each sensor-day is its own partition, bounded by 86,400 readings at one per second. Reading a week means querying seven partitions, which the application can do in parallel. Choose the bucket size (hour, day, month) so that partitions stay within limits, while typical queries touch only a few buckets.
Each row is stored on replication factor (RF) nodes, typically 3. Every read and write chooses a consistency level:
ONE: one replica must respond. Fastest, but the weakest guarantee.QUORUM / LOCAL_QUORUM: a majority of replicas (2 of 3). The standard choice.ALL: every replica. Rarely used, because one slow node blocks the request.Using QUORUM for both reads and writes means every read overlaps with the latest acknowledged write (W + R > RF). LOCAL_QUORUM does the same within one data centre, avoiding cross-region latency. Replicas that miss writes are brought up to date by hinted handoff, read repair and scheduled anti-entropy repair. Running repair regularly is an operational must, not an optional extra.
Conflicts are resolved by last-write-wins on cell timestamps. Two concurrent updates to the same column keep whichever has the later timestamp, so clock synchronization (NTP) matters. For compare-and-set semantics, such as "create this username only if it doesn't exist", Cassandra offers lightweight transactions (INSERT … IF NOT EXISTS), which use Paxos. They're correct, but several times slower than normal writes, so reserve them for the rare operations that truly need them.
Deleting data in Cassandra writes a tombstone, a marker saying "this is deleted", which is kept for gc_grace_seconds (10 days by default), so that replicas that missed the delete don't resurrect the data. Workloads that delete heavily and then scan the same partitions (queue-like tables are the classic example) end up reading through thousands of tombstones, which gets slow, and eventually fails queries.
To avoid that:
Good fits: very high write throughput with predictable access patterns, such as time series, event and activity logs, messaging history, IoT telemetry, and user activity feeds, especially when multi-region availability matters.
Poor fits: ad-hoc analytics and reporting (export to a warehouse instead), workflows needing multi-row transactions, heavy update-in-place or delete patterns, and small datasets where a relational database is far simpler to run.
Q: Why does Cassandra encourage duplicating data across tables? A: Queries must be served from a single partition to be efficient, and there are no joins. So each access pattern gets a table whose partition key matches that query. Writes are cheap, so writing the same data to several tables is the intended trade-off for fast, predictable reads.
Q: What's the difference between the partition key and the clustering columns? A: The partition key decides which nodes store the data, and groups rows into partitions. Clustering columns decide the sort order of rows within a partition, which enables efficient range queries and ordered reads inside it.
Q: How do you stop a partition from growing too large? A: Add a bucketing component to the partition key, typically a time bucket (day or month), or a hash bucket for very active entities, so each partition stays bounded. Queries then read a small number of buckets, often in parallel.
Q: What consistency level should I use?
A: LOCAL_QUORUM for both reads and writes is the usual default: reads see the latest acknowledged writes within a data centre, and the operation tolerates one replica failure with RF = 3. Use ONE only where stale reads are acceptable and latency matters most.
Q: Why are heavy deletes a problem? A: Deletes create tombstones that stay around until they're safe to purge. Reads over partitions containing many tombstones must skip them all, which is slow, and beyond configured thresholds queries fail. Prefer TTL-based expiry with time-bucketed partitions, and avoid queue-like delete-heavy patterns.