TopicsDatabases
Databases
Indexes, isolation, storage engines, shard and partition keys, and zero-downtime migrations you can ship without a maintenance window.
Common tags: indexes, transactions, storage, migrations, partitioning, shard-keys
- Databases
Sync vs Async vs Semi-Sync Replication — Durability, RPO & Physical vs Logical Logs
Cluster · Database Replication — Leader-Follower, Multi-Leader & Leaderless
Async, 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.
Open study →- databases
- replication
- distributed-systems
- consistency
- failover
- quorums
- interview
- Databases
Replication Lag & Session Guarantees — Read-Your-Writes, Monotonic Reads & Consistent Prefix
Cluster · Database Replication — Leader-Follower, Multi-Leader & Leaderless
Async 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.
Open study →- databases
- replication
- distributed-systems
- consistency
- failover
- quorums
- interview
- Databases
Failover & Split Brain — Detection, Promotion, Fencing & Lost Writes
Cluster · Database Replication — Leader-Follower, Multi-Leader & Leaderless
Failover 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.
Open study →- databases
- replication
- distributed-systems
- consistency
- failover
- quorums
- interview
- Databases
Multi-Leader & Active-Active — Conflict Detection, LWW, CRDTs & Home Regions
Cluster · Database Replication — Leader-Follower, Multi-Leader & Leaderless
Multi-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.
Open study →- databases
- replication
- distributed-systems
- consistency
- failover
- quorums
- interview
- Databases
Leaderless Replication — N/R/W Quorums, Hinted Handoff, Read Repair & Anti-Entropy
Cluster · Database Replication — Leader-Follower, Multi-Leader & Leaderless
Leaderless (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.
Open study →- databases
- replication
- distributed-systems
- consistency
- failover
- quorums
- interview
- Databases
Database Replication — Leader-Follower, Multi-Leader & Leaderless Quorums
Cluster · Database Replication — Leader-Follower, Multi-Leader & Leaderless
Replication 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.
Open study →- databases
- replication
- distributed-systems
- consistency
- failover
- quorums
- interview
- Databases
Sharding, Updates, and Stale Embeddings — Production Vector Search
Cluster · Vector Indexes — Semantic Search, ANN & Exact kNN
A 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.
Open study →- databases
- vector-search
- ann
- hnsw
- ivf-pq
- semantic-search
- interview
- Databases
Recall, Latency, and Memory — Tuning and Evaluation
Cluster · Vector Indexes — Semantic Search, ANN & Exact kNN
Every 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.
Open study →- databases
- vector-search
- ann
- hnsw
- ivf-pq
- semantic-search
- interview
- Databases
IVF, PQ, and ScaNN — Partition, Quantize, Re-rank
Cluster · Vector Indexes — Semantic Search, ANN & Exact kNN
IVF 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.
Open study →- databases
- vector-search
- ann
- hnsw
- ivf-pq
- semantic-search
- interview
- Databases
Vector Indexes — Semantic Search, ANN, and When Exact kNN Wins
Cluster · Vector Indexes — Semantic Search, ANN & Exact kNN
Semantic 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.
Open study →- databases
- vector-search
- ann
- hnsw
- ivf-pq
- semantic-search
- interview
- Databases
HNSW — Layers, Greedy Search, M and ef
Cluster · Vector Indexes — Semantic Search, ANN & Exact kNN
HNSW 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.
Open study →- databases
- vector-search
- ann
- hnsw
- ivf-pq
- semantic-search
- interview
- Databases
Filtered and Hybrid Search — Metadata, BM25, and Broken Recall
Cluster · Vector Indexes — Semantic Search, ANN & Exact kNN
Real 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.
Open study →- databases
- vector-search
- ann
- hnsw
- ivf-pq
- semantic-search
- interview
- Databases
Inverted Index, Analyzers, Tokenization & Mappings
Cluster · Elasticsearch & OpenSearch
An 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.
Open study →- databases
- elasticsearch
- opensearch
- lucene
- search
- inverted-index
- interview
- Databases
Ecosystem — OpenSearch vs Elasticsearch vs Solr (and when not to use a search engine)
Cluster · Elasticsearch & OpenSearch
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.
Open study →- databases
- elasticsearch
- opensearch
- lucene
- search
- inverted-index
- interview
- Databases
Sharding, Replicas, Routing & Cluster Health
Cluster · Elasticsearch & OpenSearch
An 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.
Open study →- databases
- elasticsearch
- opensearch
- lucene
- search
- inverted-index
- interview
- Databases
Query DSL, Relevance Scoring (TF-IDF/BM25) & Filters vs Queries
Cluster · Elasticsearch & OpenSearch
Query 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.
Open study →- databases
- elasticsearch
- opensearch
- lucene
- search
- inverted-index
- interview
- Databases
Elasticsearch & OpenSearch — Inverted Indexes, Relevance & Ops
Cluster · Elasticsearch & OpenSearch
Interview 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.
Open study →- databases
- elasticsearch
- opensearch
- lucene
- search
- inverted-index
- interview
- Databases
Indexing Pipelines, Bulk, ILM & Snapshots
Cluster · Elasticsearch & OpenSearch
Production 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.
Open study →- databases
- elasticsearch
- opensearch
- lucene
- search
- inverted-index
- interview
- Databases
Sharding Failure Modes — Orphans, Partial Commits, Dual-Writes & Idempotency
Cluster · DB Sharding & Partitioning
Sharding 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.
Open study →- databases
- shard-keys
- resharding
- cross-shard
- interview
- Databases
Shard Keys — Hotspots, Fan-out Queries & Cardinality
Cluster · DB Sharding & Partitioning
The 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.
Open study →- databases
- shard-keys
- scatter-gather
- table-partitioning
- interview
- Databases
Resharding & Rebalancing — Dual-Write, Online Moves & Consistent Hash Rings
Cluster · DB Sharding & Partitioning
If 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.
Open study →- databases
- resharding
- shard-keys
- cross-shard
- interview
- Databases
Partition Strategies — Range, Hash, List & Composite
Cluster · DB Sharding & Partitioning
Before 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.
Open study →- databases
- table-partitioning
- partition-pruning
- shard-keys
- interview
- Databases
Database Sharding & Partitioning — Keys, Hotspots & Rebalancing
Cluster · DB Sharding & Partitioning
Vertical 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.
Open study →- databases
- table-partitioning
- shard-keys
- resharding
- scatter-gather
- interview
- Databases
Cross-Shard Queries — Scatter-Gather, Aggregations & Avoidance
Cluster · DB Sharding & Partitioning
The 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.
Open study →- databases
- scatter-gather
- cross-shard
- shard-keys
- interview
- Databases
Zero-Downtime Database Migrations — Expand/Contract, Dual Write & Online DDL
Cluster · Zero-Downtime DB Migrations
Zero-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.
Open study →- databases
- migrations
- expand-contract
- online-ddl
- dual-write
- backfill
- zero-downtime
- interview
- Databases
Rollback, Feature Flags & Cutover — Instant Reverse Without Data Loss
Cluster · Zero-Downtime DB Migrations
Cutover 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.
Open study →- databases
- rollback
- feature-flags
- cutover
- dark-launch
- zero-downtime
- interview
- Databases
Online DDL — Locks, CONCURRENTLY, gh-ost & pt-osc
Cluster · Zero-Downtime DB Migrations
Online 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).
Open study →- databases
- online-ddl
- postgres
- mysql
- gh-ost
- pt-osc
- locks
- interview
- Databases
Expand/Contract Pattern — Additive Schema Changes Without Downtime
Cluster · Zero-Downtime DB Migrations
Expand/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.
Open study →- databases
- migrations
- expand-contract
- parallel-change
- zero-downtime
- interview
- Databases
Dual Write / Dual Read — Migrating Data Paths Safely
Cluster · Zero-Downtime DB Migrations
When 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.
Open study →- databases
- dual-write
- dual-read
- migrations
- idempotency
- outbox
- interview
- Databases
Backfills & Reconciliation — Idempotent Jobs, Checksums, Shadow Reads
Cluster · Zero-Downtime DB Migrations
Backfills 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.
Open study →- databases
- backfill
- reconciliation
- checksums
- shadow-reads
- idempotency
- interview
- Databases
Write/Read/Space Amplification — B-Tree vs LSM Tradeoffs
Cluster · Database storage engines
Quantify WA/RA/SA; B-Tree lower cached RA vs LSM sequential writes + compaction WA; OLTP vs ingest.
Open study →- write-amplification
- read-amplification
- space-amplification
- b-tree
- lsm
- tradeoffs
- database-internals
- interview
- Databases
Write-Ahead Log — Durability, Checkpoints & Crash Recovery
Cluster · Database storage engines
Force log before data (ARIES-style); LSN, checkpoints, REDO/UNDO; sync vs group commit vs async; torn pages / doublewrite.
Open study →- wal
- durability
- checkpoint
- crash-recovery
- aries
- lsn
- postgres
- innodb
- database-internals
- interview
- Databases
LSM Trees — MemTable, SSTables & Compaction
Cluster · Database storage engines
MemTable → immutable → flush SSTable; leveled vs size-tiered compaction; blooms; RocksDB/Cassandra model.
Open study →- lsm
- memtable
- sstable
- compaction
- rocksdb
- cassandra
- bloom-filter
- database-internals
- interview
- Databases
fsync, Group Commit & Durable Latency
Cluster · Database storage engines
OS page cache lie; fsync/fdatasync; group commit coalesces commits; latency vs throughput; disk-lies / BBWC.
Open study →- fsync
- fdatasync
- group-commit
- durability
- latency
- wal
- postgres
- innodb
- database-internals
- interview
- Databases
Database Storage Engines — WAL, B-Trees & LSM Trees
Cluster · Database storage engines
Storage 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.
Open study →- database-internals
- storage-engines
- wal
- b-tree
- lsm
- rocksdb
- postgres
- innodb
- senior-swe
- interview
- Databases
B-Tree Internals — Pages, Splits & Buffer Pool
Cluster · Database storage engines
Pages, fanout, leaf/internal, splits; buffer pool caches dirty pages async; WAL still required; Postgres/InnoDB.
Open study →- b-tree
- b-plus-tree
- buffer-pool
- pages
- fanout
- postgres
- innodb
- database-internals
- interview
- Databases
VACUUM, Bloat & XID Wraparound
Cluster · MVCC & isolation
Dead 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.
Open study →- database-internals
- transactions
- Databases
SSI vs Snapshot Isolation
Cluster · MVCC & isolation
Snapshot 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.
Open study →- database-internals
- transactions
- Databases
Row Locks: SELECT FOR UPDATE
Cluster · MVCC & isolation
SELECT 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.
Open study →- database-internals
- transactions
- Databases
Phantoms vs Write Skew
Cluster · MVCC & isolation
Phantoms 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.
Open study →- database-internals
- transactions
- Databases
Isolation Levels Deep Dive
Cluster · MVCC & isolation
Isolation 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.
Open study →- database-internals
- transactions
- Databases
MVCC, Snapshot Isolation & Write Skew
Cluster · MVCC & isolation
xmin/xmax visibility; SI vs SSI; write skew; SERIALIZABLE + 40001 retry; FOR UPDATE; VACUUM/bloat.
Open study →- database-internals
- transactions