Why do databases use B+ Trees instead of Binary Search Trees or B-Trees (where internal nodes also store row payloads)? Explain the exact hardware and WAL.

A strong answer

A strong candidate frames Why do databases use B+ Trees instead of Binary Search Trees or B-Trees (where internal nodes also store row payloads)? Explain the exact hardware and WAL. around the exact memory layout, compiler semantics, and hardware execution model covered in [B+ Tree Fan-Out Math, Leaf Sibling Links, and Why Random UUIDv4 Primary Keys Destroy Write Throughput (UUIDv7 Fix)]. Calculate internal node fan-out ($B \approx 500$ because internal nodes omit row payloads!), trace leaf doubly-linked sibling pointers for range scans, and contrast UUIDv4 midpoint splits + WAL Full-Page Writes with UUIDv7's 48-bit timestamp prefix.

Your team switches a 500-million-row orders table's primary key from BIGSERIAL to random UUIDv4 (gen_random_uuid()). Once the B+ Tree index exceeds available RAM (64 GB), insert throughput collapses from 45,000 inserts/sec to 1,800 inserts/sec, the B+ Tree index bloats to 2x its expected disk size (~50% fill factor), and Write-Ahead Log (WAL) disk bandwidth spikes by 8x! Why did changing how the 16-byte UUID bits are generated destroy the storage engine?

When engineers encounter this bottleneck in production, a surface-level API description rarely explains what went wrong. In real workloads, every line of code executes against concrete hardware constraints: fixed-width memory cells, CPU cache lines, kernel descriptor tables, network socket buffers, or accelerator memory buses. Without a clear mental model of B+ Tree Fan-Out, Leaf Page Splits, Fragmentation, and UUIDv4 vs Time-Ordered UUIDv7, debugging degrades into guesswork—tuning timeouts, adding retries, or scaling instances blindly while the underlying architectural flaw remains untouched.

In a production interview, always connect the specification rule to its concrete runtime consequence: state what lives on the stack versus the heap or static storage segment, how the compiler or operating system validates alignments, lifetimes, and error codes, and what happens under adversarial or boundary inputs (such as zero-length buffers, integer overflow, concurrent mutation, or partial I/O). Backing your explanation with a small trace of register/memory state transitions immediately separates senior systems engineers from candidates who only memorized syntax.

What the interviewer is listening for

• Mastery of core invariant: Calculate B+ Tree branching factor ($B \approx 500$) and explain why storing payloads only in leaf nodes keeps tree height $\le 4$.
• Mastery of core invariant: Diagnose why random UUIDv4 keys cause 50% midpoint page-split fragmentation, Buffer Pool thrashing, and WAL Full-Page Write storms.
• Mastery of core invariant: Design append-friendly primary keys using RFC 9562 UUIDv7 and native 16-byte binary UUID storage.
• Hardware and ABI awareness: Explaining how sizes, alignments, calling conventions, or kernel boundaries behave on real 64-bit targets rather than relying on implementation-defined assumptions.
• Defensive engineering: Naming the exact compiler flags (-Wall -Wextra -Werror), static analyzers, or runtime sanitizers (-fsanitize=address,undefined,thread) that verify correctness in CI.

Common mistakes

• Explaining only surface syntax without tracing the underlying memory addresses, ownership lifetime, or kernel/user-space state transitions.
• Assuming undefined behavior (such as signed overflow, uninitialized reads, strict-aliasing violations, or dangling pointers) will "just work" because it passed an unoptimized -O0 debug build.
• Ignoring return codes, partial reads/writes, alignment padding, or cleanup paths when an allocation or syscall fails midway through a function.