PRIMARY KEY Syntax: Composite Partition Keys and Ordered Clustering Columns

Learning outcomes

AtlasMart wants “the newest order events for one customer in one month” without scanning the cluster or sorting a large result in application memory. The table's PRIMARY KEY must encode both where that customer's month lives and how rows are ordered inside that partition. This lesson turns the punctuation in CQL primary-key syntax into a physical mental model.

1. PRIMARY KEY is two decisions, not one label

In Cassandra Query Language (CQL), a primary key uniquely identifies a row, but its first component has a second job: it is the partition key. Cassandra serializes the partition-key value or values and the configured partitioner maps that byte representation to a token. The token determines the token range and therefore the natural replica set. Columns after the partition key are clustering columns; they do not choose another node. They order rows that already belong to the same partition and extend row uniqueness.

2. AtlasMart schema: customer + month chooses placement; time chooses order

The read contract is: the caller knows customer_id and month_bucket, wants a bounded time slice, and normally reads newest events first. That yields a composite partition key (customer_id, month_bucket). Within the partition, event_time and event_id give deterministic ordering and uniqueness. The event_id tie-breaker matters because two events can share a millisecond timestamp.

CREATE TABLE atlasmart_keys.order_events_by_customer_bucket (    customer_id uuid,    month_bucket text,    event_time timestamp,    event_id timeuuid,    order_id uuid,    event_type text,    payload text,    PRIMARY KEY ((customer_id, month_bucket), event_time, event_id)) WITH CLUSTERING ORDER BY (event_time DESC, event_id DESC)  AND compaction = {'class':'UnifiedCompactionStrategy'};

The declared descending order is a storage choice optimized for the dominant newest-first access pattern. Cassandra can read the complete clustering order in the declared direction or its reverse; it is not a general-purpose ORDER BY any_column engine.

3. Observe token identity and sorted rows

# If you already have the Chapter 01-06 lab, verify it first.docker exec atlasmart-cass-1 nodetool versiondocker exec atlasmart-cass-1 nodetool status# Standalone recreation path (skip existing resources as needed).docker network inspect atlasmart-cassandra >/dev/null 2>&1 || docker network create atlasmart-cassandradocker volume create atlasmart-cass-1-datadocker volume create atlasmart-cass-2-datadocker volume create atlasmart-cass-3-datadocker inspect atlasmart-cass-1 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-1 --hostname atlasmart-cass-1 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack1 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -v atlasmart-cass-1-data:/var/lib/cassandra cassandra:5.0.9# Wait until node 1 answers before starting peers.docker exec atlasmart-cass-1 nodetool statusdocker inspect atlasmart-cass-2 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-2 --hostname atlasmart-cass-2 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack2 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -e CASSANDRA_SEEDS=atlasmart-cass-1 -v atlasmart-cass-2-data:/var/lib/cassandra cassandra:5.0.9docker inspect atlasmart-cass-3 >/dev/null 2>&1 || docker run -d --name atlasmart-cass-3 --hostname atlasmart-cass-3 --network atlasmart-cassandra -e CASSANDRA_CLUSTER_NAME=atlasmart-course -e CASSANDRA_DC=dc1 -e CASSANDRA_RACK=rack3 -e CASSANDRA_ENDPOINT_SNITCH=GossipingPropertyFileSnitch -e CASSANDRA_NUM_TOKENS=16 -e CASSANDRA_SEEDS=atlasmart-cass-1 -v atlasmart-cass-3-data:/var/lib/cassandra cassandra:5.0.9# Continue only when all three nodes report UN.docker exec atlasmart-cass-1 nodetool statusdocker exec atlasmart-cass-1 cqlsh -e "CREATE KEYSPACE IF NOT EXISTS atlasmart_keys WITH replication = {'class':'NetworkTopologyStrategy','dc1':3};"docker exec atlasmart-cass-1 cqlsh -e "DESCRIBE KEYSPACE atlasmart_keys"

CONSISTENCY LOCAL_QUORUM;INSERT INTO atlasmart_keys.order_events_by_customer_bucket(customer_id,month_bucket,event_time,event_id,order_id,event_type,payload)VALUES (11111111-1111-1111-1111-111111111111,'2026-09','2026-09-07T10:00:00Z',11111111-1111-11f1-8000-000000000001,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa,'CREATED','{}');INSERT INTO atlasmart_keys.order_events_by_customer_bucket(customer_id,month_bucket,event_time,event_id,order_id,event_type,payload)VALUES (11111111-1111-1111-1111-111111111111,'2026-09','2026-09-07T10:05:00Z',22222222-2222-11f1-8000-000000000002,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa,'PAID','{}');INSERT INTO atlasmart_keys.order_events_by_customer_bucket(customer_id,month_bucket,event_time,event_id,order_id,event_type,payload)VALUES (11111111-1111-1111-1111-111111111111,'2026-09','2026-09-07T10:10:00Z',33333333-3333-11f1-8000-000000000003,aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa,'SHIPPED','{}');SELECT customer_id, month_bucket, token(customer_id,month_bucket) AS partition_token,       event_time, event_typeFROM atlasmart_keys.order_events_by_customer_bucketWHERE customer_id=11111111-1111-1111-1111-111111111111  AND month_bucket='2026-09';

Expected shape: all three rows show the same partition_token because the same two partition-key values identify one partition; rows appear newest first because of the declared clustering order. The exact token is environment/data dependent and should be captured, not copied from a tutorial.

SELECT event_time,event_typeFROM atlasmart_keys.order_events_by_customer_bucketWHERE customer_id=11111111-1111-1111-1111-111111111111  AND month_bucket='2026-09'  AND event_time >= '2026-09-07T10:00:00Z'  AND event_time <  '2026-09-07T10:11:00Z';SELECT event_time,event_typeFROM atlasmart_keys.order_events_by_customer_bucketWHERE customer_id=11111111-1111-1111-1111-111111111111  AND month_bucket='2026-09'ORDER BY event_time ASC, event_id ASC;

4. Broken design: “month is in the primary key, so it must shard by month”

A common mistake is PRIMARY KEY (customer_id, month_bucket, event_time). Here only customer_id is the partition key. Month and time are clustering columns. A heavy customer therefore accumulates every month in the same physical partition, defeating the intended retention bound.

-- This query is intentionally invalid for the correct composite-key table.SELECT * FROM atlasmart_keys.order_events_by_customer_bucketWHERE customer_id=11111111-1111-1111-1111-111111111111  AND event_time >= '2026-09-01T00:00:00Z';

Cassandra should reject the request because a complete composite partition key is not supplied and the request does not describe a single contiguous partition slice. The exact error wording can vary by patch. The repair is not ALLOW FILTERING; the repair is to make the query contract and primary-key grammar agree.

5. Production judgment

Primary-key design is where workload fit becomes physical. A useful partition key has enough cardinality to distribute traffic, enough locality to serve the target read without scatter/gather, and a growth bound that survives retention, repair, compaction and failure recovery. Clustering columns should mirror the order and range predicates you actually use. RF and consistency level determine how many replicas must participate, but they do not rescue a hot single partition: every replica for that partition still sees its traffic. Larger rows, long TTL/delete histories and large partitions amplify SSTable, compaction, tombstone, repair, cache and JVM costs. Application drivers benefit from knowing the complete partition key for token-aware routing; timeout/retry policy must still respect idempotency. Security, SAI and vector features do not change the underlying primary-key placement contract.

Verification checklist

• You can identify the partition-key component by reading parentheses, not by guessing from column names.
• The token is constant for rows sharing (customer_id,month_bucket).
• The default result order matches the declared clustering order.
• The bounded time-slice query works only after the complete partition key is supplied.
• You can explain why adding month_bucket as a clustering column would not bound physical partition growth.

# Destructive only to this disposable chapter keyspace.docker exec atlasmart-cass-1 cqlsh -e "DROP KEYSPACE IF EXISTS atlasmart_keys;"# Keep the shared course containers for the next chapter, or remove them only if you want a full reset:# docker rm -f atlasmart-cass-1 atlasmart-cass-2 atlasmart-cass-3# docker volume rm atlasmart-cass-1-data atlasmart-cass-2-data atlasmart-cass-3-data# docker network rm atlasmart-cassandra

Summary and next bridge

The primary key is simultaneously row identity, partition placement, and within-partition sort order. Next we quantify whether candidate partition keys have enough cardinality and sufficiently bounded growth to remain healthy under real AtlasMart load.

Authoritative references

• CQL data definition — primary-key grammar, partition keys, clustering columns, and clustering order.
• CQL data manipulation — primary-key restrictions, contiguous clustering slices, token queries, and ordering behavior.
• Cassandra data-modeling introduction — partition-key and clustering-key physical meaning.
• CREATE TABLE reference — composite partition keys and CLUSTERING ORDER BY.
• nodetool tablehistograms — table-level percentile evidence including partition-size distribution.
• nodetool tablestats — table statistics and partition-size-related metrics.