Наблюдение и обслуживание PostgreSQL

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

Наблюдаемость — возможность по метрикам, журналам и планам объяснить, что происходит в системе. SLO — измеримая цель сервиса, например «99,9% запросов чтения завершаются быстрее 200 мс». Ёмкость — запас процессора, памяти, диска, соединений и пропускной способности на будущий рост.

#Текущие сеансы

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

SELECT
    pid,
    usename,
    application_name,
    state,
    xact_start,
    query_start,
    wait_event_type,
    wait_event,
    backend_xid,
    backend_xmin,
    query
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY xact_start NULLS LAST;

active означает выполняемую команду, но не обязательно проблему. idle означает, что клиент сейчас не выполняет команду, но соединение всё равно занято.

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

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

#Накопительные счётчики базы

SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    temp_bytes,
    deadlocks,
    checksum_failures,
    stats_reset
FROM pg_stat_database
WHERE datname = current_database();

Большинство значений накапливаются со времени stats_reset. Для графика полезна не сама цифра xact_commit, а скорость роста за минуту. После сброса статистики разница должна начинаться заново.

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

#Какие запросы тратят ресурсы

Расширение pg_stat_statements объединяет похожие запросы и собирает статистику их выполнения. Его нужно добавить в shared_preload_libraries, перезапустить сервер и выполнить CREATE EXTENSION.

SELECT
    queryid,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    shared_blks_hit,
    shared_blks_read,
    temp_blks_read,
    temp_blks_written,
    wal_bytes,
    query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time находит запросы с большим общим расходом, даже если каждый вызов короткий. mean_exec_time показывает среднее, но скрывает редкие очень медленные вызовы. Процентили, например p95, обычно считают в метриках приложения.

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

#События ожидания

SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active'
GROUP BY wait_event_type, wait_event
ORDER BY count(*) DESC;

Событие ожидания (wait event) показывает, чего процесс ждёт. Это начало расследования, а не готовая причина:

Lock ведёт к поиску блокирующего процесса;
IO требует посмотреть запрос, план и состояние диска;
Client может означать медленного клиента или обычное ожидание протокола.

#Autovacuum и ANALYZE

UPDATE и DELETE оставляют старые версии строк. VACUUM делает их место повторно используемым, обновляет карту видимости и предотвращает переполнение счётчиков транзакций. ANALYZE обновляет статистику для планировщика.

autovacuum — фоновые процессы, которые запускают обе операции автоматически:

SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    n_mod_since_analyze,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

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

ALTER TABLE event_state SET (
    autovacuum_vacuum_scale_factor = 0.03,
    autovacuum_analyze_scale_factor = 0.01,
    autovacuum_vacuum_insert_scale_factor = 0.05
);

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

#Долгие транзакции и слоты репликации

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

SELECT slot_name, slot_type, active,
       pg_size_pretty(
           pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots;

Остановившийся получатель способен заполнить диск ведущего сервера. Не удаляйте слот вслепую: получатель потеряет позицию и может потребовать полной переинициализации. Оповещение должно сработать задолго до заполнения диска.

#Разрастание и место на диске

Обычный VACUUM делает место повторно используемым внутри таблицы, но чаще всего не уменьшает файл пропорционально удалённым строкам. VACUUM FULL переписывает таблицу, требует свободное место и сильную блокировку.

Сначала устраните причину разрастания: долгие транзакции, отстающий autovacuum, лишние индексы, характер обновлений или неподходящий fillfactor. Оценивайте таблицу и индексы вместе, а не только n_dead_tup.

#Настройки и планирование ёмкости

Нет универсальных правил shared_buffers = 25% и «увеличьте work_mem». Планируйте совместно:

число соединений и стоимость процессов на фоне пула;
рабочий набор данных, кеш ОС и фактическое чтение;
число одновременных сортировок и хешей, каждая из которых может получить work_mem;
скорость WAL, контрольные точки и архивирование;
рост таблиц, индексов и временных файлов;
запас места для перестроения индекса и восстановления;
способность autovacuum и реплик успевать за записью.

Меняйте одну гипотезу и сохраняйте измерения до и после. Настройки с единицами смотрите в pg_settings, а не угадывайте, означает число байты, страницы или миллисекунды.

#Полезное оповещение

Хорошее оповещение содержит симптом, интервал, порог и первый шаг инструкции. Полезные сигналы:

запросы нарушают SLO по времени или ошибкам;
растёт возраст транзакции и очередь блокировок;
архив или реплика отстают настолько, что нарушается допустимая потеря данных;
autovacuum не успевает или приближается переполнение счётчика транзакций;
прогноз показывает заполнение диска с учётом WAL и временных файлов.

Сигнал «доля кеша ниже 99%» без обычного уровня конкретной системы чаще создаёт шум.

#Практика

В лаборатории 25 вы создадите представление состояния базы и отдельные правила обслуживания для часто изменяемой таблицы.

Подробнее: наблюдение за активностью, регулярный VACUUM, потребление ресурсов.