Hide outline
Feedback
Guide
Was this lesson helpful?
Dive Deeper
Generate a follow-up sub-lesson on any aspect of this topic
Guide complete
Choosing Between SQL and NoSQL Datastores
Generate a follow-up sub-lesson on any aspect of this topic
Choosing Between SQL and NoSQL Datastores
TLDR;
- A single relational database can hit 2k orders per second, but lock contention and multi-entity reads surface first.
- The query shapes you cannot change decide whether joins and multi-row transactions are mandatory.
- Hidden scaling costs like secondary index fanout and cross-partition transactions often flip the “obvious” choice.
OrderStream is an online checkout system that starts simple with one database and then gets forced into a datastore choice when load turns correctness problems into latency problems. At 2k orders per second with a p99 latency target of 200 ms, a single PostgreSQL instance can still look fine on paper, but the first real pain usually shows up as write contention on hot rows and slower cross-entity reads when the same request needs orders, payments, and inventory together.
The baseline design is a checkout service writing to one Postgres schema with orders, payments, and inventory tables, then reading back enough state to confirm the purchase and show order history. That one-box design is valuable because it makes the tradeoffs visible. The moment we need more throughput, we are no longer only picking a database. We are picking which guarantees stay inside the datastore and which guarantees move into application code.
The diagram puts numbers on the baseline. The web app drives checkout, checkout hits one PostgreSQL primary, and every order write and validation read shares that same write-ahead log and lock manager. At 2k orders per second, the system fails in a very specific way. The p99 stretches when many transactions touch the same inventory SKUs or when an order confirmation must join across multiple tables under load.
The SQL versus NoSQL decision is mostly about what the datastore enforces for us when concurrent requests hit shared data. Schema rigidity means the database rejects writes that do not match declared tables and types, which keeps data shape stable as the system grows. ACID means a transaction is atomic, consistent against declared rules, isolated from other transactions, and durable once committed, which is the package of guarantees most checkout flows assume.
Joins let one query combine multiple tables by keys, which is convenient when the read model is not precomputed. Horizontal scale means adding more machines increases capacity, but it also forces the system to decide which machine owns which rows. A consistency model describes what reads are allowed to observe after a write, especially during replication lag or a network partition. Operational complexity is the ongoing cost of keeping the datastore healthy, which includes backups, schema changes, rebalancing data, and incident response when a node fails.
Design rule A datastore choice is really a choice of which invariants are enforced by one transaction boundary.
A relational database earns its keep when OrderStream has to keep multiple facts true at the same time. Foreign keys and unique constraints stop the system from creating impossible states, like an order_item referencing a missing order. A single transaction can insert the order, reserve inventory, and record a payment authorization so the checkout either commits as one unit or rolls back entirely.
Those building blocks map directly to the workload. Foreign keys and constraints protect integrity even when the application has a bug. Transactions protect correctness when two checkouts race for the last units of a SKU. Joins support order history and customer service views without duplicating data, but they concentrate work on the primary and can push p99 latency up when joins touch many rows or compete with write locks.
NoSQL is not one thing. It is a set of families that usually optimize for partitioned throughput and simpler single-key access at the cost of some combination of joins, rich transactions, or strict consistency. A partition key is the field used to decide which node stores a record, and it is the core lever that makes horizontal scale practical.
The scan highlights how each family behaves. Key value stores excel at GET and PUT by primary key with very low latency, but they tend to have limited secondary indexes and no joins. Document stores keep related fields together, which helps read an order or product in one fetch, but cross-document transactions are narrower than in a relational database. Wide-column stores are built for massive write throughput and time-series style access by partition and clustering keys, but query flexibility is constrained. Graph stores optimize traversals over relationships, which is rarely the hot path for checkout.
OrderStream checkout is an OLTP workload where the primary requirement is fast, correct writes under concurrency. When a request must answer from multiple entities, the system either pays for joins at read time or pays to precompute and denormalize data at write time. A denormalized view is a copy of data shaped for a specific query, updated alongside the source to avoid joins during reads.
The checkpoint makes the “one query” requirement concrete. Queries like “show the last 20 orders with payment status and shipment state” naturally want joins or a precomputed read model. Queries like “fetch order by order_id” fit a single-partition read in a document store if the full order is embedded. The important change is not the database brand. It is whether the team is willing to shift work into pipelines that maintain denormalized views and accept the failure modes that come with them.
Consistency decisions show up as customer-visible correctness issues in checkout. Strong consistency means a read reflects the latest committed write in the required scope. Eventual consistency means replicas converge over time, so a read may temporarily return older data. The dangerous cases are not abstract. They are overselling inventory, double-capturing a payment, or showing an order as confirmed when payment later fails.
The tuner illustrates what changes during a region partition. If OrderStream chooses availability and lets each side accept writes, the system can keep checkout success rates high but risks conflicting inventory decrements. If it chooses strong consistency across regions, the system blocks or fails checkouts when quorum cannot be reached, protecting stock counts and payment state. A common compromise is to keep the core ledger strongly consistent in one region and allow eventually consistent features like recommendations or “recently viewed” to degrade gracefully.
Caveat Eventual consistency is easiest when updates are commutative, like counters or append-only events.
SQL scaling usually starts with vertical headroom and read replicas, then runs into limits when writes dominate. A read replica helps when queries are read-only and can tolerate replication lag. For checkout, most pressure comes from writes and write-adjacent reads, so the primary still bottlenecks on WAL throughput, locks, and index maintenance.
The estimate turns traffic into storage and shard pressure. If each order averages items and the order record is bytes, daily write volume is roughly , then multiplied by the replication factor. Sharding reduces per-node write load, but it introduces hot partitions when many checkouts touch the same partition key, and it makes secondary indexes more expensive because each shard maintains its own index and queries often fan out. Rebalancing shards later is also a real operational event, not a background detail.
The right comparison keeps criteria consistent and ties them to the workload rather than to slogans. SQL is usually favored when OrderStream requires cross-entity invariants and ad-hoc joins, and when the team wants correctness failures to be blocked rather than silently accepted. NoSQL is usually favored when the hottest queries can be expressed as single-partition reads and writes and when the system prefers predictable scaling to complex transactional behavior.
The table makes the constraints explicit. Data shape and query complexity decide whether the read path depends on joins. Transaction scope decides whether checkout can be one atomic commit or becomes a saga with compensating actions. Consistency requirements decide whether replicas can lag. Scaling path and ops cost decide whether the team pays earlier for partitioning and denormalized views or later for sharding and tuning a relational primary.
A defensible design for OrderStream often uses more than one datastore because each subsystem has a different “must not be wrong” boundary. The key is to name the constraint that justifies each datastore, then keep the boundaries sharp so failures do not cascade into the checkout transaction.
The decision matrix should land on a split that matches the workload. A SQL core ledger fits orders, payments, and inventory reservations because it needs multi-row transactions and constraints. A document store fits the product catalog because schema flexibility and whole-document reads matter more than joins. A wide-column store fits append-only events like clickstream or order lifecycle events for analytics and monitoring. A key value store fits sessions and caching because primary-key latency dominates and losing a cache entry is recoverable.
Practical test If you cannot explain the partition key for a NoSQL table, you have not chosen a design.
The durable way to choose for OrderStream is to start from invariants and query shapes, then only scale out when the bottleneck is measurable. Keep the checkout invariant small and enforce it where it is cheapest to reason about, usually inside a single transactional datastore. Then add specialized stores for catalog, search, analytics, and caching where staleness and denormalization are acceptable.
The reveal points at redesign triggers that are easy to underestimate. Secondary index fanout can make a “simple” NoSQL table act like many writes per request. Cross-partition transactions can quietly reintroduce coordination costs that were the reason for leaving SQL. Dual-write migrations can corrupt data when one write succeeds and the other fails, especially during retries. If OrderStream needs true multi-region active-active checkout, grows by 10×, or suddenly requires new ad-hoc queries for fraud and support, the correct response is to revisit the invariants and boundaries, not to argue SQL versus NoSQL in the abstract.