Checkpoint in DBMS — Why 60s Timeout Killed Production I/O

Every production database — Postgres, MySQL InnoDB, Oracle, SQL Server — will crash at some point. Power cuts happen. Kernel panics happen. Someone trips over the wrong cable. The question isn't if your database will die mid-transaction; it's how fast it can get back on its feet with zero data loss. That answer lives almost entirely in one mechanism: the checkpoint.

Without checkpoints, crash recovery means replaying every single log record ever written — potentially years of transactions — before the database can accept a single query. That would make restarts take hours or days. Checkpoints solve this by periodically writing all dirty pages from memory to disk and recording a 'you can start recovery from here' marker in the write-ahead log. That single act turns a potential multi-hour recovery into seconds.

By the end of this article you'll understand exactly what happens inside a checkpoint cycle, the difference between sharp and fuzzy checkpoints and why fuzzy ones exist, how the WAL and buffer pool interact during checkpointing, what the performance trade-offs look like in PostgreSQL's real configuration knobs, and the production gotchas that bite engineers who treat checkpoints as a background detail.

What Is a Checkpoint and Why Does It Exist?

A checkpoint in a DBMS is a synchronization point where the buffer pool's dirty pages (modified but not yet on disk) are flushed to the data files, and the WAL is updated to mark that all changes up to that point are safely stored. This drastically shortens crash recovery: instead of replaying the entire WAL from the beginning, the database can start from the most recent checkpoint. The checkpoint record in the WAL contains the LSN (Log Sequence Number) that acts as the new recovery start point.

Checkpoints are not optional – every ACID-compliant database implements them. Without checkpoints, recovery time would grow linearly with the age of the database. A ten-year-old database would need to replay ten years of transactions after each restart. That simply doesn't work at scale.

-- PostgreSQL: Checkpoint in action
-- This triggers a manual checkpoint
CHECKPOINT;

-- Check checkpoint-related statistics
SELECT checkpoints_timed, checkpoints_req,
       buffers_checkpoint, buffers_clean, buffers_backend,
       checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;

-- Show last checkpoint location
SELECT pg_current_wal_lsn(), 
       pg_last_checkpoint_location() AS last_checkpoint_lsn,
       pg_last_checkpoint_timestamp();

WAL and Checkpoint Interaction: The Recovery Handshake

The Write-Ahead Log (WAL) records every change before it's applied to data files. A checkpoint ensures that all WAL records up to a certain LSN have been fully applied to the data files on disk. After checkpoint, the WAL can be truncated up to that LSN. During recovery, the database reads the last checkpoint location from the WAL and replays all subsequent records. This is called the redo phase. Some databases also have an undo phase to roll back incomplete transactions.

The key insight: the checkpoint record itself is written after all dirty pages are safely on disk. If the checkpoint record is missing, recovery falls back to the previous checkpoint. That's why checkpoint writes are synchronous and include a WAL flush. The checkpoint writes use direct I/O to ensure persistence.

-- Show where the last checkpoint is in the WAL
SELECT pg_last_checkpoint_location() AS checkpoint_lsn;

-- Show the LSN of a recent transaction
SELECT txid_current(), pg_current_wal_lsn();

-- After a checkpoint, old WAL segments are reusable
SELECT pg_switch_wal(); -- Force WAL segment switch
CHECKPOINT;
-- Now inspect WAL directory
SELECT * FROM pg_ls_waldir() ORDER BY name;

Sharp vs Fuzzy Checkpoints: The Performance Trade-off

Sharp checkpoints flush all dirty pages at one moment. This is simple but causes a massive I/O spike. Fuzzy checkpoints (used by PostgreSQL, MySQL InnoDB) spread the flushing over a configurable window. They start writing dirty pages early in the interval and try to complete before the next checkpoint. The WAL checkpoint record marks the boundary, but data writes are asynchronous.

In PostgreSQL, checkpoint_completion_target controls how much of the interval is used for flushing. A value of 0.9 means 90% of checkpoint_timeout is spent writing dirty pages, with the final 10% reserved for the sync. This avoids a sudden burst but requires a steady I/O capacity.

Sharp checkpoints still exist in some systems (e.g., SQL Server simple recovery model), but modern systems prefer fuzzy for stability.

-- View current checkpoint parameters
SHOW checkpoint_timeout;
SHOW checkpoint_completion_target;
SHOW max_wal_size;
SHOW min_wal_size;

-- Example: configure for a heavy-write workload
ALTER SYSTEM SET checkpoint_timeout = '10min';
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
ALTER SYSTEM SET max_wal_size = '4GB';
ALTER SYSTEM SET min_wal_size = '1GB';
-- Reload configuration
SELECT pg_reload_conf();

Checkpoint Tuning in PostgreSQL: Real Knobs and Constraints

PostgreSQL provides several knobs to control checkpoint behaviour. The most important are:

• checkpoint_timeout: Maximum time between automatic checkpoints (default 5 minutes).
• max_wal_size: Target for total WAL size. When exceeded, a checkpoint is forced.
• min_wal_size: Minimum WAL size to keep for recycling.
• checkpoint_completion_target: Fraction of checkpoint_timeout spent flushing dirty pages.
• checkpoint_flush_after: Number of pages after which to flush writes to OS.

Understanding the interaction: checkpoints can be triggered by time (timed checkpoints) or by WAL size (requested checkpoints). The pg_stat_bgwriter view shows how many of each occurred. A high ratio of requested to timed means the WAL size is often hitting max_wal_size — that can signal write bursts or insufficient max_wal_size.

-- Analyze checkpoint effectiveness
SELECT 
  (checkpoints_timed + checkpoints_req) AS total_checkpoints,
  checkpoints_timed,
  checkpoints_req,
  buffers_checkpoint,
  buffers_clean,
  buffers_backend,
  (buffers_backend * 100.0 / NULLIF(buffers_checkpoint, 0)) AS backend_write_ratio
FROM pg_stat_bgwriter;

-- Check if checkpoints are being forced by WAL size
SELECT * FROM pg_stat_bgwriter 
WHERE checkpoints_req > checkpoints_timed;

-- View current WAL size
SELECT pg_size_pretty(SUM(size)) FROM pg_ls_waldir();

Common Checkpoint Mistakes and How to Fix Them

Even experienced DBAs misconfigure checkpoints. The most common issues:

1. Using default settings in production: Default checkpoint_timeout=5min may be fine for dev but causes I/O spikes in heavy-load production. Always baseline I/O and adjust.
2. Ignoring full_page_writes: PostgreSQL writes full page images to WAL during first modification after a checkpoint. Disabling this saves space but risks page corruption on crash. Only disable on replicas with data checksums.
3. Setting checkpoint_completion_target >0.95: Leaves no buffer for OS I/O delays. A checkpoint that doesn't complete before the next timeout triggers a forced sync, causing a sharp I/O spike.
4. Not monitoring buffers_backend: If backend processes are writing dirty pages themselves, the checkpointer is overwhelmed. Increase checkpoint_timeout or add I/O capacity.

Checkpoint Impact on Recovery Time: Why a 5-Minute Checkpoint Can Mean 30-Minute Recovery

When a junior asks why recovery takes so long after a crash, the answer isn't magic—it's checkpoint frequency. Every dirty page not yet flushed to disk must be replayed from the WAL during recovery. The checkpoint record is the starting line for that replay. If your checkpoint interval is 5 minutes, you risk replaying up to 5 minutes of transactions. In high-throughput systems, that's gigabytes of WAL. Recovery time grows linearly with the distance from the last checkpoint. The real pain hits when you have a burst of writes just before the crash—your last checkpoint can be minutes old, and the system spends that entire time scanning and replaying. The fix isn't simply cranking up checkpoint frequency; that creates its own overhead. You need to match your checkpoint interval to your acceptable recovery time objective (RTO). A rule I've learned the hard way: if you can't afford 10 minutes of recovery, don't let your checkpoint drift past 5 minutes. Measure your WAL generation rate, calculate the redo work, and set that interval with purpose.

// io.thecodeforge
import psycopg2

def get_recovery_estimate(conn):
    """Estimate recovery time from last checkpoint."""
    cur = conn.cursor()
    
    # Get last checkpoint WAL position
    cur.execute("""
        SELECT redo_lsn, redo_wal_size
        FROM pg_stat_bgwriter
    """)
    redo_lsn, redo_wal_size = cur.fetchone()
    
    # Current WAL write position
    cur.execute("SELECT pg_current_wal_lsn()")
    current_lsn = cur.fetchone()[0]
    
    # Bytes of WAL to replay (simplified)
    bytes_behind = pg_lsn_diff(current_lsn, redo_lsn)
    
    # Assume 100 MB/s replay speed (SSD)
    replay_speed_mbps = 100
    seconds = (bytes_behind / (1024 * 1024)) / replay_speed_mbps
    
    print(f"WAL to replay: {bytes_behind / 1e6:.1f} MB")
    print(f"Estimated recovery: {seconds:.1f} seconds")
    
    return seconds

Fuzzy Checkpoints in Action: How PostgreSQL Actually Writes Dirty Pages

Sharp checkpoints are the dream—stop the world, write everything, proceed. Real databases use fuzzy checkpoints because they don't block writes. Here's how it works in PostgreSQL: a background process (checkpointer) scans the shared buffer pool, writing all dirty pages to disk. But it doesn't hold a lock on those pages. New writes can happen concurrently, turning a just-flushed page dirty again. The checkpoint becomes 'fuzzy' because it doesn't capture a consistent snapshot of the buffer pool at a single point in time. The WAL ensures consistency: the checkpoint record is written after all prior dirty pages are flushed, but before the next transaction commits. Recovery uses that record to know where to start replay. The performance trade-off is worth it: writes never stall. But you pay in complexity—the checkpointer must coordinate with the WAL writer to ensure no dirty page is written before its WAL record hits disk. I've seen teams disable background writes thinking they'd speed things up. Don't. Let the fuzzy checkpoint do its job, and tune the number of buffers it writes per cycle (checkpoint_completion_target) to spread I/O load smoothly.

// io.thecodeforge
const { Client } = require('pg');

async function checkCheckpointHealth() {
  const client = new Client({ database: 'postgres' });
  await client.connect();

  const res = await client.query(`
    SELECT
      checkpoint_write_time / 1000 AS write_secs,
      checkpoint_sync_time / 1000 AS sync_secs,
      buffers_checkpoint,
      buffers_clean,
      maxwritten_clean
    FROM pg_stat_bgwriter
  `);

  const stats = res.rows[0];
  
  // Fuzzy checkpoint is healthy if write time < sync time
  if (stats.write_secs > stats.sync_secs * 2) {
    console.warn('Checkpoint write phase dominating. Increase checkpoint_completion_target.');
  }
  
  if (stats.maxwritten_clean > 0) {
    console.warn('Background writer hitting max limit. Dirty buffers piling up.');
  }
  
  console.log(`Checkpoint writes: ${stats.write_secs}s, sync: ${stats.sync_secs}s`);
  await client.end();
}

checkCheckpointHealth().catch(console.error);