SQL vs NoSQL

The decision

A SQL database keeps data in tables with fixed columns. It can join tables, and it can change many rows in one transaction, which is a group of changes that all happen or none happen. NoSQL is a name for databases that are not relational: document, key-value and wide-column stores. Each gives up some SQL power, such as joins, and gets something back, such as easy spreading of data over many servers.

Choose SQL when

• You do not know all the questions yet. Any column can be searched, and any two tables can be joined.
• One action must change several rows together, such as a payment and an order.
• Your data and your writes fit on one server (see the numbers below).
• People ask for new reports every week.

Choose NoSQL when

• You know every query in advance, and each one looks up one key. Example: a cart by user id.
• Writes per second are more than one SQL server can take, and you must spread them over many servers.
• You read an item as a whole, and items have different shapes, such as a restaurant menu.
• You accept no joins. You copy data so that each query reads one place.

Pick the kind by the lookup. A key-value store returns a value for a key. A document store keeps whole JSON records. A wide-column store groups rows by a partition key and sorts them inside the group, for example by time.

The numbers that change the answer

Assumption: one SQL server takes 2,000 simple writes per second. Measure your own, because row size, indexes and hardware change it.

Why 1 TB? It is a warning line, not a hard limit, because a SQL server can hold many terabytes. The pain is time. Assumption: a restore reads 100 MB per second. 1 TB is 1,000,000 MB. So 1,000,000 / 100 = 10,000 seconds, which is 2.8 hours of downtime after a bad failure.

Worked example (Assumption: a food app). It has 2,000,000 orders per day (20 lakh) and 100,000 riders online all day. Each rider sends a location every 5 seconds. One order writes 4 rows. The peak is 10 times the average.

• Orders: 2,000,000 / 86,400 = 23 per second. The peak is 10 times that, about 231 per second. 231 x 4 = 924 writes per second, which is 924 / 2,000 = 46% of one server. Stay with SQL.
• Locations: 100,000 / 5 = 20,000 writes per second, 10 times one server. Assumption: one location is 100 bytes. 20,000 x 86,400 x 100 = 172.8 GB per day, so 1 TB fills in 5.8 days.
• Result: orders in SQL, locations in a wide-column store with the rider id as partition key.

What breaks with each choice

• SQL hits a write limit on one server. Joins and transactions stop working across servers once you split the data. Fix in this order: batch the writes, remove indexes you do not need, move the heaviest table to its own server, then split by key. A cache and read copies help reads only.
• NoSQL breaks when a question arrives that you did not plan for. You must build a new table. Copies can disagree, and one hot key can overload one server. Fix: a second table for the new question, a good partition key, and a repair job that compares copies with the source.

What it costs

• Each extra database needs monitoring, backups and upgrades. Assumption: 2 engineer-days per month.
• Changing your mind: copying 1 TB at 50 MB per second takes 1,000,000 / 50 = 20,000 seconds, which is 5.6 hours. Code costs more. Assumption: 2 engineers for 8 weeks is about 4 engineer-months. At Assumption Rs 250,000 per engineer-month, that is Rs 1,000,000 (10 lakh).

How each level answers

SDE-1: SQL has tables, joins and transactions. NoSQL is not one thing. Key-value, document and wide-column stores each fit one kind of lookup.

SDE-2: I pick SQL for orders because one order changes several rows together and I do not know all the reports yet. I pick a key-value store for carts because the app only reads a cart by user id.

Senior: Orders peak at 924 writes per second, 46% of one server, so SQL with a read copy is enough. Locations are 20,000 per second, so I use a wide-column store and partition by rider id to avoid a hot partition. Batching 100 locations per insert gives 200 writes per second, but 172.8 GB per day still fills the disk. Data size decides.

Staff: A second database costs on-call people, not only servers. I start with SQL behind one data layer, so locations can move later. The trigger is writes above 70% of one server (1,400 per second) or a table above 1 TB. The platform team owns the new store and its on-call. I measure write load as a share of the limit, restore time, and p99 (the time within which 99 of 100 requests finish) query time. The choice was right if p99 stays flat while load grows.

Say it in the interview

"I use SQL for orders, because one order changes several rows together and the peak is 924 writes per second, about 46% of one server. Rider locations are 20,000 writes per second, ten times one server, so I use a wide-column store partitioned by rider. If order writes pass 70% of a server, I batch writes and move heavy tables out first, and split by key last."

Common mistakes

• Saying "NoSQL scales better" with no number. One SQL server may already cover your writes, so compare your write rate with a measured limit.
• Choosing NoSQL to avoid designing a schema. The schema moves into your code, and every reader must handle every old shape of the data.
• Using multi-document transactions by default in a document database. The MongoDB manual says they usually cost more than single-document writes, so keep related data in one document.

Sources

• MongoDB, transactions: https://www.mongodb.com/docs/manual/core/transactions/ (opens in a new tab) (checked 26 Oct 2026).
• Apache Cassandra, data modelling introduction (one table for each query, no joins): https://cassandra.apache.org/doc/latest/cassandra/developing/data-modeling/intro.html (opens in a new tab) (checked 26 Oct 2026).
• All write rates, sizes, restore speed, effort and rupee amounts are assumptions made for teaching. The arithmetic is calculated from them.