Database Internals Advanced: MVCC Anomalies, Cost Models, Sharding, and Storage Engines

1. MVCC, isolation, and write skew

MVCC gives transactions snapshots of row versions, but a repeatable view of individual rows does not automatically protect every cross-row invariant. Write skew can occur when two transactions read overlapping facts, update different rows, and each leaves the combined invariant false. For example, two doctors each observe that another doctor is on call, then each marks their own row off-call; the rows do not conflict, but the “at least one on-call” rule is violated.

Isolation names differ in implementation. PostgreSQL Repeatable Read uses snapshot isolation and can permit write skew; Serializable adds conflict detection and may abort a transaction that must be retried. Other engines define their levels differently. Protect invariants with a database constraint where expressible, explicit locking of the invariant's coordination row, or serializable transactions with retry handling.

2. B-tree versus LSM-tree

B-trees are common for relational indexes and support ordered scans and range queries. They update pages in place with balancing and write-ahead logging. Log-structured merge designs batch writes into sequential structures and compact them later; they can offer high write throughput but may incur compaction cost and read amplification. “Database uses B-trees” or “LSM is faster” is incomplete: workload, compaction, cache, durability, and query pattern decide the trade-off.

PostgreSQL, MySQL/InnoDB, and Oracle primarily expose B-tree-oriented relational indexes, with additional specialized index types or structures. Distributed engines such as CockroachDB and Spanner add replication and consensus considerations beyond local index format.

3. Cost-based optimizer mistakes

The optimizer compares estimated plan costs using statistics such as row counts, distinct values, histograms, and correlation. A sequential scan can beat an index scan when a predicate returns a large fraction of the table or when table lookups are scattered. A sudden plan regression can result from stale statistics, data skew, parameter selectivity, changed correlation, or a schema/index change.

Compare estimated and actual rows at each plan node. PostgreSQL EXPLAIN (ANALYZE, BUFFERS), MySQL EXPLAIN ANALYZE where available, and Oracle DBMS_XPLAN with actual row-source statistics reveal different evidence. Refresh statistics when justified, test representative bind values, and change one variable at a time. Do not force an index until the estimate problem and workload shape are understood.

Partial and expression indexes encode specific predicates or computed values. A partial index on active rows can reduce size, while an expression index supports a normalized lookup. Match the query expression and collation exactly; expression indexes add write cost and engine-specific migration concerns.

-- PostgreSQL partial index
CREATE INDEX CONCURRENTLY order_open_created_idx
  ON orders (created_at DESC, id) WHERE status = 'open';

-- PostgreSQL expression index for normalized lookup
CREATE INDEX CONCURRENTLY customer_email_lower_idx
  ON customer (lower(email));

MySQL functional-index support depends on version; a generated column plus index is a portable fallback within MySQL. Oracle supports function-based indexes, subject to deterministic-expression and privilege rules. Verify the expression matches the query and inspect the plan after statistics are current.

4. Sharding and consistent hashing

Sharding distributes data and load across nodes using a partition key. A good key spreads writes and aligns with common queries; a bad key creates hotspots or forces cross-shard fan-out. Consistent hashing can reduce key movement when membership changes, but it does not eliminate rebalancing, replication, metadata, or hot-key problems.

Before sharding, measure whether vertical scaling, partitioning within one database, caching, or query/index improvements solve the bottleneck. Shard transactions and joins are harder, and resharding is an operational project. Define routing, migration, cross-shard uniqueness, and failure recovery before adopting the model.

5. Index bloat and safe rebuilds

In PostgreSQL, REINDEX INDEX CONCURRENTLY index_name rebuilds an index while allowing ordinary writes, subject to extra work, disk needs, version restrictions, and possible failure cleanup. Monitor progress and free disk; a concurrent rebuild can temporarily require substantial additional space. For table bloat, inspect dead tuples, vacuum progress, transaction age, and write patterns before rebuilding. Reindexing alone does not solve long snapshots that prevent cleanup.

Connection pools are part of the database capacity plan. Set pool limits across all application replicas, account for administrative and migration connections, and observe pool wait alongside database saturation. An oversized aggregate pool can turn queueing in the app into connection contention in the database.

For a Java service using HikariCP, make pool limits and acquisition timeout explicit rather than letting each replica open an unbounded number of sessions:

spring:
  datasource:
    hikari:
      maximum-pool-size: 16
      minimum-idle: 4
      connection-timeout: 2000

The right maximum is a global budget: multiply by application replicas and workers, then reserve capacity for migrations, monitoring, and failover. A longer connection timeout can hide saturation while requests pile up.

Interview and troubleshooting drills

Interview: Why might PostgreSQL choose a sequential scan despite a usable B-tree index? Discuss selectivity, page locality, stale statistics, parameter values, and actual-vs-estimated rows.

Troubleshooting: A write-heavy table has bloat and slow queries. Inspect transaction age and vacuum, sample plans and buffer reads, check dead tuples and index usage, then schedule a concurrent index rebuild only with disk headroom and a recovery plan.

Challenge: Given a composite index (a, b, c), a query filtering only b and c generally cannot use the leading prefix as efficiently as a query constraining a. Explain skip-scan exceptions as engine/version-specific, and compare adding (b, c) with scanning a large fraction of the table. Validate with actual plans, not index folklore.