Databases

What is it?

A running program keeps its variables in memory, but memory disappears the moment the program stops or restarts — not great for a user's account, posts, or orders. A database is software specifically designed to store data reliably on disk, retrieve it quickly, and keep it consistent even when many things are reading and writing at once.

Almost every real application has a database sitting behind its server, holding the actual persistent data the app depends on.

Explain like I'm 10

If a server is the chef preparing your order, the database is the pantry and fridge — a well-organized place where ingredients (data) are stored so the chef can reliably find and use them, even after the kitchen closes and reopens the next day.

Examples

A server reading from a database

// Simplified example
async function getUser(id) {
  const user = await database.query(
    "SELECT * FROM users WHERE id = ?",
    [id]
  );
  return user;
}

The server doesn't store user data itself — it asks the database, which is responsible for storing and retrieving it reliably.

How it works

A database organizes data (often into tables, or collections of documents), and provides a query language or API to read and write that data. It also manages tricky details automatically — like making sure two simultaneous writes don't corrupt each other, and that data survives a crash or restart.

Why does it exist?

Applications need data to persist reliably — surviving crashes, restarts, and simultaneous use by many users at once. Building that reliability from scratch for every app would be enormously wasteful; databases exist so every application can rely on the same well-tested foundation.

When to use it

Use a database anytime data needs to survive beyond a single request or process — user accounts, orders, posts, anything that must still be there tomorrow, or on a different server entirely.

When not to use it

For data that's only ever needed for the lifetime of a single request — a temporary calculation, a value passed between two functions — a database is unnecessary overhead. Keep that in memory instead.

Common mistakes

• Storing important data only in server memory, losing it whenever the server restarts.

• Not thinking about how a database will scale as data grows into the millions of records.

• Trusting user input directly in a database query, which can lead to serious security issues (like SQL injection).

Practice exercises

1. Easy:

   Explain, in your own words, why a to-do list app needs a database instead of just keeping tasks in the browser's memory.

2. Medium:

   Describe what data you'd store for a simple blog (e.g. posts, authors, comments) and how those pieces might relate to each other.

3. Hard:

   Explain what could go wrong if two users tried to buy the last item in stock at the exact same moment, and how a database might prevent it.

Interview questions

Why can't an application just keep all its data in server memory?

Memory is wiped when a process restarts or crashes, and it doesn't scale across multiple servers — a database provides durable, shared storage instead.

What's the difference between reading and writing data in terms of design concerns?

Reads are typically far more frequent and easier to scale (e.g. via caching or replicas); writes need stronger guarantees around consistency and conflict handling.

What is data persistence?

The property of data surviving beyond the lifetime of the process that created it — e.g. still being there after a server restarts.

What does ACID stand for, and why does it matter for a transaction?

Atomicity, Consistency, Isolation, Durability — a transaction either fully applies or not at all, leaves the data in a valid state, doesn't interfere with other concurrent transactions, and once committed, survives a crash.

Why do multiple statements often need to be grouped into a single transaction, rather than run one at a time?

If a transfer debits one account and credits another as two separate statements, a crash between them could lose money — wrapping both in one transaction guarantees they either both apply or neither does.

What is a database index, and why isn't every column simply indexed by default?

An index is a separate, sorted structure that lets the database find matching rows without scanning the whole table — but every index also has to be updated on every write, and takes up its own storage, so indexing everything slows down writes for little read benefit on rarely-queried columns.

Mechanically, why does an index make a lookup fast where scanning the whole table wouldn't?

Most indexes are a B-tree — a sorted structure the database can binary-search, so finding a match takes roughly log(n) steps regardless of table size, instead of a full table scan that checks every one of the n rows.

What's the difference between optimistic and pessimistic concurrency control?

Pessimistic control locks a row upfront so no other transaction can touch it until it's done; optimistic control lets transactions proceed freely and only checks at commit time whether the data changed underneath it, retrying if it did — better when conflicts are rare.

What is a deadlock, and how does a database typically handle it?

Two transactions each hold a lock the other one needs, so neither can proceed — the database detects this cycle and forcibly aborts one of the transactions (rolling it back) to break the deadlock.

What does normalizing a schema mean, and what's the tradeoff of deliberately denormalizing it?

Normalizing splits data across tables to avoid storing the same fact twice, keeping it consistent by construction; denormalizing intentionally duplicates data to make reads faster (fewer joins), at the cost of having to keep every copy in sync on writes.

What is the N+1 query problem, and how do you fix it?

Fetching a list of N records, then running one additional query per record in a loop to get related data — 1 + N queries where one join or batch load would do. Fixed by eager-loading the related data in a single query (a join, or one WHERE id IN (...) batch fetch).

Why can an ORM sometimes hurt performance compared to hand-written SQL?

An ORM's convenience often hides exactly which queries it's actually running — a chain of object accesses can silently trigger an N+1 query pattern, or fetch far more columns/rows than the code actually needs, and it's easy not to notice until it's slow at scale.

What is connection pooling, and why does it matter?

Opening a new database connection involves a real cost (a TCP handshake and authentication), so a connection pool keeps a set of already-open connections ready to reuse across requests instead of opening and closing one per request.

What does a query's `EXPLAIN` plan tell you, and when would you reach for it?

It shows how the database actually intends to execute a query — whether it uses an index or scans the whole table, and in what order it joins tables — and it's the first thing to check when a specific query is unexpectedly slow.

What's the difference between a primary key and a foreign key?

A primary key uniquely identifies each row within its own table; a foreign key stores another table's primary key to represent a relationship, and the database can enforce that the referenced row actually exists.

What's the difference between OLTP and OLAP databases?

OLTP (Online Transaction Processing) handles many small, frequent reads and writes, like placing an order; OLAP (Online Analytical Processing) runs fewer but much larger queries that scan huge amounts of historical data for reporting — the two workloads are different enough that they're often served by differently-optimized systems.

Why isn't replication a substitute for backups, even though both involve keeping copies of data?

A replica mirrors changes as they happen — including a mistaken delete or corruption on the primary, which replicates just as faithfully as any legitimate write. A backup is a separate, point-in-time snapshot you can restore from after the fact, which replication alone doesn't provide.

Scenario: a `products` query filters by `category` and sorts by `price`, and it's slow at 50 million rows despite an index on `category` alone. What's likely happening?

The index quickly finds the matching rows for that category, but the database still has to sort all of those matches by price separately, often spilling to disk at that volume. A composite index on (category, price) would let it return already-sorted results directly — worth confirming by checking whether the query plan shows a separate sort step.

What is a "dirty read," and what prevents it?

Reading a row that another transaction has changed but not yet committed — meaning it might still be rolled back, so you've read data that never actually existed. Any isolation level stricter than the weakest one (read uncommitted) — read committed and above — prevents it.

What's the difference between a "non-repeatable read" and a "phantom read"?

A non-repeatable read is re-reading the same row twice within one transaction and getting different values, because another transaction updated it in between. A phantom read is re-running the same range query twice and getting a different set of rows, because another transaction inserted or deleted rows matching that filter in between.

When would you reach for vertical scaling of a database versus horizontal scaling (replication or sharding)?

Vertical scaling (a bigger server) is simpler and has no distributed-systems complexity, and works well up to a point — but it has a hard ceiling, cost grows faster than capacity near the top end, and it's still a single point of failure. Horizontal scaling spreads load and risk across multiple machines at the cost of real operational complexity, and is what you reach for once vertical scaling's ceiling or single-server risk becomes the actual constraint.

Scenario: your database's CPU is pegged near 100% during peak hours, mostly from read queries. Name two different fixes and what each costs.

Adding read replicas spreads read load across more machines, at the cost of replication lag and more operational complexity. Adding a caching layer in front of the database avoids hitting it at all for repeat reads, at the cost of occasionally serving slightly stale data. (Scaling the database vertically is a third option, but only buys you until the next ceiling.)

Why is directly building a SQL query string from user input dangerous, and what's the standard fix?

If user input is concatenated straight into a query, an attacker can craft input that changes the query's actual meaning (SQL injection) — for example, ending the intended string early and appending their own clause. The fix is parameterized queries (prepared statements), where user input is always passed as data and can never be interpreted as part of the query's structure.

What is a write-ahead log, and why do databases use one?

Before actually modifying data on disk, the database first appends the intended change to a durable, sequential log. If it crashes mid-write, it can replay that log on restart to recover to a consistent state, rather than losing or corrupting whatever it was in the middle of doing — it's the mechanism behind the "D" (durability) in ACID.

Scenario: you need to add a `NOT NULL` column to a table with 200 million existing rows, with zero downtime allowed. What has to be handled carefully?

You generally can't add a NOT NULL column with no default in one step on a table that size without a long-held lock or table rewrite. The safer path is adding the column as nullable first, backfilling existing rows in small batches to avoid locking the whole table at once, and only adding the NOT NULL constraint once every row has a value.

What's the difference between a database-enforced constraint (like a foreign key or unique constraint) and the same rule checked only in application code?

A database constraint holds no matter what writes the data — a bug, a different service, or a race between two concurrent writes can't violate it. An application-level check can be bypassed by any other path to the same database, and is itself vulnerable to a race condition between the check and the write it's guarding.