"SQL or NoSQL?" comes up in almost every design interview. The answer that actually scores points is never a personal preference. It's a mapping from what your queries look like to which storage engine handles that shape best. This lesson walks through that mapping, what a database index actually is, why two common storage designs behave so differently, and the transaction guarantees you're already relying on, whether you know it or not.

Picking a store from the access pattern

Start from the questions you'll actually ask the database, not from whichever technology you've heard of.

Three things worth saying out loud when you make this choice.

"Postgres until proven otherwise." A well-run Postgres database handles JSON documents, full-text search, location data, and time-series data reasonably well all at once. Running one system you actually know how to operate beats running four you don't. Reaching for Cassandra at 200 requests a second is treated as a mistake, not a strength.

Using more than one kind of database at once is normal, but each one has a real cost. Every extra store is one more thing to back up, monitor, secure, and keep in sync with the others. Justify each one you add.

When you do use two stores, say clearly which one is the source of truth. For example: "Postgres holds the real data; Elasticsearch is a search index rebuilt from a stream of Postgres's own changes, and it might lag behind by about a second."

A one-line comparison of three popular wide-column and document stores. Cassandra has no single leader, spreads writes across many equal machines, and lets you tune correctness per request; it's hard to beat for write-heavy systems spread across regions. DynamoDB is a managed service shaped the same way, but it demands careful choice of the key you partition by. MongoDB has a leader-based design, is pleasant to build with day to day, and needs extra care once your data starts looking more like related tables than standalone documents.

Remember: start from the query you need to answer, not from a favourite database. "Postgres until proven otherwise" is a strong, defensible default.

Indexes: what they are and when they hurt

Think of a textbook with no index at the back. To find every page that mentions "mitosis," you'd have to read the whole book cover to cover. An index at the back tells you the exact page numbers, so you jump straight there instead. A database index works the same way: it's a separate structure that maps a column's values to where the matching rows actually live, so the database can jump straight to them instead of reading every single row.

When an index covers several columns, the order you list them in matters enormously. An index on columns (a, b, c) speeds up a query that filters on a alone, one that filters on a and b together, and one that filters on all three. This is called a left prefix, meaning it only helps when your query's filter starts matching from the leftmost column onward. It does not speed up a query that filters on b alone, skipping a. A rule worth remembering: put the columns you filter for an exact match first, and put the column you sort or filter by a range on last.

A covering index stores every column a query needs directly inside the index itself, so the database never has to go fetch the actual row separately. In Postgres this is written with INCLUDE (...). This turns two separate reads into one, and it's often the first thing to try when a specific query is running too slowly.

Indexes are not free. Here is what they cost you:

• Every time you insert, update, or delete a row, every index on that table has to be updated too. A write-heavy table with eight indexes on it is really doing nine writes for every one you asked for.
• They take up storage space and memory that the table's own frequently-used pages would otherwise use.
• The database's query planner can sometimes choose a worse plan when its internal statistics are out of date.
• Indexing a column with only a few possible values rarely helps. An index on a true-or-false column usually loses to simply scanning the whole table, because jumping around randomly through an index to fetch half the rows is slower than just reading them in order.

Indexes can silently fail to help in a few common situations: wrapping the column in a function, like WHERE lower(email) = ..., needs a special index built on that exact function; a search pattern starting with a wildcard, like LIKE '%foo', can't use a normal index either; neither can an implicit type conversion, or an OR condition spanning two different columns, which is often better rewritten as two queries combined with UNION.

Remember: a composite index on (a, b, c) only helps queries that filter starting from the left: a alone, a and b together, or all three. It cannot help a query that filters on b alone.

B-tree vs LSM-tree: why write-heavy stores are different

Picture two libraries handling returned books. Library A puts every returned book straight back onto its exact alphabetical shelf spot right away. That's slower per book, but any book is instantly findable afterward. Library B just drops every return onto a cart by the door, and once the cart is full, carries the whole thing to the shelves in one batch. That's much faster per return, but you might have to check both the cart and the shelf to find a specific book. Library A is how a B-tree works. Library B is how an LSM-tree works. This single difference explains almost everything about how a database behaves under reads versus writes.

A B-tree is used by Postgres, MySQL's InnoDB engine, and most relational databases. It's a balanced tree of fixed-size pages, and it updates those pages in place, directly where they already live.

• Reading is predictable: roughly a handful of page reads no matter how big the table gets, and because the lowest level of the tree is kept sorted, scanning a range of values is cheap.
• Writing means finding the right page, changing it, and writing it back. That's a random write to disk, plus an entry in a separate log kept purely to survive a crash. Over time, pages that keep splitting apart become fragmented.
• Best suited to read-heavy or mixed workloads, range queries, and strong correctness on a single machine.

An LSM-tree, short for log-structured merge-tree, is used by Cassandra, RocksDB, LevelDB, HBase, and ScyllaDB. New writes land first in an in-memory table, get appended to a log on disk for safety, and are periodically flushed out into new, unchangeable, sorted files. A background process called compaction later merges those files back together.

• Writing only ever appends data, never rewrites it in place, which is dramatically faster. This is exactly why LSM-based stores dominate write-heavy workloads.
• Reading may have to check the in-memory table plus several of those sorted files on disk, a cost called read amplification. This is softened by keeping a Bloom filter per file, a small structure that can quickly say "definitely not in this file," and by the ongoing compaction process merging files together.
• The costs: compaction uses real disk and CPU time in the background and can cause sudden slowdowns; a delete is really just a marker called a tombstone, and the space isn't actually freed until the next compaction; and duplicate copies of the same data can pile up temporarily before compaction cleans them up.

The one sentence worth memorising: B-trees make reads fast by paying a small cost on every write. LSM-trees make writes fast by paying that cost later, on reads and during background compaction.

Remember: a B-tree write updates data in place immediately. An LSM-tree write just appends, and cleans up later. That's why LSM-trees win on write-heavy workloads.

Transactions: ACID and isolation levels

Picture transferring 100 rupees from your account to a friend's. Two things have to happen: your balance drops by 100, and your friend's balance rises by 100. If the system crashes right after the first step but before the second, the money has simply vanished. A transaction is the database's promise that a group of changes like this happen together completely, or not at all.

ACID is the set of guarantees a transaction makes, and you should be able to defend each letter:

• Atomicity means every statement in the transaction commits, or none of them do.
• Consistency here means the transaction moves the database from one valid state to another, respecting whatever rules you've declared, like a column that must never be negative. This is a different "C" from the one in CAP, a topic covered in a later lesson; interviewers like to test whether you know that.
• Isolation means transactions running at the same time don't see each other's unfinished work, at least to the degree the chosen isolation level actually promises.
• Durability means once a transaction is confirmed, it survives a crash, thanks to a safety log, forcing data to disk, and often replication too.

Isolation levels decide which problems can still happen. Learn this table well:

A few terms defined plainly. A dirty read means reading another transaction's work before it's even confirmed. A non-repeatable read means reading the same row twice in one transaction and getting two different answers. A phantom read means running the same range query twice and seeing new rows appear the second time. A lost update means two transactions each read a row, change it, and write it back, and one of those two changes silently disappears. Read Committed, Postgres's own default, does not protect you from a lost update.

The practical rule most teams follow: use Read Committed everywhere by default, and add explicit locking or an atomic write only in the small number of places where a lost update would actually matter.

Choosing between locking ahead of time (pessimistic) and checking afterward (optimistic) is a real trade-off worth naming. Locking ahead of time is right when many people are competing for the same thing at once, like booking the very last seat at a popular event. Checking afterward is right when competition is rare, like editing your own profile, because it never holds a lock and so scales better, at the cost of having to retry the occasional guess that turned out wrong.

MVCC (multi-version concurrency control) is the mechanism behind all of this in Postgres and MySQL. Instead of overwriting a row, a writer creates a new version of it, so readers never have to wait for writers, and writers never have to wait for readers. The cost is that old versions pile up and need periodic cleanup, called vacuuming, and a transaction left running for a long time can hold that cleanup back and let the table bloat.

Remember: Read Committed does not prevent a lost update. Two people reading the same row and both writing back is a real bug, and the fix is one single atomic UPDATE statement, not two separate application steps.

Quick recap