PostgreSQL тормозит: 30 реальных причин и как их устранить

Запросы уходят на 5 секунд, CPU простаивает, а веб-сервер возвращает 504 - знакомо? В 90% случаев виноват не сам PostgreSQL, а то, как его используют и настраивают. Разберём 30 причин деградации производительности и покажем, как каждую из них найти и исправить.

Как диагностировать медленный PostgreSQL

Прежде чем оптимизировать, нужно понять, что именно тормозит. PostgreSQL даёт три базовых инструмента:

• EXPLAIN ANALYZE - план выполнения конкретного запроса с реальным временем.
• pg_stat_statements - агрегированная статистика по всем запросам.
• pg_stat_activity - текущие сессии, состояние, ожидания.

Без этих данных любая «оптимизация» - гадание на кофейной гуще.

EXPLAIN ANALYZE: читаем план за 5 минут

Запускайте команду так:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, name FROM users WHERE email = 'user@example.com';

Что ищем в выводе:

Сигнал	Что значит	Что делать
Seq Scan на большой таблице	Нет индекса или он не применяется	Добавить индекс, проверить статистику
rows=1 в плане, rows=50000 по факту	Устаревшая статистика	ANALYZE <таблица>
Nested Loop с тысячами loops	N+1 или нет индекса по FK	Переписать запрос, добавить индекс
Hash Batches > 1	work_mem мал для хеш-соединения	Увеличить work_mem
Время планирования > времени выполнения	Сложный запрос с большим числом JOIN	Упростить или использовать CTE

Параметр BUFFERS показывает, сколько страниц взято из кэша (shared hit) и считано с диска (read). Большое значение read - сигнал нехватки shared_buffers.

pg_stat_statements и log_min_duration_statement

Включите расширение один раз:

-- В postgresql.conf:
shared_preload_libraries = 'pg_stat_statements'

-- После рестарта в нужной БД:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Топ-10 самых дорогих запросов:

SELECT query,
       calls,
       round(total_exec_time::numeric, 2) AS total_ms,
       round((total_exec_time / calls)::numeric, 2) AS avg_ms,
       rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Авторская ремарка. Лично я всегда сначала смотрю на avg_ms * calls - это реальная «боль» конкретного запроса. Запрос за 100 мс, вызванный 100 000 раз в сутки, опаснее разового запроса за 10 секунд.

Параметр log_min_duration_statement = 1000 (в мс) запишет в лог всё, что выполняется дольше секунды - без расширений, прямо в системный лог PostgreSQL.

Причины 1–10: запросы и схема данных

Большинство проблем с производительностью живут здесь - и здесь же самые быстрые победы.

Причина 1. Нет индекса на колонку в WHERE

PostgreSQL читает всю таблицу (Seq Scan), если нет индекса по условию фильтра. На таблице в 100 000 строк cost Seq Scan ≈ 1750, cost Index Scan ≈ 8 - разница в 200 раз.

-- Проверить: нет ли Seq Scan на большой таблице
EXPLAIN SELECT * FROM orders WHERE status = 'pending';

-- Решение
CREATE INDEX CONCURRENTLY idx_orders_status ON orders(status);

CONCURRENTLY строит индекс без блокировки таблицы - используйте на проде.

Причина 2. Индекс есть, но не используется

PostgreSQL игнорирует индекс, если:

• кардинальность колонки низкая (например, status с двумя значениями на 95% строк);
• запрос применяет функцию к индексированной колонке: WHERE LOWER(email) = ...;
• статистика устарела и планировщик ошибается в оценке selectivity.

Решение - создать индекс по выражению:

CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Причина 3. SELECT * тянет лишние данные

SELECT * читает все колонки, включая TOAST-поля (JSON, TEXT, bytea). Это перегружает I/O и shared_buffers. Запрашивайте только нужные поля.

Причина 4. N+1 запросов из ORM

ORM загружает список объектов, затем для каждого делает отдельный SELECT. Вместо одного запроса - тысячи. Признак в pg_stat_statements: один шаблон запроса с calls в десятки тысяч.

Решение: JOIN или prefetch_related (Django), eager loading (другие ORM). Лучше - написать SQL вручную.

Причина 5. Широкие таблицы (100+ колонок)

Одна страница PostgreSQL - 8 КБ. Чем шире строка, тем меньше строк в странице, тем больше страниц читает каждый запрос. Таблицу с 300 колонками разделите на основную (частые поля) и ext (редкие), связав через первичный ключ.

Причина 6. Устаревшая статистика

Планировщик опирается на статистику из pg_statistic. После массовой загрузки данных или смены распределения значений она устаревает. Результат - неверный план, Seq Scan вместо Index Scan.

-- Обновить статистику по таблице
ANALYZE orders;

-- Проверить дату последнего анализа
SELECT relname, last_analyze, n_live_tup
FROM pg_stat_user_tables
ORDER BY last_analyze NULLS FIRST;

Причина 7. Нет частичного или покрывающего индекса

Частичный индекс покрывает только нужное подмножество строк:

-- Индексируем только активные заказы
CREATE INDEX idx_orders_active ON orders(created_at)
WHERE status = 'active';

Покрывающий индекс (INCLUDE) позволяет обойтись Index Only Scan - без обращения к heap:

CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name);

Причина 8. CTE как fence для оптимизатора

До PostgreSQL 12 каждый CTE (WITH ... AS (...)) выполнялся независимо как «optimization fence» - планировщик не мог перенести предикаты внутрь. Начиная с PG 12, CTE по умолчанию инлайнятся. Если у вас старая версия или явный MATERIALIZED - проверьте, не изолирует ли CTE оптимизацию.

Причина 9. Неоптимальный тип JOIN

PostgreSQL выбирает между Hash Join, Merge Join и Nested Loop. На маленьких выборках Nested Loop быстр; на больших - Hash Join. Если планировщик ошибается, причина обычно в устаревшей статистике или нехватке work_mem для хеш-таблицы.

Причина 10. Нет индекса по внешнему ключу

PostgreSQL не создаёт индекс по FK автоматически. При JOIN по FK без индекса на дочерней таблице - Seq Scan. Найти незаиндексированные FK:

SELECT c.conname, c.conrelid::regclass, a.attname
FROM pg_constraint c
JOIN pg_attribute a ON a.attrelid = c.conrelid
    AND a.attnum = ANY(c.conkey)
WHERE c.contype = 'f'
    AND NOT EXISTS (
        SELECT 1 FROM pg_index i
        WHERE i.indrelid = c.conrelid
        AND a.attnum = ANY(i.indkey)
    );

Причины 11–20: autovacuum и bloat

Bloat (раздутие) - один из самых коварных источников деградации. Он не виден без специальных запросов, но стабильно замедляет всё: и SELECT, и INSERT.

Причины 11–12. Как работает MVCC и почему растёт bloat

PostgreSQL использует MVCC: при UPDATE старая версия строки не удаляется физически, а помечается как «мёртвая» (dead tuple). Живут мёртвые кортежи до тех пор, пока VACUUM их не уберёт. Накапливаются они быстро при интенсивных UPDATE/DELETE.

Оценить раздутие таблиц можно через расширение pgstattuple или просто через pg_stat_user_tables:

SELECT schemaname, tablename,
       n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric / nullif(n_live_tup,0) * 100, 1) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY dead_ratio DESC NULLS LAST
LIMIT 10;

Если dead_ratio стабильно выше 10–20% - autovacuum не справляется.

Причина 13. Autovacuum настроен с дефолтами «для бедных»

По умолчанию autovacuum_vacuum_scale_factor = 0.2: autovacuum запускается, когда мёртвых строк стало 20% от таблицы. Для таблицы в 100 млн строк это 20 млн мёртвых кортежей до первого вакуума.

Решение - снизить порог для активных таблиц:

ALTER TABLE orders
SET (autovacuum_vacuum_scale_factor = 0.01,
     autovacuum_analyze_scale_factor = 0.005);

Причина 14. autovacuum_vacuum_cost_delay слишком высок

autovacuum_vacuum_cost_delay = 20ms (дефолт до PG 13) означает паузу 20 мс между обработкой каждой «порции» страниц. Autovacuum работает медленно, чтобы не мешать запросам, но на высоконагруженной таблице это превращается в хроническое отставание.

В PostgreSQL 13+ дефолт снижен до 2 мс. Если у вас PG 12 и ниже - понизьте вручную.

Причина 15. Bloat индексов

Индексы раздуваются так же, как таблицы. Обычный VACUUM убирает мёртвые строки из heap, но не перестраивает индексы. Для индексов нужен REINDEX CONCURRENTLY или pg_repack.

-- Перестроить индекс без блокировки (PG 12+)
REINDEX INDEX CONCURRENTLY idx_orders_status;

Причина 16. Transaction ID wraparound (исчерпание XID)

PostgreSQL использует 32-битный счётчик транзакций. Когда он приближается к пределу (~2 млрд), СУБД переходит в режим только-чтение, чтобы не потерять данные. Именно для борьбы с XID wraparound autovacuum запускает «аварийный» anti-wraparound vacuum - он агрессивен и ломает производительность.

Следите за счётчиком:

SELECT datname,
       age(datfrozenxid) AS xid_age,
       2000000000 - age(datfrozenxid) AS xid_remaining
FROM pg_database
ORDER BY xid_age DESC;

Если xid_age > 1,5 млрд - пора срочно разобраться с autovacuum.

Авторская ремарка. Я видел продакшн, где аварийный anti-wraparound vacuum выключал базу в буквальном смысле. Это предотвратимо: мониторинг xid_age должен быть в каждом алёрте.

Причина 17. Нет fillfactor - HOT update не работает

fillfactor задаёт, сколько страницы занимают данные при вставке. По умолчанию 100% - свободного места нет. Из-за этого UPDATE не может сделать HOT (Heap Only Tuple) update на той же странице и создаёт новую версию строки в другом месте, что ведёт к фрагментации.

-- Оставить 20% страницы свободной для HOT update
ALTER TABLE orders SET (fillfactor = 80);

Причина 18. VACUUM FULL на проде без планирования

VACUUM FULL перестраивает таблицу целиком и держит ExclusiveLock на всё время. На таблице в сотни ГБ - это часы простоя. Альтернатива - pg_repack, который делает то же самое в фоне.

Причины 19–20. Bloat из-за долгих транзакций и репликации

Долгоживущая транзакция не даёт VACUUM убрать строки, которые она «видит». Даже одна незакрытая транзакция на 10 минут может заблокировать autovacuum на всей БД.

Аналогично - отставание слота репликации (replication slot lag) не даёт VACUUM продвигаться вперёд по WAL.

-- Найти долгие транзакции
SELECT pid, now() - xact_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle'
    AND xact_start IS NOT NULL
ORDER BY duration DESC;

Причины 21–27: конфигурация сервера

Дефолтные настройки PostgreSQL рассчитаны на сервер с 256 МБ RAM. Их надо менять под каждое конкретное железо.

Причина 21. shared_buffers слишком мал

shared_buffers - буфер страниц в RAM. Дефолт - 128 МБ. Типичная рекомендация: 25% RAM сервера. На сервере с 32 ГБ RAM ставьте 8 ГБ. Если в EXPLAIN BUFFERS много read (страниц с диска) и мало hit (из кэша) - shared_buffers мал.

Причина 22. work_mem слишком мал - сортировки уходят на диск

work_mem - память на одну операцию сортировки или хеша. Дефолт - 4 МБ. При нехватке PostgreSQL пишет временные файлы на диск. Признак в EXPLAIN: Sort Method: external merge Disk.

Поднимайте осторожно: при 100 соединениях и 10 операциях сортировки каждое потребление = 100 × 10 × work_mem. Лучше ставить per-session для тяжёлых запросов:

SET work_mem = '256MB';
SELECT ...;

Причина 23. effective_cache_size занижен

effective_cache_size - подсказка планировщику, сколько данных поместится в кэш ОС + PostgreSQL. Не выделяет реальную память. Если занижен, планировщик думает, что данных в кэше мало, и предпочитает Seq Scan индексным. Типичное значение: 75% RAM.

Причина 24. random_page_cost не скорректирован под SSD

Дефолт random_page_cost = 4.0 рассчитан на HDD. На SSD случайное чтение стоит почти столько же, сколько последовательное. Установите random_page_cost = 1.1 - и планировщик начнёт охотнее выбирать Index Scan.

-- В postgresql.conf
random_page_cost = 1.1  -- для SSD
seq_page_cost = 1.0

Причина 25. Частые checkpoint грузят I/O

Checkpoint сбрасывает грязные страницы из буфера на диск. Слишком частые checkpoint штурмуют I/O. checkpoint_completion_target = 0.9 позволяет растянуть запись на 90% интервала между checkpoint - I/O распределяется равномерно.

Причина 26. wal_buffers по умолчанию

Дефолт wal_buffers = -1 (автоматически 1/32 от shared_buffers). При высоком темпе записи - поднимите до 64 МБ вручную.

Причина 27. Параллельные запросы отключены

PostgreSQL 10+ умеет выполнять SELECT параллельно. Если max_parallel_workers_per_gather = 0 - параллелизм выключен. Включите и настройте:

max_parallel_workers_per_gather = 4
max_parallel_workers = 8

Проверьте в EXPLAIN: должны появиться узлы Parallel Seq Scan или Parallel Hash Join.

Причины 28–30: соединения и блокировки

Причина 28. Слишком много соединений без пула

Каждое соединение в PostgreSQL - отдельный процесс с ~5–10 МБ RSS. При max_connections = 200 и work_mem = 64 МБ пиковое потребление RAM - более 10 ГБ только на соединения. PgBouncer в режиме transaction pooling держит 10–30 реальных соединений при тысячах клиентских запросов.

# pgbouncer.ini
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Причина 29. Долгие блокировки и deadlock

Длинные транзакции удерживают row-level и table-level локи. Найти:

SELECT pid, wait_event_type, wait_event, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE wait_event_type = 'Lock'
ORDER BY duration DESC;

Для предотвращения deadlock - соблюдать единый порядок доступа к таблицам, ставить lock_timeout = '5s' на критичные транзакции.

Причина 30. PostgreSQL на том же сервере, что приложение

PostgreSQL конкурирует за CPU, RAM и I/O с веб-сервером, Redis, Celery и агентами мониторинга. Вынос СУБД на отдельный сервер или переход на DBaaS - самая простая «оптимизация» без единой строки SQL.

Альтернативный взгляд

Некоторые DBA считают, что главная причина тормозов - не конфиг и не bloat, а плохая архитектура приложения: разработчики проектируют схему «как получилось», не думая о характере запросов. С этой точки зрения оптимизация PostgreSQL - лечение симптомов, а не болезни. Лечить нужно схему: денормализация для OLAP, партиционирование для time-series, ClickHouse для аналитики - всё это убирает нагрузку с PostgreSQL эффективнее любого тюнинга конфига.

Нетривиальный факт

PostgreSQL не обновляет строку на месте - он создаёт новую физическую копию и помечает старую как мёртвую. Это означает, что даже «простой» UPDATE генерирует нагрузку на вакуум. Именно поэтому на таблицах с частыми обновлениями fillfactor = 80 и грамотный autovacuum дают ощутимый прирост производительности без единого изменения SQL.

FAQ

Как быстро найти самые медленные запросы в PostgreSQL?

Включите pg_stat_statements и выполните SELECT query, calls, round(total_exec_time/calls,2) AS avg_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 10; Сортировка по total_exec_time покажет запросы, которые суммарно отнимают больше всего времени за период.

Почему PostgreSQL делает Seq Scan вместо Index Scan?

Пять причин: нет индекса; статистика устарела (ANALYZE); кардинальность колонки низкая (планировщик считает дешевле читать всю таблицу); функция над индексированным полем ломает индекс; effective_cache_size или random_page_cost настроены неверно.

Что такое bloat и как его измерить?

Bloat - накопление мёртвых кортежей из-за MVCC. Измерьте через pg_stat_user_tables: колонка n_dead_tup / n_live_tup. Более 10–20% на активной таблице - сигнал к проверке autovacuum.

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

Снизьте пороги на уровне таблицы: ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.005); Также уменьшите autovacuum_vacuum_cost_delay до 2–5 мс на PG 12 и ниже.

Как сделать VACUUM FULL без простоя?

Используйте pg_repack: перестраивает таблицу и индексы в фоне без ExclusiveLock. Установка: CREATE EXTENSION pg_repack;, затем pg_repack -d mydb -t orders.

Какие параметры postgresql.conf критичны для производительности?

shared_buffers (25% RAM), work_mem (осторожно - умножается на число соединений × операций), effective_cache_size (75% RAM), random_page_cost = 1.1 для SSD, checkpoint_completion_target = 0.9.

Нужен ли PgBouncer при 50+ соединениях?

Да. Каждое соединение - отдельный процесс 5–10 МБ RAM. PgBouncer в режиме transaction держит 10–20 реальных соединений к PostgreSQL при тысячах клиентских - снижает overhead и конкуренцию за ресурсы.