Skip to content

Interview PrepFoundations · Basics

Database Fundamentals ​

1. What is the difference between SQL and NoSQL databases? ​

The short answer

SQL databases, such as PostgreSQL, MySQL and SQL Server, store data in tables with a fixed schema, link tables with keys, are queried with SQL, and give strong guarantees through transactions. NoSQL databases is a loose name for everything else: document stores such as MongoDB, key-value stores such as Redis and DynamoDB, wide-column stores such as Cassandra, and graph databases such as Neo4j. They trade some of SQL's flexibility in querying, or its strict consistency, for flexible schemas, easy horizontal scaling, or speed for one access pattern. Choose by your data's shape and how you will read it.

SQL, relationalNoSQL
Data modelTables, rows, columnsDocuments, key-values, columns, graphs
SchemaFixed, enforced by the databaseFlexible, often enforced by the app
RelationshipsJoins with foreign keysEmbedded data or app-side joins
TransactionsMulti-row and multi-table, ACIDVaries: often per document or per key
ScalingMostly vertical, plus read replicas; sharding is harderOften built for horizontal scaling
Query flexibilityAny question, with SQLFast for the access patterns you designed for

The four NoSQL families:

FamilyExampleGood for
DocumentMongoDB, FirestoreRecords read as a whole, such as a product with its variants
Key-valueRedis, DynamoDBLookup by key at huge scale: sessions, carts, counters
Wide-columnCassandra, ScyllaDBHuge write volumes, time series, event logs
GraphNeo4jMany-hop relationships: social graphs, fraud rings

The answer that impresses. "I default to PostgreSQL, because most business data is relational and needs transactions, and Postgres also handles JSON documents well. I reach for a NoSQL store when one access pattern dominates and must scale beyond one machine, such as a session store in Redis or an event log in Cassandra." Many large systems use both, each for what it does best.

2. What is an ORM, and what is an ODM? ​

The short answer

An ORM, object-relational mapper, lets you work with relational database rows as objects in your programming language: you call orders.findMany() instead of writing SELECT by hand, and it maps tables to classes and rows to objects. Examples are Prisma, Drizzle, TypeORM and Sequelize in Node, SQLAlchemy in Python. An ODM, object-document mapper, does the same for document databases, such as Mongoose for MongoDB. They speed up everyday work and add type safety, but can hide slow queries, such as the N+1 problem, behind innocent-looking code.

ts
// Prisma, an ORM: typed queries without writing SQL
const recent = await prisma.order.findMany({
  where: { customerId: 42, status: 'paid' },
  include: { items: true },                                                    // (1)
  orderBy: { createdAt: 'desc' },
  take: 10,
})

// Mongoose, an ODM: schema and model for a MongoDB collection
const Product = model('Product', new Schema({ name: String, priceCents: Number, tags: [String] }))   // (2)
const shoes = await Product.find({ tags: 'running' }).limit(20)
  1. include loads the related items in the same call. The ORM decides how, usually one extra query for all of them.
  2. The ODM adds a schema to a schemaless database, validating documents in your app before they are saved.
GoodWatch out
Less boilerplate, typed resultsHidden N+1 queries (the N+1 question)
Migrations and schema in codeComplex reports are awkward; drop to raw SQL
Safer: parameterised by defaultLoading whole objects when you need two columns

Query builders such as Knex or Kysely sit in between: you write SQL-shaped code with types, and you see exactly what runs.

3. What is database connection pooling, and why is it needed? ​

The short answer

Opening a database connection is slow, often several milliseconds for TCP, TLS and authentication, and each PostgreSQL connection uses a whole server process with several megabytes of memory. A pool opens a small number of connections once and lends them to requests: a request borrows a connection, runs its queries, and returns it. This makes queries faster, and protects the database from thousands of connections when traffic spikes. Size it small, typically 10 to 20 per app instance, and remember the total across all instances must stay under the database's limit.

requestsGET /ordersPOST /cartGET /mewaiting: 3pool, max 5conn 1 · busyconn 2 · busyconn 3 · freeconn 4 · busyconn 5 · freePostgreSQL5 open connectionsborrowreused
A few connections, shared by many requests. The connections stay open, so no request pays the cost of connecting. When all are busy, extra requests wait in line, which protects the database instead of overwhelming it.
ts
import { Pool } from 'pg'

export const pool = new Pool({
  connectionString: config.DATABASE_URL,
  max: 10,                                                                     // (1)
  idleTimeoutMillis: 30_000,                                                   // (2)
  connectionTimeoutMillis: 2_000,                                              // (3)
})

const { rows } = await pool.query('SELECT id, total FROM orders WHERE customer_id = $1', [customerId])   // (4)
  1. At most 10 connections from this instance. With 8 instances, that is 80 against the database's limit, often 100 by default.
  2. Close connections idle for 30 seconds, so a quiet instance gives them back.
  3. Wait at most 2 seconds for a free connection, then fail fast with an error instead of hanging.
  4. pool.query borrows, runs and returns in one call.

Why bigger is not better. A database with 8 CPU cores can only run about 8 queries truly at once. A pool of 200 just makes queries fight each other for CPU and memory. When you have many instances or serverless functions, put an external pooler such as PgBouncer in front of PostgreSQL, so thousands of app connections share a few dozen real ones.

4. What is a database transaction? ​

The short answer

A transaction is a group of database operations that succeed or fail as one unit. You start it with BEGIN, run several statements, and finish with COMMIT to make all changes permanent, or ROLLBACK to undo all of them. The classic example is a transfer: subtract £100 from Asha and add £100 to Ben. If the server crashes in between, the transaction ensures neither change happens, so money is never lost or created. While a transaction runs, other sessions see its changes only according to the isolation level.

ts
export async function placeOrder(customerId: number, items: CartItem[]) {
  const client = await pool.connect()                                          // (1)
  try {
    await client.query('BEGIN')
    const { rows: [order] } = await client.query(
      'INSERT INTO orders (customer_id, status) VALUES ($1, $2) RETURNING id', [customerId, 'pending'])
    for (const item of items) {
      const res = await client.query(
        'UPDATE products SET stock = stock - $1 WHERE id = $2 AND stock >= $1', [item.qty, item.productId])   // (2)
      if (res.rowCount === 0) throw new HttpError(409, `Out of stock: ${item.productId}`)
      await client.query('INSERT INTO order_items (order_id, product_id, qty) VALUES ($1, $2, $3)', [order.id, item.productId, item.qty])
    }
    await client.query('COMMIT')                                               // (3)
    return order.id
  } catch (err) {
    await client.query('ROLLBACK')                                             // (4)
    throw err
  } finally {
    client.release()                                                           // (5)
  }
}
  1. One connection for the whole transaction. BEGIN and COMMIT must run on the same connection, so you cannot use pool.query for each statement.
  2. Check and change stock in one statement, so two buyers cannot both take the last item.
  3. Make everything permanent at once.
  4. Any error undoes every statement, including the order already inserted.
  5. Always return the connection, or the pool slowly runs dry.

5. What are the ACID properties? ​

The short answer

ACID is the set of guarantees a transaction gives. Atomicity: all of it happens or none of it does. Consistency: it moves the database from one valid state to another, never breaking rules such as foreign keys, unique constraints or checks. Isolation: transactions running at the same time do not see each other's half-finished work, to the degree the isolation level promises. Durability: once COMMIT returns, the change survives a crash, because it was written to the write-ahead log on disk first.

PropertyMeansHow the database provides it
AtomicityAll or nothingUndo information; rollback on error or crash
ConsistencyRules always holdConstraints, foreign keys, checks, triggers
IsolationConcurrent work does not interfereLocks and MVCC snapshots (question 8)
DurabilityCommitted means savedWrite-ahead log flushed to disk before COMMIT returns

Write-ahead logging in one sentence. Before changing the data files, the database appends a record of the change to a log and flushes the log to disk. After a crash, it replays the log, so every committed change is recovered.

"Consistency" here is not the C in CAP. ACID consistency means your data rules hold. CAP consistency means every reader sees the latest write (question 7). Interviewers like this distinction.

6. What is the difference between ACID and BASE? ​

The short answer

ACID describes strict transactional guarantees, typical of relational databases: every committed change is immediately visible and correct. BASE, Basically Available, Soft state, Eventually consistent, describes systems that stay available and scale across many machines by relaxing that: replicas may briefly disagree, and data converges to a consistent state shortly after writes stop. Banking balances want ACID. A social feed's like count can be BASE. Many modern databases let you choose per operation.

ACIDBASE
PriorityCorrectness nowAvailability and scale
After a writeEvery reader sees itSome readers may see old data briefly
TypicalPostgreSQL, MySQL, SQL ServerCassandra, DynamoDB with eventual reads, DNS
Good forPayments, inventory, bookingsFeeds, counters, analytics, caches

Eventual consistency, concretely. You change your profile photo. For a second or two, a friend on another continent still sees the old one, because their nearest replica has not received the update yet. Nothing is wrong; it will catch up. That is acceptable for a photo, and not for a bank balance.

7. What is the CAP theorem, and what trade-offs does it describe? ​

The short answer

The CAP theorem says that when the network between parts of a distributed database fails, a partition, the system must choose between consistency, where every read returns the latest write or an error, and availability, where every request gets a non-error response, possibly with stale data. You cannot have both during a partition. Partitions will happen, so the real choice is "CP or AP". The PACELC extension adds that even without partitions, you trade latency against consistency.

London nodebalance = 50, newNew York nodebalance = 100, oldnetwork partitionCP choiceNew York refuses:"try again later"AP choiceNew York answers:100, possibly stalea reader asks New Yorkfor the balance
When the link breaks, pick one. The New York node cannot know about London's write. It can refuse to answer, staying consistent, or answer with what it has, staying available. No design avoids this choice during a partition.
ChoiceDuring a partitionExample systemsGood for
CPSome requests failPostgreSQL with synchronous replicas, etcd, ZooKeeper, SpannerPayments, inventory, leader election
APAnswers may be stale; conflicts merged laterCassandra, DynamoDB with eventual reads, DNSCarts, feeds, presence, caches

Common misreading. CAP does not say "pick any two of three" at all times. Without a partition, a system can be both consistent and available. And many databases are tunable: Cassandra can do quorum reads and writes for stronger consistency at the cost of availability.

PACELC in one line. If there is a Partition, choose A or C; Else, choose Latency or Consistency. A synchronous replica in another region makes every write slower, even on a perfect network.

8. What are database isolation levels? ​

The short answer

Isolation levels decide how much concurrent transactions can see of each other's work. The SQL standard defines four, from weakest to strongest. Read Uncommitted may see other transactions' uncommitted changes. Read Committed sees only committed data, but each statement may see newer data than the last. Repeatable Read sees one consistent snapshot for the whole transaction. Serializable guarantees the result is as if transactions ran one at a time. Stronger levels prevent more anomalies but cause more waiting or retries. PostgreSQL defaults to Read Committed; MySQL's InnoDB defaults to Repeatable Read.

LevelDirty readNon-repeatable readPhantom readNotes
Read UncommittedPossiblePossiblePossiblePostgreSQL treats it as Read Committed
Read CommittedNoPossiblePossiblePostgreSQL default
Repeatable ReadNoNoPossible by standardPostgreSQL also prevents phantoms; MySQL default
SerializableNoNoNoTransactions may fail and must be retried

How PostgreSQL does it: MVCC. Multi-version concurrency control keeps several versions of a row. A writer creates a new version instead of overwriting, so readers keep reading the old version without waiting. Each transaction, or each statement in Read Committed, gets a snapshot, a view of which versions were committed when it started. Readers never block writers, and writers never block readers. The cost is old versions that must be cleaned up later by VACUUM.

ts
await client.query('BEGIN ISOLATION LEVEL SERIALIZABLE')
// ... reads and writes ...
await client.query('COMMIT')   // may throw: could not serialize access, error code 40001

At Serializable, PostgreSQL aborts one of two conflicting transactions with error code 40001. Your code must catch it and run the whole transaction again (handling transaction failures).

9. What are dirty reads, non-repeatable reads, and phantom reads? ​

The short answer

They are the anomalies isolation levels prevent. A dirty read sees another transaction's change before it commits, which may then be rolled back, so you acted on data that never existed. A non-repeatable read reads the same row twice in one transaction and gets different values, because someone committed a change in between. A phantom read runs the same query twice and gets a different set of rows, because someone inserted or deleted matching rows in between. Two more to know are the lost update and write skew.

dirty readB: UPDATE stock = 0 (not committed)→ A: reads stock = 0→ B: ROLLBACK, stock is really 7A acted on a value that never existednon-repeatable readA: reads price = 10→ B: UPDATE price = 12, COMMIT→ A: reads price = 12the same row changed inside one transactionphantom readA: COUNT today's orders = 5→ B: INSERT an order, COMMIT→ A: COUNT again = 6the set of matching rows changed
Three ways concurrent work leaks in. Each line runs left to right in time. Read Committed stops the first. Repeatable Read stops the second, and in PostgreSQL the third too. Serializable stops all of them, plus write skew.

Two anomalies interviewers add:

AnomalyWhat happensFix
Lost updateTwo transactions read stock 5, each writes 4; one sale is lostUpdate in one statement, SET stock = stock - 1, or lock the row, or optimistic version check
Write skewTwo doctors each check "another doctor is on call", and both go off callSerializable, or lock the rows being checked with SELECT … FOR UPDATE

The lost update is the one you will meet in real code:

ts
// wrong: read, then write, with a gap in between
const { rows: [p] } = await client.query('SELECT stock FROM products WHERE id = $1', [id])
await client.query('UPDATE products SET stock = $1 WHERE id = $2', [p.stock - 1, id])   

// right: let the database do the arithmetic atomically
await client.query('UPDATE products SET stock = stock - 1 WHERE id = $1 AND stock > 0', [id])

Combine chapters into one PDF

Pick the chapters you want. They print in the order you pick them.

Interview Prep
Databases & Caching
Foundations
SQL
Performance
Concurrency
Scaling
Caching
Other topics
Nothing picked yet