The performance ceiling is in the hardware

No matter how smart the query optimizer or how clever the index design, every SQL statement ultimately runs on a CPU and fetches data from memory or disk. Many “counter-intuitive” database designs exist to respect the physical characteristics of hardware:

┌────────────────────────────────────────────────────────┐
│                     Database System                     │
├────────────────────────────────────────────────────────┤
│   Query Engine / Index / Transaction  ← software layer  │
├────────────────────────────────────────────────────────┤
│   CPU   │   RAM    │   SSD   │   HDD   ← hardware traits │
│  ~1ns   │  ~100ns  │ ~100μs  │  ~10ms  │  10⁷× speed gap
└────────────────────────────────────────────────────────┘

A key fact: storage closer to the CPU is faster, but more expensive and smaller. From registers to disk, latency spans seven orders of magnitude:

Storage TierTypical LatencyCapacityVolatile
L1 Cache~1 nsKBYes
L2/L3 Cache~10 nsMBYes
Main Memory (DRAM)~100 nsGBYes
SSD (NAND)~100 μsTBNo
HDD (Disk)~10 msTB+No

What understanding hardware solves

  • Why is sequential IO far faster than random IO? — see Hard Disk Drives and SSDs
  • Why does a buffer pool boost performance so much? — memory is ~10⁵× faster than disk (see Memory)
  • Why is columnar / vectorized execution fast? — CPU SIMD and cache-line friendliness (see CPU)
  • Why write WAL first? — sequential writes beat random writes on both SSD and HDD

Contents of this series

This series decomposes the root causes of database performance from a hardware perspective:

  1. How CPUs Work: instruction pipelines, cache hierarchy, branch prediction, SIMD
  2. How Memory Works: DRAM cells, refresh, bandwidth, NUMA
  3. How SSDs Work: NAND floating gate, FTL, write amplification, wear leveling
  4. How HDDs Work: platters, seek, rotational latency, IOPS

Understand these, and you can move from “tuning parameters” to “designing storage and queries from first principles”.

References