Let me tell you about a mistake I see we developers make all the time.
Someone is building a new product. Maybe a food delivery app, maybe a social platform, maybe an e-commerce store. They sit down to design the database and immediately open a browser tab: “MongoDB vs MySQL which is better.” They read three or four blog posts, understand maybe 60% of it, and pick one mostly because it sounds modern or because their friend used it. Then they move on and start writing code.
Six months later, one of two things happens. Either the system starts behaving in very strange ways that nobody can fully explain. Or a senior engineer sits them down in a meeting and asks, “Why did you choose this database for this use case?“
And the honest answer? They did not really choose. They guessed.
This blog is about not guessing. It is about understanding SQL and NoSQL so deeply that the right choice becomes obvious from the problem itself. The kind of understanding that shows up in interviews, in architecture reviews, and in production when things get difficult.
And I will say this upfront, there is no universal “better” here. SQL and NoSQL are genuinely different tools for genuinely different problems. The goal is not to pick a favourite. The goal is to understand both so well that you can match the tool to the problem every single time.
Let us start from zero.
Quick Recap, Where We Left Off
In Part 3, we covered back-of-envelope estimation, CAP theorem, and database scaling. We understood that as your system grows, your single database starts showing strain, queries slow down, CPU climbs, things break. And we went through the scaling ladder: vertical scaling, indexing, partitioning, master-slave, multi-master, and finally sharding.
But all of that assumed you already had a database. We never asked: which database should I be using in the first place?
That is what this blog answers.
What Is a Database, Really?
Before we get into SQL vs NoSQL, let me make sure we are on the same page about what a database actually does.
Your backend application needs to save data somewhere. When Swiggy processes your order, that order needs to be stored, the items, the price, your address, the delivery person’s ID, the timestamp. When you log into Instagram, your session needs to be remembered. When you write a review for a restaurant, that review needs to be persisted so other people can see it tomorrow.
A database is the system responsible for storing and retrieving all of this data reliably. Not just in RAM, which disappears when the server restarts, but on disk, persistently, so it survives power failures, server crashes, and restarts.
There are two fundamentally different ways to organise this storage. One is structured and disciplined, SQL. The other is flexible and fast, NoSQL. Both are trying to solve the same fundamental problem (store data reliably) but they make different tradeoffs to get there.
What Is SQL?
What “SQL” Actually Means
SQL stands for Structured Query Language. It is the language you use to talk to relational databases. When engineers say “SQL database,” they mean databases like MySQL, PostgreSQL, Oracle, SQL Server, or SQLite.
But the important thing is not the language. The important thing is the underlying idea: data is organised into tables.
Think of a table exactly like an Excel spreadsheet. It has rows and columns. Each column has a name and a data type, this column stores text, this one stores numbers, this one stores dates. Every row in the table is one record, and every record must have values for the defined columns.
Look at a typical users table. It has five columns: user_id, name, email, city, and joined. Every single user in that table, whether it is user number 1 or user number 50 million, has exactly those five fields. No more, no less.
This structure is called the schema. And here is the critical part: in SQL, you must define the schema before you can insert a single row of data. You are signing a contract with the database: “I promise that all data in this table will follow this exact structure.” The database holds you to that contract on every insert, update, and delete.
This sounds restrictive. And honestly, sometimes it is. But it also gives you something extremely valuable, predictability. You always know exactly what shape your data is in. You can write queries with complete confidence about what fields exist. Your data cannot become inconsistent or malformed without the database explicitly rejecting the write.
What Is Schema Design?
Schema design is the process of deciding what tables you need, what columns each table should have, what data types those columns should be, and how tables relate to each other.
For example, an e-commerce system might have a users table, an orders table, and an order_items table. The orders table has a user_id column that references the users table, this is called a foreign key, and it tells the database “this order belongs to this user.” The relationship is explicit and enforced.
Good schema design is a skill in itself. Getting it right upfront saves enormous pain later. Getting it wrong means expensive migrations when you need to change the structure.
What Is ACID? (The Four Guarantees That Make SQL Trustworthy)
This section right here is the heart of understanding why SQL exists. ACID is not a feature you turn on. It is the fundamental promise that SQL databases make to you about every operation.
ACID stands for four properties: Atomicity, Consistency, Isolation, and Durability. Let me explain each one using the same example throughout, you are sending ₹500 via PhonePe to a friend.
What Is Atomicity?
A UPI payment involves two operations. First, ₹500 is debited from your account. Second, ₹500 is credited to your friend’s account.
These two operations are bundled into a single transaction.
Atomicity means that a transaction is treated as one indivisible unit. Either both operations succeed, or neither of them does. There is no in-between state.
What happens without atomicity? Your account gets debited. The server crashes before crediting your friend. Your ₹500 has vanished into the void. You lost money and your friend received nothing.
With atomicity, the database guarantees this never happens. If anything goes wrong between the two steps, server crash, network failure, power cut, the entire transaction is rolled back. Your account is restored to its original balance. You can try again safely. The money never disappears.
This is the “all or nothing” guarantee. It is why you trust that your NEFT transfer either goes through completely or does not go through at all. There is no partial state.
What Is Consistency?
Your account balance is ₹200. You try to send ₹500.
Consistency means the database will never allow this transaction to complete, because it would violate a rule, your balance cannot go below zero. The database enforces all rules, constraints, and validations that you have defined.
More broadly, consistency means that every successful transaction takes the database from one valid state to another valid state. The total amount of money in the system before a payment equals the total after. If you have 10 items in stock, you cannot sell 11. Primary key constraints, foreign key constraints, not-null constraints, all of these are part of consistency.
The database would rather reject an operation than allow it to break a rule. This sounds rigid, but it is exactly what you want when real money is involved.
What Is Isolation?
You and your spouse both use the same joint bank account. You both open the Axis Bank app at the same moment and both initiate a payment simultaneously.
Without isolation, this is dangerous. Your transaction might read the account balance and see ₹5,000. Your spouse’s transaction also reads ₹5,000. Both of you transfer ₹4,000 each. Both transactions see enough balance to proceed. Together they deduct ₹8,000 from an account that only had ₹5,000. This is called a dirty read, and it is a real bug in poorly designed systems.
Isolation means each transaction executes as if it is the only thing happening. Every transaction gets a clean, consistent snapshot of the database at the moment it starts. Changes from one transaction are not visible to another transaction until the first one is fully committed.
Think of it like separate checkout queues at a supermarket. Each person in their own queue processes independently. Nobody cuts in mid-transaction. No confusion, no conflicts.
What Is Durability?
You press “Pay” on PhonePe. The screen shows “Payment Successful ✓”. The server then suffers a complete power failure two seconds later.
What happens to your payment?
Durability guarantees: your payment is safe. It is permanent. Once a transaction is committed, it has been written to durable storage, to disk, in a way that survives any failure. When the server comes back up, your payment record is exactly where it should be.
This is achieved through something called a Write-Ahead Log (WAL). Before any transaction is even applied to the main database files, it is first written to a log on disk. So even if the server dies at the absolute worst possible moment, the log is there. When the server restarts, it reads the log and recovers any transactions that were in-flight.
Committed means permanent. Always. No exceptions.
These four properties together, Atomicity, Consistency, Isolation, Durability, are why banks, payment gateways, stock trading platforms, and hospital records systems run on SQL. When you cannot afford for data to be wrong, incorrect, or partially committed, you need ACID. And ACID is what SQL gives you, reliably, every single time.
Which Databases Are SQL?
The most common SQL databases you will encounter:
MySQL, the most widely used open-source SQL database. Powers a massive number of websites and applications globally. Instagram, Airbnb, Twitter all used MySQL at various points.
PostgreSQL, often called Postgres. More feature-rich than MySQL, with better standards compliance and more advanced features. The preferred choice for many modern applications.
Oracle, enterprise-grade SQL database used by banks, large corporations, government systems. Expensive but extremely capable and battle-tested.
SQL Server, Microsoft’s SQL database. Common in corporate environments, especially those already using Microsoft infrastructure.
SQLite, a tiny, file-based SQL database. No separate server needed. Used in mobile apps, embedded systems, and local development.
What Is NoSQL?
Why NoSQL Even Exists
Okay, so SQL is reliable, structured, and gives you ACID. Why would anyone want anything else?
Because not every problem needs a contract.
Imagine you are building the product catalogue for a site like Flipkart. You have millions of products. A USB cable has 3 attributes: length, type, and compatibility. A laptop has 47 attributes: RAM, processor generation, display type, graphics card, battery life, weight, operating system, number of USB ports, and on and on. A book has completely different attributes: author, ISBN, publisher, genre, page count.
In SQL, you have two options. Either you create one giant table with hundreds of columns where most of them are empty for any given product (a USB cable does not have a “battery life” column). Or you do extremely complex workarounds with separate attribute tables and JSON columns. Both are painful.
NoSQL says: just store each product as a document with whatever fields it needs. A laptop document has 47 fields. A USB cable document has 3. A book has its own set. No rigid structure, no empty columns, no workarounds. Each thing looks exactly like it needs to look.
Beyond schema flexibility, NoSQL databases are also designed from the ground up to scale horizontally, adding more machines to handle more data. SQL databases were designed for a single powerful server, and horizontal scaling is added on top with difficulty. NoSQL databases have horizontal scaling baked into their DNA.
The term NoSQL literally means “Not Only SQL”, or sometimes “No SQL at all.” It is a category of databases that do not use the rigid table-and-schema model. Beyond that, different NoSQL databases work in completely different ways. There are four main types.
Document Databases (MongoDB, CouchDB)
What Is a Document Database?
In a document database, data is stored as flexible documents, usually in JSON format. Each document is a self-contained object with any fields you want, in any nesting structure.
Here is what a user record looks like in MongoDB:
{
"id": 1,
"name": "Revathi Rana",
"email": "revathi@gmail.com",
"address": {
"city": "Delhi",
"pincode": "110001",
"state": "Delhi"
},
"orders": [101, 205, 308, 412],
"preferences": {
"theme": "dark",
"language": "English",
"notifications": true
},
"joined": "2022-01-12"
}Notice what is different from SQL. The address is nested inside the user document, not in a separate table. The orders are an array inside the document. And another user’s document could have completely different fields, a loyalty_tier field, a social_logins array, a billing_address. No error, no migration needed. Just insert data in whatever shape makes sense.
This is the famous flexible schema of NoSQL. You can add new fields to new documents without changing the schema for existing documents. This is enormously convenient when your product is evolving rapidly and you are not sure exactly what shape your data needs to be.
When to Use a Document Database
E-commerce product catalogues, where different products have completely different sets of attributes. User profiles, where different users might have different information. Blog posts, reviews, comments, activity logs. Anywhere the structure of your data varies from record to record or changes frequently.
Key-Value Stores (Redis, DynamoDB)
What Is a Key-Value Store?
This is the simplest database model imaginable. Store a value, give it a key, retrieve it by that key later. That is the entire model.
session:user_9812 → { "logged_in": true, "cart": [201, 305] }
otp:9876543210 → "482910"
cache:homepage_html → "<html>... entire page content ...</html>"
rate_limit:api:user1 → "43"A key on the left, a value on the right. No querying inside the value, no joining, no relationships. Just store and retrieve.
Redis is the most famous key-value store. The key reason Redis is used everywhere is speed, it stores everything in memory (RAM), which means reads and writes happen in under a millisecond. It is ridiculously fast compared to any disk-based database.
When to Use a Key-Value Store
Caching, you query PostgreSQL for the homepage data, which involves five table joins and takes 200ms. You store the result in Redis with a 1-minute expiry. Next 10,000 requests hit Redis, get the answer in 1ms, never touch PostgreSQL. Your database gets 10,000 times less load on that query.
Session management, when you log into Swiggy, your session (who you are, what is in your cart, your authentication token) is stored in Redis. Every API request checks Redis to verify you are logged in. Extremely fast.
OTP storage, when you request a login OTP on any Indian app, that 6-digit number is stored in Redis with your phone number as the key and a 10-minute expiry timer. After 10 minutes, Redis deletes it automatically. Perfect use case.
Rate limiting, how many API calls has this user made in the last minute? Increment a counter in Redis on every call. Check before processing. If over the limit, reject. Simple, fast, no load on the main database.
Column-Family Databases (Apache Cassandra, HBase)
What Is a Column-Family Database?
In a normal SQL database, data is stored row by row. When you have a table with 20 columns and you run SELECT name FROM users WHERE id = 5, the database still reads the entire row from disk, all 20 columns, and then throws away the 19 you did not ask for. For analytical queries that only need a few columns across millions of rows, this is very wasteful.
A column-family database organises data differently. Instead of storing all columns of one row together, it stores all values of one column together. So all name values are physically stored next to each other on disk. All age values next to each other. If you ask for name and age only for a million users, the database only reads those two columns from disk. It does not touch the other 18. This is dramatically more efficient for certain access patterns.
Cassandra is also built from the ground up for massive scale. You can have 50 Cassandra nodes across multiple data centres, and the system handles sharding and replication automatically. It is designed to never have a single point of failure.
A key property: in Cassandra, different rows can have completely different sets of columns. User 1 might have a premium_since column. User 2 might not have it at all. This is more flexible than SQL but more structured than document stores.
When to Use a Column-Family Database
This is the database of choice for time-series data and analytics at extreme scale. Uber uses Cassandra to store driver location history, every GPS ping from every driver, billions of records per day. Netflix uses it to store user activity data. IoT platforms use it for sensor readings from millions of devices. WhatsApp used Cassandra for message storage at massive scale.
If you are storing enormous amounts of data where you frequently query specific columns across many rows, Cassandra is worth considering.
Graph Databases (Neo4j, Amazon Neptune)
What Is a Graph Database?
This is the most conceptually unique type. Every database we have discussed so far stores data as individual records with attributes. A graph database stores data as nodes (things) and edges (relationships between things).
Think about LinkedIn’s “People You May Know” feature. You want to find people who are connected to people you know, second-degree connections. In SQL, you need to join the users table with a connections table, then join again to get the next level. The query becomes exponentially more complex at every level. Third-degree connections are a nightmare in SQL.
In a graph database, this is the native operation. The database literally stores data as a graph, circles (nodes) connected by lines (edges). Revathi is a node. Priya is a node. The line between them says “friends.” A query that says “find everyone within 2 hops of Revathi” is natural and fast.
(Revathi) --FRIENDS_WITH--> (Priya) --FRIENDS_WITH--> (Rohan)
(Revathi) --WORKS_AT--> (TechCorp) <--WORKS_AT-- (Priya)
(Revathi) --BOUGHT--> (Product A) <--BOUGHT-- (Rohan)You can ask questions like “Who does Revathi know through both friendship and work?” or “What products do users who bought Product A also tend to buy?” and Neo4j traverses the graph to answer efficiently.
When to Use a Graph Database
Social networks, friend graphs, follower graphs, connection recommendations. Fraud detection, following chains of suspicious transactions across accounts. Recommendation engines, “users who bought X also bought Y.” Route finding in maps. Knowledge graphs. Any problem where the relationships between data are as important as the data itself.
ACID vs BASE (The Philosophy Behind the Difference)
We talked about ACID for SQL. What do NoSQL databases follow?
Most NoSQL databases follow a philosophy called BASE:
Basically Available, the system always responds to requests, even if the data might be slightly stale.
Soft state, the state of the data in the system may change over time, even without new input, as different nodes sync with each other.
Eventually consistent, given enough time, all copies of the data across all nodes will converge to the same value.
This is the AP choice from CAP theorem that we discussed in Part 3. NoSQL accepts that two nodes might temporarily show slightly different data, as long as they eventually sync up.
For a social media platform, this is completely fine. If your Instagram post takes 2 extra seconds to appear on your friend’s feed because of a momentary sync delay, nobody suffers. Nobody even notices. But for a bank balance, never, not even for 2 milliseconds.
The trade-off is deliberate and real. NoSQL databases get speed, scale, and flexibility. They give up the strict guarantees of ACID. Neither is better. The question is always: does your specific piece of data need ACID?
Scaling: SQL Goes Up, NoSQL Goes Out
This is one of the clearest practical differences, and it matters a lot when you are designing for growth. Let me go much deeper here than most blog posts do, because this is where interviews and real architectural decisions get made.
How SQL Scales, The Full Ladder
SQL databases were designed in an era when “the server” was one machine and you made that machine bigger when you needed more power. That assumption shapes everything about how SQL scales.
Vertical Scaling (Scale Up)
The first and simplest move is to make your server more powerful. More RAM so the database can keep more data in memory and avoid slow disk reads. A faster CPU so queries run quicker. Faster SSD storage so disk reads and writes complete faster.
This works surprisingly well for a long time. A well-tuned PostgreSQL instance on a machine with 64GB of RAM and fast SSDs can handle tens of thousands of queries per second. Most applications never need to go beyond this.
The problem is that vertical scaling has a ceiling. The most powerful single server you can buy has a hard limit, there is no machine with infinite RAM. And the cost curve is brutal. Doubling your CPU from 16 cores to 32 cores might cost 3x as much and give you maybe 1.5x the throughput. At some point, you hit diminishing returns.
And if that one server goes down, your entire database goes down. Single point of failure.
Indexing
Before you spend money on hardware, make sure you have indexed your tables properly. An index is a separate data structure (usually a B-tree) that the database maintains to speed up lookups on a specific column.
Without an index on user_id, a query like SELECT * FROM orders WHERE user_id = 12345 makes the database scan every single row in the orders table until it finds the matching ones. If you have 50 million orders, it reads 50 million rows to find maybe 12 of them. This is called a full table scan and it is catastrophically slow.
With an index on user_id, the database jumps directly to the matching rows using the B-tree. Instead of 50 million reads, it does maybe 3-4 reads. The same query goes from taking 10 seconds to taking 2 milliseconds.
Indexing is almost always the first thing you should do when a query gets slow. It costs disk space (indexes take up room) and slightly slows down writes (the index must be updated on every insert), but for read-heavy workloads it is a massive win.
Read Replicas (Master-Slave Replication)
Most production applications read data far more often than they write it. Your social media app reads posts and profiles constantly but writes new posts far less frequently. Your e-commerce site reads product pages constantly but only writes when an order is placed.
Read replicas take advantage of this pattern. You have one primary server (the master) that handles all writes. The master continuously streams every write operation to one or more replica servers (slaves). The replicas apply those same operations and stay in sync.
Your application then sends write queries to the master and read queries to the replicas. You might have one master and three replicas. Your read capacity just quadrupled. The master handles 100% of the write load, each replica handles a fraction of the read load.
This is how most mid-size applications scale. Instagram ran on MySQL with read replicas for a very long time before needing anything more exotic.
There is a small catch: replication lag. The replicas are almost in sync with the master, but not perfectly, there might be a delay of a few milliseconds to a few seconds depending on load. So a read from a replica might return data that is very slightly stale. For most use cases (someone’s profile page, product details, a news feed) this is completely acceptable. For something like a bank balance, you would route that specific query to the master.
Caching Layer (Redis)
Before adding more database servers, many systems add a caching layer. The idea is simple: for data that is expensive to compute and does not change often, compute it once, store the result in Redis, and serve it from there for the next N minutes.
A homepage query that joins 5 tables and takes 200ms in PostgreSQL gets computed once and cached in Redis. The next 50,000 requests get that result from Redis in under 1ms. The database never sees those 50,000 queries. This is often the single highest-leverage optimisation you can make before touching your database architecture at all.
Vertical Partitioning (Splitting Tables)
Over time, your tables might get very wide, dozens of columns that are not all needed at the same time. A users table might have user_id, name, email, phone (needed on every request) and also bio, profile_picture_url, preferences_json, notification_settings_json (needed only when viewing a profile page).
Vertical partitioning means splitting these into two tables: users_core with the frequently-accessed columns and users_profile with the less-frequently-accessed ones. Your common query only reads users_core, which is smaller and fits better in memory and cache. The full profile data is fetched separately only when someone actually views the profile.
Horizontal Partitioning (Sharding), The Hard Part
This is where SQL scaling gets genuinely painful, and it is important to understand why.
Sharding means splitting your data across multiple database servers. Instead of one server with all 100 million users, you have 10 servers with 10 million users each. The question is: how do you decide which user goes on which server?
The most common approach is range sharding or hash sharding on the primary key. With hash sharding, you take user_id % 10, the result (0 through 9) tells you which shard to use. User 9812 goes to shard 2, user 9813 goes to shard 3, and so on. This distributes data roughly evenly across all shards.
Now here is why sharding SQL is painful:
The moment your data lives on multiple servers, ACID across servers becomes a distributed systems problem. Consider this: user A (on shard 0) sends money to user B (on shard 7). In a single-server SQL database, this is a simple transaction. On a sharded system, it requires something called a distributed transaction, the two shards must coordinate to ensure atomicity across machines. This requires a protocol called Two-Phase Commit (2PC), which is slow, complex to implement correctly, and catastrophically hard to recover from if a server fails in the middle of it.
Joins across shards are also gone. If you need to JOIN the users table (spread across 10 shards) with the orders table (also spread across 10 shards), you cannot do it at the database level. You have to pull data from multiple shards into your application code and do the join there. This is more code, more complexity, and usually much slower.
And when a shard gets too large? You need to reshard, split shard 0 into two new shards and redistribute the data. This is an extremely painful operation on a live production system. Data needs to be moved while the system is serving traffic. You have to write migration scripts, test them extensively, schedule a maintenance window or do a live migration, and pray nothing goes wrong.
This is why experienced engineers say: “Shard as a last resort, not as a first move.” Most systems that think they need sharding actually need better indexing, read replicas, and a caching layer first. Sharding should happen when you have genuinely exhausted the other options.
That said, sharding SQL does work and is used in production by large companies. The point is that it is a significant engineering investment, and SQL was not designed with it in mind. You are working against the grain.
Multi-Master Replication
Read replicas give you read scale but do not help if your write volume is the bottleneck. Multi-master replication allows multiple servers to accept writes simultaneously.
The problem: if two masters accept a conflicting write at the same time (two users both trying to claim the same username, for example), you have a conflict. The system needs a conflict resolution strategy: last-write-wins, first-write-wins, or custom application logic. This gets complicated fast and is another reason SQL scaling at the write tier is hard.
Multi-master setups like Galera Cluster for MySQL exist and are used in production, but they are complex to operate and the conflict handling adds latency to every write operation.
How NoSQL Scales, Horizontal by Design
Now here is why NoSQL databases are fundamentally different in their scaling story. They did not bolt on horizontal scaling as an afterthought. They were designed from day one with the assumption that data would eventually need to live on multiple machines.
The Core Difference: Partition Tolerance Built In
Remember CAP theorem from Part 3? NoSQL databases typically choose AP (Availability + Partition Tolerance) over consistency. This design choice, accepting that different nodes might temporarily disagree, is exactly what makes horizontal scaling so much cleaner.
When you do not need to coordinate every write across all nodes to maintain strict consistency, adding a new node becomes simple. The new node joins the cluster, the system redistributes some data to it, and it starts serving traffic. No complex distributed transactions. No 2PC. No application changes needed.
How Cassandra Scales
Cassandra uses a masterless ring architecture. Every node in a Cassandra cluster is equal, there is no single primary node. Data is distributed across all nodes using consistent hashing. When you add a new node, it slots into the ring and automatically takes over responsibility for a portion of the data from its neighbours.
If you have 10 Cassandra nodes and you add 5 more, your cluster now has 15 nodes. Cassandra rebalances automatically. Your application does not need to know about this. No maintenance window required. No manual resharding script. The system handles it.
Cassandra also replicates each piece of data to N nodes (you configure the replication factor, typically 3 for production). So if one node goes down, two other nodes still have the data. The cluster continues to serve reads and writes for that data without any downtime. This is automatic failover built into the architecture.
How MongoDB Scales
MongoDB uses a different approach. It has automatic sharding built in as a first-class feature, not an afterthought.
You tell MongoDB: “shard this collection on this key.” MongoDB manages the chunk distribution across shards automatically. When a chunk gets too large, MongoDB splits it and redistributes. This resharding happens automatically in the background without a maintenance window.
MongoDB also has a built-in concept of replica sets, every shard is itself a three-node replica set with automatic failover. If the primary node of a shard goes down, the two replicas hold an election and one becomes the new primary within seconds. The application is not impacted.
The NoSQL Scaling Summary
Adding capacity to a NoSQL cluster is usually as simple as adding a node, letting the system rebalance, and you are done. No complex distributed transaction protocols. No cross-shard join problem (because the data model discourages joins in the first place). No painful resharding migrations.
This is why companies that need to store and serve petabytes of data at high velocity almost universally use NoSQL for that tier. Cassandra, MongoDB, DynamoDB, these are the databases serving billions of reads per day for Netflix, Instagram, Airbnb, and similar companies. Not because SQL cannot scale, but because NoSQL scales with dramatically less operational pain at that level.
The Scaling Comparison in Plain English
Imagine you are running a dhaba and business is booming. Here is the SQL vs NoSQL scaling story as a dhaba analogy.
SQL is like a single very skilled chef. When business picks up, you buy better equipment for that chef (vertical scale). You hire helpers to handle serving (read replicas, they take orders but the chef does all the cooking). You pre-make popular dishes and keep them warm (caching). Eventually if you need to truly scale, you open a second kitchen, but now you have to coordinate between two kitchens for any order that needs ingredients from both, complex and error-prone (sharding pain).
NoSQL is like a cloud kitchen model. You start with 3 kitchen units. As orders come in, you just open more units and the order router sends orders to whichever unit has capacity. Units are identical and interchangeable. One unit goes down? The others continue. Need more capacity? Open two more units. Each unit works independently and nobody needs to coordinate with anyone else for every order. That independence is what makes it so simple to scale.
The SQL approach produces a better-coordinated, more reliable meal (ACID). The NoSQL approach serves far more orders per hour but accepts that occasionally two units might briefly have a slightly different version of “today’s specials board” while they sync up.
When to Use Which Database?
Alright. Everything above is context. This is the section that actually helps you make decisions.
Use SQL When…
Data integrity cannot be compromised. If you are storing financial transactions, payment records, account balances, stock trade history, insurance records, medical histories, or legal documents, use SQL. ACID is not optional for these categories. The cost of inconsistent data is too high. PhonePe, Razorpay, Zerodha, HDFC Bank, IRCTC ticket bookings, all of these are SQL.
Your data is structured and the schema is well understood. You know what fields a user has. You know what an order looks like. You know what a product in your inventory contains. If the structure is clear and stable, SQL’s schema enforcement is a feature, not a burden.
You need complex queries. Reporting dashboards, analytics, business intelligence, admin panels that need to JOIN multiple tables, GROUP BY, aggregate, filter by multiple conditions, SQL handles all of this beautifully with a single query. NoSQL databases handle this poorly or require you to pull data out and process it in application code, which is significantly more work.
You need transactional operations across multiple records. Placing an order involves creating an order record, decrementing inventory, creating payment records, and sending a notification all as one atomic operation. SQL handles this naturally with transactions.
Use NoSQL When…
Your data is unstructured or the schema changes frequently. E-commerce product catalogues, user-generated content, reviews, social media posts, event logs, anything where different records have different shapes or where you are still figuring out what fields you actually need.
You need to store and retrieve massive amounts of data at very high speed. Social media feeds, real-time GPS coordinates, sensor readings, click-stream logs, session data. High volume, high velocity, simple access patterns, this is NoSQL’s home.
You need to scale horizontally without complex workarounds. If your product is growing very rapidly and you know you will need to spread data across many machines, starting with a database designed for horizontal scaling saves significant pain later.
Your access pattern is simple. If most of your reads are “give me the document for this user” or “give me the value for this key” rather than complex multi-table joins, NoSQL is simpler and faster.
The Quick Reference Table
| Use Case | Database | Why |
|---|---|---|
| PhonePe / Razorpay payments | SQL (PostgreSQL) | ACID mandatory |
| Zerodha stock trading | SQL (MySQL/PostgreSQL) | Consistency non-negotiable |
| IRCTC seat booking | SQL | Isolation critical (no double booking) |
| Flipkart order management | SQL | ACID + complex queries |
| Hospital patient records | SQL | Data integrity, relationships |
| Instagram posts and feeds | NoSQL (Cassandra) | Massive scale, AP is fine |
| Zomato product catalogue | NoSQL (MongoDB) | Flexible schema per restaurant |
| Swiggy driver real-time location | NoSQL (Redis/Cassandra) | High write throughput |
| OTP and session storage | NoSQL (Redis) | Speed, TTL, key-value |
| LinkedIn connection graph | NoSQL (Neo4j) | Relationship traversal |
| YouTube watch history | NoSQL (Cassandra/Bigtable) | Massive volume, timeseries |
| Business sales dashboard | SQL | Complex joins and aggregations |
The Most Important Thing Nobody Tells You
Most real production systems at scale use both SQL and NoSQL at the same time.
Swiggy uses SQL for order records, customer accounts, and payment data, all of this needs ACID. They use Redis for real-time driver location updates happening every few seconds, high-throughput key-value, no ACID needed. They use something like MongoDB for restaurant menus and reviews, flexible schema, high read volume.
Flipkart uses SQL for customer accounts, order records, and inventory. They use NoSQL for the product catalogue, millions of products with different attributes, and for sessions and caching.
This approach is sometimes called polyglot persistence, using multiple different databases simultaneously, each chosen for a specific type of data. It is standard at any company of meaningful scale. The decision is not “SQL or NoSQL for the whole system.” It is “the right database for each specific piece of data, given these specific requirements.”
The One Question That Makes This Simple
After all of this, here is the single question I always ask when I am stuck choosing:
“What is the absolute worst thing that happens if two different users see different data for 30 seconds right now?”
If the answer involves money disappearing, an item being sold twice, a seat being double-booked, a wrong medical dose being administered, or any legal liability, choose SQL. Consistency is non-negotiable.
If the answer is “one user sees a like count of 4,200 and another user sees 4,198, and in 30 seconds they will both see 4,203 once the nodes sync”, choose NoSQL. Eventual consistency is completely fine and not worth the scaling cost of SQL for that use case.
That one question cuts through all the noise.
Frequently Asked Questions
Q: Is MongoDB always a bad choice for beginners?
Not at all. MongoDB is excellent for prototyping, for applications where the schema is not fully defined, and for genuinely document-oriented data. It is a bad choice when people use it to avoid thinking about data structure, and then later discover they needed ACID and complex joins. The problem is misuse, not the tool itself.
Q: My startup is small. Should I just use PostgreSQL for everything?
Yes, honestly. PostgreSQL is one of the most capable databases ever built. It handles significant scale on a single well-sized server. It has full ACID support, a powerful query language, JSON column support for semi-flexible data, full-text search, and excellent scaling options. For most startups, PostgreSQL for your primary data plus Redis as a caching layer is all you will need for a very long time. Do not over-engineer at the start.
Q: Is NoSQL faster than SQL?
Not in general. This is a very common misconception. Redis is fast because it runs in memory, not because it is NoSQL. A well-indexed PostgreSQL instance is extremely fast. The real advantage of NoSQL is not raw speed on a single machine, it is the ease of horizontal scaling when you need to spread data across many machines.
Q: Should I use MongoDB for my first project?
If you are building something as a learning project or rapid prototype, MongoDB is completely fine. But if you are building something you plan to grow, take the extra week to design a proper SQL schema. You will thank yourself later when you need to run real queries and enforce real constraints.
Key Takeaways
SQL is structured, schema-first, and gives you ACID properties. Use it when your data is well-defined and consistency is critical, payments, orders, accounts, medical records, legal data. It scales vertically through hardware upgrades and horizontally through read replicas, then sharding, but sharding is complex and comes with real costs.
NoSQL is flexible, schema-optional, and prioritises availability and scale over strict consistency. It scales horizontally by design, adding nodes is the intended path, not a painful workaround. This is why high-volume, high-velocity data at companies like Netflix, Instagram, and Uber lives in NoSQL systems.
The scaling difference is fundamental: SQL scales by making one machine better. NoSQL scales by adding more machines. Both work. SQL scaling gets painful at extreme scale. NoSQL scaling is built for it.
Most real systems use both SQL and NoSQL together. The goal is matching the tool to the data, not picking a religion.
