How does flash garbage collection affect checkpoint spikes?
In the architecture of a high-performance database, the CHECKPOINT is the heavy lifter. It’s the moment the Database Writer (DBWn) flushes dirty buffers from RAM to persistent storage. While we expect a surge in I/O during this time, the "performance heart attack" often felt during a checkpoint is actually caused by a collision with Flash Garbage Collection (GC).
To your database, this manifests as a "stall" where I/O latency triples for no apparent reason. To the SSD, it’s a desperate race to clean up the mess you just made.
Flash memory cannot simply overwrite data. To change a single bit, the SSD controller must:
Read an entire Block (made of many Pages).
Erase the Block.
Write the new data back.
The Checkpoint Trigger: During a database checkpoint, you are sending a massive burst of random writes to the SSD. This quickly fills up the "Free Pages." The SSD controller is forced to start Garbage Collection—moving "stale" data around to create empty blocks—at the exact same time your database is trying to finish its checkpoint.
Here is how the interaction degrades your database performance:
The Burst: The Checkpoint starts. The SSD uses its internal DRAM cache and SLC buffer to handle the first few seconds of writes. Latency is low ($<50\mu s$).
The Saturation: The SSD's "clean" blocks run out. The Garbage Collector kicks into high gear.
The Conflict: The SSD internal bus is now split between Host Writes (your data) and Internal Moves (GC).
The Spike: Because the controller is busy erasing blocks (a slow process), your "Write Latency" spikes from $50\mu s$ to $5,000\mu s$ or more.
The Database Hang: The Database Writer (DBWn) stalls. In Oracle, this triggers checkpoint not complete or high db file parallel write waits. Since the DBWn is stuck, the log writer might also stall, freezing the entire application.
The severity of the spike depends heavily on the NAND type:
| Feature | SLC / Optane | Enterprise TLC |
| GC Overhead | Minimal (Simple mapping) | Heavy (Complex voltage management) |
| Checkpoint Latency | Flat line | "Sawtooth" pattern (Spiky) |
| Recovery Time | Instant | Minutes (Controller must "catch up") |
You can’t stop Garbage Collection, but you can prevent it from colliding with your checkpoints:
By leaving 20% of your SSD unpartitioned, you give the Garbage Collector more "scratch space" to work with. This reduces Write Amplification and allows the GC to run more efficiently in the background without blocking your checkpoint writes.
Instead of a "Panic Checkpoint" (flushing everything at once), use incremental checkpointing.
Oracle: Tune FAST_START_MTTR_TARGET.
PostgreSQL: Adjust checkpoint_completion_target (e.g., to 0.9).
Result: This spreads the I/O load over a longer period, allowing the SSD's Garbage Collector to keep up without hitting the "latency wall."
Ensure your OS and Filesystem support TRIM/UNMAP. This tells the SSD which blocks are no longer used by the database (e.g., after a table drop). This allows the Garbage Collector to ignore those blocks, significantly reducing the amount of data it has to move during a checkpoint.
A "Checkpoint Spike" is rarely just a software issue; it is a hardware-scheduling conflict. When your database flushes data faster than the SSD can clean its internal blocks, the resulting Garbage Collection "stall" creates the latency spikes that kill application responsiveness. By smoothing your checkpoints and over-provisioning your flash, you give the SSD the "breathing room" it needs to stay fast.