SQL, NoSQL, and Indexes

Intuition: a card catalog or a warehouse of boxes

Picture two libraries. The first has a strict catalog: every book has a card, a shelf, and a number, and every checkout is recorded in the log atomically. A book can't be checked out to two people at once. That's a relational database (PostgreSQL, MySQL).

In the second library, books sit in boxes, and each box has a key written on it. You need box user:42, so you just grab it without checking any log. It's fast, and the boxes can be spread across a hundred warehouses. That's NoSQL.

Neither library is better than the other. They simply solve different problems. In an interview, nobody cares about your favorite technology; what counts is being able to explain why you picked this one.

Relational databases and ACID

Data lives in tables with a strict schema. Relationships between tables are defined with foreign keys, and data is pulled together with JOIN. The relational database's trump card is transactions with ACID guarantees:

• A — Atomicity. A transaction either completes in full or doesn't happen at all. A money transfer can't debit one account without crediting the other.
• C — Consistency. After a transaction, all constraints hold: UNIQUE, FOREIGN KEY, CHECK.
• I — Isolation. Concurrent transactions don't see each other's half-finished data.
• D — Durability. Once the database says "committed," the data survives a server crash.

💡 In an interview: "For orders and payments I'd go with PostgreSQL: we need transactions and integrity. It's harder to scale horizontally, but at the start one primary with read replicas is plenty."

NoSQL: four families

NoSQL databases usually give up some guarantees (a strict schema, JOINs, sometimes immediate consistency) in exchange for easy horizontal scaling and speed.

Type	Examples	Data model	When to use it	What you pay
Relational	PostgreSQL, MySQL	Tables, relationships	Money, orders, complex relationships and reports	Sharding is hard
Document	MongoDB	JSON documents	Profiles, catalogs with varying structure	Weak JOINs, possible duplication
Key-value	Redis, DynamoDB	key → value	Cache, sessions, counters	Lookups by key only
Wide-column	Cassandra, HBase	Rows with column families	A huge stream of writes: logs, events, metrics	Queries have to be designed up front
Graph	Neo4j	Nodes and edges	Social graphs, recommendations, "friends of friends"	Niche, scales worse

Real systems often combine databases (polyglot persistence):

⚠️ Don't drag four databases into every design. Every extra database is one more source of failures and one more system to maintain. Add a database only when you can name a concrete reason.

Indexes: a book's index

You're looking for the word "transaction" in a book. Without an index, you flip through all 800 pages: in a database, that's a full scan. With the alphabetical index at the back, you jump straight to the right page: that's an index.

In almost every SQL database, the default index is a B-tree. It's a balanced tree where values are kept sorted and every node holds hundreds of keys. A search goes from the root down and throws out almost every option at each step:

In reality, a B-tree node is a disk page with hundreds of keys, so the tree ends up very shallow. Let's do the math:

Parameter	Value
Rows in the table	100,000,000
Keys per node	~500
Tree depth	log₅₀₀(10⁸) ≈ 3–4 levels
Page reads with an index	~4
Reads without an index	all ~1,000,000 pages

Besides exact-match lookups, a B-tree gives you ranges (BETWEEN, >) and sorting (ORDER BY): the data in it is already ordered.

Why writes get slower

An index is a separate structure that has to be kept up to date. Every INSERT, UPDATE, or DELETE changes not just the table but all of its indexes. Sometimes that means splitting overflowing tree pages. Five indexes on a table mean roughly six writes instead of one. On top of that, indexes take up space on disk and in memory.

Rule: index the columns that show up in frequent WHERE, JOIN, and ORDER BY clauses. Don't index "just in case," especially on write-heavy tables.

Composite indexes and column order

A composite index (user_id, created_at) works like a phone book: entries are sorted first by last name, and within the same last name, by first name. That gives you the leftmost prefix rule:

Query	Does the (user_id, created_at) index work?
WHERE user_id = 42	✅ yes
WHERE user_id = 42 AND created_at > '2024-01-01'	✅ yes, perfectly
WHERE user_id = 42 ORDER BY created_at DESC	✅ yes, sorting comes free
WHERE created_at > '2024-01-01'	❌ no: it's like searching a phone book by first name only

How to choose the column order:

1. Put equality columns (=) first and range columns (>, BETWEEN) last.
2. Match the order to your most frequent queries.

Example: a user's order feed, newest first.

CREATE INDEX idx_orders_user_created
    ON orders (user_id, created_at DESC);

-- the query uses the whole index: lookup + sorting
SELECT id, total FROM orders
WHERE user_id = 42
ORDER BY created_at DESC
LIMIT 20;

What it sounds like in an interview

"The main store is PostgreSQL: orders need ACID. The main query is a user's order history, so I'll add a composite index on (user_id, created_at): an equality lookup plus sorting for free. The index slows down inserts, but we write about 100 orders a second and read them dozens of times more often, so the trade-off pays off. The click stream goes to Cassandra: millions of writes and no need for JOINs."

That's a good answer: it has a choice, a reason, the load, and the cost of the decision.

Common mistakes

• "NoSQL is faster, so let's use MongoDB." Speed depends on the data model and access pattern, not the label.
• Forgetting transactions where money or inventory is involved.
• An index on every column. Writes slow down and you run out of disk.
• The wrong order in a composite index, like (created_at, user_id) for lookups by user.
• An index on a column with two values (is_active). It barely filters anything, and the database may do a full scan anyway.

Remember the main thing: first look at how the system reads and writes data, then choose the database, and build indexes for specific queries.