Disk I/O Saturation

The storage device reaches its IOPS or throughput ceiling, causing all disk- dependent database operations to queue behind I/O requests, driving latency from sub-millisecond to hundreds of milliseconds and degrading all database operations simultaneously.

Database performance is ultimately bounded by storage I/O. PostgreSQL requires disk I/O for: WAL writes (every write transaction), dirty buffer flushes (checkpoint), uncached page reads (data not in shared_buffers), autovacuum (reading and writing heap pages), and temporary files (sort and hash operations spilling to disk). When the cumulative I/O demand from all these sources exceeds the storage device's capacity, the I/O subsystem queues requests.

AWS EBS volume characteristics: GP2 provides burst to 3,000 IOPS up to a burst credit bucket, with baseline at 3 IOPS/GB (a 100GB GP2 volume has 300 IOPS baseline). GP3 provides 3,000 IOPS baseline regardless of size, configurable up to 16,000 IOPS. io1/io2 provides up to 64,000 IOPS. A GP3 volume at 3,000 IOPS baseline will saturate with PostgreSQL running a sustained checkpoint + autovacuum + OLTP write workload on a moderately sized table.

I/O saturation is insidious because it affects all operations uniformly. A checkpoint consuming 2,800 of the 3,000 available IOPS leaves only 200 IOPS for WAL writes. WAL writes stall. Transaction commits stall waiting for WAL fsync. All active write transactions queue. The database appears frozen with no queries completing. Read operations that require uncached pages also queue. Even simple queries slow dramatically.

The Linux I/O scheduler queues I/O requests when the device is saturated. Queue depth grows. The kernel reports iowait CPU time: the fraction of CPU time spent waiting for I/O rather than executing instructions. Sustained iowait > 10% on a database host is a warning; iowait > 30% indicates severe saturation.

Temporary file I/O is a particularly dangerous contributor: a query that exceeds work_mem writes a sort or hash file to disk. A poorly indexed query sorting 10GB of data generates 10GB of disk reads (sort input) + 10GB of writes (sort output) : consuming I/O capacity for the duration of the query and degrading all concurrent operations.

I/O access patterns differ by storage engine shape, not just by workload. B-Tree engines (PostgreSQL heap tables plus B-Tree indexes, InnoDB's clustered B-Tree) generate substantial random I/O: an index lookup walks non-contiguous pages, and a row fetch through a secondary index, or in InnoDB through the clustered index after an indirection lookup, touches pages scattered across the file. This is why B-Tree engines are far more sensitive to a storage device's random-IOPS ceiling than to its raw sequential throughput. LSM engines such as Cassandra shift the write side toward sequential I/O: memtable flushes and compaction both write sorted, immutable runs as sequential append operations, which is cheap on both spinning disks and SSDs. The cost moves to reads instead. A point read may have to probe the active memtable and multiple SSTables across several levels before it finds, or rules out, a key, and each SSTable probe is a separate I/O unless a Bloom filter attached to that SSTable short-circuits the miss without touching disk. This read amplification grows with compaction debt (see lsm_compaction_debt): the more unmerged SSTables accumulate, the more of them a single read may have to check. Columnar/OLAP engines are a third shape again. ClickHouse's MergeTree engine is built around large sequential scans over column files; there is comparatively little random I/O in steady state. I/O saturation there is usually a function of scan volume and codec choice (LZ4 by default, or ZSTD, Delta, and DoubleDelta for better ratios at more CPU cost) rather than seek contention, so the primary levers are pruning, letting the primary key and skip indexes rule out whole granules before they are read, and compression, not raw IOPS provisioning.