Under the Hood of OLTP: Compaction Storms, Index Bloat, and the Hidden Taxes on Your Cloud Bill

If you’ve been reading Martin Kleppmann’s Designing Data-Intensive Applications (specifically Chapter 4 on Storage and Retrieval), it’s easy to treat storage engines as flawless abstractions. We write a clean SQL query or throw a JSON document at an API, assuming the database will magically take care of the rest.

But underneath those high-level structures lies an unforgiving mechanical reality governed by physical hardware—such as page-allocation layouts, solid-state drive (SSD) block-wear lifecycles, and disk I/O channels.

When mapping these concepts to real-world architectures, the trade-offs become stark. To see how these abstractions handle intensive production environments, let's explore two specific operational failures—one featuring Log-Structured Merge (LSM) Trees and the other revolving around traditional B-Trees.

Case Study 1: The LSM-Tree “Compaction Storm” in High-Velocity IoT Streams

The Scenario: Imagine an Agri-Tech platform tracking continuous IoT telemetry streams (soil moisture, greenhouse humidity, truck temperatures) across 10,000 active farmlands. The system must ingest an unrelenting stream of 100,000 write operations per second.

Traditional relational databases struggle with this workload because their in-place storage engines trigger erratic, random disk seeks. An LSM engine (like RocksDB or Cassandra) handles this effortlessly on the write path by buffering updates in an in-memory tree layout (the MemTable) and continuously flushing them to disk as immutable, sequentially written Sorted String Table (SSTable) files. Because sequential writes are blindingly fast, application inserts remain steady and responsive.

The Hidden Friction: Read Amplification & Write Stalls

The dark side of this architecture emerges during peak operational spikes. LSM engines rely on background compaction threads to continuously clean up, merge, and purge duplicate or overwritten historical data segments.

If an unexpected write burst hits the engine, the following cascade occurs:

1. The L0 Explosion: Rapidly filling MemTables flush frequently to Level 0 (L0) storage. Because these are raw, un-compacted memory dumps, their keyspaces heavily overlap.
2. The Read Penalty: To service a simple point-lookup or time-range query for a sensor, the engine can no longer check a single file. It must scan all overlapping L0 SSTables, causing read latencies (P_99 and P_99.9) to skyrocket.
3. Compaction Saturation & Death Spiral: Background compaction threads aggressively wake up to merge these L0 files into Level 1 (L1) to clear the bottleneck. However, compaction is highly I/O-intensive. If compaction threads start violently fighting application threads for disk throughput, the engine triggers a Write Stall—deliberately freezing client writes to let background routines catch up. This introduces severe latency bubbles across the entire system.

System Tuning Blueprint

To prevent a "compaction storm" from tanking your system performance during a write spike, you can deploy a multi-layered tuning strategy:

• Hard I/O Rate Limiting: Put a strict ceiling on background tasks (using properties like RocksDB's rate_limiter) to cap compaction traffic to 50–60% of sequential write capacity, preserving the rest for client queries.
• Size-Tiered Compaction Strategy: Switch high-velocity streams to Size-Tiered or Universal compaction. This groups similarly sized SSTables and merges them less frequently, dramatically slashing the Write Amplification Factor (WAF) and freeing up immediate disk I/O.
• Scale Memory Buffers: Increase the write_buffer_size (e.g., from 64MB to 256MB or 512MB). Larger MemTables allow data to sit in RAM longer, giving the engine time to pre-sort and de-duplicate records in memory before ever touching physical storage.
• Maximize Bloom Filters: Allocate more bits per key (~14 bits) to your Bloom Filters so read threads can confidently short-circuit and bypass un-compacted disk segments without initiating expensive disk lookups.

For a comprehensive architectural breakdown of this scenario, view the full document: Case Study 1: Massive Write Spikes.

Case Study 2: The B-Tree “Index Bloat” Tax on Cloud Ledgers

The Scenario: Now pivot to a completely different workload: a core transactional ledger for a commodities exchange. The system processes millions of spot-market financial balances. It demands sub-millisecond, highly predictable read responses for real-time lookups and date-range auditing.

This is the natural home territory of a page-oriented B-Tree engine, like PostgreSQL. Because it organizes data into fixed-size blocks (typically 8KB) matching the native layout of the underlying filesystem, lookups remain shallow and deterministic. It completely lacks the erratic tail-latency spikes caused by LSM compaction cycles.

The Hidden Friction: Internal Fragmentation & Page Splitting

Instead of background cleanups, B-Trees utilize an In-Place Update philosophy. When a balance or status row is modified, the engine navigates down the tree pointer network, locates the exact 8KB leaf page on disk, and mutates those precise bytes directly.

The problem arises during random inserts. If a new record must be slotted into an 8KB leaf page that is already 100% full, the block cannot dynamically expand. Instead, it triggers a Page Split. The page fractures into two half-empty blocks, a new page is provisioned on disk, and the parent index is modified to accommodate the new boundary line. Over time, millions of updates and deletes result in deep internal fragmentation where your index pages sit at only 50% physical utilization.

The Three-Pronged Infrastructure Tax

In a cloud-managed environment (such as AWS RDS, Aurora, or GCP Cloud SQL backed by network storage like EBS or Persistent Disks), running a bloated 50% utilized index turns structural layout inefficiencies directly into an expensive monthly infrastructure tax across three vectors:

1. The "Dead Space" Capacity Tax: Cloud storage is billed based on provisioned capacity, not your active business rows. If you have 5 TB of actual ledger data, but aggressive page splits leave your pages half-empty, your database physically occupies 10 TB of disk space. You are actively paying a 2x premium on raw storage volume ($/GB/month) just to host empty disk bytes.
2. The Provisioned IOPS Bandwidth Tax: Data moves between disk and memory in fixed 8KB pages. The storage controller does not care if a page is completely full or half empty; it must fetch the full block. When information density drops by 50%, a date-range scan that should take 10 page reads suddenly requires 20 page reads. Because your database must execute twice as many physical I/O operations to accomplish the same task, you are forced to over-provision expensive IOPS and throughput tiers to maintain performance.
3. The Downstream Buffer Pool Memory Tax: This is often the most expensive line item. Databases don’t query the disk directly for every operation; they pull blocks into an in-memory buffer pool (RAM). Because memory management occurs at page granularity, a 50% full page consumes the exact same amount of precious, high-cost RAM as a 100% full page. Your active buffer pool becomes heavily diluted with empty space, driving up cache misses and forcing you to vertically scale your cloud instance sizes (e.g., from an r6g.2xlarge to an r6g.4xlarge) purely to acquire more RAM to fit your bloated working set.

For a detailed look at calculating internal fragmentation costs, view the full document: Case Study 2: Index Utilization Drop.

The Verdict: Hardware Always Wins

Ultimately, Chapter 4 teaches us that software design patterns are fundamentally constrained by hardware realities.

There is no magical, one-size-fits-all database engine. If you optimize for sequential appends, you pay a background housekeeping and read path complexity price. If you optimize for in-place page lookups, you pay a random-access write and space fragmentation tax. Choosing the right database is entirely about figuring out which specific hardware tax your application is best suited to pay.