SQL vs. NoSQL & Data Model Selection

SQL and NoSQL are broad families, not opposing performance modes. Start with the invariants that must hold, the reads and writes the product performs, the query shapes that may evolve, the required availability, and the operational skills of the team. Only then choose a database family and a specific product.

1. 01Write down the invariants, such as an order total matching its line items or inventory never dropping below zero.
2. 02List the highest-volume and latency-sensitive queries, including their partition or tenant boundaries.
3. 03Estimate data size, write rate, read rate, hot keys, retention, and growth instead of choosing from current traffic alone.
4. 04Decide which operations require one atomic transaction and which can tolerate asynchronous reconciliation.
5. 05Evaluate operational requirements: backup, restore, schema evolution, observability, regional placement, and team experience.

For checkout, useful invariants include “every order line references a valid product,” “a payment attempt is recorded once per idempotency key,” and “confirmed inventory never becomes negative.” A relational database can enforce some of these with keys, constraints, and transactions. A partitioned key-value design may instead keep one invariant inside a single item or partition and coordinate cross-partition work with a workflow.

1
2
3
4
5
6

Invariant: confirmed_stock >= 0
Command: reserve(product_id, quantity, idempotency_key)
Atomic boundary: one product partition
Read path: lookup reservation by idempotency_key
Failure rule: timeout means UNKNOWN; retry with the same key
Reconciliation: compare reservation events with payment outcomes

1. 01Measure the slow query and inspect its plan; remove accidental full scans and N+1 access before changing database families.
2. 02Add an index whose leading columns match the filter and ordering, while accounting for extra write and storage cost.
3. 03Bound connection concurrency with a pool and reject or queue overload before the database thrashes.
4. 04Cache stable hot reads when staleness is acceptable, and move non-interactive reporting away from the transactional primary.
5. 05Use read replicas only for reads that tolerate replication lag; never silently route read-after-write invariants to a stale replica.
6. 06Partition when size, maintenance, or write contention has a measured boundary and the partition key matches dominant access paths.

Modern products blur family labels: relational systems can shard and replicate; document stores can support multi-record transactions; key-value systems can offer conditional writes; distributed SQL systems can provide a relational model across nodes. Read the exact guarantee for the exact operation and topology you will deploy.

A large analytical scan does not automatically lock an entire MVCC table. The more common production failure is resource interference: scans consume CPU, I/O bandwidth, buffer-cache space, memory, and connections needed by short transactions. A separate analytical store isolates that workload, but the data pipeline becomes another distributed system.

1. 01Commit the business transaction and its outbox record atomically in the system of record.
2. 02Publish the change with a stable event ID and source position so consumers can deduplicate and resume.
3. 03Apply idempotent transformations to the analytical model; preserve the source timestamp and ingestion timestamp.
4. 04Measure end-to-end freshness lag, rejected records, schema-version failures, and source-to-sink count differences.
5. 05Retain enough source history or snapshots to rebuild the analytical view after corruption or a transformation bug.

A common production design keeps orders and payments in a relational system, publishes committed changes through an outbox or change-data-capture stream, and builds a separate search or analytical view. This is useful when the second access pattern cannot be served safely and economically by the primary store. It also creates lag, replay, monitoring, backup, and incident-response obligations.