SQL vs NoSQL: A Decision Framework

What you'll learn: By the end of this lesson you will be able to split "SQL or NoSQL?" into six separate questions (what must change together, how well the queries are known, the shape of the data, how relationships are used, what breaks first under load, and who operates it), answer each with what the PostgreSQL, MongoDB, Cassandra and DynamoDB documentation actually says, write the outcome as a decision record, and name the signal that should reopen the decision.

Imagine two ways to store paperwork. A filing cabinet with one drawer per customer finds "everything for customer 42" in seconds, and is hopeless at "every red folder from last March". A records room with a card index on every column answers both, but someone must maintain the index cards. Neither is wrong. The question is which questions you will ask tomorrow.

Databases Basics introduced the NoSQL families, and ShopStream's data model sorted its data types into stores. This lesson is the dedicated framework behind those choices: the reasons, the exact trade-offs, and how to record and revisit the decision.

Verified against the PostgreSQL 18 documentation, the current MongoDB manual, the Apache Cassandra documentation and the Amazon DynamoDB Developer Guide, all read on 2026-11-08. These products change; check the page for your version.

The core idea

"SQL versus NoSQL" is a bundle of separate decisions, not one. A database gives you a data model, a query model, a transaction scope, a schema policy and a way to scale, and the families differ on each. Decide each axis for your workload and the label takes care of itself. "NoSQL scales, SQL does not" is not an axis, and the labels blur: PostgreSQL stores JSON, and MongoDB has multi-document transactions.

Three labelled illustrations run through the lesson: a minimal example (one question asked of two query models), a realistic example (a ShopStream activity history) and a failure example (a new question asked of a store designed for old ones).

Six questions

1. What must change together?

A transaction groups steps so they all happen or none do. PostgreSQL's tutorial calls it a "single, all-or-nothing operation" that can span any statements, and constraints back it: CHECK, NOT NULL, UNIQUE and foreign keys, which maintain "referential integrity" between tables. The NoSQL stores scope this differently:

If a wrong answer costs money or breaks a uniqueness rule across many records, start relational. Otherwise this axis stays open.

2. How well do you know the queries?

A relational schema can be designed from the data and queried later in ways nobody planned, because joins and secondary indexes are general. Key-oriented stores invert this. The DynamoDB guide says that, unlike RDBMS design, "you shouldn't start designing your schema until you know the questions that it needs to answer". Cassandra's documentation says "data modeling is query-driven" and that joins are not supported. MongoDB's rule is "data that's accessed together should be stored together".

So: stable, known, few queries favour a key-oriented design; queries that will keep changing favour relational. Write the query list down before choosing.

3. What shape is the data, and how stable?

MongoDB documents in one collection "are not required to have the same set of fields or data types", and the manual adds schema validation when you want a fixed structure; by default an invalid insert or update is rejected. PostgreSQL covers part of this ground with jsonb, which supports GIN indexes for containment queries such as @>. Its documentation still recommends that JSON documents keep "a somewhat fixed structure" and warns that "any update acquires a row-level lock on the whole row", so documents should stay a manageable size. Varied, read-whole records point at a document store; fixed columns that are joined and filtered point at tables.

4. How are relationships used?

Relational stores join at query time. Document stores embed what is read together and reference the rest. Cassandra and DynamoDB design deliberately around one access path: Cassandra "de-normalizes data by duplicating data in multiple tables", and DynamoDB's guide describes grouping data by access pattern, for example embedding an order with its items when they are always read together. Duplication makes reads cheap and writes responsible: you keep the copies in step.

5. What breaks first under load, and is it the database?

Name the bottleneck before the store. If reads dominate, replicas and caches come first (replication, caching). If one primary's writes are the limit, the lever is to spread data by a key: Cassandra uses a partition key "to partition data among the nodes", and DynamoDB's Query requires one partition key value, so the model and the scaling lever are the same thing. Relational systems can shard too (sharding), at the cost of cross-shard joins and transactions. Estimate with the shared numbers in estimation, and measure rather than assume.

6. Who operates it, and how hard is a change to undo?

Every added store is another system to back up, monitor and be on call for. Ask what a wrong choice costs to reverse: adding an index to a relational table is routine, while changing a key-oriented store's key design is typically a much bigger job (this is engineering judgement, not a documented figure).

Figure 1. The six questions as a flow.

How to read this: take the questions in order. The first two steer toward relational because they protect correctness and flexibility. The last box applies to every outcome, because the best model can still be the wrong operational choice.

Minimal example: one question, two query models

Ask "a shopper's orders since 1 October, newest first". In SQL (illustrative, not run here):

SELECT order_id, placed_at, total_cents
FROM orders
WHERE shopper_id = 42 AND placed_at >= DATE '2026-10-01'
ORDER BY placed_at DESC;

In DynamoDB, the same question is a Query whose partition key is the shopper and whose sort key is the time (illustrative request shape, following the form in the developer guide):

{
  "TableName": "Orders",
  "KeyConditionExpression": "ShopperId = :s AND PlacedAt >= :from",
  "ExpressionAttributeValues": { ":s": { "S": "42" }, ":from": { "S": "2026-10-01" } },
  "ScanIndexForward": false
}

Both answer it, and the key design makes it direct. Now ask a second question: "open orders for store 7". In SQL you write a WHERE and, if it is slow, add an index. In the key design, the key is ShopperId, so the question has no key to start from. The guide's own example makes the point: a query on a non-key attribute "would need to use a Scan operation", and "as more items are added to the table, scans of all the data would become slow and inefficient". The cure is a global secondary index, which has costs of its own (Figure 4).

Figure 2. The same table, two questions.

How to read this: the table was designed for the first question. The second is not impossible, but it is a different design decision with its own cost.

Trade-offs at a glance

In practice: ShopStream shopper activity history

This is the realistic example: an educational scenario on the running example, ShopStream. Nothing here describes a real company, and the numbers come from the project's shared illustrative assumptions.

This is a realistic scenario, not a case history.

The situation

ShopStream wants a "recently viewed and recently ordered" shelf. Product views and add-to-cart taps are recorded per shopper and read back as "the last 50 events for this shopper". The browsing path peaks at about 2,300 requests per second (shared, illustrative); if each page view wrote one event, that is the write rate, an assumption for this example. Orders, payments and stock stay in PostgreSQL, which also carries checkout.

Starting architecture

Figure 3. Where the new data would live.

How to read this: the new data gets its own store, so its write rate does not compete with checkout on the relational primary.

Options considered

Applying the six questions:

The decision

Analytics questions, such as "views by product", go to the warehouse from a copy of the events, not onto the live table.

Rollout and verification

Shadow-write events for a week, compare the shelf from the new store against a query on the old events, then switch reads. Watch write errors, read latency for the shelf and the lag of the warehouse copy.

The 2 a.m. failure

Marketing asks for "views by product" and an engineer adds a global secondary index on product to the live table. The DynamoDB guide warns that when the index is created, "write operations to the base table can be throttled if the GSI activity ... exceeds the GSI's provisioned write capacity", and that an index's write capacity should be at least the base table's. Writes start failing at peak and shelves go stale.

Figure 4. How an index added at peak becomes a write outage.

How to read this: the index is not free background work. Each table write must also write to the index, so a small index capacity limits the whole table.

First three checks: write error and throttling signals from the service, recent changes to the table (the index creation), and the index's provisioned write capacity as returned by DescribeTable. Mitigation: raise the index's write capacity to at least the table's, or remove the index. Follow-ups: create indexes off-peak, review index capacity in the change checklist, and answer analytics from the warehouse copy.

Architect's notes

• Write the access patterns down first. Every key-oriented design starts from that list, and relational choices are easier to defend with it.
• Default to relational when invariants or changing queries are in play, and say so in the record.
• Check the version before quoting a limit. DynamoDB transaction limits, MongoDB transaction requirements and PostgreSQL jsonb behaviour are documented per version.
• Treat a second store as a cost: it needs backups, alerts and an owner. See Microservices decomposition and data ownership for who owns which data.
• Put a revisit trigger in every decision record.

Common mistakes

• Choosing by label or fashion instead of by the six questions.
• Reading "NoSQL scales" as a reason to leave a database that is not the bottleneck.
• Designing a key-oriented store without a written list of queries.
• Adding indexes to a live table without checking their write cost.
• Assuming a document store cannot do transactions, or that PostgreSQL cannot hold JSON: both are untrue and both change the answer.
• Keeping duplicated copies of data with no process to keep them in step.

Key takeaways

• "SQL vs NoSQL" is six decisions: invariants, queries, shape, relationships, load and operations.
• Invariants across many records and changing queries favour relational.
• Key-oriented stores trade query flexibility for a clear scaling lever; design the key from the queries.
• Each store has a transaction scope: know it before relying on it.
• Every new store and every new index has an operating cost.
• Record the decision and the signal that reopens it.

Practice exercises

Exercise 1 (30 min). Take ShopStream's cart, product catalogue and courier pings and answer the six questions for each. Compare with the data-model lesson.

Exercise 2 (45 min). Write five access patterns for a food-delivery app and decide which can be served by one key design.

Exercise 3 (1 hour). For the activity history, sketch the key design for a second query, "events for a product in the last hour". Say whether an index or a copy is the better fit and why.

Exercise 4 (1 hour). Write a decision record, with a revisit trigger, for storing a clinic's appointments, using the double-booking scenario in Databases Basics as the context.

Related reading

• Databases Basics: the NoSQL families and ACID.
• ShopStream Stage 3: the data model: the same choices in context.
• Storage Engines, Sharding and Replication
• Consistency Models and CAP and PACELC
• Search Indexing: when a search engine is the better second store.

Official documentation

• PostgreSQL 18: Transactions tutorial, Constraints, JSON types
• MongoDB manual: Data modeling, Transactions, Schema validation
• Apache Cassandra: Data modeling and Guarantees
• Amazon DynamoDB Developer Guide: Modeling relational data, Transactions, Global secondary indexes