Relational modeling asks “what is the data?” NoSQL modeling asks “what are the queries?” — and you must answer that question before you create the table.
Prerequisites: Relational Modeling, Databases Overview Time to read: ~26 minutes
In a relational database you model the domain, normalize it, and then write whatever queries you need. The join is your escape hatch — a query you didn’t anticipate is still answerable.
In NoSQL there is no escape hatch. No joins, and often no ad-hoc queries at all. So the process reverses:
Relational: entities → normalize → schema → queries (whatever you want, later)
NoSQL: queries FIRST → design tables that serve them exactly
🚨 This is the single most important thing to understand about NoSQL modeling, and it’s where teams fail. They model Cassandra like Postgres, discover six months later that the product needs a new query shape, and find it requires a new table plus a backfill of two billion rows.
The corollary: you must know your access patterns before you start. If you don’t — if the product is exploratory and queries will change — that’s an argument for a relational database, not against your modeling skill.
Before any schema, list every access pattern with its frequency and latency requirement.
For a social app:
| # | Access pattern | Frequency | Latency |
|---|---|---|---|
| 1 | Get a user’s profile by user ID | Very high | < 10 ms |
| 2 | Get a user’s posts, newest first, paginated | Very high | < 50 ms |
| 3 | Get a post by ID with its comments | High | < 50 ms |
| 4 | Get a user’s followers | Medium | < 100 ms |
| 5 | Get who a user follows | Medium | < 100 ms |
| 6 | Get a user’s home feed | Very high | < 100 ms |
| 7 | Count a user’s followers | High | < 10 ms |
🎙️ In an interview, doing this explicitly is worth a lot: “Before I design the tables, let me list the access patterns — in a wide-column store the schema is derived from these, so getting them right first matters more than the schema itself.”
Now each pattern gets a table designed to serve it in one query, from one partition.
Two concepts do all the work:
CREATE TABLE posts_by_user (
user_id UUID,
created_at TIMESTAMP,
post_id UUID,
content TEXT,
like_count COUNTER, -- (counters actually need their own table; simplified here)
PRIMARY KEY ((user_id), created_at, post_id)
-- ↑partition ↑clustering (sort)
) WITH CLUSTERING ORDER BY (created_at DESC);
This serves access pattern #2 perfectly: one partition, already sorted, one disk seek.
SELECT * FROM posts_by_user WHERE user_id = ? LIMIT 20; -- newest 20, instantly
🚨 And it serves nothing else. “Get a post by ID” is impossible against this table — you’d have to scan every partition. So you create another table:
CREATE TABLE posts_by_id (
post_id UUID PRIMARY KEY,
user_id UUID,
created_at TIMESTAMP,
content TEXT
);
Same data, stored twice, because there are two access patterns. That’s not a workaround — that is the model. Storage is cheap; a query that must scan the cluster is not.
🚨 You are now responsible for consistency between those copies. Change a username, and every denormalized copy must be updated. Options:
BATCH gives atomicity across partitions in a limited sense, but
it’s a performance trap if abused (it’s not a general transaction).user.renamed event; consumers update each table. Eventually
consistent, and the pragmatic answer.🎙️ “I’d denormalize the username into the posts table for read performance, and update it via an event when it changes. Usernames change rarely and brief staleness is acceptable — but I’d not denormalize anything where staleness has a real cost.”
This is the hard part, and the source of most Cassandra production problems.
-- ❌ Unbounded: a busy sensor accumulates readings forever in one partition
PRIMARY KEY ((sensor_id), reading_time)
-- ✅ Bucketed: a new partition each month
PRIMARY KEY ((sensor_id, year_month), reading_time)
📐 Target: under ~100 MB and under ~100,000 rows per partition. Beyond that, reads slow down, compaction struggles, and repairs become painful. A partition is read as a unit — an unbounded one eventually becomes unreadable.
🚨 Time bucketing is the standard fix, and it introduces a trade-off: a query spanning six months now touches six partitions. Choose the bucket size from the query pattern — daily buckets for high-volume sensors, monthly for low-volume.
-- ❌ Every write today goes to one partition — one node saturates
PRIMARY KEY ((event_date), event_time)
-- ✅ Spread across N partitions per day
PRIMARY KEY ((event_date, bucket), event_time) -- bucket = hash(event_id) % 10
This is the hot key problem in wide-column form. The fix is a composite partition key that adds entropy.
Their published design is the canonical illustration:
PRIMARY KEY ((channel_id, bucket), message_id)
-- bucket = a fixed time window (~10 days of messages)
Without the bucket, a busy channel’s partition grows without bound. With it, partitions stay bounded, and “load recent messages” reads the current bucket — usually one partition, occasionally two at a boundary.
DynamoDB has partition key (PK) and sort key (SK), and its distinctive technique is putting multiple entity types in one table with overloaded keys.
PK SK Attributes
─────────────────────────────────────────────────────────────
USER#42 PROFILE {name: "Bilal", email: ...}
USER#42 ORDER#2026-07-22#001 {total: 4200, status: "paid"}
USER#42 ORDER#2026-07-20#003 {total: 1800, status: "shipped"}
USER#42 ADDRESS#home {city: "Lahore"}
ORDER#001 ITEM#SKU123 {qty: 2, price: 1500}
ORDER#001 ITEM#SKU456 {qty: 1, price: 1200}
🚨 Why this works, and why it looks bizarre at first:
PK = USER#42 gathers everything about that user into one partition, so
Query(PK = "USER#42") returns the profile, all orders, and addresses in a single request.
That’s a join — performed by co-location instead of computation.Query(PK="USER#42", SK begins_with "ORDER#2026-07") gets July’s orders.Global Secondary Indexes (GSIs) provide alternative access patterns:
GSI1PK = "STATUS#pending" → all pending orders across all users
⚖️ GSIs are effectively separate tables maintained for you: they cost extra write capacity, they’re eventually consistent, and a GSI with low cardinality creates a hot partition. They are not free.
📐 Access-pattern-to-key mapping is the entire design exercise. Write the patterns first, then work out what PK/SK/GSI structure serves each one in a single query.
The central question: embed or reference?
Embed when the child data is:
// ✅ Addresses: few per user, always read with the user
{ _id: "user42", name: "Bilal",
addresses: [ {type: "home", city: "Lahore"}, {type: "work", city: "Karachi"} ] }
Reference when the child data is:
// ✅ Posts referenced, not embedded — unbounded
{ _id: "user42", name: "Bilal" }
{ _id: "post1", author_id: "user42", content: "...", created_at: ... }
🚨 The unbounded array is the classic MongoDB failure, and it’s worth knowing precisely:
// ❌ This will eventually break
{ _id: "post1", content: "...", comments: [ /* 50,000 and growing */ ] }
MongoDB has a 16 MB document limit, so it will hard-fail eventually. Before that, every read of the post fetches all 50,000 comments, and every new comment rewrites the entire document — growing it beyond its allocated space and forcing a move on disk. Performance degrades long before the limit.
The subset pattern is the fix: embed the most recent 10 comments for display, reference the rest.
{ _id: "post1", content: "...", comment_count: 50000,
recent_comments: [ /* last 10 */ ] }
Adjacency list — model a graph in a key-value store: PK = NODE#A, SK = EDGE#B gives you A’s
edges in one query.
Composite sort keys — encode hierarchy so prefix queries work:
SK = "2026#07#22#14" supports year, month, day, and hour range queries from one key.
Sparse indexes (DynamoDB) — only items with the indexed attribute appear in the GSI. Set
gsi1pk only on pending orders, and the index contains just pending orders. Elegant and cheap.
Write sharding — append a suffix to spread a hot key: PK = "COUNTER#5" where 5 is
random(0..9); read all 10 and sum. The general answer to hot partitions.
Materialized aggregates — maintain follower_count as a field rather than counting rows, because
COUNT(*) across a partition isn’t available or isn’t cheap.
Time-to-live (TTL) — both DynamoDB and Cassandra expire rows automatically. Excellent for sessions, caches, and any data with a natural lifespan; it’s free garbage collection.
| Decision | Gain | Cost |
|---|---|---|
| Access-pattern-first modeling | Every query is a single-partition read | New query shapes require new tables and backfills |
| Denormalization | No joins; fast reads | You own consistency between copies |
| One table per pattern | Optimal reads | Write amplification; more storage |
| Single-table design (DynamoDB) | Multi-entity fetch in one request | Genuinely hard to read and reason about |
| Embedding (MongoDB) | One read gets everything | Document size limits; whole-doc rewrites |
| Referencing | Bounded documents | Multiple round trips |
| Time bucketing | Bounded partitions | Multi-partition queries across boundaries |
| GSIs | Alternative access patterns | Extra write cost; eventually consistent |
((channel_id, bucket), message_id) — is the
clearest published example of bucketing to bound partitions. Their engineering posts on hitting
and then fixing hot partitions are excellent reading.1. Model the same app both ways. Take the social app access patterns from the top of this chapter. Write the Postgres schema (3NF) and the Cassandra schema. Count the tables. Then answer: “show me all posts containing the word ‘system’” against each. The Postgres version is a query; the Cassandra version requires a search engine.
2. Cause an unbounded partition. In Cassandra, create a table partitioned by a single ID and insert 500,000 rows into one partition. Then:
nodetool tablehistograms keyspace.table # look at partition size and read latency
Now redo it with a time bucket in the partition key and compare.
3. Try single-table design. In DynamoDB Local, implement the user/order/item example above. Write
Query(PK="USER#42") and get the profile plus all orders in one call. Then try to answer “all
pending orders across all users” — and discover you need a GSI, and that designing it is the actual
work.
4. Hit MongoDB’s document limit. Embed comments in a post document and insert them in a loop. Watch write latency climb as the document grows, then watch it fail at 16 MB. Then implement the subset pattern.
PRIMARY KEY ((sensor_id), reading_time) for a high-frequency sensor?