Case Study: Key-Value Stores

Concept. A key-value store retrieves a value through a primary key. Most key-value APIs omit joins and general ad hoc queries, but their support for schemas, conditional writes, and transactions varies. Cassandra uses a partition key plus clustering columns. Bigtable instead orders rows by a lexicographically sorted row key and groups columns into families.

Intuition. In a Spotify-like architecture, SQL can store structured listening records while object storage holds large audio files. A key-value index maps each song_id to its object location. For Mickey's 20 most recent plays, Cassandra can group one user's plays into a partition and order them by time.

Match each kind of data to its access pattern

SQL fits structured facts that require filtering, joins, aggregation, constraints, or transactions. A listening record with a user, song, and timestamp belongs naturally in a relational table.

Large media files have a different access pattern. An object store can hold the audio, while a database records its metadata and location.

A Spotify-scale lookup

A Spotify-scale service can retrieve an audio object through a simple mapping:

song_123 → s3://audio-bucket/song_123.mp3

The object store holds the large file. A key-value store or relational table can hold the mapping from song_id to object location. The application supplies a key and receives the corresponding value.

SQL vs. Key-Value: The Trade-offs

Feature	SQL Database	Key-Value Store
Querying	Joins, aggregation, and flexible filters	Direct access through a primary key
Data model	Declared columns, plus options such as JSON	Application-defined values or store-specific item schemas
Integrity	Constraints and multi-row transactions	Varies by system; some support conditional writes and transactions
Best fit	Related data and changing query patterns	Stable, key-driven access patterns

Beyond Simple Keys: Cassandra's Wide Rows

A pure key-value API returns a value through one key. Cassandra extends that model with a primary key designed around the query:

Partition key determines the partition's replica set. Rows with the same partition key stay together on that set of replicas.
Clustering columns determine row order within the partition.

The classic shape is the per-user inbox: one user's items all share a partition key, sorted by time inside the partition.

CREATE TABLE plays_by_user (
    user_id   text,
    played_at timestamp,
    song_id   text,
    PRIMARY KEY ((user_id), played_at)
) WITH CLUSTERING ORDER BY (played_at DESC);

-- "Mickey's 20 most recent plays"
SELECT song_id, played_at FROM plays_by_user
WHERE user_id = 'mickey' LIMIT 20;

Mickey's plays share one partition and appear newest-first within it. Cassandra can therefore read the first 20 rows from one partition without gathering rows from many partitions.

A SQL database can support the same query efficiently with a composite index on (user_id, played_at DESC). Cassandra makes that access path part of the table's primary-key design. SQL keeps it as an index that supports one of many possible queries.

Bigtable reaches a similar result with a composite row key whose prefix groups related rows and whose remaining bytes determine their lexicographic order. Bigtable does not expose Cassandra's partition-key and clustering-column model.

Takeaway

Use object storage for large media and a database to map keys to object locations. Use a key-value store when direct keyed access dominates the workload. Use a wide-column design when the key must also group and order related rows. Keep SQL when the application needs joins, constraints, or several query patterns.