0Pricing
PostgreSQL Performance & Query Optimization · Lesson

Tuning WAL and Checkpoints for Ingestion

Adjust WAL settings and unlogged tables to sustain high write rates without I/O stalls.

Why WAL Matters for Ingestion

Every change you commit in PostgreSQL is first written to the Write-Ahead Log (WAL) before the data files are updated. This guarantees durability and crash recovery, but during heavy bulk loads the WAL becomes a major source of I/O.

  • Each INSERT or COPY generates WAL records.
  • Periodically a checkpoint flushes dirty pages from shared buffers to disk.
  • If checkpoints fire too often, you pay double I/O and get stalls.

Tuning WAL and checkpoint behaviour is the key to sustaining high write rates without I/O spikes.

Inspecting Current WAL Settings

Before changing anything, look at what your server is running with. The relevant knobs live in pg_settings and can be queried with SHOW.

The most important ingestion-related parameters are max_wal_size, checkpoint_timeout, checkpoint_completion_target, and wal_compression.

SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'max_wal_size',
  'min_wal_size',
  'checkpoint_timeout',
  'checkpoint_completion_target',
  'wal_compression'
);

All lessons in this course

  1. COPY vs Multi-Row INSERT Throughput
  2. Deferring Indexes and Constraints During Load
  3. Tuning WAL and Checkpoints for Ingestion
  4. Upserts at Scale with ON CONFLICT
← Back to PostgreSQL Performance & Query Optimization