Architecture and operating model
PostgreSQL writes data pages during checkpoints while WAL records ordered changes for crash recovery. A checkpoint triggered by checkpoint_timeout or max_wal_size can increase application latency and storage queue depth when a large dirty-page set is written in a short period. Not every slow commit is a checkpoint problem; separate WAL flush, data-file writes and lock waits.
checkpoint_completion_target helps spread writes across the checkpoint interval. Lowering it can compress work into a shorter window and increase peak I/O. Increasing max_wal_size may reduce checkpoint frequency, but crash-recovery time, disk usage and replication-slot or archive retention must be considered.
Depending on PostgreSQL release, pg_stat_bgwriter and pg_stat_checkpointer expose different metrics. Counters are cumulative and need a known start time. Place database metrics, block-device latency, fsync time, WAL generation and application p95/p99 latency on the same timeline.
- 1Transactions and WAL generation
- 2WAL flush
- 3Checkpoint page writes
- 4Storage latency and application response
Design parameters
- Checkpoint cause
- Separate timed and requested checkpoints; investigate max_wal_size and WAL generation when requested checkpoints are frequent.
- WAL volume
- Measure hourly and peak WAL byte rates; independently monitor archive and replication consumers falling behind.
- Storage
- Measure p95/p99 write latency, queue depth and fsync behavior rather than only average IOPS.
- Durability
- Do not treat disabling fsync or full_page_writes as performance tuning; preserve recovery boundaries.
Worked example
If a workload produces 120 GiB of WAL in 15 minutes, the average is about 136 MiB/s. If a ten-minute checkpoint also writes 300 GiB of dirty pages, roughly 512 MiB/s of additional data writes may be required. Measure actual cache, full-page-write and background-writer effects.
Every ten seconds during diagnosis, collect pg_stat_checkpointer or pg_stat_bgwriter, pg_stat_wal and operating-system disk latency. Correlate log_checkpoints write/sync duration with application p99 spikes and retain a full workload-cycle baseline before changing settings.
Example commands: replace lab values and confirm permissions and software versions before use.
SELECT now(), checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time
FROM pg_stat_bgwriter;
SELECT now(), wal_records, wal_fpi, wal_bytes, wal_write_time, wal_sync_time
FROM pg_stat_wal;
SHOW checkpoint_timeout;
SHOW checkpoint_completion_target;
SHOW max_wal_size;Troubleshooting
| Observation | Likely cause / distinction | Verification |
|---|---|---|
| p99 spikes at regular intervals | Checkpoint writes may saturate I/O in a short window. | Correlate log_checkpoints duration with storage latency and checkpoint counters. |
| WAL directory grows rapidly | Replication slots, archive_command or a recovery consumer may retain WAL. | Inspect replication slots, archiver state and oldest required LSN. |
| Commits are slow while checkpoints are quiet | WAL fsync, synchronous standby or storage caching may dominate. | Check wait events, wal_sync_time and standby delay. |
Acceptance checks
- Record statistics reset time and PostgreSQL release.
- Separate timed from requested checkpoints.
- Measure WAL byte rate at baseline and peak.
- Enable checkpoint write/sync duration logging.
- Correlate storage p99 latency and queue depth.
- After tuning, test crash recovery and replica/archive flow.
Related concepts
Transaction logs and recovery chains
WAL, redo and transaction-log mechanisms record changes in order for durability and recovery, with product-specific details. Data copies and logs must belong to compatible timelines and chains. Having log files alone does not prove point-in-time recovery: a valid starting backup and the required uninterrupted log range are also needed. Measure archival lag, log volume and replay time. Do not delete logs arbitrarily to regain space; use supported retention and cleanup methods.
IOPS and block size
IOPS measures operations per second, whereas MB/s measures transferred data. Identical IOPS values produce very different bandwidth at different block sizes. For example, 10,000 operations/s at 4 KiB is approximately 39.1 MiB/s. Read/write mix, random versus sequential access and protection mechanisms affect the result. Measure actual block distributions and concurrency rather than transferring a benchmark result directly to production. High disk IOPS does not prove good application response; latency must be examined alongside it.
Latency distribution
Latency is the time between starting an operation and receiving its result. An average can hide a small number of very slow operations; medians and p95/p99 percentiles answer different questions. Network RTT, storage waits, processor queues and application processing contribute to end-to-end time. State whether measurements come from the client or server. Check whether increases coincide with traffic growth, maintenance or capacity limits. Record normal and peak-hour baselines before selecting an alert threshold.
Bandwidth and useful throughput
Link capacity differs from useful application throughput. Protocol headers, encryption, retransmissions, small files and storage waits reduce net transfer speed. Keep bits and bytes distinct: 1 Gbit/s corresponds to a theoretical 125 MB/s, not an application performance guarantee. Estimate transfer time as data size divided by measured useful throughput. Observe the network, source reads and destination writes together to locate the bottleneck. Consider temporary slowdowns and competing workloads as well as average speed.
Durability and power loss
Survival of committed data depends on write guarantees across the database, OS, filesystem, controller and disks. Do not disable safety settings for speed without understanding caches and flush behaviour. Power-loss-protected storage helps but does not prove correctness of the entire chain. Run failure and recovery tests in a controlled lab, not production. Validate application records and supported consistency checks rather than merely opening a sample file.
Telemetry and time correlation
Telemetry combines logs, metrics and events that explain system behaviour. A log describes an event, a metric shows behaviour over time, and a distributed trace follows a request across components. Clock differences can make one event appear to occur at several times. Use synchronized clocks, reliable source identifiers and consistent time-zone handling. Alarm design should consider duration and user impact alongside thresholds. Monitor gaps in collection separately: absence of logs must not be interpreted as absence of incidents.
Primary documentation
Prepared by the Doz Teknoloji technical team using the primary references below. Calculations and lab scenarios state their assumptions; validate the applicable product version before rollout.