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.