Fsync & Commit Latency

Why guaranteeing you won't lose data makes your database slow.

The idea

When you tell a database (like PostgreSQL) to save data, it doesn't just write to the physical hard drive instantly. Hard drives are slow. To go fast, the Operating System caches the write in RAM (the Page Cache) and says "Done!". But if the server loses power 1 millisecond later, the RAM is wiped, and your "saved" data is gone. To prevent this, databases use a system call called fsync(). Fsync explicitly forces the OS to flush the data from RAM down to the physical disk platter or SSD cells, and blocks the database until the hardware confirms it is safe. This guarantees Durability, but it introduces massive Commit Latency.

Step 1: Fsync is OFF. The DB writes to RAM. It is extremely fast (1ms).

How it works (The WAL)

To minimize the pain of fsync(), relational databases use a Write-Ahead Log (WAL). Instead of doing an expensive, random fsync across the entire database file to update a row, they do a fast, sequential append to the end of the WAL file, and fsync() just that log. If the power fails, the DB reads the log on reboot and reconstructs the data.

// Pseudocode of a Database Commit

function commitTransaction(data) {
    // 1. Write the change to the WAL file (Sequential)
    file.write(WAL, data);
    
    // 2. FORCE the OS to flush it to hardware disk
    // This is the bottleneck! It takes ~5ms on an SSD.
    os.fsync(WAL); 
    
    // 3. Only after hardware confirms, tell the user it succeeded
    return "Commit Successful!";
}

Cost

You cannot cheat physics. If an SSD takes 5 milliseconds to flush data, and your database forces an fsync() on every single commit, your maximum theoretical throughput is 200 transactions per second (1000ms / 5ms), no matter how many CPUs you have. To go faster, you have to use Group Commit (bundling multiple transactions into one fsync), or you have to turn off fsync entirely (accepting that a power failure will destroy the last 1 second of data).

Watch out for