SQL vs NoSQL

Concept

The choice between SQL (Relational) and NoSQL (Non-Relational) is the most fundamental database decision in system design. It determines how your data is structured, how it scales, and what guarantees you have regarding consistency.

Mental Model

SQL (Relational Databases)

Examples: PostgreSQL, MySQL, Oracle.
How it works: Data is stored in strict tables (rows and columns). Relationships between tables are strictly enforced using Foreign Keys.

Pros:

• ACID Compliant: Guarantees absolute data integrity. If a bank transfer fails midway, the entire transaction rolls back safely.
• Powerful Queries: SQL allows complex JOINS across dozens of tables to generate reports.
• Normalized Data: Data is stored exactly once, reducing redundancy.

Cons:

• Scaling: Very difficult to scale horizontally (sharding). You usually have to scale vertically (buy a bigger server).
• Rigidity: Changing the schema (adding a column to a table with 1 billion rows) can lock the database and cause downtime.

NoSQL (Non-Relational Databases)

Types & Examples:

Type	Description	Examples
Document	Store data as JSON/BSON-like documents	MongoDB, CouchDB
Key-Value	Store data as key-value pairs	Redis, DynamoDB
Wide-Column	Store data in rows with flexible columns	Cassandra, HBase
Graph	Store data as nodes and relationships	Neo4j

How it works: Data is usually stored as JSON-like documents. There are no strict schemas. Relationships are often handled by nesting data directly inside the document.

Pros:

• Horizontal Scalability: Designed from the ground up to be sharded across hundreds of commodity servers. (Highly Available / Partition Tolerant).
• Flexibility: You can add new fields to documents on the fly without running migrations.
• Read Speed: Fetching a user profile doesn’t require 5 complex table JOINS; all the user’s data is embedded in one single JSON document.

Cons:

• Eventual Consistency: Many NoSQL databases sacrifice strict ACID guarantees for speed.
• Data Duplication (Denormalization): Because JOINS are bad/impossible, if a user changes their username, you might have to update 10,000 comment documents where their username was duplicated.

Real-World Usage

• SQL: E-commerce transactions, financial ledgers, ERP systems. Any domain where the data is highly structured and correctness is more important than speed.
• NoSQL: Product catalogs (where every product has completely different attributes), real-time IoT sensor data, massive social media feeds.

Interview Questions

Q: You are building Twitter. Which database would you use for storing user tweets, and which for storing user billing data for Twitter Blue?
A: I would use a NoSQL Wide-Column store (like Cassandra) for the tweets. Twitter generates billions of tweets a day; we need massive horizontal scalability and write-throughput, and we can tolerate eventual consistency (it’s okay if a tweet shows up 1 second late). However, for billing data, I would strictly use a SQL database (PostgreSQL) because financial transactions require perfect ACID compliance to prevent double-billing.

Q: How does a Document NoSQL database handle a Many-to-Many relationship?
A: NoSQL discourages complex relations, but there are two ways:

1. Embedding (Denormalization): If the data is small (e.g., an Article has many Tags), store an array of tag strings directly inside the Article document.
2. Referencing: If the data is large (e.g., a User has many Followers), store an array of follower_id in the User document, and require the application layer to perform a second query to fetch those specific user documents (a manual application-level join).