PostgreSQL WAL Settings Query Guide

PostgreSQL WAL Settings Query Guide

This PostgreSQL query retrieves all Write-Ahead Log (WAL) configuration settings from the pg_settings system view. WAL settings control durability, crash recovery, checkpointing, archiving, and replication log generation.

Purpose and Overview

The Write-Ahead Log is the foundation of PostgreSQL's durability guarantee. Every data change is written to WAL before it is applied to the data files, ensuring that committed transactions survive a crash. WAL settings also control whether enough log is generated for streaming replication and point-in-time recovery.

Understanding and correctly configuring WAL settings is critical for database reliability, recovery time objectives (RTO), and replication topology.

SQL Script

 1SELECT
 2    name,
 3    setting,
 4    -- category,
 5    vartype,
 6    short_desc,
 7    -- enumvals,
 8    source,
 9    min_val,
10    max_val,
11    boot_val,
12    reset_val,
13    -- not available on PostgreSQL < 9.5
14    pending_restart
15FROM
16    pg_settings
17WHERE
18    -- Write-Ahead Log / Settings
19    -- Write-Ahead Log / Recovery Target
20    -- Write-Ahead Log / Checkpoints
21    -- Write-Ahead Log / Archive Recovery
22    -- Write-Ahead Log / Archiving
23    category ILIKE '%Write-Ahead Log%';

Code Breakdown

Filter Condition

1WHERE category ILIKE '%Write-Ahead Log%'

Matches all subcategories under the Write-Ahead Log group:

• Write-Ahead Log / Settings — core WAL configuration
• Write-Ahead Log / Checkpoints — checkpoint frequency and behaviour
• Write-Ahead Log / Archiving — WAL archiving to external storage
• Write-Ahead Log / Archive Recovery — recovery and restore command settings
• Write-Ahead Log / Recovery Target — point-in-time recovery target configuration

Selected Columns

name: The configuration parameter name such as wal_level or fsync.

setting: The current active value.

vartype: The data type (bool, integer, real, string, enum).

short_desc: A brief description of what the parameter controls.

source: Where the value originates — default, configuration file, or command line.

min_val / max_val: Allowed range for numeric parameters.

boot_val: Compiled-in default value.

reset_val: Value used if the parameter is reset.

pending_restart: Whether a server restart is needed for a change to take effect.

Key WAL Parameters

Core WAL Settings

wal_level — Controls how much information is written to WAL. Options: minimal (crash recovery only), replica (streaming replication), logical (logical decoding and replication). Must be replica or logical for standby servers to connect.

fsync — When on, PostgreSQL uses fsync() calls to ensure data is written to disk before acknowledging commits. Disabling fsync dramatically increases performance but risks data corruption after a crash. Should never be disabled on production servers.

synchronous_commit — Controls when a transaction is considered committed relative to WAL flush. Options range from off (async) to remote_apply (synchronous on standby). Setting to off improves write performance with a small risk window of data loss on crash.

wal_sync_method — The method used to force WAL to disk. Options include fsync, fdatasync, open_sync, and open_datasync. The default is usually optimal for the platform.

wal_buffers — Amount of shared memory used for WAL data not yet written to disk. Default is auto-tuned. Increase for write-heavy workloads.

wal_writer_delay — How often the WAL writer flushes WAL to disk when not otherwise forced by commits.

wal_writer_flush_after — Amount of WAL written by the WAL writer before triggering a flush to disk.

wal_compression — When enabled, compresses WAL records for full-page writes. Reduces WAL volume at the cost of CPU.

wal_init_zero — When enabled, new WAL files are pre-filled with zeros before use. Reduces variability in WAL write latency.

Checkpoints

checkpoint_timeout — Maximum time between automatic checkpoints. Longer intervals reduce checkpoint frequency and associated I/O burst, at the cost of longer crash recovery time.

checkpoint_completion_target — Fraction of the checkpoint interval over which checkpoint writes are spread. Default of 0.9 spreads I/O over 90% of the interval to reduce peak disk load.

checkpoint_warning — Logs a warning if checkpoints are triggered more frequently than this interval, indicating WAL generation rate is too high for the current checkpoint configuration.

max_wal_size — Maximum total WAL size that triggers an automatic checkpoint. Larger values reduce checkpoint frequency but increase crash recovery time.

min_wal_size — Minimum WAL disk space reserved. PostgreSQL will not shrink WAL below this size.

Archiving

archive_mode — Enables WAL archiving. Options: off, on, always. Must be on or always for point-in-time recovery (PITR) and WAL-based standbys that use archive recovery.

archive_command — The shell command used to archive a completed WAL segment. Must return exit code 0 on success.

archive_timeout — Forces a WAL segment switch after this interval, ensuring WAL is archived even during low-activity periods. Important for minimising data loss window in PITR setups.

Recovery Target (PITR)

recovery_target_action — Action to take when the recovery target is reached: pause, promote, or shutdown.

restore_command — The shell command used to retrieve archived WAL segments during recovery. Required for archive recovery and PITR.

Practical Applications

Durability Audit

Verify fsync = on and synchronous_commit is set appropriately for your durability requirements. Document any deviation from the defaults with justification.

Replication Prerequisites

Confirm wal_level = replica or logical before setting up streaming replication. A minimal WAL level will prevent standbys from connecting.

PITR Setup Verification

Check archive_mode, archive_command, and archive_timeout together to confirm point-in-time recovery is properly configured. A misconfigured archive_command that returns non-zero exit codes silently stops archiving.

Checkpoint Tuning

Review checkpoint_timeout, max_wal_size, and checkpoint_completion_target if you observe periodic I/O spikes. Increasing max_wal_size and the timeout spreads checkpoint I/O over a longer interval.

Version Compatibility

Requires PostgreSQL 9.5 or later due to the pending_restart column. wal_compression was added in PostgreSQL 9.5. max_wal_size and min_wal_size replaced checkpoint_segments in PostgreSQL 9.5. Tested on PostgreSQL 9.5, 9.6, 10.x, 11.x, 12.x, and 13.0.

Best Practices

Never disable fsync in production: The performance gain is not worth the risk of data corruption. If write throughput is a bottleneck, tune wal_buffers, synchronous_commit, and checkpoint settings instead.

Set archive_timeout to limit data loss window: In a PITR setup, WAL is only archived after a segment is complete (default 16 MB). If activity is low, this could mean hours of exposure. Set archive_timeout to a value consistent with your recovery point objective (RPO).

Monitor checkpoint frequency: Enable log_checkpoints = on and watch for frequent checkpoint warnings. Checkpoints more frequent than checkpoint_timeout indicate WAL generation is high and max_wal_size may need to be increased.

Verify archive_command independently: Test the archive command by running it manually with a real WAL filename. Silent failures in archive_command can go undetected until recovery is needed.

References

• PostgreSQL Documentation - Write Ahead Log - Official reference for all WAL configuration parameters
• PostgreSQL Documentation - Reliability and the Write-Ahead Log - Conceptual guide to WAL operation and durability
• PostgreSQL Documentation - Continuous Archiving - Guide to WAL archiving and point-in-time recovery
• Source SQL Script - postgres_settings_wal.sql - Original script by Hari Sekhon