Отчёт нагрузки своими руками: запросы по каждому разделу

← назад к разделу

Готовый отчёт нагрузки — удобная вещь, но однажды выясняется, что поставить его некуда: база чужая, доступ только на чтение, разбираться надо сейчас. Хорошая новость в том, что все числа отчёта PostgreSQL считает сам и отдаёт обычными запросами — инструмент лишь красиво их складывает.

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

Как читать сам отчёт и ставить по нему диагноз — в соседней статье. Все запросы ниже выполнены на PostgreSQL 16; где версии расходятся, это оговорено отдельно.

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

Как это выполнять: параметры и права

Прежде чем первый запрос, две практические вещи, без которых ничего не запустится.

Откуда берутся :from и :to. Это не синтаксис SQL, а переменные psql: он подставляет их значения перед отправкой запроса на сервер. Задаются они командой \set, и для дат обязательно в кавычках — иначе подставится голый текст и сервер не поймёт:

\set from '2026-09-24 14:00'
\set to   '2026-09-24 16:00'

SELECT count(*) FROM ash WHERE sample_at BETWEEN :'from' AND :'to';

Форма :'from' (с кавычками внутри) подставляет значение как строковый литерал — это то, что нужно почти всегда; форма :from подставляет как есть и годится для чисел и имён таблиц. Задать их можно и при запуске, не открывая сессию:

psql -d orders -v from='2026-09-24 14:00' -v to='2026-09-24 16:00' -f report.sql

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

Какие права нужны. Это решающий вопрос для сценария «база чужая, доступ только на чтение», и обычная роль с правом SELECT здесь не годится. Без особых прав вы увидите:

• в pg_stat_activity — только свои сессии; у чужих поля query, state, wait_event будут пустыми (сами строки видны, а содержимое скрыто);
• в pg_stat_statements — вместо текста чужих запросов отметку <insufficient privilege>, и это самое обидное, потому что числа есть, а понять, о каком запросе речь, нельзя;
• функции вида pg_cancel_backend — недоступны.

Нужна одна из встроенных ролей, и их выдают именно для таких задач:

GRANT pg_monitor TO analyst;          -- всё нужное сразу: статистика, настройки, размеры
-- или точнее:
GRANT pg_read_all_stats TO analyst;   -- только статистика и тексты запросов

pg_monitor включает в себя pg_read_all_stats плюс чтение настроек и служебных функций — это то, что просят для дежурного или аналитика. Права суперпользователя для всего разобранного в статье не нужны, и просить их не стоит: аргумент «нам достаточно pg_monitor» обычно снимает возражения владельцев базы.

Отдельно про сборщик выборок: ему нужно право писать в свою таблицу, то есть своя схема. Если и этого не дают, остаётся вариант «читать в свою сторону»: запускать выборку с соседней машины и складывать результат в свою базу — те же запросы, только соединение снаружи.

Почему нельзя просто спросить «что было с двух до четырёх»

Первое, обо что спотыкаются все, — статистика в PostgreSQL накопительная.

Это значит, что pg_stat_statements не хранит историю: он держит одну строку на запрос, и в ней сумма за всё время с момента запуска сервера или последнего сброса. Запрос, который вчера отработал двести раз, а сегодня двадцать тысяч, выглядит в ней как одна строка с общим итогом. Спросить «а сколько было между двумя и четырьмя» напрямую невозможно — этой информации там просто нет.

Поэтому все инструменты делают одно и то же: периодически сохраняют состояние счётчиков и потом вычитают одно из другого. Снимок плюс снимок — и разница между ними и есть отчёт за период.

Минимальное хранилище снимков — таблица и процедура, которая складывает в неё текущее состояние:

CREATE TABLE snap_statements (
    snap_id           int         NOT NULL,
    taken_at          timestamptz NOT NULL DEFAULT now(),
    queryid           bigint,
    query             text,
    calls             bigint,
    total_exec_time   double precision,
    rows              bigint,
    shared_blks_hit   bigint,
    shared_blks_read  bigint,
    temp_blks_written bigint,
    wal_bytes         numeric
);
CREATE SEQUENCE snap_seq;

CREATE PROCEDURE take_snapshot() LANGUAGE sql AS $$
    INSERT INTO snap_statements (snap_id, queryid, query, calls, total_exec_time, rows,
                                 shared_blks_hit, shared_blks_read, temp_blks_written, wal_bytes)
    SELECT currval('snap_seq'), queryid, min(left(query, 200)),
           sum(calls), sum(total_exec_time), sum(rows),
           sum(shared_blks_hit), sum(shared_blks_read), sum(temp_blks_written), sum(wal_bytes)
      FROM pg_stat_statements
     WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
     GROUP BY queryid;
$$;

Про GROUP BY queryid стоит сказать отдельно, потому что без него отчёт молча удваивает числа. Строку в pg_stat_statements определяет не один queryid, а четвёрка: пользователь, база, сам запрос и признак верхнего уровня (toplevel). Один и тот же запрос от приложения и от миграций — уже две строки; при pg_stat_statements.track = all к ним добавляются вызовы внутри функций. Сложите их в снимок как есть, соедините два снимка по queryid — и каждая строка размножится на каждую. Суммирование при съёме снимает вопрос: в снимке остаётся ровно одна строка на queryid.

Дальше это вызывается по расписанию — из cron, pg_cron или чего угодно, что умеет ходить в базу: SELECT nextval('snap_seq'); CALL take_snapshot();. Шаг в пять минут — обычный выбор: реже нельзя, потому что короткий всплеск усреднится и пропадёт; чаще уже незачем.

Отчёт за интервал — это разница двух снимков по каждому запросу:

WITH b AS (SELECT * FROM snap_statements WHERE snap_id = 1),
     e AS (SELECT * FROM snap_statements WHERE snap_id = 2)
SELECT round((e.total_exec_time - coalesce(b.total_exec_time, 0))::numeric, 1) AS total_ms,
       round((100 * (e.total_exec_time - coalesce(b.total_exec_time, 0))
              / nullif(sum(e.total_exec_time - coalesce(b.total_exec_time, 0)) OVER (), 0))::numeric, 1) AS pct,
       e.calls - coalesce(b.calls, 0) AS calls,
       round(((e.total_exec_time - coalesce(b.total_exec_time, 0))
              / nullif(e.calls - coalesce(b.calls, 0), 0))::numeric, 2) AS mean_ms,
       e.shared_blks_read - coalesce(b.shared_blks_read, 0) AS blks_read,
       pg_size_pretty(e.wal_bytes - coalesce(b.wal_bytes, 0)) AS wal,
       left(regexp_replace(e.query, '\s+', ' ', 'g'), 60) AS query
  FROM e LEFT JOIN b USING (queryid)
 WHERE e.total_exec_time - coalesce(b.total_exec_time, 0) > 0
 ORDER BY total_ms DESC
 LIMIT 20;

Здесь легко получить отчёт, который врёт, и не заметить этого.

Первое — как соединяются снимки. Запрос мог появиться уже внутри интервала: скажем, вышел новый релиз, и в нём новая выборка. В раннем снимке такой строки нет вообще, и при обычном JOIN она молча выпадет из отчёта — а это ровно тот случай, когда виновник и есть новичок. Поэтому соединение внешнее, а недостающие значения подменяются нулями через coalesce.

Второе — типы. Времена в pg_stat_statements хранятся как double precision, а функции round(double precision, integer) в PostgreSQL нет: запрос упадёт с сообщением про несуществующую функцию. Спасает приведение ::numeric — и это, пожалуй, самая частая ошибка при написании таких отчётов вручную.

Третье — сброс счётчиков. Если между снимками кто-то вызвал pg_stat_statements_reset(), поздний снимок окажется меньше раннего, и разница уйдёт в минус. Отрицательные числа в таком отчёте означают не аномалию нагрузки, а сброс статистики.

Дальше идут разделы отчёта. Запросы показаны в простом виде — по текущему состоянию счётчиков; чтобы получить «за период», каждый оборачивается той же разницей снимков.

Сколько всего работы сделала база

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

Общую картину по базе даёт pg_stat_database:

SELECT datname,
       xact_commit, xact_rollback,
       tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted,
       blks_read, blks_hit,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS hit_pct,
       temp_files, pg_size_pretty(temp_bytes) AS temp,
       deadlocks,
       round(blk_read_time::numeric, 1)  AS read_ms,
       round(blk_write_time::numeric, 1) AS write_ms
  FROM pg_stat_database
 WHERE datname = current_database();

Что тут важно понять про колонки.

xact_commit и xact_rollback — завершённые и откатившиеся транзакции. Небольшая доля откатов нормальна, заметная означает либо ошибки в приложении, либо срабатывающие таймауты; и то и другое стоит искать по журналам.

hit_pct — доля страниц, которые нашлись в буферном кэше и не потребовали чтения с диска. Низкое значение на нагруженной базе — сигнал, что рабочий набор перестал помещаться в память. Но и высокое ничего не гарантирует: прочитать миллион лишних страниц из кэша тоже дорого, просто дешевле, чем с диска.

Пара tup_returned и tup_fetched как раз про это. Первое — сколько строк база просмотрела, второе — сколько из них реально пригодилось. Когда первое в сотни раз больше второго, база честно перебирает таблицы там, где должна была прийти по индексу.

temp_files и temp_bytes — сортировки и соединения, которым не хватило work_mem и которые ушли на диск. А blk_read_time заполняется только при включённом track_io_timing: без него все времена ввода-вывода будут нулями, и половина отчёта окажется пустой. Накладные расходы на современном оборудовании небольшие, включать стоит.

Объём журнала считается отдельно — эта вьюха появилась в 14-й версии:

SELECT wal_records, wal_fpi, pg_size_pretty(wal_bytes) AS wal,
       wal_buffers_full, wal_write, wal_sync,
       round(wal_write_time::numeric, 1) AS write_ms,
       round(wal_sync_time::numeric, 1)  AS sync_ms
  FROM pg_stat_wal;

С 18-й версии эта вьюха похудела: wal_write, wal_sync, wal_write_time и wal_sync_time из неё убрали, эти числа переехали в pg_stat_io — строки с object = 'wal'. Запрос выше на 18-й упадёт на неизвестной колонке: оставьте в нём wal_records, wal_fpi, wal_bytes и wal_buffers_full, а времена записи и синхронизации берите из pg_stat_io.

Отдельного внимания заслуживает wal_fpi — полностраничные образы. При первом изменении страницы после чекпоинта PostgreSQL пишет в журнал не разницу, а страницу целиком. Чем чаще чекпоинты, тем больше таких страниц, и запись растёт при той же нагрузке. Если объём журнала вырос, а приложение работает как раньше, смотреть надо сюда и на чекпоинты.

На чём стоит время: ожидания

Дальше вопрос «во что упирается». Сессия в каждый момент либо считает на процессоре, либо чего-то ждёт — диска, блокировки, соседа. Текущее состояние всех сессий видно в pg_stat_activity:

SELECT coalesce(wait_event_type, 'CPU') AS wait_type,
       coalesce(wait_event, 'on CPU')   AS wait_event,
       count(*) AS sessions
  FROM pg_stat_activity
 WHERE state = 'active'
   AND backend_type = 'client backend'
   AND pid <> pg_backend_pid()
 GROUP BY 1, 2
 ORDER BY sessions DESC;

Тут есть тонкость, которая сбивает с толку при первом знакомстве. Если сессия активна, а wait_event_type пустой — это не значит «ничего не ждёт и всё хорошо». Это значит, что она прямо сейчас работает на процессоре. Поэтому в запросе пустое значение подменяется на CPU: в отчётах эта строка обычно и называется «on CPU».

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

Но перед тем как строить сборщик, стоит задать более грубый вопрос, на который есть готовый ответ: база вообще работала или ждала приложение? С PostgreSQL 14 в pg_stat_database есть накопительные длительности, и разница двух снимков отвечает на него без всяких выборок:

SELECT round((b.active_time - a.active_time) / 1000.0, 1)               AS active_s,
       round((b.idle_in_transaction_time - a.idle_in_transaction_time) / 1000.0, 1)
                                                                        AS idle_in_tx_s,
       round((b.session_time - a.session_time) / 1000.0, 1)             AS session_s,
       round(100.0 * (b.active_time - a.active_time)
             / nullif(b.session_time - a.session_time, 0), 1)           AS active_pct,
       b.sessions_abandoned - a.sessions_abandoned                      AS abandoned,
       b.sessions_killed - a.sessions_killed                            AS killed
  FROM snap_database a
  JOIN snap_database b USING (datname)
 WHERE a.snap_id = :from_snap AND b.snap_id = :to_snap
   AND a.datname = current_database();

Три вывода, которые из этих чисел получаются сразу. active_pct — доля времени, когда соединения выполняли запросы, от всего времени, когда они были открыты: пять процентов означают, что база девяносто пять процентов времени ждала приложение, и оптимизировать запросы бессмысленно. idle_in_tx_s — сколько времени транзакции были открыты и ничего не делали: это прямая мера того, что приложение держит транзакцию вокруг сетевых вызовов, и именно это блокирует уборку мусора и держит блокировки. В таблице ожиданий та же картина видна как преобладание Client, но здесь она в секундах, а не в долях выборок, — и это гораздо убедительнее в разговоре с разработчиками.

sessions_abandoned и sessions_killed объясняют загадочные «соединения кончились»: первое — оборванные без закрытия (упал контейнер, моргнула сеть), второе — прибитые вручную.

Для этого нужен всего лишь ещё один снимок: таблица snap_database устроена так же, как snap_statements из первого раздела, и заполняется той же процедурой.

Активные сессии за период: свой ASH

Идея, на которой построен весь раздел ASH в любом отчёте, простая: если раз в секунду записывать, кто чем занят, то доля сэмплов приблизительно равна доле времени. Двадцать сэмплов из ста с ожиданием блокировки — значит, примерно пятую часть периода система стояла на блокировках.

Хранилище и сборщик выглядят так:

CREATE TABLE ash (
    sample_at       timestamptz NOT NULL,
    pid             int,
    datname         text,
    usename         text,
    state           text,
    wait_event_type text,
    wait_event      text,
    query_id        bigint,
    xact_start      timestamptz,
    query           text
);

CREATE PROCEDURE take_ash_sample() LANGUAGE sql AS $$
    WITH s AS (SELECT clock_timestamp() AS ts)
    INSERT INTO ash
    SELECT s.ts, a.pid, a.datname, a.usename, a.state, a.wait_event_type, a.wait_event,
           a.query_id, a.xact_start, left(a.query, 100)
      FROM s, pg_stat_activity a
     WHERE a.backend_type = 'client backend'
       AND a.state = 'active'
       AND a.pid <> pg_backend_pid();
$$;

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

Первая — время. Кажется естественным написать now(), но now() возвращает время начала транзакции и внутри неё не меняется. Пока сборщик дёргают по расписанию, каждый раз в своей транзакции, разницы не видно. А сделайте его так, как его обычно и делают, — циклом с паузой внутри одной процедуры, — и все сэмплы получат одинаковую метку, а период схлопнется в одно мгновение. clock_timestamp() берёт время по-настоящему сейчас и верен в обоих случаях.

Вторая тоньше. clock_timestamp() вычисляется для каждой строки отдельно — значит, две сессии, попавшие в один сэмпл, получат разные метки времени. После этого попытка посчитать «сколько всего было сэмплов» через count(DISTINCT sample_at) даст завышенное число: во столько раз, сколько сессий было активно. Поэтому время берётся один раз в отдельном подзапросе и приклеивается ко всем строкам сэмпла — за это отвечает WITH s AS (SELECT clock_timestamp()).

Когда сборщик работает, по накопленной таблице считается всё, ради чего он затевался:

-- Профиль ожиданий за период
SELECT coalesce(wait_event_type, 'CPU') AS wait_type,
       coalesce(wait_event, 'on CPU')   AS wait_event,
       count(*) AS samples,
       round(100.0 * count(*) / sum(count(*)) OVER (), 1) AS pct
  FROM ash
 WHERE sample_at BETWEEN :from AND :to
 GROUP BY 1, 2
 ORDER BY samples DESC;

-- Кто именно занимал время
SELECT pid, count(*) AS samples,
       round(100.0 * count(*) / (SELECT count(DISTINCT sample_at)
                                   FROM ash WHERE sample_at BETWEEN :from AND :to), 1) AS pct_of_time,
       coalesce(max(wait_event_type), 'CPU') AS wait_type,
       max(query_id) AS query_id,
       left(max(query), 60) AS query
  FROM ash
 WHERE sample_at BETWEEN :from AND :to
 GROUP BY pid
 ORDER BY samples DESC
 LIMIT 10;

Со знаменателем во втором запросе есть тонкость. count(DISTINCT sample_at) считает не все такты, а только те, в которые хоть одна сессия была активна: когда база простаивала, сборщик не записал ни строки, и этих тактов в таблице просто нет. На ровной нагрузке разница невелика, а на рваной доли времени оказываются заметно завышенными. Если это важно, считайте такты по расписанию — число секунд в окне, делённое на шаг — или пишите в каждый такт одну строку-маркер, даже когда активных сессий нет.

Колонка query_id появилась в pg_stat_activity в 14-й версии и заполняется, когда включён compute_query_id — при значении auto он включается сам, если загружен pg_stat_statements. Именно она связывает два мира: по ней видно не просто «сессия стояла на блокировке», а какой конкретно запрос там стоял и сколько он суммарно стоит по данным топов.

Сколько это места и когда удалять

У сборщика выборок есть свойство, которое легко пропустить: он пишет непрерывно, а таблица растёт линейно. Посчитаем на обычных числах: секундная выборка, десять активных сессий в среднем — это 10 строк в секунду, 36 тысяч в час, около 860 тысяч строк в сутки. При примерно 150 байтах на строку (с учётом текста запроса) выходит порядка 150 МБ в сутки, то есть 4–5 ГБ в месяц. А поскольку мы договорились, что база может быть чужой, оставить там такое без спроса — плохая идея.

Что с этим делают.

Не писать текст запроса. Самая большая часть строки — текст. Вместо него хранят query_id и отдельную небольшую таблицу «идентификатор → текст», куда запись добавляется один раз. Это сокращает объём в разы.

Выбирать частоту по задаче. Секунда нужна для разбора коротких всплесков. Для картины «куда ушло время за два часа» достаточно пяти-десяти секунд, и это в пять-десять раз меньше строк при почти той же точности долей.

Удалять старое по расписанию. Простейшая уборка, которую ставят сразу вместе со сборщиком:

DELETE FROM ash WHERE sample_at < now() - interval '7 days';

Здесь важно, что обычное удаление оставляет мёртвые строки и таблица не уменьшается сама, а только перестаёт расти. Поэтому при секции по времени всё проще и правильнее: таблицу делят по дням, и вместо удаления старая секция отсоединяется и удаляется целиком — мгновенно, без мусора:

CREATE TABLE ash (
    sample_at timestamptz NOT NULL,
    pid int, datname text, state text,
    wait_event_type text, wait_event text, query_id bigint
) PARTITION BY RANGE (sample_at);

CREATE TABLE ash_2026_09_24 PARTITION OF ash
    FOR VALUES FROM ('2026-09-24') TO ('2026-09-25');

Свернуть старое в итоги. Промежуточный вариант для тех, кто хочет длинную историю: раз в сутки посчитать по выборкам агрегаты (доли типов ожиданий по часам, топ запросов по числу попаданий) в маленькую таблицу и удалить подробные строки. Место сокращается на порядки, а вопрос «как было в прошлом месяце» остаётся отвечаемым.

И договорённость, которая важнее техники: у сборщика в чужой базе должен быть известный владелец и срок. Таблица ash, найденная через год в чужой схеме без объяснений, — это то, что портит отношения с администраторами баз надолго.

Кто кого держит: блокировки

Когда в ожиданиях преобладает Lock, нужен следующий вопрос — кто держит. PostgreSQL отвечает на него функцией pg_blocking_pids(), которая для процесса возвращает список тех, кого он ждёт:

SELECT a.pid, a.state, a.wait_event_type, a.wait_event,
       now() - a.xact_start AS xact_age,
       pg_blocking_pids(a.pid) AS blocked_by,
       left(regexp_replace(a.query, '\s+', ' ', 'g'), 60) AS query
  FROM pg_stat_activity a
 WHERE a.backend_type = 'client backend'
   AND cardinality(pg_blocking_pids(a.pid)) > 0;

Дальше по этим номерам смотрят держателя — и почти всегда обнаруживают, что он не делает ничего тяжёлого. Типичная картина: сессия открыла транзакцию, обновила строку и ушла в idle in transaction, потому что приложение в этот момент ходило во внешний сервис. Виноват не запрос, а незакрытая транзакция; как это разбирать — в статье про блокировки.

Найти такие транзакции можно и заранее, не дожидаясь жалоб:

SELECT pid, state, wait_event_type, wait_event,
       now() - xact_start   AS xact_age,
       now() - state_change AS in_state,
       left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query
  FROM pg_stat_activity
 WHERE backend_type = 'client backend'
   AND xact_start IS NOT NULL
   AND now() - xact_start > interval '1 minute'
 ORDER BY xact_start;

Долгая транзакция вредна не только тем, что кого-то блокирует. Пока она открыта, автовакуум не имеет права убрать мёртвые строки, которые она теоретически ещё может увидеть, — и таблица распухает у всех.

Обслуживание: вакуум и мёртвые строки

Раздел про обслуживание отвечает на вопрос, справляется ли база с уборкой за собой:

SELECT relname,
       n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, autovacuum_count, last_autoanalyze
  FROM pg_stat_user_tables
 ORDER BY n_dead_tup DESC
 LIMIT 10;

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

Что происходит с уборкой прямо сейчас, видно отдельно:

SELECT p.pid, p.datname, c.relname, p.phase,
       p.heap_blks_total, p.heap_blks_scanned, p.heap_blks_vacuumed,
       round(100.0 * p.heap_blks_scanned / nullif(p.heap_blks_total, 0), 1) AS scanned_pct
  FROM pg_stat_progress_vacuum p
  LEFT JOIN pg_class c ON c.oid = p.relid;

Такие же вьюхи прогресса есть для перестроения индекса и для CLUSTER — pg_stat_progress_create_index и pg_stat_progress_cluster. Полезны они тем, что превращают «оно висит уже час» в «просмотрено 60% страниц, осталось примерно столько же».

И последнее в этом разделе — таблицы, которые читаются целиком:

SELECT relname, seq_scan, seq_tup_read, idx_scan,
       pg_size_pretty(pg_relation_size(relid)) AS size,
       n_tup_ins, n_tup_upd, n_tup_del
  FROM pg_stat_user_tables
 ORDER BY seq_tup_read DESC
 LIMIT 10;

Большое seq_tup_read на большой таблице при почти нулевом idx_scan — самый частый источник лишнего чтения. Маленькую таблицу база и должна читать целиком, это дешевле индекса, поэтому размер здесь смотрится обязательно.

Топы SQL: четыре вопроса к одной таблице

pg_stat_statements — одна таблица, но отвечает она на разные вопросы в зависимости от того, как её отсортировать. Поэтому в отчётах не один топ, а несколько, и каждый ведёт к своему диагнозу.

Куда ушло время базы. Главный топ, с него начинают:

SELECT round(total_exec_time::numeric, 1) AS total_ms,
       round((100 * total_exec_time / nullif(sum(total_exec_time) OVER (), 0))::numeric, 1) AS pct,
       calls,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       rows,
       shared_blks_hit, shared_blks_read, temp_blks_written,
       pg_size_pretty(wal_bytes::numeric) AS wal,
       left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query
  FROM pg_stat_statements
 WHERE dbid = (SELECT oid FROM pg_database WHERE datname = current_database())
 ORDER BY total_exec_time DESC
 LIMIT 10;

Колонка pct здесь важнее абсолютных миллисекунд: она сразу говорит, стоит ли вообще заниматься этой строкой. Запрос на 3% общего времени можно ускорить вдвое и не заметить разницы.

Не выполняется ли что-то в цикле. Тот же набор, отсортированный по числу вызовов:

SELECT calls,
       round(mean_exec_time::numeric, 3) AS mean_ms,
       round(total_exec_time::numeric, 1) AS total_ms,
       rows / nullif(calls, 0) AS rows_per_call,
       left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query
  FROM pg_stat_statements
 ORDER BY calls DESC
 LIMIT 10;

Колонка rows_per_call добавлена не для красоты. Когда она равна единице, а вызовов сотни тысяч, картина почти однозначна: приложение в цикле достаёт записи по одной. Это не лечится индексом — запрос и так быстрый, их просто слишком много. Лечится пакетной выборкой на стороне сервиса.

Что читается с диска. Здесь ищут нехватку памяти и пропущенные индексы:

SELECT shared_blks_read, shared_blks_hit,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       round(blk_read_time::numeric, 1) AS read_ms,
       left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query
  FROM pg_stat_statements
 WHERE shared_blks_read > 0
 ORDER BY shared_blks_read DESC
 LIMIT 10;

Чему не хватило памяти на сортировку. Временные файлы — прямая подсказка про work_mem:

SELECT temp_blks_written,
       pg_size_pretty(temp_blks_written * current_setting('block_size')::int) AS temp,
       calls,
       left(regexp_replace(query, '\s+', ' ', 'g'), 60) AS query
  FROM pg_stat_statements
 WHERE temp_blks_written > 0
 ORDER BY temp_blks_written DESC
 LIMIT 10;

Про версии здесь стоит помнить одно: имена колонок менялись. До 13-й версии время было одно и называлось total_time, без разделения на планирование и выполнение. В 17-й переименовали времена ввода-вывода: blk_read_time стал shared_blk_read_time, и рядом появились отдельные счётчики для локальных и временных блоков. Если готовый запрос падает на неизвестной колонке — почти наверняка дело в этом.

Стоит ли собирать это самому

Запросы выше хороши тем, что работают везде, куда пустили psql, и не требуют ничего, кроме pg_stat_statements. Знать их полезно: это тот минимум, с которым можно разобраться в чужой базе, где никаких инструментов нет.

Но собирать из них собственный отчёт стоит только тогда, когда готовый поставить нельзя. У pg_profile и pgpro_pwr уже сделаны хранилище снимков, ротация, HTML и сравнение периодов — а самодельный сборщик придётся отлаживать ровно в тот момент, когда он нужнее всего.

Глубже: сравнение с эталонным периодомрасширенное

Все топы выше отвечают на вопрос «куда ушло время», и почти никогда не отвечают на главный: стало хуже или так было всегда. Запрос на 38 % времени базы — это авария или его обычная доля? Без второго периода ответа нет, и именно сравнение превращает набор чисел в диагноз.

Механика простая: у вас есть снимки, значит есть и второй интервал — тот же день неделю назад, спокойные утренние часы, период до выката. Соединяем две разницы по queryid и смотрим отношение:

WITH now_period AS (
    SELECT b.queryid, b.query,
           b.calls - a.calls                   AS calls,
           b.total_exec_time - a.total_exec_time AS total_ms
      FROM snap_statements a
      JOIN snap_statements b USING (queryid)
     WHERE a.snap_id = :now_from AND b.snap_id = :now_to
), base_period AS (
    SELECT b.queryid,
           b.calls - a.calls                   AS calls,
           b.total_exec_time - a.total_exec_time AS total_ms
      FROM snap_statements a
      JOIN snap_statements b USING (queryid)
     WHERE a.snap_id = :base_from AND b.snap_id = :base_to
)
SELECT round(n.total_ms::numeric, 0)                        AS now_ms,
       round(coalesce(b.total_ms, 0)::numeric, 0)           AS base_ms,
       round((n.total_ms / nullif(b.total_ms, 0))::numeric, 1) AS times_worse,
       n.calls                                              AS now_calls,
       coalesce(b.calls, 0)                                 AS base_calls,
       round((n.total_ms / nullif(n.calls, 0))::numeric, 2)  AS now_mean_ms,
       round((b.total_ms / nullif(b.calls, 0))::numeric, 2)  AS base_mean_ms,
       left(regexp_replace(n.query, '\s+', ' ', 'g'), 60)    AS query
  FROM now_period n
  LEFT JOIN base_period b USING (queryid)
 ORDER BY n.total_ms - coalesce(b.total_ms, 0) DESC
 LIMIT 15;

Сортировка здесь важнее самих колонок: не по времени, а по приросту времени — так наверху оказывается то, что изменилось, а не то, что всегда было дорогим. И LEFT JOIN обязателен по той же причине, что и в первом разделе: запрос, появившийся только сейчас (новый код), в эталоне отсутствует, и внутреннее соединение выбросило бы самого вероятного виновника.

Дальше диагноз читается по двум колонкам:

Что видно	Диагноз
Вызовов столько же, среднее выросло	Запрос стал дороже: план, данные, распухание, чтение с диска
Вызовов в разы больше, среднее прежнее	Изменилось приложение или нагрузка: цикл в новом коде, рост трафика
Вызовов больше и среднее больше	Обычно следствие первого: база не справляется, и запросы копятся
В эталоне нет вовсе	Новый запрос — ищите, что выкатили
Есть в эталоне, сейчас нет	Запрос перестал вызываться: либо починили, либо перестал работать код

Что нужно для того, чтобы это работало: эталонные снимки должны существовать, то есть сборщик должен работать заранее, а не быть запущенным в момент аварии. Отсюда практическое правило — включить сбор до того, как понадобится, и сохранять номера «спокойных» интервалов куда-нибудь в заметку: в момент разбора искать спокойный период по графикам некогда.

Глубже: состояние сервера: чекпоинты, архивация, возрастрасширенное

Последний раздел отчёта — про сервер целиком, а не про отдельные запросы.

-- PostgreSQL 16 и раньше
SELECT checkpoints_timed, checkpoints_req,
       round(checkpoint_write_time::numeric / 1000, 1) AS ckpt_write_s,
       round(checkpoint_sync_time::numeric / 1000, 1)  AS ckpt_sync_s,
       buffers_checkpoint, buffers_clean, maxwritten_clean,
       buffers_backend, buffers_backend_fsync, buffers_alloc
  FROM pg_stat_bgwriter;

Главное здесь — соотношение двух первых колонок. Чекпоинт бывает плановый, по расписанию, и запрошенный — когда журнал вырос до max_wal_size и ждать больше нельзя. В здоровой системе плановых большинство. Перевес запрошенных означает, что база пишет чекпоинты чаще, чем задумано, а за этим тянется рост полностраничных образов, о которых говорилось выше.

В 17-й версии эту вьюху разделили: счётчики чекпоинтов уехали в pg_stat_checkpointer — там они называются num_timed и num_requested, — а в pg_stat_bgwriter остались только buffers_clean, maxwritten_clean и buffers_alloc.

Начиная с 16-й версии есть куда более подробный разрез ввода-вывода — по типам процессов:

SELECT backend_type, object, context,
       reads, round(read_time::numeric, 1) AS read_ms,
       writes, round(write_time::numeric, 1) AS write_ms,
       extends, hits, evictions
  FROM pg_stat_io
 WHERE reads > 0 OR writes > 0
 ORDER BY reads + writes DESC;

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

Архивация журналов — две строки, но именно они молча ломают резервное копирование:

SELECT archived_count, last_archived_wal, last_archived_time,
       failed_count, last_failed_wal, last_failed_time
  FROM pg_stat_archiver;

Растущий failed_count означает, что журналы не уезжают. Последствий два, и оба неприятные: восстановление на точку во времени уже невозможно, а место под журналы рано или поздно закончится.

Возраст базы — то, о чём вспоминают поздно:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       round(100.0 * age(datfrozenxid) / 2000000000, 1) AS pct_to_wraparound
  FROM pg_database
 ORDER BY xid_age DESC;

Номера транзакций в PostgreSQL конечны, и чтобы они не переполнились, старые строки периодически «замораживаются» вакуумом. Если этого не происходит, база приближается к пределу и в какой-то момент останавливает запись, уходя в аварийную уборку. Смотреть на этот процент стоит регулярно, а не когда он уже перевалил за восемьдесят.

И напоследок — настройки, особенно те, которые кто-то менял:

SELECT name, setting, unit, source, pending_restart
  FROM pg_settings
 WHERE source NOT IN ('default', 'override')
 ORDER BY name;

Колонка pending_restart со значением true означает, что параметр в файле изменили, а сервер не перезапустили: в конфигурации написано одно, работает другое. Удивительная доля долгих расследований заканчивается именно этой строкой.

Глубже: от найденного queryid к планурасширенное

Все топы заканчиваются идентификатором и обрезанным текстом, а следующий шаг разбора — план. Здесь есть ступенька, о которую спотыкаются: EXPLAIN по нормализованному тексту выполнить нельзя. В статистике вместо значений стоят $1, $2, и сервер на такой запрос ответит ошибкой про неопределённый параметр.

Шаг первый — получить полный текст. В топах он обрезан ради читаемости, поэтому берут отдельно:

SELECT query FROM pg_stat_statements WHERE queryid = :queryid;

Если текст обрезан и здесь, дело в настройке track_activity_query_size (по умолчанию 1024 байта) — и это повод её поднять на будущее.

Шаг второй — подставить параметры. Их надо откуда-то взять, и есть три источника, по убыванию удобства. Из журнала: при включённом log_min_duration_statement в нём лежит запрос с настоящими значениями — самый точный вариант, потому что это реальный вызов. Из плана, снятого автоматически: auto_explain пишет план вместе с параметрами, если включён auto_explain.log_parameter_max_length. Руками: посмотреть на условия и подобрать значения самому — годится, но осторожно, см. ниже.

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

PREPARE probe(bigint, timestamptz) AS
    SELECT * FROM orders WHERE customer_id = $1 AND created_at > $2;

EXPLAIN (ANALYZE, BUFFERS) EXECUTE probe(42, '2026-09-01');

И главное предостережение: план зависит от значений. Один и тот же нормализованный запрос для клиента с тремя заказами пойдёт по индексу за миллисекунду, а для клиента с миллионом заказов прочитает половину таблицы. В статистике оба слиты в одну строку со средним, поэтому подбирать параметры «попроще» — верный способ увидеть прекрасный план и не найти проблему. Брать надо тяжёлый случай: либо из журнала, где лежит настоящий медленный вызов, либо самое «большое» значение, какое есть в данных.

Отсюда же практическое следствие: если среднее время запроса велико, а план на подобранных параметрах прекрасен, — вероятно, дело не в плане, а в разбросе. Такие запросы разбирают иначе: смотрят распределение (min_exec_time, max_exec_time, stddev_exec_time в той же таблице) и ищут, чем отличаются дорогие вызовы. Большое стандартное отклонение при умеренном среднем — почти всегда признак, что план для разных параметров разный.

Куда идти дальше с планом в руках — в статье про EXPLAIN: что означают оценки, как читать разницу между оценкой и фактом и когда виноваты устаревшие данные о распределении.

Коротко

• Статистика накопительная: «за период» получается только разницей двух снимков, отдельным запросом это не спросить. В разностном запросе обязательны внешнее соединение (новый запрос отсутствует в раннем снимке) и ::numeric перед round; отрицательная разница означает сброс статистики, а не аномалию.
• Пустой wait_event_type у активной сессии — это работа на процессоре, а не отсутствие ожиданий. Сборщик ASH пишет clock_timestamp(), и метка времени берётся одна на сэмпл, иначе доли времени считаются неверно.
• Топов SQL несколько, потому что это четыре разных диагноза: суммарное время, число вызовов, чтение с диска, временные файлы.
• Запрошенных чекпоинтов больше плановых — база упирается в max_wal_size; растущий failed_count у архиватора — сломанное восстановление на точку во времени.
• Версии: pg_stat_wal с 14-й, pg_stat_io с 16-й, в 17-й чекпоинты уехали в pg_stat_checkpointer, а времена ввода-вывода в pg_stat_statements переименованы в shared_blk_*; в 18-й из pg_stat_wal убрали счётчики записи и синхронизации — они теперь в pg_stat_io.
• :from и :to — переменные psql (\set и форма :'from' для строк), а из другого клиента границы подставляют в текст или берут из своей таблицы настроек.
• Нужна роль pg_monitor или pg_read_all_stats: без них чужие запросы видны как <insufficient privilege>, а поля чужих сессий пусты; суперпользователь не нужен.
• Таблица выборок растёт примерно на 860 тысяч строк в сутки при секундной частоте: текст запроса выносят в отдельную таблицу, частоту снижают, старое удаляют секциями или сворачивают в итоги.
• Числа получают смысл только в сравнении с эталонным периодом, и сортировать надо по приросту времени, а не по времени; поэтому сбор включают заранее и запоминают спокойные интервалы.
• EXPLAIN по нормализованному тексту не выполнить: полный текст берут из статистики, параметры — из журнала или автоматических планов, и обязательно тяжёлый случай, иначе план окажется прекрасным.

Что почитать дальше

• Отчёт нагрузки PostgreSQL — как читать готовый отчёт и ставить диагноз.
• Мониторинг PostgreSQL — что держать на дашборде в реальном времени.
• EXPLAIN и планы запросов — следующий шаг после того, как виновник найден.
• VACUUM и распухание — почему растут мёртвые строки и чем это кончается.