Partition key picks the machines. Clustering key sorts the rows.
Rows of a partition sit together: the latest 50 messages are one sequential read. DynamoDB says partition key and sort key.
CQL looks like SQL. Add a WHERE?
Bad: Filter any column
No partition key: an index or ALLOW FILTERING, which scans.
Good: One table per query
Write each fact to every table or GSI that reads it.
DynamoDB shares Dynamo’s name, not its design.
Cassandra: any node coordinates, you pick the level, newest timestamp wins. DynamoDB: one Paxos leader per partition, so a strong read is one flag.
1,000/s
one-kilobyte writes, at most, per DynamoDB partition
30,000 writes a second needs 30 partitions’ worth, which DynamoDB creates if keys spread. One key at 1,500 a second throttles however big the table is.
Out of room? DynamoDB splits partitions; you add Cassandra nodes.
A DynamoDB partition splits past 10 GB or when hot, usually within minutes. A new Cassandra node takes token ranges and streams their data.
Messages keyed by channel_id alone. A busy channel writes 20 a second.
The catch
864 MB a day, 315 GB a year, on three nodes. Bucket by time: an hour for busy channels.
When to pick them
- Pick: heavy writes, every read names a key
- AWS, zero ops: DynamoDB. Many regions: Cassandra
- Not: joins, ad-hoc filters, multi-row invariants
- Not: unknown queries, search, caches, queues
Say it like this
Heavy writes and known queries: Cassandra, or DynamoDB on AWS. Key by (channel_id, bucket), partitions under ≈ 100 MB, LOCAL_QUORUM per region. Joins or changing queries: PostgreSQL.