SQL vs NoSQL

This is probably the most common database interview question. “When would you pick SQL vs NoSQL?” Let’s break it down properly.

In simple language, SQL databases store data in structured tables with a predefined schema. NoSQL databases store data in flexible formats — documents, key-value pairs, graphs, or wide columns.

The Core Differences

Schema: Rigid vs Flexible

With SQL, we define the schema upfront. Every row in a table must follow it. Want to add a new field? We need to run an ALTER TABLE migration.

-- SQL: Schema is defined upfront
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INT
);

-- Every user MUST have these columns. No exceptions.
-- Adding a "phone" field later requires a migration:
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

With NoSQL (say MongoDB), documents in the same collection can have completely different fields. One user document might have a phone field, another might not. No migration needed.

-- MongoDB: No predefined schema
-- Document 1:
-- { "name": "Manish", "email": "manish@example.com", "age": 26 }
--
-- Document 2 (same collection, different fields):
-- { "name": "Rahul", "email": "rahul@example.com", "phone": "+91..." }
--
-- Both live happily in the same collection

Scaling: Vertical vs Horizontal

Vertical scaling means getting a bigger, faster machine. More RAM, more CPU, faster disk. This is how most SQL databases scale. There’s a ceiling though — machines can only get so big.

Horizontal scaling means adding more machines to share the load. NoSQL databases are designed for this from the ground up. Data gets distributed (sharded) across multiple nodes.

Now, this isn’t a hard rule anymore. PostgreSQL can scale horizontally with tools like Citus. MongoDB added ACID transactions. The lines are blurring. But the default design philosophy still differs.

ACID vs BASE

SQL databases follow ACID — every transaction is Atomic, Consistent, Isolated, and Durable. When we transfer money between accounts, either both updates happen or neither does. No partial state.

NoSQL databases typically follow BASE — Basically Available, Soft state, Eventually consistent. The data might be slightly stale for a moment, but the system stays available and eventually catches up.

In simple language: ACID says “everything is perfectly correct, always.” BASE says “things will be correct eventually, but we prioritize staying online.”

We cover both in detail in their own notes.

When to Pick SQL

• Our data has clear, well-defined relationships (users → orders → products)
• We need strong consistency and ACID transactions (banking, payments)
• We know our schema and it won’t change dramatically
• We need complex queries with JOINs, aggregations, subqueries
• Our data fits on one (admittedly large) machine

When to Pick NoSQL

• Our schema evolves rapidly (startup MVP, experimental features)
• We need to scale to massive read/write throughput across many servers
• Our data is semi-structured or varies by record (user profiles, product catalogs)
• We need low-latency reads and can tolerate eventual consistency
• We’re building real-time features like chat, feeds, or gaming leaderboards

The Dirty Secret: Most Apps Don’t Need NoSQL

Here’s the thing most people won’t tell us in an interview — for 90% of applications, PostgreSQL is more than enough. It handles JSON, full-text search, and can scale pretty far vertically.

NoSQL makes sense at massive scale (think Twitter, Netflix, Uber) or when the data genuinely doesn’t fit a relational model. For a typical web app with a few million rows? PostgreSQL all day.

Hybrid Approaches

Modern databases are blurring the lines:

• PostgreSQL has jsonb columns for document-style storage inside a relational database
• MongoDB added multi-document ACID transactions (since v4.0)
• CockroachDB and YugabyteDB give us SQL + horizontal scaling (NewSQL)
• FaunaDB combines document flexibility with relational guarantees

The real skill isn’t knowing “SQL good, NoSQL bad” (or vice versa). It’s knowing when each approach makes sense and being able to justify the decision.

Interview Tip

Never say “NoSQL is better because it scales.” That’s a red flag. Instead, talk about the specific trade-offs for the specific use case. Interviewers love hearing “for this use case, I’d pick X because…” followed by concrete reasoning.