Databases & Transactions Interview Questions

Summary

ACID describes transaction guarantees: Atomicity (all or nothing), Consistency (constraints hold before and after), Isolation (concurrent transactions don't see each other's partial work, to a configured degree), and Durability (committed data survives crashes).

Longer Answer

Atomicity is implemented with undo/redo logs, durability with write-ahead logs (WAL) flushed to disk, isolation with locks and/or MVCC snapshots. Consistency also depends on your schema constraints and application logic. Many NoSQL systems relax some of these properties for scale (BASE: basically available, soft state, eventual consistency).

Key Points

• Atomicity: all or nothing
• Consistency: constraints hold
• Isolation: levels control anomalies
• Durability: WAL on disk
• BASE as the relaxed alternative

Summary

Read Uncommitted allows dirty reads; Read Committed prevents dirty reads; Repeatable Read prevents non-repeatable reads (and in Postgres/MySQL InnoDB mostly phantoms too); Serializable prevents all anomalies, including write skew, making transactions behave as if run one at a time.

Longer Answer

Defaults: PostgreSQL uses Read Committed, MySQL InnoDB uses Repeatable Read. Anomalies: dirty read (seeing uncommitted data), non-repeatable read (a row changes between reads), phantom (new rows appear in a range), lost update, and write skew (two transactions each read and then write based on stale assumptions, e.g., two doctors both going off call). Serializable in Postgres uses SSI and may abort transactions with serialization failures that clients must retry.

Key Points

• Read Committed (Postgres default)
• Repeatable Read (MySQL default)
• Serializable prevents write skew
• Retry serialization failures

Comparison / Tradeoffs

Summary

Most indexes are B-trees that keep column values sorted with pointers to rows, turning full-table scans into logarithmic lookups for equality, range and ORDER BY queries. They cost extra storage and slow down writes, because every insert/update must maintain them.

Longer Answer

Index columns used in WHERE, JOIN and ORDER BY with good selectivity. Composite index column order matters (leftmost prefix rule). Covering indexes include all needed columns so the table isn't touched. Functions on columns (WHERE lower(email) = ...) need expression indexes. Verify with EXPLAIN ANALYZE and remove unused indexes.

Key Points

• B-tree: sorted, log-time lookups
• Help reads, cost writes
• Composite: leftmost prefix
• Covering and expression indexes
• Verify with EXPLAIN

Summary

N+1 happens when code loads a list (1 query) and then lazily queries a relation for each item (N queries), multiplying round trips. Fix it with eager loading (JOIN or batched IN queries), DataLoader-style batching, or selecting needed data in one query.

Longer Answer

Every ORM has tools: selectinload/joinedload (SQLAlchemy), with() (Eloquent), JOIN FETCH/@EntityGraph (JPA), include (Prisma), prefetch_related/select_related (Django). Detect it with query logging, APM traces or test assertions on query counts.

Key Points

• 1 + N round trips
• Eager or batched loading
• DataLoader for GraphQL
• Detect via logs/traces/tests

Summary

Choose relational (PostgreSQL, MySQL) for structured data with relationships, transactions and flexible querying; choose NoSQL for specific access patterns: document stores (MongoDB) for flexible nested documents, key-value/wide-column (DynamoDB, Cassandra) for massive scale with known queries, graph databases for relationship traversal, and search engines for full-text search.

Longer Answer

Start from access patterns, consistency needs, scale and team expertise. Postgres covers many 'NoSQL' needs (JSONB, full-text search, extensions). Polyglot persistence is common: a relational source of truth plus Redis, a search index and an analytics store.

Key Points

• Relational: transactions + flexible queries
• Document: flexible nested data
• KV/wide-column: scale with known access
• Start from access patterns
• Polyglot persistence

Summary

A pool keeps a set of open database connections that requests borrow and return, avoiding the cost of opening connections per request and capping concurrent load on the database.

Longer Answer

Size so that pool_size × app instances stays below the database's max connections (minus headroom for admin/migrations). Bigger isn't better: databases perform best with a modest number of active connections (often ~2–4× CPU cores). Use external poolers (PgBouncer, RDS Proxy) when you have many instances or serverless functions. Set timeouts for acquiring connections.

Key Points

• Reuse connections
• Pool × instances < DB max
• Modest pools perform best
• PgBouncer/RDS Proxy at scale
• Acquire timeouts

Summary

Use the expand/contract pattern: first make backward-compatible additions (new nullable columns, new tables), deploy code that writes to both or reads the new shape, backfill data in batches, switch reads, then remove old columns in a later release.

Longer Answer

Avoid long locks: add indexes concurrently (CREATE INDEX CONCURRENTLY), avoid table rewrites on huge tables (adding a column with a volatile default), set lock timeouts, and test migrations against production-sized data.

Key Points

• Expand → migrate → contract
• Batched backfills
• Concurrent index creation
• Lock timeouts
• Old and new code must coexist

Summary

Use the saga pattern: a sequence of local transactions where each step publishes an event or command, and failures trigger compensating actions (refund, release inventory). Combine with the transactional outbox to publish events reliably and idempotent consumers.

Longer Answer

Two-phase commit (2PC) across services is rarely used because it couples availability and blocks on coordinator failures. Sagas can be choreographed (events) or orchestrated (a coordinator such as Temporal or Step Functions).

Key Points

• Sagas with compensations
• Outbox for reliable events
• Idempotent consumers
• Avoid 2PC across services