PostgreSQL Tutorial: Tuning I/O Performance with pg_stat_io

April 21, 2026

Summary: In this tutorial, you will learn about pg_stat_io view, including byte-level I/O statistics, WAL tracking, and comprehensive real-world use cases for better database performance tuning.

Table of Contents

Introduction to pg_stat_io

The pg_stat_io view, first introduced in PostgreSQL 16, provides detailed insights into I/O operations across your PostgreSQL cluster. PostgreSQL 18 significantly improves this view with byte-level statistics, WAL I/O tracking, and per-backend monitoring capabilities.

This view is great for understanding database performance because it breaks down I/O activity by backend type, I/O object, and context. It is handy when troubleshooting slow queries, optimizing buffer cache settings, or analyzing system-wide I/O patterns as pg_stat_io provides the detailed metrics you need.

Understanding pg_stat_io Structure

The pg_stat_io view organizes I/O statistics using three key dimensions that help categorize different types of database activity.

Backend Types

PostgreSQL tracks I/O separately for different process types:

  • client backend: Regular database connections from applications
  • autovacuum worker: Automatic vacuum and analyze processes
  • autovacuum launcher: The coordinator for autovacuum workers
  • background writer: Process that writes dirty pages to disk
  • checkpointer: Process that performs database checkpoints
  • walwriter: Process that writes WAL data to disk
  • background worker: Custom background processes

I/O Objects

The view tracks I/O on different types of database objects:

  • relation: Tables, indexes, and other relation data
  • temp relation: Temporary tables and indexes (local buffers)
  • wal: Write-Ahead Log data

I/O Contexts

Different operational contexts affect how I/O is performed:

  • normal: Standard database operations
  • bulkread: Bulk read operations (sequential scans, COPY)
  • bulkwrite: Bulk write operations (COPY, CREATE TABLE AS)
  • vacuum: Vacuum and analyze operations
  • init: Database initialization operations (new for WAL tracking)

Basic Usage and Queries

Let’s explore how to use pg_stat_io to monitor your database’s I/O performance.

Viewing Overall I/O Activity

Start by looking at the basic structure of the view:

-- See what's in pg_stat_io
SELECT backend_type, object, context, reads, writes
FROM pg_stat_io
WHERE reads > 0 OR writes > 0;

This quick peek helps you confirm which backends and objects are currently active before diving deeper into analysis.

Next, look at which areas have the most activity:

-- Find busiest backend types
SELECT backend_type, sum(coalesce(reads, 0) + coalesce(writes, 0)) AS total_ios
FROM pg_stat_io
GROUP BY backend_type
ORDER BY total_ios DESC;

This aggregates activity across backend types, so you can immediately spot which processes are generating the heaviest load.

Analyzing Buffer Cache Efficiency

Check how well your buffer cache is working:

-- Simple cache hit ratio check
SELECT
    backend_type,
    reads,
    hits,
    (hits * 100 / (reads + hits)) AS hit_percent
FROM pg_stat_io
WHERE object = 'relation' AND reads + hits > 0
ORDER BY hit_percent;

This ratio gives you a quick sense of how effective your shared buffers are. A low number hints at cache pressure or large scans.

A hit percentage below 90% suggests you might need more shared_buffers or have queries doing large scans.

Monitoring WAL I/O Activity

See which processes are writing the most WAL data:

-- Check WAL activity
SELECT
    backend_type,
    writes,
    pg_size_pretty(write_bytes) AS wal_volume
FROM pg_stat_io
WHERE object = 'wal' AND writes > 0
ORDER BY write_bytes DESC;

This query makes WAL writers visible, showing where the bulk of log traffic is coming from. It helps identify if your WAL generation is excessive, which can impact performance and disk space.

Identifying Autovacuum I/O Overhead

Autovacuum workers are essential for keeping tables and indexes bloat-free. But in high-write workloads, they can create noticeable I/O pressure.

Using pg_stat_io, you can track the I/O load from autovacuum processes:

SELECT backend_type, sum(reads) as total_reads, sum(writes) as total_writes
FROM pg_stat_io
WHERE backend_type = 'autovacuum worker'
GROUP BY backend_type;

This helps DBAs fine-tune autovacuum parameters (autovacuum_vacuum_cost_delay, autovacuum_max_workers) to balance between cleanup efficiency and I/O overhead.

Monitoring Background Writer Efficiency

The background writer is responsible for writing dirty buffers to disk, reducing checkpoint pressure. With pg_stat_io, you can see how much I/O it performs and whether it’s effective:

SELECT backend_type, writes, write_time
FROM pg_stat_io
WHERE backend_type = 'background writer';

If writes are consistently high, you may want to adjust bgwriter_lru_maxpages or checkpoint settings to spread out I/O more evenly.

Configuration and Timing

To get detailed timing information from pg_stat_io, you need to enable timing collection.

Enabling I/O Timing

Before you can see how much time queries spend waiting on I/O, you need to turn on timing collection. PostgreSQL exposes this through two configuration parameters (track_io_timing and track_wal_io_timing), which you can check and enable as follows:

-- Enable timing (requires superuser)
SET track_io_timing = on;
SET track_wal_io_timing = on;

With timing enabled, you can see time spent on I/O:

-- View timing information
SELECT
    backend_type,
    reads,
    read_time,
    writes,
    write_time
FROM pg_stat_io
WHERE reads > 0 OR writes > 0
ORDER BY (coalesce(read_time, 0) + coalesce(write_time, 0)) DESC;

This query shows how much time is spent on I/O operations, helping you identify slow queries or processes.

Resetting Statistics

Whenever needed, you can reset the I/O statistics to start fresh:

-- Reset all I/O statistics
SELECT pg_stat_reset_shared('io');

This is useful after making configuration changes or when establishing new performance baselines.

See more

PostgreSQL Optimization

Search

Categories

Tags