Cruxly › System design › Databases: Choosing One

NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB

list the queries first, give each one a key, and know the limit and the cost of every copy

These stores do not let you model the data and ask questions later. You list the access patterns, turn each into a key or an index, and accept that data is stored more than once. The craft is in the keys: which partition a request lands on, how big that partition can grow, how hot it can get, and what a new query costs when it arrives. This lesson models the same workload in DynamoDB, Cassandra and MongoDB, with the limits and failure modes of each.

Model the queries first

Relational modelling starts from entities and normal forms, then answers whatever query comes along. DynamoDB, Cassandra and MongoDB at scale reverse that. The unit of distribution is a key, so a query is fast only if it names that key. Everything else is a scan, a scatter-gather across nodes, or a rewrite.

The method is the same in all three:

1. List the access patterns with rates. "Latest 50 messages in a channel, 20,000 per second" is a pattern. "Show messages" is not.
2. Give each pattern a key. Aim for one partition (DynamoDB, Cassandra) or one shard plus an index (MongoDB), read in the stored order.
3. Duplicate data to match. A second query shape usually means a second copy: an index, a table, or a differently shaped document.
4. Size and heat-check each key. How large can one key value grow, and how many requests can one value take?
5. Write down the pattern you do not support. Ad hoc filters and joins are the cost of this design, so say so.

:::key Say it out loud "Which five queries matter, and at what rate?" is the first question. If the interviewer cannot answer, state assumptions. Every key you design afterwards is justified by those numbers. :::

I do not re-argue here whether to use one of these stores at all. That is the decision framework. This lesson assumes you have chosen, and now have to model it without hurting yourself.

DynamoDB keys, query versus scan, and secondary indexes

A DynamoDB table has a partition key (hashed to pick the partition) and an optional sort key (orders items within it). Limits from the Developer Guide: the partition key value is 1 to 2,048 bytes, the sort key value 1 to 1,024 bytes, and an item is at most 400 KB including attribute names. Names count, so a long attribute name on a billion items is real money.

Query requires equality on the partition key, optionally a condition on the sort key, and reads one partition. Scan reads the whole table and bills for everything it reads, even if a filter then throws most away. Both return at most 1 MB per call, so you paginate with LastEvaluatedKey.

Capacity units are the unit of cost:

Worked example (assumption: 3 KB items, 400 eventually consistent reads per second): each read fits one 4 KB unit, eventual reads cost half, so you need 200 read units. A 20 KB item costs 5 units per strong read.

Secondary indexes answer a second access pattern:

A table can have up to 5 LSIs, and the default quota is 20 GSIs. A write that changes an indexed key costs two index writes (delete the old entry, add the new one). GetItem does not work on an index; you query it. GSIs also support multi-attribute keys: a partition key of up to four attributes and a sort key of up to four attributes, which removes some hand-built concatenated keys.

:::warn Choose the LSI with care On a table with an LSI, the 10 GB item-collection cap means one very large customer can stop accepting writes (DynamoDB may return ItemCollectionSizeLimitExceededException). If a key value could grow unbounded, prefer a GSI, which has no such cap. :::

Single-table design: a worked example

Single-table design puts several entity types in one table so that related items share a partition key and come back in one request. It trades readability and flexibility for fewer round trips. It is a pattern, not a rule: DynamoDB does not require it, and a team that cannot yet list its access patterns should not start with it.

Entities: customer, order. Access patterns:

The items:

PK     SK                        attributes
C#7    PROFILE                   name, email
C#7    ORDER#2026-10-12#1001     total 40, status shipped, lines [...]
C#7    ORDER#2026-10-14#1002     total 90, status open,    lines [...]

Every item with the partition key C#7 is one item collection: stored together, sorted by the sort key. Date in the sort key gives pattern 2 its ordering. Order lines live inside the order item as a bounded list, because the 400 KB item limit is far away and they are always read with the order.

Pattern 4 has no customer id to give, so the base key cannot help. GSI1 re-keys the orders by order id. Each order write now also writes its index entry. That is the cost of the extra pattern.

Rules that keep this healthy:

• Name keys generically (PK, SK, GSI1PK) so entity types can share them.
• Keep item collections bounded, or the partition (and an LSI's 10 GB cap) becomes the problem.
• Do not index by a low-cardinality value like status = open alone. All open orders then share one key, which is a hot partition (next section). Combine it with a date or a shard suffix.
• Document the patterns table in the repo. Without it the key names mean nothing to the next engineer.

Hot partitions, limits, transactions and capacity modes

The per-partition ceiling. The Developer Guide says every partition delivers at most 3,000 read units and 1,000 write units per second. Adaptive capacity raises a hot partition toward that maximum and can isolate a busy item on its own partition, but it cannot go past it; a single item can never be split, and the docs say a collection will not be split by a sort key that increases monotonically. Interview example: 5,000 updates per second to one item is impossible, however much table capacity you provision.

Spreading a hot key is write sharding: append a suffix.

For high-volume time series the docs give a separate, more efficient pattern; check it before inventing one.

Other limits (Developer Guide, 2026): Query and Scan return 1 MB per call; BatchGetItem takes 100 items or 16 MB, BatchWriteItem 25 requests or 16 MB. The default per-table quota is 40,000 read and 40,000 write units, adjustable.

Transactions (TransactWriteItems, TransactGetItems):

• Up to 100 distinct items and 4 MB in one transaction, in one Region.
• Each item is billed twice, for prepare and commit, and the capacity is consumed even if the transaction is cancelled.
• Transactions cannot be performed using indexes. A client token makes a retry idempotent for 10 minutes after the request finishes.
• Transactions are serializable against single-item reads and writes and against other transactions. Query, Scan and BatchGetItem (as a unit) are only read-committed, so they can see some of a transaction's items in the new state and others in the old; use TransactGetItems for a consistent read.
• Global tables do not give atomicity across Regions.

Treat that as a tool for small invariants (a counter and its record), not for batch jobs.

On-demand versus provisioned:

Compare the current prices on the AWS pricing page rather than trusting a number from a blog post. Prices change.

:::warn Hot key before hot table Table capacity is not the limit that bites first. Check the heaviest single key value in every access pattern. :::

Cassandra: model for one partition, then bound it

The Cassandra docs say it plainly: you do not start with the data model, you start with the query model. The partition key decides which nodes store a row. Clustering columns decide the stored order inside the partition, and that order is fixed when you create the table. There are no joins, so you denormalize by duplicating data across tables for each query, and the docs call denormalization "perfectly normal". Replication factor (RF) sets how many nodes hold each partition.

Query: "latest 50 messages in a channel."

CREATE TABLE messages_by_channel (
  channel_id bigint,
  bucket     int,          -- fixed time window derived from the id
  message_id bigint,       -- time-sortable id (snowflake)
  author_id  bigint,
  body       text,
  PRIMARY KEY ((channel_id, bucket), message_id)
) WITH CLUSTERING ORDER BY (message_id DESC);

SELECT * FROM messages_by_channel
WHERE channel_id = ? AND bucket = ? LIMIT 50;

This is the shape Discord described in its public engineering post: messages partitioned by channel plus a bucket (a static time window), with chronologically sortable ids. Their numbers show the cost of running it: 12 Cassandra nodes in 2017, 177 nodes with trillions of messages at the start of 2022, then 72 ScyllaDB nodes at 9 TB each, with historical read p99 falling from 40 to 125 ms to about 15 ms and insert p99 from 5 to 70 ms to about 5 ms. They also hit hot partitions: one busy channel and bucket slowed its nodes, and with quorum reads and writes the slowdown spread to other queries. Part of the fix was data services that coalesce identical concurrent requests, routed by channel id; Discord said hot partitions still occurred, less often.

Why the bucket exists. Without it, a channel is one partition for all time. Size it from your busiest key. Assumption: a hot channel writes 100 messages per second at 200 bytes each, which is 20 KB per second or about 1.7 GB per day. A daily bucket would be 1.7 GB in one partition; an hourly bucket is about 72 MB but 360,000 rows; a ten-minute bucket is about 12 MB and 60,000 rows.

The Cassandra data-modeling docs give a general guideline, with the caveat that requirements vary by application: keep the number of values per partition below 100,000 and the disk space per partition below 100 MB. By that guideline the hourly bucket above is too big by row count, so size the bucket on both measures. The partition_size_warn_threshold and partition_size_fail_threshold guardrails exist in cassandra.yaml but default to null (disabled), so turn them on if you want enforcement.

Reading older history means walking buckets from newest to oldest until you have enough rows. That loop belongs in a data access layer, not scattered through the app.

Use ScyllaDB or Cassandra for very high write rates, time-ordered data per key and multi-datacenter writes, where every query is known. Do not use them for ad hoc filters or multi-row ACID.

Cassandra: tombstones, quorum arithmetic and lightweight transactions

Tombstones. SSTables are immutable, so a delete writes a marker, a tombstone. The grace period exists so that a replica that was down during the delete can recover and receive the tombstone; otherwise its old copy could come back as a "zombie" (Cassandra compaction docs). A tombstone can be dropped by compaction only after the grace period, and not before a compaction actually runs. Current defaults: gc_grace_seconds is 864,000 seconds (10 days), set per table; in cassandra.yaml, tombstone_warn_threshold is 1,000 and tombstone_failure_threshold is 100,000, counted per read or scan. A read has to step over every tombstone in its range to find live rows, so a table where you insert, process and delete (a queue) gets slower exactly as it is used. Discord's migration stalled on token ranges with huge uncompacted runs of tombstones until they compacted that range.

Rules: model deletes as rare, expire data with a bucketed table you can drop whole rather than deleting row by row, and avoid using a table as a queue.

Tunable consistency. The coordinator asks replicas and waits for a number of acknowledgements. Levels:

The docs state the rule in Dynamo's terms: if write replicas W plus read replicas R exceed RF, a read overlaps the latest acknowledged write. With RF = 3 and quorum both ways, 2 + 2 > 3. Worked failure: one replica is down and misses a write acknowledged by two. A quorum read asks two replicas, and any two of three must include at least one that took the write, so the read can return it.

That is still not linearizable. Concurrent writers, retries and partial failures can interleave; the docs present lightweight transactions (IF NOT EXISTS, IF conditions) as the way to get linearizable consistency, and they use Paxos, which the CQL docs say has a non-negligible performance cost.

LWT cost. Extra round trips per operation (with the default paxos_variant: v1 the cassandra.yaml docs expect 4 round trips for a write and 3 for a read; v2 is recommended) and contention on a hot key. Use one to claim a unique name, not on a hot write path. Prefer LOCAL_QUORUM across datacenters so a request does not wait on another region, accepting that reads in a remote datacenter are not covered by the guarantee.

:::warn Quorum is not a lock "We use QUORUM, so we are consistent" is an incomplete answer. State what anomalies remain and what you use LWT for. :::

MongoDB: embed, reference, transactions and the shard key

MongoDB's modelling rule is that data accessed together should be stored together. The data modelling docs describe two ways to link data:

Worked example. Orders with line items: embed the lines, which are bounded and always read with the order. Reference the product catalog by id, since products are shared and change alone. Reviews grow without limit, so each review is its own document in a reviews collection, with the product id. The five newest reviews for a product page are a query on that collection, not an array in the product.

The 16 MiB limit. A BSON document is capped at 16 MiB (Limits and Thresholds). An array that grows with usage will reach it eventually, long before it is pleasant to read.

Transactions. The docs state that an operation on a single document is atomic, and that embedded documents and arrays mean multi-document transactions are not necessary for many cases. They also say distributed transactions cost more than single-document writes and are no replacement for schema design. Multi-document transactions exist (replica sets from 4.0, sharded clusters from 4.2), with defaults to know: runtime under one minute (transactionLifetimeLimitSeconds), and a lock wait of 5 ms before the transaction aborts. Model so common operations touch one document; use a transaction for the rare multi-document invariant and retry on failure.

The shard key. Choose it for:

• High cardinality, or you cap the number of chunks. A key with 7 values lets 7 chunks exist.
• Low frequency per value, or one value's chunks become a bottleneck or a jumbo chunk.
• No monotonic growth. An ever-increasing key sends all inserts to the chunk with maxKey, one shard.

Queries without the shard key go to every shard and do not scale linearly with shard count. A common multi-tenant choice is a compound key of tenant id plus a high-cardinality field.

Changing it later. You can refine the key by adding suffix fields (refineCollectionShardKey, since 4.4), and from MongoDB 5.0 you can reshard to a new key (reshardCollection). The resharding docs ask for free storage on each recipient shard of at least twice (collection size plus index size) divided by the shard count, say your application must tolerate about two seconds when the collection blocks writes, and say a resharding operation always takes at least 5 minutes. From 7.0, analyzeShardKey measures a candidate key on sampled queries.

The same access pattern modelled three ways, and the move from relational

Pattern: latest 20 orders of one customer.

New pattern: open orders for today.

Moving from relational. Four habits to unlearn:

1. Normalize less, duplicate on purpose. A copy keyed for the query is the design, not a smell. You own keeping copies consistent.
2. No ad hoc queries on the hot path. Reporting goes to a warehouse or a search index fed from the store, never a scan of production.
3. Joins become modelling. Either the data lives together (item collection, embed) or the application joins in a second call.
4. Transactions are an exception. Design so common writes touch one item, one partition or one document.

Migration practice: dual-write behind a feature flag, backfill, compare reads, then cut over. Keep the relational source until the new reads match.

If the product team cannot list its queries, or the data is highly relational with many cross-entity transactions, say that a relational database with replicas and a cache is probably the better fit. That is the decision framework lesson, and the honest answer is sometimes "do not use these stores".

Interview: common wrong statements and follow-ups

Follow-up questions to prepare:

• 5,000 updates per second to one item? One partition caps at 1,000 write units (1 KB items), and one item cannot be split. Write-shard with a suffix and scatter-gather on read.
• Why can an LSI table not hold a 50 GB customer? An LSI caps each partition key value, with its index entries, at 10 GB.
• Latest 50 messages per chat in Cassandra? Partition (chat_id, day), cluster by time descending; choose the bucket from the busiest chat.
• What breaks if the shard key is a timestamp? All inserts go to the last chunk. Hash it, or lead with a high-cardinality field.
• When would you leave the store? When the query list stops being known, or when you need multi-entity transactions more than scale.

Close each answer with the limit you checked, the cost you accepted, and the trigger that would make you move.

How to say it in the interview

"I'd start from the queries, not the entities. For each query I pick a key so it reads one partition, in order. Then I check three things: how big that partition can grow, whether one key can get too hot, and what a new query will cost me. In DynamoDB that's a GSI, in Cassandra it's another table, in MongoDB it's an index or a different embed. If those costs are unacceptable, I'd say so and stay relational."

Checklist

• I can list the access patterns with rates before choosing any key
• I can explain query versus scan and what a GSI and an LSI each cost
• I can design a single-table layout with entities, patterns and keys
• I can name the per-partition ceiling and two ways to spread a hot key
• I can model a Cassandra table with a bucket, and explain tombstones and quorum
• I can choose embed or reference and a shard key, and say what each costs
• I can state the cost of a new query in each store and say when to stay relational

Related lessons

• How to choose a database
• ACID, precisely
• Isolation levels, in depth
• Data models: what each family is built for, and where it fails
• Storage engines: B-trees, LSM-trees, row vs column
• Distributed SQL: Spanner, CockroachDB, Aurora and friends