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);

Checkpoint in PostgreSQL: Full Page Writes and Checkpoint Distance

In PostgreSQL, checkpoints are tightly coupled with full page writes (FPW) and checkpoint distance. Full page writes ensure crash recovery by writing the entire page image during the first modification after a checkpoint. This prevents torn pages—partial writes that corrupt data. However, FPW increases WAL volume, especially with frequent checkpoints. The checkpoint distance parameter (checkpoint_completion_target) controls how much WAL is generated between checkpoints. A common mistake is setting checkpoint_timeout too low (e.g., 30 seconds) combined with a high checkpoint_completion_target (e.g., 0.9), causing I/O spikes. For example, with a 1GB shared_buffers and checkpoint_completion_target=0.9, the system tries to spread writes over 90% of the timeout interval, but if the timeout is 30s, it must flush all dirty pages in 27s, leading to bursty I/O. Practical tuning: set checkpoint_timeout to 5-15 minutes and checkpoint_completion_target to 0.7-0.8 to smooth I/O. Monitor pg_stat_bgwriter to see checkpoint write rate and buffers allocated. A production insight: on SSDs, reduce checkpoint_completion_target to 0.5 to avoid write amplification from FPW.

-- Check current checkpoint settings
SHOW checkpoint_timeout;
SHOW checkpoint_completion_target;
SHOW full_page_writes;

-- Monitor checkpoint activity
SELECT * FROM pg_stat_bgwriter;

-- Example: Tune for balanced I/O
ALTER SYSTEM SET checkpoint_timeout = '10min';
ALTER SYSTEM SET checkpoint_completion_target = 0.7;
ALTER SYSTEM SET full_page_writes = on;
SELECT pg_reload_conf();

Write-Ahead Logging and Recovery: ARIES Algorithm

The ARIES (Algorithm for Recovery and Isolation Exploiting Semantics) algorithm is the foundation of PostgreSQL's WAL-based recovery. It uses three phases: analysis, redo, and undo. During analysis, the system scans the WAL from the last checkpoint to determine dirty pages and active transactions. Redo reapplies changes from the checkpoint to the end of WAL to bring the database to a consistent state. Undo rolls back uncommitted transactions using log records. A key concept is the LSN (Log Sequence Number), which marks the position in WAL. Each data page stores the LSN of the last change; during recovery, only pages with LSN < redo LSN are redone. For example, if a checkpoint's redo LSN is 1000 and a page's LSN is 950, it must be redone. Practical implication: checkpoint frequency affects recovery time because the redo phase must process all WAL since the last checkpoint. A 5-minute checkpoint can mean 30-minute recovery if the WAL volume is high. ARIES also supports partial rollbacks and savepoints. In PostgreSQL, the recovery process is automatic on crash, and you can monitor progress via pg_stat_progress_recovery. Production insight: to minimize recovery time, ensure checkpoints are frequent enough to keep the redo scope small, but not so frequent that they cause I/O storms.

-- Check current WAL LSN position
SELECT pg_current_wal_lsn();

-- Monitor recovery progress (during recovery)
SELECT * FROM pg_stat_progress_recovery;

-- Simulate recovery: view checkpoint redo LSN
SELECT redo_lsn, redo_wal_size FROM pg_control_checkpoint();

-- Example: Calculate recovery time estimate
-- Requires pg_stat_bgwriter and WAL generation rate

Fuzzy Checkpoints vs Sharp Checkpoints

Fuzzy checkpoints and sharp checkpoints represent two strategies for writing dirty pages to disk. Sharp checkpoints (used in some databases like early MySQL/InnoDB) flush all dirty pages at once, blocking all writes during the process. This guarantees a consistent state but causes severe I/O spikes and latency. Fuzzy checkpoints (used by PostgreSQL) spread the write of dirty pages over time, allowing concurrent writes. PostgreSQL's checkpoint process writes dirty pages in batches, using a background writer and checkpointer. The key advantage: fuzzy checkpoints avoid the 'checkpoint stall' that kills production I/O. For example, a sharp checkpoint on a 100GB buffer pool could stall writes for minutes, causing timeouts. Fuzzy checkpoints, with checkpoint_completion_target, smooth the I/O over the checkpoint interval. However, fuzzy checkpoints mean the checkpoint record in WAL is written before all dirty pages are flushed, requiring a redo phase during recovery. Practical example: In PostgreSQL, you can observe fuzzy checkpoint behavior via pg_stat_bgwriter: checkpoints_timed vs checkpoints_req. A high number of checkpoints_req indicates the system is forced to checkpoint due to WAL size, often a sign of misconfiguration. Production insight: For OLTP workloads, fuzzy checkpoints are superior; sharp checkpoints are only acceptable for batch processing or when recovery time is not critical.

-- Check checkpoint statistics
SELECT checkpoints_timed, checkpoints_req, buffers_checkpoint, buffers_clean, maxwritten_clean
FROM pg_stat_bgwriter;

-- Simulate sharp checkpoint behavior (not recommended)
-- Force a checkpoint that writes all dirty pages synchronously
CHECKPOINT;

-- View checkpoint timing
SELECT * FROM pg_stat_checkpointer;

-- Example: Detect if fuzzy checkpoint is smoothing I/O
SELECT (buffers_checkpoint / (checkpoints_timed + checkpoints_req)) AS avg_buffers_per_checkpoint
FROM pg_stat_bgwriter;