Основні команди моніторингу в PostgreSQL — pg_stat_activity і pg_stat_user_tables

Monitoring бази даних без pg_stat_activity і pg_stat_user_tables — це як слідкувати за здоров’ям тільки по одному показнику температури. Ти не зможеш зрозуміти, де проблема, якщо дивишся лише на загальну картину. Оці дві ключові команди PostgreSQL допоможуть тобі не просто спостерігати, а й реально аналізувати, що відбувається у твоїй базі.

Що таке pg_stat_activity?

pg_stat_activity — це системне представлення PostgreSQL, яке показує інфу про всі підключення до твоєї бази даних. Воно відповідає на питання: хто підключений до бази, які запити виконуються прямо зараз, і які підключення "зависли" у стані бездіяльності. Це твій інструмент для аналізу поточної активності на сервері.

Давай розберемо основні поля, доступні в pg_stat_activity. Поле datname містить ім’я бази, до якої підключений клієнт, а usename показує ім’я користувача, який зробив підключення. application_name вказує на ім’я додатку, що юзає підключення, client_addr містить IP-адресу клієнта, підключеного до сервера. backend_start показує час, коли клієнт підключився до сервера, state відображає поточний стан підключення (active, idle, idle in transaction), а query містить запит, який виконується або виконувався останнім.

Приклад 1: перегляд усіх активних підключень

Щоб побачити активні підключення, виконай наступний запит:

SELECT datname, usename, client_addr, state, query
FROM pg_stat_activity
WHERE state = 'active';

Зверни увагу на поле query. Воно показує запити, які зараз виконуються. Якщо запит займає занадто багато часу, можливо, з ним щось не так.

Приклад 2: Аналіз стану транзакцій

Іноді підключення "застрягають" у стані idle in transaction. Це означає, що транзакція була почата, але не завершена, а це може призводити до блокувань.

SELECT pid, usename, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Як це пофіксити? Якщо ти знайшов "завислу" транзакцію, можна завершити її за допомогою команди:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction';

Деякі розробники цим занадто захоплюються. Радимо спочатку уточнити у команди, чи можна "вбити" процес. Ой, вибач, я мав на увазі — завершити підключення.

Моніторинг використання таблиць: представлення pg_stat_user_tables

Якщо pg_stat_activity дозволяє слідкувати за підключеннями, то pg_stat_user_tables розповідає про продуктивність таблиць. З його допомогою ти дізнаєшся: як часто дані з таблиць читаються чи пишуться, які таблиці використовуються найчастіше, і де можуть бути трабли з продуктивністю.

Ось основні поля pg_stat_user_tables, які допоможуть тобі аналізувати таблиці. relname містить ім’я таблиці, seq_scan показує кількість послідовних сканувань таблиці, idx_scan — кількість сканувань з використанням індексу. n_tup_ins відображає кількість рядків, вставлених у таблицю, n_tup_upd — кількість рядків, оновлених у таблиці, а n_tup_del — кількість рядків, видалених із таблиці.

Приклад 1: порівняння використання індексів і послідовних сканувань

Якщо індекс використовується занадто рідко (idx_scan близько до нуля), скоріш за все, запити до цієї таблиці можна оптимізувати.

SELECT relname, seq_scan, idx_scan
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

Приклад результату:

Якщо ти бачиш, що таблиця orders має дуже багато послідовних сканувань (seq_scan), подумай про додавання індексу. Уяви таблицю orders з 3500 послідовними скануваннями і лише 100 скануваннями по індексу, в той час як таблиця employees має 50 послідовних сканувань і 1000 сканувань по індексу — це явний сигнал для оптимізації.

Приклад 2: аналіз кількості операцій з таблицями

Щоб побачити, наскільки "живі" дані в таблицях, запитай інфу про вставлені, оновлені і видалені рядки:

SELECT relname, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY n_tup_ins DESC;

Що ти можеш дізнатися? Таблиці з великим числом операцій вставки (n_tup_ins) і видалень (n_tup_del) можуть бути гарячими точками у твоїй базі. Це означає, що їх продуктивність заслуговує особливої уваги.

Практичне застосування команд для аналізу продуктивності: комбінуємо дані pg_stat_activity і pg_stat_user_tables

Коли ти аналізуєш продуктивність бази даних, можна об’єднати показники з двох джерел. Спочатку визнач довгі запити через pg_stat_activity, потім перевір, які таблиці використовуються цими запитами, через pg_stat_user_tables. Якщо запити довго виконуються над таблицями з високим seq_scan, спробуй оптимізувати запити або додати індекс.

Приклад запиту:

WITH active_queries AS (
    SELECT pid, query
    FROM pg_stat_activity
    WHERE state = 'active' AND query <> '<IDLE>'
)
SELECT a.pid, a.query, t.relname, t.seq_scan, t.idx_scan
FROM active_queries a
JOIN pg_stat_user_tables t ON a.query LIKE '%' || t.relname || '%';