SQL or NoSQL?

"Should we use SQL or NoSQL?" is one of the most argued-about questions in software, and most of the arguing is about the wrong thing. People pick a database because a big company uses it, because it "scales", or because a blog post said relational databases are old.

The better way to choose is boring and reliable: look at the shape of your data and the questions you'll ask it. Then pick the simplest database that answers those questions well. In this lesson we'll do exactly that for Tiffin.

What "SQL" and "NoSQL" actually mean

SQL databases (also called relational databases) store data in tables with fixed columns. Rows in different tables point at each other with ids, and you query them with SQL. PostgreSQL, MySQL, SQL Server and SQLite are all relational.

NoSQL isn't one thing. It's a catch-all name for "everything else", and the members have very little in common:

So "SQL or NoSQL?" is really "relational, or one of five other quite different things?". That's a more useful question, because each of those five is good at something specific.

Start from the data

Let's list what Tiffin stores and how the pieces relate.

• A restaurant has many menu categories, each with many menu items.
• A customer has many addresses and places many orders.
• An order belongs to one customer and one restaurant, has many order items, a payment, and eventually one delivery with one rider.

%% caption: Tiffin's data is a web of related things. The order sits in the middle and touches almost everything.
flowchart TD
  R["Restaurant"] --> MI["Menu item"] --> OI["Order item"]
  C["Customer"] --> O["Order"]:::hot --> OI
  O --> P["Payment"]
  O --> D["Delivery"] --> RD["Rider"]

This is highly related data. An order on its own is meaningless; it only makes sense with its customer, restaurant, items and payment. That's the classic shape relational databases were built for.

Then list the questions

The second input is the queries. Here are some Tiffin will run, from the apps and from the business:

1. Show restaurant 42's menu.
2. Show a customer their last 20 orders, with restaurant names.
3. Show restaurant 42's placed orders, oldest first.
4. Place an order: check items are available and save the order and its items, all together.
5. Give two riders the same delivery offer; exactly one gets it.
6. Finance, monthly: total sales and commission per restaurant.
7. Support: find every order from this phone number in the last 90 days.

Questions 1 to 3 are simple lookups. Any database handles them.

Questions 4 and 5 need transactions: several changes that either all happen or none do, without another request seeing a half-done state. Losing that guarantee means real bugs: an order saved without its items, or two riders at the same door. (A card charge happens at a payment provider, outside the database, so no database transaction can cover it. The lab at the end of this module shows how to order the steps safely.)

Questions 6 and 7 are the interesting ones. Nobody designed the schema around them, and new ones like them will appear every week. Relational databases are very good at questions you didn't plan for, because SQL lets you join and filter any tables by any column.

ACID, in plain words

Relational databases are known for ACID transactions. The four letters describe what "together" really means:

• Atomic. All the changes in a transaction happen, or none do. If the server crashes after saving the order but before saving its items, the whole thing is rolled back.
• Consistent. Rules you've declared (this id must exist, this number can't be negative) hold after every committed transaction. A transaction that would break one is rejected.
• Isolated. Two transactions running at once don't see each other's half-finished work. (How strictly depends on the isolation level, which Module 20 covers.)
• Durable. Once committed, it survives crashes.

Here's what placing a Tiffin order looks like:

BEGIN;
-- some columns left out to keep it short
INSERT INTO orders (customer_id, restaurant_id, status, total_paise)
  VALUES (881, 42, 'placed', 53000) RETURNING id;      -- say 9481
INSERT INTO order_items (order_id, menu_item_id, name, quantity, price_paise)
  VALUES (9481, 7, 'Chicken dum biryani', 2, 28000), (9481, 19, 'Raita', 1, 4000);
COMMIT;

Either the order and both items exist, or nothing does. You get that for free.

Many NoSQL databases now offer transactions too, but often with limits: only within one document, or one partition, or at extra cost. When your core operation touches several related records at once, as ordering does, a relational database makes it easy.

Where NoSQL databases shine

That doesn't make NoSQL databases bad. They're often the right tool, for specific jobs:

• Very high write volume with simple access patterns. Storing every rider's GPS location every 4 seconds, across many cities, and reading it back by rider and time. A wide-column store like Cassandra handles millions of writes a second spread over many machines.
• Fast lookups by key. Sessions, caches, rate limit counters. Redis is the standard choice, and we'll lean on it heavily from Module 3.
• Records with genuinely varied shapes, read and written as a whole. A product catalogue where a phone and a sofa have totally different attributes suits a document database.
• Full-text search. "biryani near me, open now, under ₹300" is a search engine's job. We'll add one in Module 9.

Notice the pattern. Each of these is a specific access pattern that one kind of database handles better. They usually sit alongside a main relational database, not instead of it.

"But does it scale?"

The usual argument for NoSQL is that it scales and relational databases don't. That's half true.

Many NoSQL databases were designed from the start to spread data across many machines, so adding capacity is built in. A relational database is easiest on one machine, and splitting it across many (sharding, Module 9) is real work.

But one machine goes a long way. A single well-configured PostgreSQL server on modern hardware handles tens of thousands of simple queries a second and terabytes of data. Tiffin at 50,000 orders a day writes a handful of rows a second on average, and a few dozen at the dinner peak. We are hundreds of times away from that limit. Choosing a database for a scale you might reach in five years, at the cost of transactions and flexible queries today, is a bad trade.

Choose a database for the data and queries you have, with room for the next 10 to 100 times of growth. Not for the next 10,000 times.

The choice for Tiffin

For Tiffin version one: PostgreSQL.

• The data is highly related, and the order is in the middle of everything.
• Ordering and rider assignment need multi-row transactions.
• Finance and support will keep inventing new queries.
• It's well understood, free, and every cloud provider runs it for you.
• One server will easily handle our numbers.

We're not ruling out other databases forever. When a specific need shows up that PostgreSQL is bad at (caching, live locations at city scale, full-text search), we'll add the right tool for that job and keep PostgreSQL as the source of truth.