Databases

Databases

Indexes, isolation, storage engines, shard and partition keys, and zero-downtime migrations you can ship without a maintenance window.

• Database Replication — Leader-Follower, Multi-Leader & Leaderless

  6 studies
  1. 1.Database Replication — Leader-Follower, Multi-Leader & Leaderless QuorumsReplication keeps the same data on several machines for durability, read scale, or multi-region latency. The three shapes are leader-follower, multi-leader, and leaderless. Who may accept a write and when the client hears committed set lag, RPO, failover risk, and whether you must resolve conflicts.
  2. 2.Sync vs Async vs Semi-Sync Replication — Durability, RPO & Physical vs Logical LogsAsync, semi-sync, quorum, and fully sync commit modes trade latency for RPO. Postgres synchronous_commit levels and physical versus logical logs (plus replication slots) decide what a failover can lose and what CDC can see.
  3. 3.Replication Lag & Session Guarantees — Read-Your-Writes, Monotonic Reads & Consistent PrefixAsync replicas introduce read-your-writes, monotonic-read, and consistent-prefix anomalies. Fix them with LSN/GTID tokens, sticky replicas, and bounded-staleness routing — measure lag correctly first.
  4. 4.Failover & Split Brain — Detection, Promotion, Fencing & Lost WritesFailover is detect, elect, pick the most caught-up replica, fence the old leader, promote, repoint, and rewind. Split brain without fencing loses or duplicates writes. Patroni, managed HA, and GitHub 2018 make the trade-offs concrete.
  5. 5.Multi-Leader & Active-Active — Conflict Detection, LWW, CRDTs & Home RegionsMulti-leader and active-active give local writes in every region and create conflicts. Last-writer-wins can silently drop data; version vectors, CRDTs, and home-region routing are the real tools.
  6. 6.Leaderless Replication — N/R/W Quorums, Hinted Handoff, Read Repair & Anti-EntropyLeaderless (Dynamo-style) replication uses N/R/W quorums, hinted handoff, read repair, and Merkle anti-entropy. R+W>N is not linearizability. Tombstones and clock-based LWW are the usual footguns.

• Database storage engines

  6 studies
  1. 1.Database Storage Engines — WAL, B-Trees & LSM TreesStorage engine = on-disk layout + recovery; B-Tree vs LSM families share a WAL durability spine; pick by write:read, latency SLO, and vacuum/compaction ops cost.
  2. 2.Write-Ahead Log — Durability, Checkpoints & Crash RecoveryForce log before data (ARIES-style); LSN, checkpoints, REDO/UNDO; sync vs group commit vs async; torn pages / doublewrite.
  3. 3.B-Tree Internals — Pages, Splits & Buffer PoolPages, fanout, leaf/internal, splits; buffer pool caches dirty pages async; WAL still required; Postgres/InnoDB.
  4. 4.LSM Trees — MemTable, SSTables & CompactionMemTable → immutable → flush SSTable; leveled vs size-tiered compaction; blooms; RocksDB/Cassandra model.
  5. 5.Write/Read/Space Amplification — B-Tree vs LSM TradeoffsQuantify WA/RA/SA; B-Tree lower cached RA vs LSM sequential writes + compaction WA; OLTP vs ingest.
  6. 6.fsync, Group Commit & Durable LatencyOS page cache lie; fsync/fdatasync; group commit coalesces commits; latency vs throughput; disk-lies / BBWC.

• DB Sharding & Partitioning

  6 studies
  1. 1.Database Sharding & Partitioning — Keys, Hotspots & RebalancingVertical scaling and a single primary eventually hit CPU, IOPS, storage, or write-throughput walls. Sharding splits data across nodes for independent write capacity. Table partitioning splits one table inside an engine for prune and maintenance. This hub is the map for when each is enough, how a key avoids hotspots, why scatter-gather explodes cost, and how online resharding works.
  2. 2.Partition Strategies — Range, Hash, List & CompositeBefore you invent an application shard layer, master in-engine partitioning: range, hash, list, and composite. The same vocabulary maps onto Vitess, Citus, and TiDB. Pick the strategy from the access pattern. A wrong one creates hotspots, immovable partitions, or queries that never prune.
  3. 3.Shard Keys — Hotspots, Fan-out Queries & CardinalityThe shard key is the product decision you cannot easily undo. Evaluate cardinality, skew, stability, and whether the hot transaction stays on one shard. Low-cardinality columns, monotonic time, and celebrity keys become hotspots. Queries that omit the key become fan-out.
  4. 4.Cross-Shard Queries — Scatter-Gather, Aggregations & AvoidanceThe day you shard, any query without the shard key becomes a scatter-gather. Latency is the slowest shard plus the merge, and every such query multiplies load by N. Merge sums and counts, never averages of averages. Keep scatter off the OLTP hot path.
  5. 5.Resharding & Rebalancing — Dual-Write, Online Moves & Consistent Hash RingsIf you cannot reshard, you cannot shard safely. Online moves are dual-write plus backfill plus cutover, engine range splits, or a consistent-hash vnode remap. Modulo-N moves most keys when N changes. A ring moves a slice. This is a data-plane move, not a schema migration.
  6. 6.Sharding Failure Modes — Orphans, Partial Commits, Dual-Writes & IdempotencySharding turns single-node ACID into a distributed problem. Orphan rows, partial multi-shard commits, dual-write divergence, lost updates at cutover, and missing idempotency keys are the failures seniors name. Prefer one shard per transaction. Use a saga when you cannot. Two-phase commit is the rare exception.

• Elasticsearch & OpenSearch

  6 studies
  1. 1.Elasticsearch & OpenSearch — Inverted Indexes, Relevance & OpsInterview hub: inverted indexes, BM25, shards, ILM, and Elasticsearch versus OpenSearch versus Solr. Use a search cluster for full-text, filters, and aggregations, and keep multi-row transactions in an OLTP store.
  2. 2.Inverted Index, Analyzers, Tokenization & MappingsAn inverted index maps each term to a posting list. Analyzers turn raw strings into those terms, and mappings decide which analyzer and field type apply. A wrong mapping silently drops recall or explodes the cluster.
  3. 3.Query DSL, Relevance Scoring (TF-IDF/BM25) & Filters vs QueriesQuery DSL composes bool, match, term, and range clauses. Relevance defaults to BM25. Filters answer yes or no and cache well. Queries compute the score that ranks hits.
  4. 4.Sharding, Replicas, Routing & Cluster HealthAn index is split into primary shards with zero or more replicas. Writes route by a key, searches scatter and gather, and cluster health is green, yellow, or red based on whether those shards are allocated.
  5. 5.Indexing Pipelines, Bulk, ILM & SnapshotsProduction search lives or dies on the ingest path: bulk requests, refresh versus flush, ILM or ISM rollover, and snapshots to a repository that is usually object storage.
  6. 6.Ecosystem — OpenSearch vs Elasticsearch vs Solr (and when not to use a search engine)Elasticsearch, OpenSearch, and Solr all sit on Lucene. They differ in API, license, and operations. The senior answer includes when a search engine is the wrong tool.

• MVCC & isolation

  6 studies
  1. 1.MVCC, Snapshot Isolation & Write Skewxmin/xmax visibility; SI vs SSI; write skew; SERIALIZABLE + 40001 retry; FOR UPDATE; VACUUM/bloat.
  2. 2.Isolation Levels Deep DiveIsolation levels are the contract for what concurrent transactions may see. ANSI labels hide engine gaps: Postgres RR is SI, MySQL RR uses gap locks, and write skew sits outside the ANSI phenomena list.
  3. 3.SSI vs Snapshot IsolationSnapshot Isolation stops lost updates on the same row but allows write skew. Postgres SERIALIZABLE (SSI) tracks rw-conflicts and aborts one txn with SQLSTATE 40001; retry the whole transaction.
  4. 4.Row Locks: SELECT FOR UPDATESELECT FOR UPDATE materializes the read set under SI so concurrent writers wait instead of write-skewing. Know NOWAIT, SKIP LOCKED, lock duration, and deadlock order.
  5. 5.VACUUM, Bloat & XID WraparoundDead MVCC versions stay until no snapshot can see them. VACUUM reclaims them and freezes XIDs; lag plus long txns cause bloat and wraparound that refuses writes.
  6. 6.Phantoms vs Write SkewPhantoms are new rows matching a predicate; write skew is disjoint writes that break a multi-row invariant. SI allows both; SSI, gap/predicate locks, or constraints close them differently.

• Vector Indexes — Semantic Search, ANN & Exact kNN

  6 studies
  1. 1.Vector Indexes — Semantic Search, ANN, and When Exact kNN WinsSemantic search turns text, images, or events into vectors and answers what is closest to the query vector. Exact kNN scans every vector: perfect recall, zero build cost, trivial filters, and cost linear in N. ANN indexes give up a little recall for sub-linear queries. This hub is the decision map across HNSW, IVF, PQ, LSH, ScaNN, and DiskANN, including when brute force is the right answer.
  2. 2.HNSW — Layers, Greedy Search, M and efHNSW is a stack of proximity graphs. Layer 0 holds every vector, and each higher layer keeps an exponentially smaller sample. A query greedily walks the sparse top layers, then runs a beam of width efSearch on layer 0. M, efConstruction, and efSearch are the three knobs that set memory, graph quality, and the recall versus latency dial.
  3. 3.IVF, PQ, and ScaNN — Partition, Quantize, Re-rankIVF partitions space with k-means and scans only the nprobe cells nearest the query. PQ compresses each vector into a few bytes of codebook ids and scores them with table lookups. Together, with an exact re-rank, that is the classic recipe from hundreds of millions to billions of vectors. ScaNN spends its quantization error where inner-product rank actually moves.
  4. 4.Filtered and Hybrid Search — Metadata, BM25, and Broken RecallReal queries are nearest vectors for this tenant, in stock, that this user may see, and often matching an exact error code. Filters fight ANN structures: post-filtering returns too few hits, a matching-only walk strands, and a filter-aware walk gets expensive when the predicate is selective. Hybrid search adds a BM25 leg and fuses ranks so one score scale cannot silently win.
  5. 5.Recall, Latency, and Memory — Tuning and EvaluationEvery ANN index trades recall, latency, and memory, with build time as the fourth wheel. The first artifact is an evaluation harness: frozen real queries, exact ground truth, recall at k plus tail recall, p50 and p99, and bytes per replica. Sweep efSearch, nprobe, quantization, and re-rank depth, then pick the cheapest point that meets the target.
  6. 6.Sharding, Updates, and Stale Embeddings — Production Vector SearchA vector index that works on a laptop fails in production in predictable ways. It outgrows one node, scatter-gather inflates p99, tombstones erode recall, and embeddings go stale when the text or the model changes. Mixing two embedding models fails silently: no error, just garbage neighbors. Hash-shard, return full k per shard, and cut over models with a second index and an alias.

• Zero-Downtime DB Migrations

  6 studies
  1. 1.Zero-Downtime Database Migrations — Expand/Contract, Dual Write & Online DDLZero-downtime migrations change schema and data paths without taking the app offline. Expand first and contract only after every reader and writer is gone. Online DDL bounds lock time. Dual-write, an idempotent backfill, and a feature-flag rollback cover moves to a new table or store.
  2. 2.Expand/Contract Pattern — Additive Schema Changes Without DowntimeExpand/contract keeps old and new code running on one schema: add the new shape, migrate traffic and data while both exist, and drop the old shape only after every reader and writer is gone. A rename is a new column plus dual-write, not one RENAME in the same deploy.
  3. 3.Online DDL — Locks, CONCURRENTLY, gh-ost & pt-oscOnline DDL applies schema changes while the database serves traffic, with locks held only for brief metadata steps. Postgres has CREATE INDEX CONCURRENTLY and a leftover INVALID index on failure. MySQL has INSTANT, INPLACE, and COPY. Hot rewrites move to gh-ost (binlog) or pt-osc (triggers).
  4. 4.Dual Write / Dual Read — Migrating Data Paths SafelyWhen rows must live in a new table or store, dual-write keeps both paths updated and dual-read flips traffic. The order is write-both/read-old, shadow compare, read-new, then write-new-only. Partial failure, ordering, and idempotency dominate. An outbox makes the second write durable intent.
  5. 5.Backfills & Reconciliation — Idempotent Jobs, Checksums, Shadow ReadsBackfills copy historical rows while dual-write covers live traffic. Use keyset pages, a persisted resume token, and a lag gate. Prove the copy with chunk checksums and shadow reads. A matching row count with a failing checksum is not permission to cut over.
  6. 6.Rollback, Feature Flags & Cutover — Instant Reverse Without Data LossCutover is when users start depending on the new path. Rollback flips the read flag in seconds, which only works while the old path still exists and dual-write kept it current. DROP COLUMN, destructive casts, and write-new-only after the old path has drained are the irreversible zone.