Databases & Caching Quiz

A B-tree index is a sorted, shallow tree over the indexed column: a lookup descends a handful of levels (a few page reads) to locate matching rows, instead of reading all 50 million rows. The trade-off is that every INSERT/UPDATE must also maintain the index, which is why you index the columns you query by, not everything.

A composite index is sorted by its leftmost column first — like a phone book sorted by last name, then first name. Searching by first name alone means the matching entries are scattered throughout, so the index can't narrow the search (the 'leftmost prefix' rule). The other three queries all start from last_name and use the index's ordering fully.

Isolation governs what concurrently running transactions can observe of each other — ideally each behaves as if it ran alone. Atomicity is all-or-nothing, durability is crash survival, and consistency is constraint preservation. In practice databases offer tunable isolation levels because perfect isolation (serializable) costs throughput.

READ COMMITTED prevents dirty reads (you never see uncommitted data) but allows non-repeatable reads: data you already read can change under you if others commit mid-transaction. REPEATABLE READ or snapshot isolation fixes it by giving the transaction a consistent point-in-time view. Knowing which anomaly each isolation level permits is a staple senior-level interview question.

The problem is 101 round trips where 1-2 would do — each query pays network latency, and per-row latency dwarfs per-row query cost. Batch the lookup with a JOIN or an IN clause (or your ORM's eager loading). Indexes and bigger pools don't remove the round trips; they just make each of the 101 slightly cheaper.

Repeated sequential scans over a working set just larger than the cache evict every entry moments before it would be needed again — a 0% hit rate, the classic 'sequential flooding' pathology. LRU thrives on temporal locality (hot keys, recent bursts, Zipf traffic). This is why databases often use scan-resistant variants like LRU-K or CLOCK for their buffer pools.

Write-through updates the cache and the underlying store in the same synchronous operation: higher write latency, but durable and consistent. Write-back acknowledges after updating only the cache and flushes later — fast, but a crash can lose acknowledged writes. Choose based on whether the data is re-derivable; for payments, write-back is the wrong answer.

The fix is to ensure only one flight recomputes: a lock/single-flight around the rebuild, serving slightly stale data during recompute, or probabilistic early refresh before expiry (jittered TTLs help across many keys). Lowering TTL makes stampedes more frequent. This failure mode is a favorite interview follow-up to any caching answer.

Redis serves everything from memory with O(1)-ish key lookups and a minimal command protocol — no parsing SQL, planning, buffer-pool misses, or disk reads. A relational database does far more work per query because it offers far more (arbitrary queries, transactions, durability by default). That's also the caveat: RAM is expensive and Redis durability is configurable, not assumed.

Hash partitioning spreads keys evenly, but it can't spread the traffic of a single key — one partition ends up serving the celebrity while others idle. Mitigations include key salting (splitting one hot key into several sub-keys), dedicated caching for hot entities, or read replicas of the hot partition. Distinguishing data skew from traffic skew is the mark of a strong answer.

Keys cached at the same moment (a deploy, a cache flush, a traffic spike) with identical TTLs expire in the same instant, sending a synchronized wave of misses to the database. Jitter spreads the expirations over a window, smoothing the recompute load. It's the many-keys cousin of the single-key stampede problem.

A new Postgres connection costs a TCP handshake, TLS, authentication, and a forked backend process — many milliseconds and real server memory. Pools keep a bounded set of warm connections that requests borrow and return, which both removes per-request setup cost and caps concurrent connections so a traffic spike can't exhaust the database. The pool size cap is itself a protection mechanism.

A hash index scatters keys uniformly, destroying order — perfect for WHERE id = 42, useless for WHERE created_at > '2026-01-01' or ORDER BY price. B-trees keep keys sorted in wide, shallow nodes sized to disk pages, so equality, ranges, prefixes, and sorted scans all walk the same structure in O(log n) page reads. Since most workloads mix equality with ranges, B-tree is the default everywhere.

Normally an index lookup yields row pointers that the database must chase into the table (heap) to fetch other columns — often the dominant cost. When the index itself contains every column the query needs (a 'covering index'), that second step disappears entirely. Adding one or two frequently-read columns to an index (INCLUDE clauses in Postgres/SQL Server) to make hot queries index-only is a standard optimization move worth naming in interviews.

Optimistic concurrency bets that conflicts won't happen: read the version, do the work, and write only if the version is unchanged (the UPDATE matches 0 rows otherwise, triggering a retry). Under low contention this is nearly free — no locks held during user think-time. Under high contention on the same rows it degrades into constant retries, and pessimistic locking (SELECT ... FOR UPDATE) that serializes writers becomes the better trade.

Each transaction holds what the other needs — a circular wait that neither can exit, which is a deadlock. Databases detect the cycle and kill one transaction (a retryable error the application must handle). Prevention is ordering: if every code path locks rows in the same order (e.g., always ascending by primary key), a cycle can't form. 'Lock in a consistent order and keep transactions short' is the expected two-part answer.

Replicas fan reads out, but every write still flows through one primary and is then replayed on every replica — total write capacity doesn't grow at all. When writes are the bottleneck, the options change: sharding the write path, batching, or moving hot counters to a store built for it. Spotting 'replicas scale reads, sharding scales writes' quickly is a reliable system-design signal.

A transaction's locks are released only at commit/rollback, so a transaction that does network calls mid-flight (the classic sin: calling a payment API inside a DB transaction) blocks every writer that touches the same rows for the full duration. In MVCC databases, long transactions also pin old row versions, bloating tables and delaying vacuum. Do the slow work outside, keep the transaction to the writes themselves.

The B-tree is ordered by the raw column value; lower(email) is a different value the index knows nothing about, so the database must compute it for every row. Fixes: a functional index on lower(email), a case-insensitive collation/citext column, or normalizing at write time. Leading-wildcard LIKE '%@gmail.com' defeats indexes the same way. When a query is slow despite an index, EXPLAIN and check whether the predicate is actually sargable.

A regular view is just a saved query — it executes fresh every time. A materialized view physically stores the result, so the dashboard reads precomputed rows in milliseconds; the price is refresh management and serving data as-of the last refresh. It's the database-native version of the precompute-and-cache pattern, ideal for analytics where minutes of staleness are acceptable, and a stepping stone to full OLAP pipelines when refresh cost grows.