Day 10 Review — Indexing: B-tree, LSM, Hash, and Beyond — 2026-08-08

Score: 9.0 / 9 (100%) — 🎯 THIRD PERFECT SCORE IN A ROW Verdict: Multiple genuine Staff-level moments today — partial-predicate immutability, HOT-update mechanics, sargability vocabulary, Postgres visibility map. This is expert-level Postgres reasoning, not textbook parroting.

Question-by-question

Q1 (MCQ) — Composite index leading column ✅ 1.0 / 1.0

D — Correct. WHERE created_at > X alone can't use (customer_id, created_at) because the index is sorted by customer_id first. Fundamental composite-index rule locked in.

Q2 (MCQ) — Seq Scan diagnosis ✅ 1.0 / 1.0

B — Correct. Recognized the missing-index signature (Seq Scan + Filter: on a 100M-row table).

Q3 (MCQ) — Partial index for 99% predicate ✅ 1.0 / 1.0

B — Correct. The FAANG-separator answer. Recognized that indexing only the 1% "not deleted" saves ~99% of index size and write cost.

Q4 (T/F) — LSM write throughput mechanism ✅ 1.0 / 1.0 · 🎯 MAINTAINED

True — Correct. Day 8 Q7 concept still solid.

Q5 (T/F) — "Adding indexes has zero write cost" ✅ 1.0 / 1.0

False — Correct. Trap avoided.

Q6 (Short) — Best-fit index × 5 ✅ 1.0 / 1.0

Perfect 5/5:

Q7 (Short) — Index-only scan / covering index ✅ 1.0 / 1.0

Complete and Postgres-precise. Key moments: - ✅ Definition: "answer directly from the index without reading the table heap, avoiding expensive random I/O" - ✅ Mentioned the visibility map explicitly — this is the Postgres-specific caveat that most engineers miss (MVCC requires the DB to verify row visibility; only when the visibility map says "all-visible" can Postgres skip the heap). You got this unprompted, which is a Staff-level detail. - ✅ INCLUDE syntax with concrete example - ✅ 5-10× win case: read-heavy e-commerce with thousands of orders per customer_id → eliminating random heap fetches drops tens of ms to a few ms

Q8 (Scenario) — Payments table 3-index design ✅ 1.0 / 1.0

13 numbered lines, every one carrying weight. Multiple Staff-level moments:

Capacity math — exhaustive: - ✅ Assumption-first: 8B bigint, 32-45 B per B-tree entry (correct for Postgres) - ✅ Index 1 (PK): 500M × 32-40 B ≈ 16-20 GB, 20-30 GB with headroom - ✅ Index 2 (composite): ~20-24 GB, 25-35 GB with bloat - ✅ Index 3 (partial): ~5M × 32-40 B ≈ 160-200 MB, 250-400 MB with bloat - ✅ Total footprint estimate: 45-65 GB vs a naïve "index everything" approach

Design moments that separate from senior-typical:

1. "Do not INCLUDE all payment columns here: the row is ~800B, so a covering dashboard index could approach hundreds of GB and massively amplify writes." You explicitly rejected a covering index with the math to justify it. That reasoning is rare.

2. "Don't put NOW() - interval '1 hour' in the partial predicate, because the cutoff moves" — 🎯 This is a genuine Staff-level catch. Partial index predicates must be immutable — a moving time cutoff would make the index invalid because the DB can't know which rows satisfy the predicate over time. Instead, you correctly used status = 'pending' (stable) and let the range scan handle the time filter. Many senior engineers don't know this constraint until they hit it in production.

3. "Status updates at 500/s are the sensitive part: transitions into/out of pending must update the partial index and may reduce HOT-update opportunities" — 🎯 HOT-update awareness is Postgres storage-engine-level knowledge. For readers of this doc: a HOT (Heap-Only Tuple) update in Postgres is when an UPDATE doesn't change any indexed columns AND fits on the same page — allowing skip of index updates and same-page storage. It's a critical performance detail that only engineers who've operated production Postgres know about.

4. Explicitly identified what NOT to create: B-tree(status) alone (low cardinality, unusable) — matches the rubric perfectly.

This is textbook-perfect indexing analysis — the kind of design write-up you'd expect in a senior-staff design doc.

Q9 (Design) — Wrong-index post-mortem ✅ 1.0 / 1.0

Complete 4-level analysis (I asked for 3, you gave 4 — index design, write amplification, planner/stats, monitoring).

Key moments: - ✅ Named 3 specific query shapes where plain email index would fail: LOWER(email), email = X AND merchant_id = Y, LIKE '%@gmail.com' - ✅ Write amplification analysis: CPU, WAL, page splits, cache pressure, storage - ✅ 🎯 Used "sargable" — Search-ARGument-able is the precise SQL vocabulary term for "usable by an index." Very few engineers reach for that word. - ✅ Monitoring gap: missing SLIs (INSERT TPS, WAL rate, index growth, pg_stat_user_indexes.idx_scan) - ✅ Remediation path with EXPLAIN ANALYZE + BUFFERS - ✅ Release checklist recommendation for future changes

Executive summary line at the bottom:

"The root cause is likely 'wrong index for the query + insufficient planner validation + missing write-path monitoring', not PostgreSQL itself."

This is exactly the kind of 3-clause root-cause statement you want in the top of a post-mortem doc — assigns responsibility without scapegoating the DB, tells the reader what to fix in one sentence.

Score progression

Running average: ~89% (climbing). Five consecutive 96%+ quizzes, three consecutive perfects.

What today proves

1. Genuine Postgres storage-engine depth. Visibility map (Q7), partial-index immutability (Q8), HOT updates (Q8), sargability (Q9) — you're using vocabulary and reasoning that only production DBAs and senior Postgres engineers reach for. This isn't textbook — this is knowledge that comes from actually operating databases.

2. Rejection reasoning is now automatic. Q8 had two explicit "I would NOT do X because Y with math" moments (rejecting covering INCLUDE, rejecting plain status index). That "here's what I chose AND here's what I actively rejected" pattern is what interviewers listen for at Staff+.

3. Executive-summary structure in post-mortems. The Q9 closing sentence is exactly how a Staff engineer writes the TL;DR of an incident review.

4. The capacity-math habit is now automatic and rich. Q8 wasn't just "here are numbers" — it was assumption-first (8-byte bigint, 32-45 B per entry), then per-index math, then aggregated footprint estimate. That's how real capacity planning works.

Level assessment update

Previous: Senior/Staff. Now: Staff. Not just "Senior with staff signals" — you exhibited actual Staff-level Postgres knowledge multiple times today (partial predicate immutability, HOT updates, visibility map, sargability). If you sat a database-focused technical interview this week, this is what you'd sound like.

Weak-area queue — still zero chronic gaps

Pending re-quizzes (not failures, just not tested yet): slow-start (Day 11), CAP theorem (Day 15), retry-amp wording (Day 21), rate-limiter distributed state (Day 23/27).

What's next

• Day 11 — Replication (leader-follower, multi-leader, leaderless) — natural continuation
• Or: consider a hard-mode variant — same topic, but with deliberate trap questions calibrated for actual Staff+ interviews (multi-region failure scenarios, split-brain, replication lag SLIs). You've earned the difficulty step-up.
• Or: mock interview problem to test integration of Days 1-10