PostgreSQL 18 pg_stat_io: как найти узкие места чтения, записи и фоновых процессов

Что такое pg_stat_io в PostgreSQL 18 и зачем он нужен

pg_stat_io - системное представление PostgreSQL, в котором статистика ввода-вывода разложена по осям: тип процесса (backend_type), объект операции (object) и контекст (context). Одна строка вывода отвечает на вопрос, сколько операций чтения, записи, расширения файлов, fsync и writeback выполнил конкретный класс процессов в конкретном режиме работы с момента последнего сброса.

Ответ на главный вопрос «кто грузит диски» получается одним запросом. Если fsyncs растут у checkpointer, а не у client backend, разбираться нужно с настройками контрольных точек. Если в лидерах autovacuum worker, смотрите на объём мёртвых строк и пороги очистки. Если всё чтение создают клиентские сессии в контексте normal, причина в запросах и индексах.

Состав столбцов и список backend_type менялись между релизами PostgreSQL. Перед тем как строить дашборды и алерты, сверьте структуру представления на своём сервере: SELECT * FROM pg_stat_io; и \d+ pg_stat_io. Точный перечень полей и допустимых значений для вашей версии приведён в официальной документации PostgreSQL в разделе о системе накопительной статистики: monitoring-stats.

Чем pg_stat_io отличается от pg_stat_database и pg_statio_*

pg_stat_database агрегирует блоки на уровне базы данных, pg_statio_user_tables и pg_statio_user_indexes - на уровне таблиц и индексов. Ни одно из них не показывает, какой процесс выполнил операцию. pg_stat_io добавляет именно это измерение плюс контекст, в котором операция произошла.

Складывается трёхуровневая схема: pg_stat_database отвечает на вопрос «какая база грузит диски», pg_statio_user_tables - «какая таблица», pg_stat_io - «какой процесс и в каком режиме». Представления дополняют друг друга, а не заменяют.

Пример цепочки. В pg_stat_database вырос blks_read. Запрос к pg_stat_io уточняет, что весь объём дал client backend в контексте normal. pg_statio_user_tables показывает конкретную таблицу с высоким heap_blks_read. Дальше EXPLAIN (ANALYZE, BUFFERS) по запросу объясняет, почему эта таблица читается с диска. Без pg_stat_io первый шаг приходилось угадывать.

Как получить и сбросить статистику pg_stat_io

Базовый запрос возвращает все накопленные строки:

SELECT * FROM pg_stat_io;

Строк будет столько, сколько сочетаний backend_type, object и context встретилось на сервере. Комбинации, которые ещё не происходили, в выводе отсутствуют, поэтому полезно сначала посмотреть фактический набор значений:

SELECT DISTINCT backend_type, object, context
FROM pg_stat_io
ORDER BY 1, 2, 3;

Сброс статистики ввода-вывода выполняется функцией сброса статистики. Точное имя функции и её поведение (сброс для всего сервера или для текущего подключения) сверяйте по официальной документации вашей версии в разделе о функциях управления статистикой: monitoring-stats. Общий принцип: счётчики накопительные, поэтому сравнивать периоды «до» и «после» приходится по заранее сохранённым значениям. Для измерения нагрузки в окне времени снимите срез, сбросьте статистику, подождите интервал и снимите срез повторно. Время последнего сброса хранится в столбце stats_reset.

Столбцы с задержками (read_time, write_time, extend_time, hit_time, eviction_time, writeback_time, fsync_time) заполняются только при включённом track_io_timing. Проверьте параметр перед анализом таймингов:

SHOW track_io_timing;

Включение таймингов даёт измерение задержек, но само по себе добавляет накладные расходы на системные вызовы, поэтому включайте его осознанно.

Ключевые поля pg_stat_io: как читать счётчики

Вывод представления читается по столбцам. Ниже справочник по основным из них. Набор полей и их названия зависят от версии, поэтому перед использованием сверяйте структуру с официальной документацией: monitoring-stats.

Приведённый перечень отражает типовой набор столбцов, но не является гарантией для любой минорной версии: проверяйте фактическую структуру командой \d+ pg_stat_io.

Поля reads, writes, extends: объём и характер операций

reads считает операции чтения, writes - операции записи, extends - расширения файлов отношений. Расширения появляются при росте таблиц, массовых вставках и построении индексов, поэтому высокий extends у client backend в контексте normal часто означает активный приток данных, а не проблему.

SELECT backend_type, object, context, reads, writes, extends
FROM pg_stat_io
ORDER BY reads DESC;

Отдельный счётчик без базы сравнения ничего не доказывает. Снимайте одинаковые срезы в спокойные и пиковые часы, тогда отклонение становится заметным.

Поля hits, evictions, reuses: эффективность кэша

hits показывает попадания в shared buffers, evictions - вытеснения страниц из них, reuses - повторное использование буферов. Рост evictions означает, что рабочего набора страниц не хватает в буферном кэше: либо его мало, либо запросы читают объём данных, который в него не помещается.

SELECT backend_type, object, context, hits, evictions, reuses
FROM pg_stat_io
WHERE evictions > 0
ORDER BY evictions DESC;

Вытеснения сами по себе не аварийный сигнал: любое сканирование большой таблицы их создаёт. Опасное сочетание - высокие evictions вместе с высокими reads по тому же объекту, когда одни и те же данные перечитываются с диска.

Поля fsyncs и writebacks: синхронизация и сброс на диск

fsyncs считает вызовы fsync, writebacks - записи грязных страниц обратно на диск. Основные источники этих операций - checkpointer и background writer. Высокий fsyncs у checkpointer указывает на частые контрольные точки или на медленный носитель, который долго подтверждает синхронизацию.

SELECT backend_type, object, context, fsyncs, writebacks
FROM pg_stat_io
WHERE fsyncs > 0
ORDER BY fsyncs DESC;

Сопоставляйте значения с длительностью checkpoint_timeout и объёмом max_wal_size, но не меняйте их вслепую: точные значения подбираются по результатам наблюдения за вашей нагрузкой.

Backend types и контексты: кто и как выполняет ввод-вывод

Разделение по backend_type и context превращает общий счётчик блоков в адресную информацию. Сгруппированный запрос даёт картину по источникам:

SELECT backend_type, context,
       sum(reads) AS total_reads,
       sum(writes) AS total_writes
FROM pg_stat_io
GROUP BY backend_type, context
ORDER BY total_reads DESC;

Полный и точный перечень допустимых значений backend_type, object и context для вашей версии приведён в официальной документации: monitoring-stats. Ниже разобраны наиболее частые источники нагрузки.

Client backend: пользовательские запросы

client backend обслуживает клиентские подключения. Он работает в контексте normal, а при определённых операциях попадает в vacuum, bulkread или bulkwrite. Высокие reads у client backend означают, что данные читают пользовательские запросы, и дальше стоит связать это с pg_stat_statements и планами выполнения.

SELECT object, context, reads, writes
FROM pg_stat_io
WHERE backend_type = 'client backend'
ORDER BY reads DESC;

Если у client backend растут hits, а reads остаются низкими, рабочая нагрузка попадает в кэш. Обратная картина говорит о промахах и лишних обращениях к диску.

Фоновые процессы: checkpointer, background writer, autovacuum, walwriter

checkpointer выполняет fsync и writebacks во время контрольных точек, background writer заранее сбрасывает грязные страницы, autovacuum worker читает и пишет в контексте vacuum, walwriter отвечает за запись WAL. Их счётчики стоит смотреть отдельно от пользовательских:

SELECT backend_type, context, fsyncs, writebacks, reads, writes
FROM pg_stat_io
WHERE backend_type IN ('checkpointer', 'background writer', 'autovacuum worker', 'walwriter')
ORDER BY fsyncs DESC;

Высокий fsyncs у checkpointer связан с настройками контрольных точек, высокие reads и writes у autovacuum worker - с частотой и объёмом очистки. Полный список доступных значений проверяйте на сервере: он различается между версиями и зависит от включённых компонентов.

Контексты normal, vacuum, bulkread, bulkwrite

normal покрывает штатные операции, vacuum - работу autovacuum и ручного VACUUM, bulkread - массовое чтение вроде COPY или CREATE TABLE AS, bulkwrite - массовую запись, например при COPY или построении индекса. Точный набор контекстов и объектов для вашей версии указан в документации: monitoring-stats.

SELECT backend_type, context, reads, writes
FROM pg_stat_io
WHERE context = 'bulkread'
ORDER BY reads DESC;

Рост bulkread и bulkwrite ожидаем во время загрузки данных. Вопрос в длительности: пока массовая операция держит диски, остальные сессии получают повышенные задержки, поэтому такие задачи лучше ставить в окна низкой нагрузки.

Практические SQL-запросы для диагностики I/O

Запросы для анализа чтения

Начните с поиска лидеров по чтению и оценки доли попаданий в кэш:

SELECT backend_type, object, context, reads, hits, evictions
FROM pg_stat_io
ORDER BY reads DESC
LIMIT 10;

Высокие reads при низких hits указывают на промахи кэша. Дальше ищите таблицу:

SELECT schemaname, relname, heap_blks_read, heap_blks_hit
FROM pg_statio_user_tables
ORDER BY heap_blks_read DESC
LIMIT 10;

Если чтение упирается в физические возможности накопителей, полезно разобрать отдельно уровень дисковой подсистемы, контроллера и файловой системы: в материале о влиянии дисков и файловых систем на скорость сервера приведён безопасный алгоритм такой диагностики.

Запросы для анализа записи

SELECT backend_type, object, context, writes, extends, writebacks
FROM pg_stat_io
ORDER BY writes DESC
LIMIT 10;

Высокие writes у client backend в контексте normal - обычная запись данных приложением. Высокие writebacks у background writer означают активный сброс грязных страниц. Рост extends без роста writes часто говорит о вставках в новые сегменты файлов.

Запросы для анализа фоновых процессов

SELECT backend_type, context, fsyncs, writebacks, reads, writes
FROM pg_stat_io
WHERE backend_type IN ('checkpointer', 'background writer', 'walwriter')
ORDER BY fsyncs DESC;

Высокий fsyncs у checkpointer при низких writebacks намекает на частые контрольные точки с малым объёмом сброса. Активность autovacuum проверяется отдельно:

SELECT pid, datname, query, wait_event_type, wait_event
FROM pg_stat_activity
WHERE backend_type = 'autovacuum worker';

Состояние очереди очистки и объём мёртвых строк удобно смотреть по таблицам статистики:

SELECT schemaname, relname, n_dead_tup, n_live_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Сценарии расследования узких мест I/O

Высокий fsync у checkpointer: причины и действия

1. Снимите срез по checkpointer: SELECT backend_type, context, fsyncs, writebacks FROM pg_stat_io WHERE backend_type = 'checkpointer';
2. Сравните fsyncs и writebacks. Много fsync при малом объёме сброса означает частые контрольные точки.
3. Проверьте статистику контрольных точек. В PostgreSQL 17 и новее часть статистики контрольных точек вынесена в отдельное представление pg_stat_checkpointer; убедитесь, что оно доступно в вашей сборке, и сверьте его состав с документацией вашей версии.
4. Посмотрите сообщения о checkpoint в журнале сервера: параметр log_checkpoints даёт длительность и объём каждого срабатывания.
5. Увеличение checkpoint_timeout или max_wal_size растягивает интервалы, но повышает объём работы в момент checkpoint и требования к свободному месту под WAL. Меняйте по одному параметру и фиксируйте эффект.

Частые evictions: нехватка shared buffers или неэффективные запросы

1. Найдите источники вытеснений: SELECT backend_type, object, context, hits, evictions FROM pg_stat_io WHERE evictions > 0 ORDER BY evictions DESC;
2. Если evictions создаёт client backend, ищите запросы с большим числом чтений через EXPLAIN (ANALYZE, BUFFERS).
3. Проверьте pg_statio_user_tables на высокий heap_blks_read по конкретным таблицам.
4. Оцените размер рабочего набора и текущее значение shared_buffers. Увеличение помогает только тогда, когда вытеснения создаёт горячий набор, а не единичные сканирования больших таблиц.

Активность autovacuum: когда это проблема

SELECT backend_type, context, reads, writes, extends
FROM pg_stat_io
WHERE backend_type = 'autovacuum worker';

1. Сопоставьте объём I/O автовакуума с числом мёртвых строк в pg_stat_all_tables.
2. Если очистка идёт постоянно на большой таблице, настройте для неё персональные параметры хранения: autovacuum_vacuum_scale_factor и autovacuum_vacuum_cost_delay через ALTER TABLE ... SET.
3. Отключать autovacuum целиком нельзя: это приводит к раздуванию таблиц и риску зацикливания счётчика транзакций.

Bulkread и bulkwrite: массовые операции

SELECT backend_type, context, reads, writes, extends
FROM pg_stat_io
WHERE context IN ('bulkread', 'bulkwrite')
ORDER BY reads DESC;

1. Найдите активные массовые операции: SELECT pid, query FROM pg_stat_activity WHERE query ILIKE '%copy %' OR query ILIKE '%create index%' OR query ILIKE '%create table%';
2. Оцените, сколько времени операция держит диски, и как в это время ведут себя reads и hits у client backend.
3. Планируйте COPY и построение индексов на окна низкой нагрузки, при необходимости используйте CREATE INDEX CONCURRENTLY.
4. Смотрите на объём временных файлов, если сортировки и хеши не укладываются в work_mem: они попадают в object = 'temp relation'.

Единый счётчик без контекста не доказывает наличие проблемы. Решение принимайте по трём факторам одновременно: источник I/O, тренд относительно baseline и влияние на задержки пользовательских запросов.

Когда понятно, что рост операций упирается в физический предел накопителей, стоит пересчитать требования по IOPS, пропускной способности и задержкам, прежде чем закупать оборудование: формулы и примеры для PostgreSQL собраны в статье о расчёте производительности СХД.

Ограничения pg_stat_io и проверка по документации

Что pg_stat_io не показывает

• Нет разбивки по таблицам, индексам и базам данных: объект ограничен типом relation или temp relation.
• Нет времени выполнения отдельных операций, если отключён track_io_timing.
• Представление не заменяет pg_stat_statements: по нему нельзя найти конкретный запрос.
• Счётчики накопительные и обнуляются при перезапуске сервера или при сбросе статистики, история между окнами измерения не хранится.
• Список backend_type и набор столбцов отличаются между версиями, поэтому переносить готовые запросы между major-версиями без проверки нельзя.

Как проверить актуальность полей и версию

Проверка занимает минуту и снимает большинство сомнений:

SELECT version();

\d+ pg_stat_io

SELECT DISTINCT backend_type FROM pg_stat_io ORDER BY 1;

Первая команда фиксирует версию сервера, вторая показывает фактическую структуру представления на этом сервере, третья перечисляет доступные классы процессов. Сверяйте имена столбцов и значений с документацией именно вашей минорной версии: описания полей в статьях и на сторонних ресурсах часто отстают от текущего релиза. Официальный раздел о системе накопительной статистики: monitoring-stats.

Как комбинировать pg_stat_io с другими инструментами мониторинга

pg_stat_io даёт источник нагрузки и контекст, а конкретику добавляют смежные представления. Собранные вместе, они образуют полный маршрут расследования, который удобно включить в общий контур наблюдения за инфраструктурой: подходы к метрикам и дашбордам описаны в руководстве по мониторингу производительности с Prometheus, Grafana и Zabbix.

Связка с pg_stat_database и pg_statio_*

pg_stat_database показывает объём чтений и попаданий на уровне базы, pg_statio_user_tables и pg_statio_user_indexes - на уровне объектов, pg_stat_io указывает процесс. Практический порядок такой: найти базу, затем таблицу, затем источник I/O.

SELECT d.datname,
       d.blks_read,
       d.blks_hit,
       round(100.0 * d.blks_hit / nullif(d.blks_hit + d.blks_read, 0), 2) AS cache_hit_pct
FROM pg_stat_database d
ORDER BY d.blks_read DESC
LIMIT 10;

Дальше найденная таблица сопоставляется со строками pg_stat_io по объекту и контексту, а конкретный запрос ищется в pg_stat_statements.

Связка с pg_stat_bgwriter и pg_stat_activity

pg_stat_bgwriter показывает общую работу по сбросу грязных страниц, pg_stat_activity - активные процессы и их ожидания. pg_stat_io дополняет их разбивкой по классам процессов.

SELECT backend_type, fsyncs, writebacks
FROM pg_stat_io
WHERE backend_type IN ('checkpointer', 'background writer');

SELECT pid, backend_type, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE wait_event_type = 'IO'
ORDER BY backend_start;

События ожидания категории IO показывают, на каком именно шаге сессия ждёт диск, например IO:DataFileRead при чтении отношения. Совмещая их с накопленными счётчиками pg_stat_io, вы отличаете разовую задержку от систематической нагрузки. Если счётчики I/O в порядке, а сервер всё равно медленно отвечает, имеет смысл проверить CPU, память и сеть по методике из руководства по метрикам CPU, памяти, диска и сети в Linux.

Рабочий порядок для дежурной смены: снять срез pg_stat_io до инцидента, во время роста задержек сравнить источники I/O, подтвердить гипотезу через pg_statio_user_tables, pg_stat_statements, pg_stat_checkpointer и pg_stat_activity, менять по одному параметру и возвращаться к тем же срезам для проверки результата.