1. Мониторинг в реальном времени через pg_stat_activity

Рассмотрим представление для мониторинга текущих активных соединений и запросов через pg_stat_activity
Запрос к pg_stat_activity покажет, какие запросы работают в БД в конкретный момент времени (при обращении к pg_stat_activity):

select * from pg_stat_activity where state <> 'idle'

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

2. Расширение для сбора статистики выполнения
запросов pg_stat_statements

• Сохраняет информацию о частоте выполнения запросов, общем времени выполнения, времени
ожидания и других параметрах.
• Используется для анализа наиболее "тяжелых" запросов.

2.1 Настройка

Чтобы включить pg_stat_statements, необходимо выполнить запрос

CREATE EXTENSION pg_stat_statements;

А также необходимо в postgresql.conf задать настройки сбора статистики:

# Подключение библиотеки pg_stat_statements. Если в
shared_preload_libraries уже указаны другие библиотеки, просто добавьте
pg_stat_statements через запятую, например
'pg_stat_statements,other_library':
shared_preload_libraries = 'pg_stat_statements'
# Максимальное количество запросов, статистика которых будет храниться:
pg_stat_statements.max = 5000
# Типы запросов, которые будут отслеживаться (all, top, или none)
pg_stat_statements.track = all
# Сохранять статистику между перезапусками:
pg_stat_statements.save = on
# Включать параметр queryid в статистику (только для PostgreSQL 14 и выше)
compute_query_id = on

2.2 Сбор статистики

Чтобы собрать статистику, можно использовать следующие варианты запросов:
• Сбор самых долгих (по общему времени выполнения) запросов:

SELECT 
 queryid,
 query,
 calls,
 total_time,
 mean_time,
 max_time,
 rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 100;

• Сбор самых долгих (по среднему времени выполнения) запросов:

SELECT 
 queryid,
 query,
 calls,
 total_time,
 mean_time,
 max_time,
 rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 100;

• Сбор самых долгих (по максимальному времени выполнения) запросов:

SELECT
 queryid,
 query,
 calls,
 total_time,
 mean_time,
 max_time,
 rows
FROM pg_stat_statements
ORDER BY max_time DESC
LIMIT 100;

Для анализа будут полезны все три вида статистики. 
Обратите внимание, параметр queryid доступен к использованию только в версиях
PostgreSQL 14 и выше. В случае использования версии PostgreSQL ниже 14, параметр
queryid необходимо исключить из запроса.

После сбора статистики вы можете отправить информацию на анализ в 
Отдел сопровождения и внедрения: support@iqstore.ru.

1.3 PWR-отчёт

В PostgresPro, коммерческом форке PostgreSQL, есть специальный инструмент, который
можно считать аналогом AWR-отчёта Oracle — pgpro_pwr.
Подробнее информацию по настройке см. в документации вендора.