SQL or NoSQL

The problem: one shop, five kinds of data

"SQL or NoSQL?" comes up in almost every system design interview, usually at the Data step. A weak answer names a brand: "MongoDB, because it scales." A strong one starts from the questions the data must answer, because different data asks different questions.

Take a busy online shop. It keeps:

• Orders and payments: an order must lower the stock at the same moment, and a payment moves money between two wallets.
• The product catalog: a shirt has sizes and colors, a laptop has memory and a screen size.
• Shopping carts: read on every page, always by the shopper's id.
• Page views: say 1 billion a day. Taking a day as about 100,000 seconds for a quick estimate, that's about 10,000 a second on average and about 50,000 a second at busy hours (5× the average).
• "Customers who bought this also bought": a question about links between people and products.

Four questions sort them. Does it need joins across tables? Must several changes happen together? Is it looked up by one key? How much is written?

What a relational (SQL) database gives you

A relational database (PostgreSQL and MySQL are common examples) keeps data in tables with a fixed set of columns, the schema. Rows point to each other by id: an order holds its customer's id, not a copy of their name and city.

customers               orders
id  name  city          id    customer  item
7   Ana   Lyon          1001  7         lamp
8   Bo    Paris         1002  8         mug
9   Cy    Lyon          1003  9         rug
                        1004  7         mug
                        1005  8         vase
                        1006  9         vase

A join combines tables when you ask, so you can answer questions nobody planned. "Which orders came from customers in Lyon?" is one query:

SELECT orders.id, orders.item
FROM orders JOIN customers ON customers.id = orders.customer
WHERE customers.city = 'Lyon';
-- 1001 lamp, 1003 rug, 1004 mug, 1006 vase

The second gift is transactions: a group of changes that happen together or not at all. Say Ana pays Sam, a seller on the shop's marketplace, $30 for a lamp. Without a transaction, each change is saved the moment it runs:

step                       Ana    Sam   together
before                     $100   $0    $100
take $30 from Ana          $70    $0    $70
crash before giving Sam    $70    $0    $70   ($30 gone)

Swapping the two changes doesn't help: a crash in between leaves Sam with $30 and Ana still with $100. Running the whole payment again after the restart is worse: Ana pays twice and ends with $40, and $30 is still missing. Wrapped in one transaction, from BEGIN to COMMIT, the database undoes the half-done change when it restarts, and Ana has her $100. The same goes for an order and the stock it lowers.

Relational databases also give strong consistency on the main server: once a transaction commits, every later read sees it. That's why orders, payments, stock and anything to do with money usually live in SQL.

What NoSQL stores give you

Much of the shop's traffic needs neither joins nor transactions: getting shopper 42's cart, showing product p7's page, saving one page view. Each touches one key. Most NoSQL stores are built for that: they find data by its key and spread the keys over many servers, and in return they give up joins and most transactions across keys. Four families are common:

• Key-value (Redis and DynamoDB are common examples): get and set one value by its key, very fast. Carts and login sessions fit: both are read on every page, by id. Kept in memory, one such server answers about 100,000 lookups a second.
• Document (MongoDB, for example): each record is a whole document with its own fields, read in one go. The catalog fits: in a SQL table every row has the same columns, so shirts and laptops would share a table full of empty columns. Many SQL databases can also keep JSON in a column, a middle way.
• Wide-column (Cassandra, for example): many rows under one key, sorted inside it, spread over many servers and built for heavy writes. Page views fit: 50,000 a second at busy hours is about 10 times what we'll assume one database server takes (5,000 simple writes a second), and they're read back as "views of p7 in the last hour". A van reporting its position every 10 seconds has the same shape.
• Graph (Neo4j, for example): the links themselves are stored, so a question many hops deep is one short query that walks them. "Also bought" goes from the mug to its buyers, Ana and Bo, then to what else they bought, the lamp and the vase: two hops. In SQL every hop is one more join, which is fine for two (many shops compute these lists ahead of time); a graph store pays off when questions go deeper, like accounts linked through shared cards and addresses.

The myth: "NoSQL scales, SQL doesn't"

SQL databases scale too. Read replicas, copies of the database, answer the reads, and sharding splits the data across servers by a key, such as the customer id, to spread the writes. Many large sites run SQL databases this way; it takes more work than in a store built for it.

The real difference is the default. NoSQL stores make splitting by key the normal way to run, and the price is whatever crosses keys: joins and transactions. Split the wallets by owner, and Ana's and Sam's can sit on different servers, so a payment between them is no longer a simple transaction. That's why "move orders to NoSQL so they scale" is a poor trade: it gives up exactly what orders need.

How to choose in a design interview

The rule most interviewers like: default to a relational database, and move a kind of data out only for a need you can name:

• key-only access at huge scale, such as carts and sessions: a key-value store;
• records whose fields differ from one to the next, such as a catalog: a document store;
• massive writes read back by key and time, such as page views: a wide-column store;
• relationships many hops deep: a graph database.

"NoSQL is newer" and "we might be huge one day" are not needs. Then say why in one sentence: "Orders go in a relational database like PostgreSQL, because an order and its stock change in one transaction, and reports need joins." Many systems use both kinds, but a small shop can keep all five kinds of data in one SQL database: every store you add needs its reason.

Common interview questions on SQL and NoSQL

When would you choose NoSQL over SQL?

When a need points there: data read only by key at very high rates, records whose shape varies, or a flood of writes to spread over many servers. Without one, a relational database is the safer default.

Can NoSQL databases do transactions?

Some can, with limits. Many make the changes to one key or one document all-or-nothing, and some offer transactions across several keys at an extra cost. For payments, a relational database is the usual answer.

Where would you store a shopping cart?

In a key-value store, keyed by the shopper's or the session's id: it's read on every page and never joined. At checkout its items become an order in the SQL database, where the order and the stock change in one transaction.

Why not use one database for everything?

Often you should: one SQL database is simpler to run and reason about. Split only when one kind of data needs something it does badly at your numbers, and say what.

Summary

• Choose by the questions the data must answer: joins, changes that go together, lookups by key, how much is written.
• SQL: tables with a fixed schema, joins, transactions, strong consistency, and queries you didn't plan for.
• Key-value for carts and sessions, document for records with varied fields, wide-column for huge writes by key and time, graph for links many hops deep.
• SQL scales with read replicas and sharding; NoSQL makes sharding by key the default and gives up joins and transactions across keys.
• Default to a relational database, move data out for a need you can name, and say why in one sentence.