Pg stat activity как использовать

pg_stat_activity – системное представление PostgreSQL, которое показывает текущее состояние всех подключений к базе данных. Оно позволяет увидеть выполняемые SQL-запросы, их статус, время начала выполнения, пользователя, приложение и адрес клиента. Эти данные используются для оперативного анализа нагрузки без включения дополнительного логирования.

Через pg_stat_activity можно определить, какие запросы реально занимают сервер в данный момент, какие сессии находятся в ожидании блокировок и сколько времени запрос уже выполняется. Например, сравнение полей state, query_start и wait_event помогает быстро понять, является ли задержка результатом медленного запроса или конкуренции за ресурсы.

Практическая ценность представления проявляется при диагностике инцидентов: резкий рост количества подключений, зависшие транзакции, не закрытые соединения из приложений. Фильтрация по backend_type, application_name и usename упрощает поиск источника проблемы, а связка с функциями завершения сессий позволяет устранить критичную нагрузку напрямую из SQL.

Назначение представления pg_stat_activity и состав возвращаемых данных

pg_stat_activity предназначено для получения среза текущего состояния всех серверных процессов PostgreSQL. Каждая строка соответствует одному backend-процессу и отражает реальное состояние соединения в момент выполнения запроса к представлению. Источник данных – внутренняя статистика сервера, обновляемая без обращения к журналам.

Ключевые группы столбцов:

• Идентификация процесса
  • pid – идентификатор backend-процесса, используется при завершении сессии.
  • backend_type – тип процесса: client backend, autovacuum worker, logical replication worker.
• Контекст подключения
  • usename – пользователь базы данных.
  • datname – имя базы.
  • application_name – значение, переданное клиентом, полезно для привязки к сервису.
  • client_addr и client_port – источник подключения.
• Состояние выполнения
  • state – текущее состояние: active, idle, idle in transaction, idle in transaction (aborted).
  • query_start – момент запуска текущего запроса.
  • state_change – время последнего изменения состояния.
• Информация об ожиданиях
  • wait_event_type – категория ожидания: Lock, IO, Client, LWLock.
  • wait_event – конкретный объект ожидания.
• SQL-контекст
  • query – текст выполняемого запроса или последнего запроса для неактивных сессий.
  • query_id – идентификатор запроса при включённом compute_query_id.

При анализе нагрузки рекомендуется:

1. Отделять active-сессии от простаивающих, чтобы не путать реальное выполнение с удержанием соединений.
2. Сравнивать now() — query_start только для active-запросов, исключая idle in transaction.
3. Обращать внимание на сочетание wait_event_type = ‘Lock’ и длительного времени выполнения.
4. Фильтровать служебные процессы по backend_type, чтобы не смешивать их с клиентскими запросами.

Текст запроса в столбце query может быть усечён параметром track_activity_query_size. Для анализа длинных SQL-запросов это значение следует увеличить и перезапустить сервер, иначе диагностика будет неполной.

Получение списка активных SQL-запросов и их состояний

Для просмотра выполняемых в данный момент запросов используется выборка из pg_stat_activity с фильтрацией по столбцу state. На практике интерес представляют сессии со значением active, так как именно они потребляют ресурсы сервера в текущий момент.

Базовый запрос для получения списка активных SQL-операций выглядит так:

SELECT pid, usename, datname, application_name, state, query_start, query FROM pg_stat_activity WHERE state = ‘active’;

Поле query_start позволяет оценить длительность выполнения через разницу с now(). Это даёт возможность быстро выделить запросы, которые работают дольше ожидаемого и требуют дополнительной проверки.

Столбец state отражает текущее положение сессии и используется для классификации:

active – запрос выполняется или ожидает ресурс;

idle – соединение открыто, запрос не выполняется;

idle in transaction – транзакция начата, но запрос не выполняется;

idle in transaction (aborted) – транзакция прервана ошибкой.

Для оперативного анализа рекомендуется исключать простаивающие подключения и служебные процессы, добавляя условия по backend_type и datname. Это снижает шум и упрощает поиск проблемных запросов в многопользовательских системах.

Для регулярного мониторинга список активных запросов целесообразно сортировать по времени выполнения и периодически фиксировать результаты. Это помогает выявлять повторяющиеся ситуации и конкретные SQL-операции, создающие нагрузку.

Определение времени выполнения запросов через pg_stat_activity

В pg_stat_activity длительность выполнения определяется косвенно, так как представление не хранит готовое значение времени работы запроса. Для расчёта используется разница между текущим временем и значением столбца query_start, который фиксирует момент запуска последнего SQL-запроса в сессии.

Типовой запрос для расчёта времени выполнения активных операций:

SELECT pid, usename, now() — query_start AS execution_time, query FROM pg_stat_activity WHERE state = ‘active’;

Для корректной интерпретации результатов рекомендуется:

Фильтровать только строки со значением state = ‘active’, так как для idle-сессий query_start отражает последний выполненный запрос и вводит в заблуждение.

Исключать транзакции в состоянии idle in transaction, где длительность относится к открытому контексту, а не к выполняемому SQL.

Сравнивать длительность с типичным временем выполнения аналогичных запросов, а не с фиксированным порогом.

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

Для выборки самых долгих операций используется сортировка по вычисленному времени:

SELECT pid, usename, now() — query_start AS execution_time, query FROM pg_stat_activity WHERE state = ‘active’ ORDER BY execution_time DESC;

Этот подход позволяет быстро выявить запросы, которые требуют оптимизации, пересмотра индексов или изменения логики работы приложения.

Поиск блокировок и ожидающих сессий в текущий момент

Ожидание блокировок в PostgreSQL фиксируется в pg_stat_activity через столбцы wait_event_type и wait_event. Если значение wait_event_type равно Lock, сессия приостановлена до освобождения ресурса и не выполняет SQL-код.

Для выявления всех сессий, находящихся в ожидании блокировок, используется выборка с явным фильтром:

SELECT pid, usename, state, wait_event_type, wait_event, query FROM pg_stat_activity WHERE wait_event_type = ‘Lock’;

Значение столбца wait_event указывает тип блокировки, например transactionid, relation или tuple. Это позволяет понять, конфликтуют ли запросы на уровне таблицы, строки или транзакции.

Для поиска источника блокировки необходимо сопоставить ожидающие сессии с активными процессами, которые удерживают ресурс. На практике сначала выделяют запросы в состоянии active с длительным временем выполнения и отсутствием ожиданий, так как именно они чаще всего блокируют остальных.

Дополнительный признак проблемной ситуации – сочетание idle in transaction и ожиданий у других сессий. Открытая транзакция без выполняемого запроса может удерживать блокировки значительно дольше, чем активная операция.

Для оперативной диагностики рекомендуется регулярно сортировать ожидающие сессии по времени выполнения запроса и фиксировать изменения. Если блокировка повторяется, следует проверить логику транзакций в приложении и сократить время между BEGIN и COMMIT.

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

Анализ подключений по пользователям, приложениям и адресам

Представление pg_stat_activity позволяет детально разобрать структуру подключений к базе данных и определить, кто и откуда создаёт нагрузку. Для этого используются столбцы usename, application_name и client_addr, которые отражают контекст каждого соединения.

Анализ по пользователям помогает выявить перекосы в распределении подключений:

• usename – показывает, какой пользователь базы инициировал соединение;
• группировка по этому полю позволяет обнаружить сервисы, открывающие чрезмерное количество сессий;
• отдельное внимание стоит уделять ролям с широкими правами, так как их запросы чаще затрагивают критичные таблицы.

Поле application_name используется для привязки соединений к конкретным сервисам, библиотекам или заданиям:

• корректно заданное значение упрощает поиск источника проблемных запросов;
• одинаковые имена у большого числа сессий указывают на пул соединений или фоновый процесс;
• пустое значение часто сигнализирует о неверной настройке клиента.

Сетевой контекст подключения определяется через client_addr и client_port:

• внешние адреса позволяют отделить пользовательские подключения от внутренних сервисов;
• один адрес с сотнями соединений обычно указывает на отсутствие или некорректную работу пула;
• частая смена портов при стабильном адресе характерна для короткоживущих соединений.

Для практического анализа применяется агрегация данных:

SELECT usename, application_name, client_addr, count(*) FROM pg_stat_activity GROUP BY usename, application_name, client_addr ORDER BY count(*) DESC;

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

Выявление зависших и неактуальных соединений

Для идентификации таких соединений удобно использовать таблицу с ключевыми параметрами:

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

SELECT pid, usename, datname, state, query_start, state_change, query FROM pg_stat_activity WHERE state IN (‘idle’, ‘idle in transaction’) AND now() — state_change > interval ’10 minutes’;

Рекомендации при работе с неактуальными соединениями:

• Фокусироваться на сессиях с длительным idle in transaction, так как они могут удерживать блокировки.
• Сравнивать query_start и state_change для определения фактического простоя.
• При необходимости завершать такие соединения через pg_terminate_backend(pid), предварительно проверив, что они не выполняют критические операции.
• Регулярно анализировать отчёты, чтобы выявлять повторяющиеся источники зависших соединений и корректировать логику приложения.

Завершение проблемных запросов и сессий штатными средствами PostgreSQL

Для прекращения выполнения проблемных запросов в PostgreSQL используются встроенные функции pg_cancel_backend(pid) и pg_terminate_backend(pid). Первая прерывает только текущий SQL-запрос, не закрывая соединение, вторая завершает весь backend-процесс, освобождая все удерживаемые ресурсы.

Пример отмены долгого запроса без завершения сессии:

SELECT pg_cancel_backend(12345);

Если сессия блокирует другие процессы или находится в состоянии idle in transaction длительное время, используется полное завершение:

SELECT pg_terminate_backend(12345);

При этом важно учитывать следующие моменты:

• Нельзя завершать системные процессы и фоновые воркеры (backend_type ≠ ‘client backend’).
• Идентификатор pid берётся из pg_stat_activity, желательно дополнительно проверять пользователя и базу.
• После pg_terminate_backend соединение закрывается, все незавершённые транзакции откатываются автоматически.
• Регулярный мониторинг позволяет заранее выявлять сессии, которые могут потребовать вмешательства.

Для оперативного управления рекомендуется составлять выборку проблемных запросов с сортировкой по длительности и состоянию ожидания:

SELECT pid, usename, state, wait_event_type, now() — query_start AS execution_time, query FROM pg_stat_activity WHERE state = ‘active’ OR (state = ‘idle in transaction’ AND now() — state_change > interval ’10 minutes’) ORDER BY execution_time DESC;

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

Вопрос-ответ:

Какие данные отображает pg_stat_activity и как их использовать для анализа нагрузки?

pg_stat_activity показывает текущее состояние всех соединений к базе данных. Среди ключевых столбцов: pid, usename, datname, application_name, state, query_start, wait_event_type, wait_event и query. Используя эти поля, можно определить активные запросы, длительность их выполнения, ожидающие блокировки процессы и источник соединения. Это позволяет оперативно выявлять запросы, которые занимают ресурсы, и понять, какие пользователи или приложения создают нагрузку.

Как определить, какие запросы занимают сервер дольше всего?

Для оценки длительности выполнения запроса используется разница между текущим временем и значением query_start. Рекомендуется выбирать только строки с state = ‘active’, так как idle-сессии не отражают фактическое выполнение. Сортировка по вычисленному времени позволяет быстро увидеть самые длительные операции и выявить потенциальные узкие места в работе базы данных.

Можно ли завершать зависшие или блокирующие запросы через pg_stat_activity?

Да, PostgreSQL предоставляет функции pg_cancel_backend(pid) для прерывания текущего запроса и pg_terminate_backend(pid) для полного завершения сессии. При этом pid берётся из pg_stat_activity. Важно проверять backend_type, чтобы не завершать системные процессы. Перед применением следует убедиться, что сессия не выполняет критические операции, иначе произойдёт откат незавершённых транзакций.

Как отличить активные запросы от простаивающих соединений?

Активные запросы имеют state = ‘active’, idle-сессии — state = ‘idle’, а транзакции, начатые, но без выполнения SQL, имеют state = ‘idle in transaction’. Анализируя wait_event_type и wait_event, можно дополнительно понять, ожидает ли запрос ресурс или блокировку. Это помогает не путать настоящую нагрузку с открытыми соединениями, которые не выполняют операций.

Какие рекомендации по мониторингу подключений пользователей и приложений?

Для анализа подключений используется агрегация по usename, application_name и client_addr. Это позволяет выявлять сервисы или пользователей с большим числом соединений, а также определить источники пиковых нагрузок. Регулярный контроль помогает находить повторяющиеся неактуальные соединения и корректировать логику работы приложения, например, использовать пул соединений или ограничивать количество одновременных подключений.