Databases & caching

Data systems are where constraints become real: latency, correctness, money, and compliance all meet the storage layer. Staff-level answers name consistency guarantees, failure modes (replica lag, split brain, hot keys), and operational habits (migrations, backups, observability)—not only “pick Postgres.”

How to use this page

• Use Core database ideas as a vocabulary checklist (ACID, isolation, plans, replication).
• Use Caching alongside databases to keep source of truth vs derived copies explicit.
• Skim Diagrams before mock interviews—practice narrating arrows out loud.
• Run Things to prepare as a literal checklist for projects or interview stories.
• Staff / SDE3 bar (Node.js + AWS): pair RDS (B-tree OLTP) with Dynamo-style partitioned thinking where relevant; treat ElastiCache as a consistency problem, not only latency—internals live under Storage engines, CAP & distributed data and Application caching & consistency.

---

Topic study plan (deep pages)

Each topic under /databases/topics/... follows: Core details → Understanding → Senior understanding → Diagram.

---

Core database ideas

Storage models (when you’d reach for each)

Deep page: Storage models & access paths (SQL vs NoSQL, warehouse vs OLTP, document modeling, when to choose which).

Interview line: “I’d choose the model after access paths and consistency requirements, not the logo on the box.”

ACID (know what each letter guards)

Isolation anomalies (name → fix intuition)

Not every endpoint needs SERIALIZABLE—cost and deadlock risk are real; scope transactions to the smallest critical section.

Indexing & the query path

Deep page: Indexing & query tuning (normalization, index Q&A, optimization patterns).

• B-tree (and friends) power most default indexes; composite index column order matches filter + sort patterns.
• Covering indexes avoid heap/table lookups when the planner can satisfy the query from the index alone.
• Cardinality & statistics skew plans—stale stats → wrong nested loops vs seq scans.

Operational loop:

1. Identify hot query text (slow log / pg_stat_statements class metrics).
2. EXPLAIN (ANALYZE) or vendor equivalent—compare estimated vs actual rows.
3. Fix with query rewrite before index soup; add indexes only when the plan proves it.

Schema evolution

Treat migrations as deploy trains: additive-first columns, dual-write / backfill phases, backward-compatible reads until cutover. Breaking changes are timelines, not single PRs.

Connections & pools

Exhausted connection pools look like “the database is slow.” Watch wait time to acquire a connection, not only query duration.

Related depth: Database query path, Profiling services & async, /backend.
AWS + integration: AWS data services & Node.js, CDC & outbox.

---

Distributed data (production realism)

Replication basics

Replica lag surfaces as intermittent “wrong data” unless you engineer read-your-writes (sticky to primary, version checks, or user-visible staleness).

Partitioning / sharding

Choose a partition key from real access paths—resharding later is expensive. Watch hot partitions (celebrity keys) and plan salting or key redesign early.

Backup & recovery (you should be able to narrate)

• RPO/RTO targets tied to product risk.
• Restore drills on a schedule—not “we assume backups work.”
• Logical vs physical backup tradeoffs (portability vs speed).

---

Caching alongside databases

Caches are derived state. The database (or durable event log) remains authority unless you consciously accept staleness with product guardrails.

Layers (stack north to south)

Patterns (who owns invalidation?)

Mitigate cache stampede: TTL jitter, request coalescing / single-flight, probabilistic early refresh.

Related depth: Caching & consistency, Caching & retry amplification.

---

Diagrams

Authority vs derived cache

Cache-aside read path

Transaction scope vs long work

---

Things to prepare

Before you design or pitch a data layer

• Access paths: list the 5–10 hottest queries and their filters/sorts.
• Consistency: which reads must be linearizable vs OK with seconds of lag?
• Growth: data volume curve, retention, archival, legal holds.
• Multi-tenancy: tenant key in every table/index story; noisy neighbor controls.
• Migration runway: additive schema steps, backfills, dual reads/writes.

Before production hardening

• Backups with documented restore drill frequency and owner.
• Replication lag dashboards and alerts tied to user-facing symptoms.
• Connection pool limits per service; saturation metrics.
• Slow query logging and top N review cadence.
• Secrets rotation path; least privilege DB roles.
• Idempotency for writers (see /backend/topics/idempotency-at-least-once).

Interview prep (90 minutes, repeatable)

Memorize one crisp line for each: isolation level choice, stampede mitigation, RPO/RTO, when not to cache.

---

Pitfalls

• Treating read replicas as strongly consistent without version checks or routing rules.
• Giant transactions holding locks while calling HTTP or queues.
• Unbounded caches without eviction, TTL, or memory caps—OOM incidents.
• Cache as source of truth for balances or inventory without durable reconciliation.
• Indexing every column without examining plans—write amplification and bloated storage.

---

Related

• /backend — lifecycle, timeouts, messaging, caching entry points.
• /performance — tail latency, retries, thundering herds, DB performance notes.
• /security — injection, least privilege, secrets.