Troubleshoot high IOPS utilization

When storage saturates, everything above it queues. The IOPS guide separates the four causes that look identical from a metric chart: queries reading more than they should, checkpoints firing too often, bloat inflating every scan, and a server that outgrew its provisioned IOPS.

Only the last one is fixed by paying more.

Symptoms this guide explains

• IOPS or disk bandwidth pinned near 100%
• Disk queue depth consistently elevated
• Query latency that worsens under write load
• Periodic latency spikes at regular intervals

Before you start

Enable Query Store Runtime, PostgreSQL Server Logs, Sessions data, and Query Store Wait Statistics; set pg_qs.query_capture_mode to TOP or ALL and pgms_wait_sampling.query_capture_mode to ALL; enable metrics.collector_database_activity; and set track_io_timing = ON.

How this guide is organized

Walkthrough: latency spikes every few minutes

1. Characterize the saturation. The Overview tab shows utilization near 100% with queue depth spiking at regular intervals rather than continuously. Periodicity is a strong hint, because steady workload pressure doesn't arrive on a schedule.

2. Rule out workload. Workload shows write activity elevated but steady, with no matching periodicity. Something is batching the writes rather than the writes themselves arriving in bursts.

3. Identify the mechanism. The Waits tab shows IO:WALWrite and IO:DataFileWrite dominating at the spike intervals. Write-side, not read-side, which points at flushing rather than querying.

4. Confirm. The Checkpoints tab shows most checkpoints classified as requested rather than timed, arriving every few minutes. max_wal_size is being exceeded well before checkpoint_timeout elapses, so the server checkpoints constantly, flushing dirty pages in expensive bursts.

5. Act. You measure actual WAL generation during peak, raise max_wal_size to accommodate it, and set checkpoint_completion_target to 0.9 so the flush spreads across the interval instead of arriving as a spike. Checkpoints shift to predominantly timed, and the periodic latency disappears.

If the waits had been read-side instead, the same walkthrough would have continued into Queries and, most likely, ended at bloat or a missing index.

Tab reference

Overview

Four metrics, each answering something different:

For background, see disk performance metrics and compute and storage options.

Workload

Read versus write tuple activity. Rising with IOPS means genuine extra work. Flat while IOPS climbs means each operation became more expensive, most often because of bloat, which forces scans to read far more pages for the same rows.

Sessions

Long-running sessions from sampled pg_stat_activity, to identify PIDs holding I/O-heavy work open.

Immediate mitigation. Cancel the running statement first. It's the less disruptive of the two options, because the session and its transaction survive:

SELECT pg_cancel_backend(<pid>);

If the session is idle in a transaction, or canceling doesn't release the resource, terminate the whole backend:

SELECT pg_terminate_backend(<pid>);

Guardrails so it doesn't recur. Set limits on how long sessions and transactions can run:

Queries

When you turn on track_io_timing, Query Store records blk_read_time and blk_write_time. This tab ranks queries by their sum, with per-bucket drill-down options that show the mean, minimum, and maximum I/O time, execution duration, data read and written, rows, and calls. Compare I/O time against rows returned. A query with heavy I/O and few rows reads far more data than it needs. This condition indicates a missing index, a predicate that can't use an existing index, or a table so bloated that the useful rows are scattered across many pages.

1. Run index tuning to get index recommendations based on your actual workload. This step is the most effective long-term fix.
2. Run EXPLAIN (ANALYZE, BUFFERS) on the query to see where time and I/O actually go. Look for sequential scans on large tables, nested loops over big row counts, and sorts that spill to disk.
3. Reduce table bloat. As a one-time step, run VACUUM (ANALYZE, VERBOSE) <table_name>;. If bloat keeps returning, the cause is upstream. See Monitor autovacuum.
4. Add the indexes the plan needs, and remove joins that don't narrow the result.
5. Partition large tables that are consistently hot.
6. Revisit table design: index foreign keys in child tables, drop unused indexes (they cost on every write), and disable triggers during bulk loads.

Before concluding that the queries are entirely at fault, review the Checkpoints tab. Checkpoint pressure inflates the I/O cost of everything running concurrently.

Waits

Identifies which queries spent the most time waiting on disk, from sampled wait events attributed to query IDs.

1. Select Top N wait events to set how many distinct events to analyze.
2. Select Top queries per wait event.
3. Select a query row to inspect its detail.

When you read the dominant event, you understand which direction to go:

Checkpoints

A checkpoint flushes all dirty pages to disk and writes a checkpoint record to the WAL. One begins every checkpoint_timeout seconds, or when max_wal_size is about to be exceeded, whichever comes first. That "whichever comes first" is the whole story:

• Timed, triggered by checkpoint_timeout. Predictable and spread out.
• Requested, triggered because max_wal_size was about to be exceeded. Frequent, bursty, expensive.

Aim for 90% or more timed checkpoints, with intervals close to checkpoint_timeout. A high proportion of requested checkpoints means max_wal_size is too small for your write rate.

Sizing max_wal_size from measurement rather than guesswork. During peak hours:

-- Run once and note the result.
SELECT pg_current_wal_lsn();

-- Wait checkpoint_timeout seconds, then run again.
SELECT pg_current_wal_lsn();

-- Compute WAL generated over that interval, in GB.
SELECT round(
    pg_wal_lsn_diff('<second_lsn>', '<first_lsn>') / 1024.0 / 1024.0 / 1024.0,
    2
) AS wal_generated_gb;

Set max_wal_size comfortably above that figure so checkpoints are driven by time rather than by WAL volume.

Two related parameters:

• checkpoint_completion_target: set to 0.9 to spread the flush across most of the interval. With a five-minute checkpoint_timeout, the flush completes over roughly 270 seconds instead of arriving as a spike.
• checkpoint_timeout: raising it reduces checkpoint frequency but lengthens crash recovery. That's the trade-off.

Storage

Storage utilization. On this platform, provisioned IOPS scale with allocated storage, so expanding storage raises the IOPS ceiling. See compute and storage options for the relationship.

Treat this as the last resort, not the first. If the cause is bloat or a missing index, more IOPS raises the ceiling you're wasting rather than reducing the waste.

What good looks like

What this guide can't tell you

• Whether the storage layer itself is degraded. These are guest-side metrics.
• Which physical table an I/O wait belongs to. You get the query; map it to tables yourself.
• Sub-sample bursts. Wait events are sampled.
• Read replica WAL causes. Replica WAL-write I/O originates from primary write activity.
• Whether more IOPS is the right answer. It raises the ceiling; it doesn't tell you whether the consumption is legitimate.

Related content

• Use the troubleshooting guides in Azure Database for PostgreSQL flexible server
• Telemetry reference for the troubleshooting guides
• Monitor autovacuum
• Compute options in Azure Database for PostgreSQL flexible server
• Autonomous tuning in Azure Database for PostgreSQL flexible server