Question 1

What is MVCC (Multi-Version Concurrency Control)?

Accepted Answer

A technique where the database maintains multiple versions of rows, allowing readers to see a consistent snapshot without blocking writers — and writers not blocking readers. MVCC: each write creates a new version with a transaction timestamp. Readers see the latest committed version as of their transaction start — no read locks needed. PostgreSQL uses MVCC extensively. Oracle uses undo segments. Enables high read concurrency.

Question 2

What is a Write-Ahead Log (WAL) and what does it enable?

Accepted Answer

Changes are written to the WAL before being applied to the database — enabling crash recovery (replay the WAL) and replication (stream WAL to replicas). WAL guarantees durability: even if the DB crashes after commit, the WAL can replay the operation on restart. It also enables streaming replication — standbys apply the same WAL. PostgreSQL's WAL is the foundation of its reliability and replication.

Question 3

What is database sharding?

Accepted Answer

Horizontally partitioning data across multiple database instances — each shard holds a subset of rows, enabling scale beyond a single machine. Shard by user_id % N: user 1001 → shard 1, user 1002 → shard 2. Each shard is an independent database. Challenges: cross-shard queries, rebalancing when adding shards, distributed transactions. Used at massive scale (Facebook, Twitter).

Question 4

What is eventual consistency and how does it relate to the CAP theorem?

Accepted Answer

A consistency model where replicas may temporarily diverge but will converge to the same value — an AP trade-off in CAP: choosing Availability + Partition tolerance over strong Consistency. CAP: you can only guarantee 2 of 3. CP systems (ZooKeeper, HBase) reject writes during partitions. AP systems (Cassandra, DynamoDB) accept writes on all nodes and reconcile later. Eventual consistency is the AP choice — acceptable for shopping carts, DNS, social media likes.

Question 5

What is a covering index and why is it significant for query performance?

Accepted Answer

An index that contains all columns needed by a query — the database can satisfy the query entirely from the index without touching the main table (index-only scan). If a query selects columns A, B, C and you have an index on (A, B, C), the DB never reads the main table. This eliminates the most expensive operation in many queries — the table heap fetch. A major optimization for frequently-run queries.

DBMS & SQL — Senior Dev Interview Questions

More Senior Dev Topics

DBMS & SQL at Other Levels