Not an explainer — you already have that. This is a design review of a system that doesn't exist yet: a small MySQL/Oracle-scale SQL engine, sized for sitting quietly under a fintech platform like Optimus. Every segment opens with a callout telling you exactly what to draw, write, or decide. Attempt it — on paper, in your head, out loud — before you reveal. The reveal is a reference design to argue with, not a correct answer.
You're not building "a database." You're building the storage-and-query layer that will sit under one slice of Optimus — say, the demat holdings and order-status tables that NSDL sync, IPO allotment, and MF redemption all read and write. Requirements first, or every later decision is a guess.
BUILD FIRST
Write your own 5–7 FR bullets (what must this engine let a caller do — not how) and NFR targets with real numbers: writes/sec, p99 read latency, and where you sit on the consistency ↔ availability line for this workload. Then write one sentence on what you're scoping OUT, and why leaving it out is itself a decision. Don't reveal until you've written numbers, not adjectives.
R1Reveal: a reference FR/NFR set for this workloadreveal
Functional requirements
Durable CRUD on typed relational rows via SQL, with foreign-key and check constraints enforced by the engine, not the caller.
Transactions spanning multiple statements with atomic commit/rollback — an IPO allotment debit and a demat credit must land together or not at all.
Secondary indexes with range-scan support (folio number, PAN, order-date range).
Point-in-time consistent reads for a single session (read-your-own-writes on the primary).
An append-only audit path for regulated tables (balances, allotments) independent of current-state tables.
An administrative surface to inspect running transactions, locks held, and replication lag.
Write throughput
3,000 tx/sec
Read p99
15 ms
Write p99
40 ms
CP or AP
CP on ledger tables
Durability
fsync before ack
Why these numbers, not others: 3,000 tx/sec is sized to peak IPO-allotment-day burst across 80+ services hitting a shared holdings table, not steady-state load — designing for average load is the single most common NFR mistake. CP over AP on ledger tables isn't a stylistic preference; a demat/LAMF balance that's briefly unavailable is an incident, one that's silently wrong is a regulatory event. Those are different failure costs, and the NFR should say so explicitly, not leave it implied.
Scope OUT: multi-region active-active writes, and a general-purpose SQL dialect (no window functions, no stored procedures). Why it matters: scoping out active-active writes early is what lets you pick a single-writer architecture in the very next segment with a clear conscience — if you leave that door open "for later," it quietly forces MVCC-with-conflict-resolution complexity into segment 2 that this workload doesn't need yet.
offset 01
Scope & the core design decision
One or two decisions here shape every segment after this. Get them wrong and segments 3–9 are just elaborate ways of fighting your own architecture.
BUILD FIRST
Commit, in writing, to: (a) storage engine shape — B+Tree page store vs LSM-tree — and (b) concurrency control — 2-phase locking vs MVCC. For each, write one sentence justifying it against your own segment-1 numbers, not against "what's popular."
Option considered
Verdict for this workload
LSM-tree (RocksDB-style)
Rejected as primary — optimizes write throughput at the cost of read-amplification and compaction stalls; this workload is read-heavy (order status, holdings lookups) with moderate, not extreme, write volume.
B+Tree page store
Chosen. In-place updates, predictable read latency, mature range-scan behavior — matches a 3,000 tx/sec write target with a much larger read fan-out.
2-phase locking (2PL)
Rejected as sole mechanism — readers block writers, and a demat holdings table read by 80+ services would create lock contention that has nothing to do with correctness.
MVCC + row-level locking for writes
Chosen. Readers never block writers; writers still take row locks to serialize conflicting updates. Matches the CP-on-writes / high-read-fanout requirement from segment 1 directly.
D1You just chose MVCC. What does that decision cost you later, and where does that cost resurface in this document?reveal
MVCC means old row versions accumulate until nothing needs them — that cost resurfaces as a garbage collection / vacuum concern inside the storage internals (segment 5) and as a real risk in the failure-handling segment (segment 7): a long-running transaction that never commits pins old versions indefinitely, which is exactly the "stuck forever" case you'll be asked to design an exit for.
Why flag it now: a design decision that only shows its cost three segments later is precisely the kind of decision a staff-level review catches early — deciding MVCC without deciding its cleanup mechanism in the same breath is an incomplete decision, not a finished one.
offset 02
Component breakdown
Turn the two decisions above into boxes. The question that matters isn't "what components exist" — it's "which of these must never share a process or a failure domain with which others."
BUILD FIRST
Draw your own 5–7 box component diagram for the engine (not the callers). Before revealing, answer: which component sits on the hot path (blocks the caller's transaction) and which sits on a slow/durable path (can lag without blocking anyone) — and why those two must be separate components, not just separate functions inside one.
C1Reveal: reference component diagramreveal
Client (Optimus microservice)
|
v
[1] Query Parser/Planner ---- hot path
|
v
[2] Transaction Manager ---- hot path (locks, MVCC snapshot)
| \
v v
[3] Buffer Pool [4] WAL Writer ---- both hot path, both fast
(page cache) (append-only, fsync)
|
v
[5] Storage Engine (B+Tree pages on disk)
|
v
[6] Checkpoint / Compaction Worker ---- slow path, background
|
v
[7] Replication Streamer ---- slow path, async to replicas
Why the split matters: [2]–[4] sit on the hot path — a caller's COMMIT literally waits on them, so they must be fast and simple. [6] and [7] can lag by seconds without breaking correctness for the caller, only for replicas or disk space. Putting checkpoint/compaction logic inside the transaction manager (a common shortcut) means a slow background compaction can stall live commits — the exact coupling you designed against in segment 1's latency NFR. Separate components isn't bureaucracy; it's a failure-domain boundary that keeps a background job from becoming a foreground outage.
offset 03
Low-level API design
The engine's real interface isn't SQL text — it's the control-plane and data-plane operations that sit underneath it. Design those explicitly, not as prose.
BUILD FIRST
Sketch your own 5–6 API endpoints — method, path, purpose, caller, payload, idempotency. Before revealing, decide: should authorization be scoped by who owns the data, or by blast radius of the operation? Those aren't the same question — pick one and say why.
POST/tx/begin
PurposeOpen a transaction, take an MVCC read snapshot, return a tx handle.
IdempotentNo — each call opens a new transaction by design.
POST/tx/{tx_id}/exec
PurposeExecute one statement inside an open transaction; acquires row locks as needed.
CallerSame service that opened the tx — never a different caller.
Payload{sql, params}
IdempotentNo — a repeated INSERT would duplicate a row. Caller must dedupe upstream.
PUT/tx/{tx_id}/commit
PurposeFlush WAL, fsync, release locks, make writes visible to future snapshots.
CallerOwning microservice.
Payloadnone
IdempotentYes — committing an already-committed tx_id returns the original result, doesn't re-apply.
DELETE/tx/{tx_id}
PurposeAbort — roll back, release locks, discard the MVCC snapshot.
CallerOwning microservice, or the Transaction Manager itself on timeout.
Payloadnone
IdempotentYes — aborting twice is a no-op.
INTERNAL/admin/checkpoint/trigger
PurposeForce a checkpoint — flush dirty buffer-pool pages, truncate WAL up to the checkpoint LSN.
CallerOperator, or an autoscaler-style internal scheduler — never an app service.
Payloadnone
IdempotentYes — a redundant checkpoint just does less work.
GET/admin/locks
PurposeIntrospect held locks, waiters, and current wait-for graph — the diagnostic surface for segment 6's deadlock detector.
CallerOperator / on-call tooling only.
Payloadnone
IdempotentYes — read-only.
A1Should these endpoints be authorized by data ownership (which service owns this table) or by blast radius (what could this call break)?reveal
Blast radius, layered on top of ownership. Ownership answers "can this service touch this table" — necessary but not sufficient. /admin/checkpoint/trigger and /admin/locks aren't owned by any single service, yet a mis-scoped call to either can degrade every tenant on the engine at once. Scoping purely by ownership would let any authenticated caller hit an admin endpoint they technically "own" access to but have no business invoking under load.
Why this is the harder question: ownership-based authz is the default every team reaches for first because it maps to org boundaries. Blast-radius authz requires someone to have actually reasoned about what each endpoint can break — which is exactly the reasoning this document is trying to force you to do explicitly, once, rather than discover during an incident.
offset 04
Data model & sizing
Not the tables your callers store — the engine's own internal catalog and control state. Get durable-vs-ephemeral wrong here and you'll debug a phantom "data loss" bug that's actually a design category error.
BUILD FIRST
Sketch your own internal schema (catalog, page directory, lock table) and estimate row counts / write throughput / storage for an engine backing 80+ microservices. Then answer: is this actually a big-data problem, or a well-indexed-medium-data problem wearing a big-data costume?
Internal table
Durable or ephemeral
Rough scale
catalog.tables
durable (system pages)
~500 rows — one per logical table across 80+ services
S1Why does conflating the lock table and the catalog — treating both as "engine metadata" — count as a design mistake, not just an implementation shortcut?reveal
Because they have opposite durability contracts. The catalog must survive a crash — losing it means the engine forgets its own schema. The lock table must not survive a crash as-is — replaying stale locks after a restart would deadlock the engine against transactions that no longer exist. Storing both the same way means either the catalog is under-protected or the lock table is over-protected; there's no shared storage policy that's correct for both.
Why it matters at scale: at 80+ services depending on this engine, a recovery path that accidentally restores stale locks doesn't cause one incident — it causes a platform-wide stall the moment the engine restarts, which is a far worse failure mode than the crash that triggered it.
S2Gut-check: at ~320GB steady state and 3,000 tx/sec, is this a "big data" problem requiring a distributed storage layer?reveal
No — and treating it as one would be over-engineering. 320GB fits comfortably on a single NVMe-backed instance with room to grow for years; 3,000 tx/sec is well within what a single well-tuned B+Tree engine with MVCC handles on modern hardware. The actual hard problems here are correctness under concurrency and failure recovery, not data volume — a distributed storage layer would trade a solved problem (fits on one box) for an unsolved one (distributed consensus) without a numbers-driven reason to.
Why this question earns a segment of its own: "should this be distributed" is usually answered by reflex (bigger systems feel more serious) rather than by the numbers already sitting in segment 1 and this segment — the discipline is refusing complexity the requirements don't justify.
offset 05
Core algorithm — the buffer pool
Pick one mechanism and go deep: the buffer pool is the component every read and write actually touches, and it's where "the engine is slow" bugs usually live.
BUILD FIRST
Write actual pseudocode — not prose — for the buffer pool's core structure and its fetch_page(page_id) operation. Cover: what happens when the pool is full and every frame is pinned, and how two threads fetching the same page concurrently don't both load it from disk.
P1Reveal: reference pseudocodereveal
struct Frame:
page_id, dirty_bit, pin_count, latch, ref_bit
BufferPool:
frames[N] // fixed-size array
page_table: map(page_id -> frame_index) // guarded by pool_latch
fetch_page(page_id):
pool_latch.lock()
if page_id in page_table:
f = frames[page_table[page_id]]
f.pin_count += 1
pool_latch.unlock()
return f // hit: no disk I/O
frame_index = find_victim() // LRU-K / clock-sweep
if frame_index is None:
pool_latch.unlock()
raise POOL_EXHAUSTED // see reveal below
victim = frames[frame_index]
if victim.dirty_bit:
flush_to_disk(victim) // WAL must already cover this page
page_table.remove(victim.page_id)
frames[frame_index] = read_from_disk(page_id)
frames[frame_index].pin_count = 1
page_table[page_id] = frame_index
pool_latch.unlock()
return frames[frame_index]
unpin_page(page_id, was_written):
f = frames[page_table[page_id]]
f.pin_count -= 1
if was_written: f.dirty_bit = true
Concurrency: the pool_latch around the page-table lookup and the frame-swap is what prevents two threads from both missing on the same page and issuing duplicate disk reads — the second thread's lookup, once it gets the latch, finds the first thread's in-flight fetch already reflected in page_table. Per-frame latches (not shown) then guard the page's actual bytes during read/write, independent of the pool-wide latch.
Pool exhausted (every frame pinned): this isn't a hang — it's a signal. The transaction manager should reject the requesting transaction's next operation with a "retry with backoff" or abort it outright if it's held the pool hostage past a timeout, rather than blocking forever. A pool that can silently deadlock waiting for a free frame is a design bug, not bad luck — the fix belongs in segment 7's failure handling, not in tuning the pool size after the fact.
MVCC handles reader/writer conflicts. It doesn't handle two writers each waiting on a row the other holds. Something has to notice that and break the tie.
BUILD FIRST
Design the detector's loop precisely — what data structure it walks, how often, and what it does the instant it finds a cycle. Then decide: should deadlock detection run centralized (one detector for the whole engine) or per-shard/distributed — and what does each cost?
L1Reveal: the detection loopreveal
every 100ms:
graph = build_wait_for_graph(lock_table)
// edge tx_A -> tx_B means "tx_A is waiting on a lock tx_B holds"
cycle = detect_cycle(graph) // DFS with recursion-stack marking
if cycle exists:
victim = pick_victim(cycle) // lowest work-done, or youngest tx
abort(victim) // releases its locks immediately
notify(victim, reason="deadlock") // caller retries at the API layer
Why 100ms, not instant: checking on every lock acquisition would make every single lock grab pay the cost of a graph walk. Polling on an interval trades a small, bounded detection delay for near-zero overhead on the hot path — and the interval should be tuned against segment 1's write-latency NFR, not chosen arbitrarily.
L2Centralized detector vs one detector per shard — what's the actual trade-off, not just "centralized is simpler"?reveal
Centralized sees every cross-shard wait edge and catches deadlocks a per-shard detector structurally cannot — but it becomes a single component every transaction's lock state must be visible to, which is a coordination bottleneck at exactly the scale where you'd want to shard in the first place. Per-shard detectors scale cleanly but are blind to a deadlock that spans two shards — which, given segment 1 scoped this engine to a single-writer, single-instance shape, currently can't happen. So for this design, centralized wins by default; the moment segment 8's scaling discussion introduces multiple write shards, this decision has to be revisited, not assumed to still hold.
Why call this out explicitly: a decision that's correct for today's scope silently becoming wrong after a later scaling decision is one of the most common ways "it worked in the design doc" diverges from "it works in production" — the fix is flagging the dependency here, not rediscovering it later.
offset 07
Failure handling & the transaction state machine
Every state mentioned so far — begin, exec, commit, abort, pool-exhausted, deadlock-victim — belongs to one state machine. Draw it before you look.
BUILD FIRST
Draw the full transaction state machine yourself, including the crash-recovery states. Then answer: which state, if the engine crashes while a transaction sits in it, risks the transaction being stuck forever — and what guarantees it eventually exits?
F1Reveal: reference state machinereveal
begin
|
v
[ACTIVE] <----------------+
/ | \ |
exec/ deadlock timeout | retry (idempotent
| victim expiry | at caller, new tx_id)
v | | |
[ACTIVE] [ABORTING] [ABORTING] |
| | | |
commit v v |
| [ABORTED] -----+-------+
v
[COMMITTING] --crash-- [RECOVERING] --WAL replay--> [COMMITTED] or [ABORTED]
|
v
[COMMITTED] --------------------> [TERMINATED] (locks released, snapshot retired)
The stuck-forever risk: [COMMITTING] — the window after the caller is told "commit accepted" but before WAL fsync is confirmed and locks are released. A crash here must resolve deterministically on restart, which is exactly what RECOVERING does: replay the WAL, and any transaction whose commit record made it to disk is COMMITTED, anything that didn't is ABORTED — there is no third outcome, and no state that requires a human decision to exit. That "no ambiguous outcome" property is the actual point of a WAL, not durability in the abstract.
Retry/idempotency plug-in point: retries happen at the caller, with a fresh tx_id, after an ABORTED or timeout — never inside the engine reusing the same tx_id, because a tx_id's fate (committed or aborted) must stay permanently decided once RECOVERING resolves it.
offset 08
Scaling this component itself
Not the 80+ services calling it — this engine. How do multiple instances of your own SQL engine stay roughly in sync without the coordination itself becoming the bottleneck.
BUILD FIRST
Decide how a second instance of this engine (a read replica, or a second write shard) stays consistent with the first. Then write a concrete metric — tied to your segment-1 NFRs — that would tell you it's time to add one.
SC1Reveal: reference scaling approachreveal
Single writer, WAL-shipped async read replicas — the WAL segments produced in segment 3's replication streamer are the sync mechanism, replayed on each replica in order. No consensus protocol, no multi-writer conflict resolution, because segment 1 explicitly scoped multi-region active-active writes out. Read replicas absorb the read-heavy fan-out (holdings lookups, order-status polling from 80+ services) without touching the single writer's lock table at all.
The scale-out trigger metric: replica replay lag crossing 200ms sustained for 5 minutes — chosen because segment 1's read p99 target was 15ms; a replica lagging past 200ms means read-your-writes callers routed to it will observe stale data often enough to matter, well before the replica looks "overloaded" by CPU or disk metrics alone.
Why this avoids a coordination bottleneck: replicas are purely downstream consumers of a log — adding a fourth or fifth replica doesn't require the writer to know or coordinate with any of them individually, it just has more WAL subscribers. The bottleneck this design deliberately avoids creating is a scale-out mechanism that makes the writer's hot path aware of how many replicas exist.
offset 09
End-to-end assembly
One diagram, from memory, both paths at once.
BUILD FIRST
Draw the whole engine from memory — the hot/data path (a transaction from begin to commit) and the control/config path (checkpoint, replication, deadlock detection, admin) in a single diagram. Don't peek at segments 1–9 while you do it.
No answer key on this one: trace your own hand-drawn diagram back against the FR/NFR list you wrote in segment 1. Find the one requirement your diagram doesn't actually satisfy yet — every real design has at least one, and the ones that ship broken are the ones where nobody went looking for it before a customer did.