8.7 How do you choose between SQL and NoSQL?

Choosing

In the office: Money goes in the ledger, because it must balance. Customer stories go in folders, because each is different and read as a whole. Nobody argues about which is "better"; each fits a different job.

In the system: Start from how the data is read and written, not from fashion.

Figure: work out your main reads and writes first, then pick the shape that fits them.

Choose relational (SQL) when:

• You need transactions across many rows or tables (orders, payments, inventory).
• You'll ask many different questions, including ones you haven't thought of yet (admin screens, reports).
• Data is naturally related and must stay correct.
• When in doubt. A relational database is a very safe default for a new product; it scales a long way with indexes, caching, bigger machines and read replicas (Chapter 6).

Choose NoSQL when the access pattern is clear and fits one family:

• Key-value: everything is fetched by one key, at very high speed (sessions, carts).
• Document: self-contained records that vary in shape and are read whole (catalogues, profiles, content).
• Wide-column: huge write rates, read by partition (events, time series, messages).
• Graph: the questions are about connections.

The line has blurred. PostgreSQL stores and indexes JSON documents; many NoSQL databases now offer transactions of some kind. And most real systems use more than one: the bookstore keeps orders in PostgreSQL, carts and sessions in Redis, and clickstream events in a wide-column store. That's called polyglot persistence.

Memory code: "Money in the ledger, stories in folders; when in doubt, ledger."

---