Choosing keys for
partitioned data

The key starts with the question

Suppose Birch has devices B1 and B2. At 10:00 they report 21 and 22 degrees. The customer-keyed layout puts both readings under Birch. A query for Birch since 10:00 returns those two records without opening Alder's or Cedar's history.

Now use device as the partition key. B1 and B2 have separate addresses. The device engineer can open B1's history directly, but the support request must first know which devices belong to Birch, query each, and combine the results. If that membership list isn't available, finding Birch's records requires searching the data more broadly.

Sorting cannot repair a missing address. Time order helps once we've found a group; it doesn't tell us which device groups contain Birch. Conversely, a good partition key doesn't make every filter cheap. If Birch's records are ordered by temperature, a time filter can require checking records throughout the group. The database may return only two rows after reading many more.

DynamoDB makes this distinction explicit. A Query supplies one partition-key value, with an optional sort-key condition. A filter expression runs after that read; it doesn't reduce the items read. Searching without the partition key uses a different operation, Scan. Its pagination and capacity consumption still matter when very few items survive the filter. Key conditions and query and scan guidance describe the boundary.

Cassandra expresses the same modelling idea with a partition key and clustering columns, which order rows inside the partition. A table might use ((customer, day), time, device) as its primary key. Customer and day together identify a group; time and device distinguish its readings. Device breaks ties when two sensors report at the same time. Without enough key fields to identify a unique reading, an insert could replace a previous value. Cassandra's modelling guide follows queries into these choices.

Keeping a second route into the history

Both screens are useful. We could store a customer-keyed history and another arranged by device. The 10:00 reading from B1 then appears in two places. Each layout answers its own question without making the reader discover the other key first.

That second route costs work on writes. An arriving reading has to update both representations. A corrected customer identifier must leave the old customer group and enter the new one. If the application maintains these copies and crashes after updating only one, the screens can disagree. Retrying safely needs a stable event identifier, and recovery needs a way to detect and repair missing copies.

In DynamoDB, a global secondary index can provide the alternate partition and sort keys. The service maintains it from the base table, with a chosen projection of attributes. That saves application bookkeeping but adds storage and write work. Global secondary index reads are eventually consistent, so a successful table write need not appear there immediately. A local secondary index retains the base partition key; it supplies another order within that key, rather than the device route we've been looking for. Secondary index documentation explains both forms.

For a DynamoDB base-table read, an application can request strong consistency. The default is eventual consistency. Cassandra and ScyllaDB instead let a request choose a consistency level specifying how many replicas must respond. These are different contracts, even when the key layout looks similar. In neither case does the word “partitioned” tell us what a reader sees after a write. DynamoDB read consistency and ScyllaDB replica acknowledgements make the product boundaries concrete.

A large customer can keep one key busy

Grouping by customer looked convenient when every customer had two sensors. Now Birch has thousands. Their incoming readings all carry the same customer key. Other customers may be quiet while this group is busy. Adding total capacity doesn't ensure that the busiest key can use it.

A day bucket bounds the history in each group: Birch plus 2 October is separate from Birch plus 1 October. This helps keep groups from growing forever and makes expiry easier to organise. It doesn't spread writes that all arrive on 2 October. The current day can still be hot.

We can add a suffix: Birch, today, bucket 0; Birch, today, bucket 1; and so on. The writer chooses a suffix for each event. That produces several addresses which the storage system can distribute. To read all of today's history, support queries every suffix and merges the results by time. Choosing more addresses has made a write problem smaller by making the read procedure larger. DynamoDB documents random and calculated suffixes; the same tradeoff arises in application-defined bucket keys elsewhere.

Splitting a large customer by device can spread its many sensors across groups. But that still leaves each individual device with one current-day address. The next model follows that remaining case: B1 becomes noisy, so we add suffixes within its device-and-day group. The read fanout is for one device on that day.

Hashing spreads distinct keys, not the traffic inside an individual key. And logical buckets aren't promises of separate machines: several may share physical capacity. Bucket design, actual placement, and the hottest customer's behaviour all need checking. A sensor that suddenly sends thousands of readings per second can expose a problem that an average writes-per-second figure hides.

Following a reading into Cassandra

The partition key explains where a reading belongs. It hasn't explained how a replica stores it. Cassandra's ordinary write path records a mutation in a commit log and an in-memory memtable. The log supports recovery; the memtable keeps recent data available to reads. When the memtable is flushed, it becomes an immutable sorted file called an SSTable.

A later correction doesn't edit an old SSTable in place. It adds another version through the write path. A read can consult memory and several files, reconciling applicable versions. Background compaction combines files and removes obsolete data where safe. This trades repeated file rewriting for keeping foreground writes simple. Deletes need tombstones so an older copy doesn't reappear; safe cleanup also depends on replicas and repair. Cassandra's storage engine documents this machinery.

ScyllaDB belongs to the same broad CQL, partition-and-clustering-key family, with its own implementation and operational choices. DynamoDB offers similar application-level keys as a managed service; those keys don't establish that its internal write path matches Cassandra's. Buffered writes and compaction are storage-engine choices, not consequences of having a partition key.

Ordinary replicated Cassandra writes also aren't Raft consensus transactions. Requiring a quorum means waiting for enough replica responses. Lightweight transactions use a separate consensus mechanism for conditional operations. DynamoDB has its own transactional APIs with documented scope and restrictions. An invariant involving several records needs that explicit contract, rather than an assumption that nearby keys make the changes atomic. See CQL conditional writes and DynamoDB transactions.

What happens when the query changes?

This design suits a stream of readings with known history queries. The writer can calculate an address without looking through existing data, and the reader can ask for a bounded interval in a known group. It is less comfortable for an analyst who invents new filters and joins every afternoon. Each useful new route can demand another index, copy, or expensive scan. A column store addresses a different part of that reporting problem.

Consider one last request: find every device across every customer that was above 25 degrees yesterday. Customer-and-day keys bound the scan to yesterday, but still require visiting all customers. A device index doesn't fix it either: we don't yet know the devices. A separate daily alert stream, maintained as readings arrive, could answer it cheaply, but now corrections and threshold changes need defined behaviour. The right layout follows the actual question, including how often it changes and how fresh its answer must be.