Тормозит Postgres: 12 шагов диагностики

Производительность PostgreSQL может проседать постепенно. Сначала запросы зависают и растёт нагрузка растёт, а затем CPU начинает работать на 100%, хотя данных больше не стало. База тормозит, что сказывается на работе всего проекта. Возможных причин много: от неоптимальных запросов до ошибок конфигурации. В таких ситуациях важно не паниковать, а действовать постепенно — от простого к сложному.

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

Шаг 1. Проверьте CPU, память и диск

Начинаем с базы — проверяем аппаратные и системные ресурсы. Иногда проблема кроется в том, что серверу объективно тяжело. CPU перегружен, память закончилась, диск устал, и PostgreSQL просто не из чего строить производительность. База работает как сениор-разработчик в последней стадии выгорания.

			bash
top       # нагрузка на CPU, load average
htop      # удобная визуализация потоков
free -h   # память и swap
iostat -x 1   # состояние дисков и задержки I/O

		

На что ориентироваться:

• Если CPU постоянно упирается в предел — анализируем активность запросов или рассматриваем масштабирование.
• Если Swap активен — не хватает памяти.
• Если видите высокий iowait или высокий %util диска, то кандидаты: медленное хранилище, случайные чтения, VACUUM, большие запросы.

Шаг 2. Изучите активность в pg_stat_activity

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

			sql
SELECT 
  pid, usename, application_name, client_addr,
  state, wait_event_type, wait_event,
  clock_timestamp() - xact_start AS xact_duration,
  LEFT(query, 120) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
  AND pid <> pg_backend_pid()
ORDER BY xact_duration DESC;

		

На что обратить внимание

• долгие транзакции;
• wait_event_type = 'Lock';
• idle in transaction — признак некорректной работы приложения с транзакциями.

Однажды в проде столкнулись с резким ростом задержек запросов, далее — массовые ошибки canceling statement due to lock timeout. Выяснилось: в ETL начали повсеместно использовать транзакции, в том числе там, где они не нужны. Долгие этапы блокировали частые операции. Перезапуск PostgreSQL помогал до следующего запуска ETL. 

Решение: идентифицировать и завершить блокирующие процессы, переработать ETL на более короткие транзакции. В дальнейшем — маркировать подобные процессы и мониторить их отдельно.

Игорь Федосеев, главный IT-инженер, тимлид ETL-команды

Шаг 3. Найдите медленные запросы в pg_stat_statements

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

			sql
SELECT
  query, calls, total_exec_time, mean_exec_time, rows,
  shared_blks_hit, shared_blks_read
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

		

Что искать:

• запросы, потребляющие больше всего времени;
• случаи, когда чтение идёт с диска, а не из буфера (shared_blks_read >> shared_blks_hit).

Шаг 4. Выявите блокировки

Ищите ситуации, когда несколько запросов пытаются работать с одними и теми же данными одновременно. Если один процесс держит строку или таблицу, остальные ждут. Тогда база не падает, а просто стоит в очереди.

			sql
SELECT 
  blocked.pid  AS blocked_pid,
  blocking.pid AS blocking_pid,
  blocked.query  AS blocked_query,
  blocking.query AS blocking_query
FROM pg_locks blocked_locks
JOIN pg_stat_activity blocked ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON ...
JOIN pg_stat_activity blocking ON blocking.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

		

Ищем цепочки блокировок. Частые причины:

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

Шаг 5. Проанализируйте план запроса с EXPLAIN ANALYZE

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

			sql
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...


		

Шаг 6. Проверьте и оптимизируйте индексы

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

Как оптимизировать:

• не создавать индекс на каждый столбец;
• использовать частичные индексы, если данные распределены неравномерно;
• регулярно проводить REINDEX для крупных объектов.

Шаг 7. Устраните раздувание таблиц — bloat

PostgreSQL не удаляет ненужные данные сразу, а копит «мёртвые» строки. Если их становится слишком много, таблицы разрастаются. Тогда база тратит ресурсы впустую.

Диагностика:

			sql
SELECT ... n_dead_tup ...

		

Очистка:

			sql
VACUUM ANALYZE table;
VACUUM FULL table; -- блокирует
pg_repack         -- без простоя

		

Шаг 8. Настройте и проверьте autovacuum

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

Проверьте эффективность процессов:

			sql
SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

		

Настройки должны соответствовать нагрузке и объёму данных.

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

Игорь Федосеев, главный IT-инженер, тимлид ETL-команды

Если n_dead_tup растёт, а autovacuum_count нет — это сигнал будущих проблем с производительностью и ростом bloat. Также важно следить за WAL: резкий рост записи может указывать на массовые обновления, проблемы с репликацией или приближающееся заполнение диска.

Владимир Васильев,руководитель внедрения ИИ и решений продвинутой аналитики, ментор по ИИ, руководитель и лектор первой в России онлайн-магистратуры по анализу данных

Шаг 9. Обновите статистику и настройте планировщик

PostgreSQL формирует план выполнения запроса на основе статистики о распределении данных в таблицах. Если статистика устарела, оптимизатор может выбрать неэффективный план.

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

Учитывайте важные параметры:

• random_page_cost для SSD ≈ 1.1
• effective_cache_size ≈ 50–75% RAM

Шаг 10. Контролируйте соединения

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

Текущее состояние соединений можно просмотреть с помощью:

			sql
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
		

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

Шаг 11. Проанализируйте логи

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

Рекомендуемые параметры в postgresql.conf:

			ini
log_min_duration_statement = 100-200 ms
log_lock_waits = on
log_temp_files = 0
		

Шаг 12. Оптимизируйте конфигурацию

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

Измените ключевые параметры конфигурации:

• shared_buffers ~25% RAM
• effective_cache_size ~50–75% RAM
• work_mem 4–16 MB per operation
• checkpoint_timeout 15–30 min
• random_page_cost = 1.1 для SSD

Многие думают, что 16 мегабайт — это глобальный лимит на соединение. На самом деле это лимит на операцию. Один запрос может использовать work_mem много раз.

Владимир Васильев,руководитель внедрения ИИ и решений продвинутой аналитики, ментор по ИИ, руководитель и лектор первой в России онлайн-магистратуры по анализу данных

Качество и целостность данных: типовые проблемы и решения

Качество данных — основа устойчивой IT-системы. Проблемы с ними часто приводят к сбоям и некорректной работе приложений. В PostgreSQL есть встроенные механизмы, которые помогают поддерживать надёжность информации.

Какие ошибки встречаются чаще всего:

• дубли, 
• неверные типы, 
• конфликтующие или устаревшие значения, 
• частичные и повреждённые записи.

Как поддерживать целостность данных:

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

Контроль качества данных включает также стратегию восстановления. Даже идеально настроенная валидация не спасает от сбоя диска или человеческого фактора. Резервные копии и репликации должны стать частью общей стратегии.

На прикладном уровне используйте SAVEPOINT, чтобы аккуратно откатывать операции внутри транзакции. На уровне инфраструктуры работает та же логика:

• проверенные бэкапы возвращают к нормальному состоянию,

• репликация сохраняет доступность, если падает основной сервер.

SAVEPOINT помогает в рамках одной транзакции, бэкапы и репликация — в рамках всей системы.

Как проверять входные данные

Задавайте допустимые форматы и диапазоны. Указывайте, какие значения вы считаете корректными. Пример:

• дата рождения не может быть позже текущего дня;
• возраст — только числа ≥ 18;
• формат email должен содержать @ и домен.

Проверяйте данные на двух уровнях. Чтобы пользователь увидел ошибку сразу, смотрите на клиенте — например, в форме сбора контактов.

Чтобы защититься, если приложение пропустило проблему — на сервере.

Используйте ограничения в базе. SQL-ограничения — это тот самый встроенный механизм надёжности:

• NOT NULL — поле не может быть пустым;
• UNIQUE — значение должно быть уникальным;
• CHECK — значение должно соответствовать условию;
• FOREIGN KEY — связь с другой таблицей;
• триггеры — дополнительная логика проверки.

Давайте понятные сообщения об ошибках. Пользователь должен понимать, что он сделал не так. Например, вместо Error 400 — invalid data напишите: «Возраст должен быть от 18 лет. Введено: 15».

Актуализируйте правила проверки. Если вчера минимальная сумма заявки была 5 тысяч, а сегодня 10 — проверка должна обновиться.

Отдайте контроль машине. Используйте автоматизированные проверки:

• поиск дублей;
• контроль пустых полей;
• сверка с бизнес-правилами;
• регулярный скрипт на аномалии.

Документируйте и обучайте. Опишите, какие данные нужны, в каком виде и почему. Покажите команде и пользователям. Чем понятнее правила, тем меньше ошибок.

Идеальный сценарий: регулярные тестируемые бэкапы + репликация с мониторингом лагов.

Чек-лист диагностики за 15 минут

1. pg_stat_activity — долгие транзакции, ожидания
2. pg_locks — есть ли блокировки
3. pg_stat_statements — тяжёлые запросы
4. iostat, htop, free — ресурсы железа
5. Проверка автовакуума
6. Проверка WAL и места на диске
7. Анализ логов

Ещё 4 неочевидные причины деградации производительности

Что делать, если диагностика по стандарту не помогла? Владимир Васильев рассказал нам, где ещё можно найти источник проблем:

• «Тихое» повреждение данных на диске. Дисковая подсистема не сообщала об ошибках: контроллер RAID с кэшем-бутербродом, где батарея отжила своё. Некоторые страницы данных на диске были битые. Postgres при чтении таких страниц не падал, а уходил в бесконечные ожидания I/O, которые выглядели как резкий скачок нагрузки на диск. Вылечили включением zero_damaged_pages на свой страх и риск, с последующим дампом/рестором. Потом, конечно, заменили железо.
• Конфликт расширений. Была история с pg_stat_statements и auto_explain, которые при определённой настройке (совместная загрузка и высокая частота сэмплирования) начинали сильно тормозить из-за конкуренции за локи в общей памяти. Симптом — высокий wait_event типа LWLock для простых запросов.
• Проблема на уровне файловой системы. Очень старый, но запоминающийся случай: файловая система была заполнена почти под завязку, больше 95%. Это привело к драматическому падению производительности, так как механизмам VACUUM и CREATE INDEX не хватало места для создания временных файлов. Симптомы были самые странные, пока не посмотрели на df -h.
• Субтильный баг в версии PostgreSQL. Однажды апгрейд на минорную версию в рамках одного major привёл к деградации конкретного типа запросов с JOIN. Оказалось, баг в планировщике — он неправильно оценивал стоимость при определённом условии. Помог откат или установка enable_<type>scan = off в качестве временного костыля.

Выводы

Основные источники проблем:

• долгие транзакции и блокировки
• неэффективные планы выполнения
• недостаточная память и I/O-узкие места
• неверные настройки work_mem, autovacuum, max_connections
• игнорирование метрик и логов

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

Краткий чек-лист диагностики:

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

Читайте также

• Основы PostgreSQL для начинающих: от установки до первых запросов
• PostgreSQL vs. ClickHouse vs. Duck DB: какую опенсорс базу выбрать для аналитики в 2025 году?
• Вредные советы по работе с базами данных, или как расстроить DBA
• SQL-оптимизация: 5 запросов, которые ломают базу
• PostgreSQL: что нужно знать о счётчике транзакций