В прошлой статье я рассказывал, как мы перевозили терабайт банковской базы с Oracle на PostgreSQL без даунтайма. Там был раздел про производительность, и в комментариях несколько человек спросили примерно одно и то же: «а что конкретно вы дебажили и как?».

Это продолжение. Три инцидента из двух разных проектов — банковской системы после миграции и enterprise-платформы на Java/Spring, где PostgreSQL жил под Hibernate. Разные домены, разные команды, но сюжет один: запрос, который вчера работал, сегодня не работает, а EXPLAIN показывает, что всё хорошо.

Если вы пришли в PostgreSQL из Oracle или из мира, где база — это «то, куда ORM пишет», три четверти статьи будут про вещи, о существовании которых вы не подозревали. Я тоже не подозревал.

Дисклеймер: проекты под NDA, названия, объёмы и часть деталей изменены. Порядок величин и сами инциденты — настоящие.

Инцидент 1. Утром база стала в два раза медленнее, ничего не меняли

Симптом. Понедельник, 9:20. Дашборд отвечает 4–6 секунд вместо привычных 300 мс. Карточка кредита открывается секунду вместо 200 мс. Релиза не было, данные не выросли скачком, нагрузка обычная. К 11 утра становится ещё хуже, к обеду — стабильно плохо.

Что показывали метрики. CPU в норме, диск в норме, количество соединений в норме. Единственное, что выбивалось — blks_read вырос примерно втрое при том же количестве запросов. База читала в три раза больше страниц, чтобы вернуть те же строки.

Это ключевой признак. Если запросов столько же, план тот же, а страниц читается больше — данные стали занимать больше места. Не «данных стало больше», а именно «те же данные стали занимать больше места».

Диагностика. Первый же запрос всё объяснил:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM   pg_stat_user_tables
WHERE  n_dead_tup > 100000
ORDER  BY n_dead_tup DESC;

На таблице графиков платежей — 62% мёртвых версий строк. last_autovacuum — позавчера.

Причина. Ночной пересчёт процентов и резервов под просрочку. Это UPDATE на несколько миллионов строк, который в Oracle был просто «сколько-то undo», а в PostgreSQL — это несколько миллионов новых версий строк. Старые версии остаются в файле до вакуума.

Дальше цепочка: таблица физически распухла вдвое → индексы тоже распухли, потому что каждая новая версия строки требует новой записи в каждом индексе → seq scan и index scan читают в разы больше страниц → shared_buffers перестаёт вмещать горячие данные → всё едет на диск.

А автовакуум не успевал, потому что дефолтный autovacuum_vacuum_scale_factor = 0.2 означает «запускайся, когда мёртвых версий накопится 20% таблицы». На таблице в 80 миллионов строк это 16 миллионов мёртвых версий, и к моменту запуска вакуум работает часами, конкурируя с дневной нагрузкой.

Что сделали.

Сначала потушили пожар. VACUUM FULL брать нельзя — он берёт ACCESS EXCLUSIVE и кладёт таблицу целиком, а система работает. Взяли pg_repack, который переливает таблицу в новую и переключается под короткой блокировкой. Размер таблицы вернулся с 340 ГБ до 180 ГБ, latency — к прежним значениям.

Потом починили причину, по пунктам:

Батчи вместо одной транзакции. Пересчёт делал один гигантский UPDATE в одной транзакции на несколько часов. Разбили на батчи по 20 тысяч строк с коммитом после каждого. Это не только снизило пиковый bloat, но и решило вторую проблему, о которой ниже.

fillfactor = 80 на таблицах с частым UPDATE. По умолчанию PostgreSQL заполняет страницу на 100%, и новой версии строки негде разместиться на той же странице. Если место есть и обновляемая колонка не входит ни в один индекс — работает HOT-update: новая версия ложится рядом, индексы не трогаются вообще. Разница на нашем пересчёте — примерно двукратная по объёму записи в WAL.

ALTER TABLE payment_schedule SET (fillfactor = 80);
-- применится к новым страницам; для существующих нужен pg_repack/VACUUM FULL

Агрессивный автовакуум на горячих таблицах. Не глобально, а точечно:

ALTER TABLE payment_schedule SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_cost_delay   = 0,
  autovacuum_analyze_scale_factor = 0.01
);

Плюс подняли autovacuum_max_workers и autovacuum_vacuum_cost_limit — на SSD дефолтный троттлинг вакуума слишком щадящий, вакуум просто не успевает.

Отдельно — расчёты, которые меняют большую часть таблицы. Для двух самых тяжёлых пересчётов UPDATE не подходил в принципе: он трогал 70% строк. Переписали на «посчитать в новую таблицу → построить индексы → ALTER TABLE ... RENAME». Дороже по месту, но bloat нулевой, а переключение — миллисекунды под короткой блокировкой.

Инцидент 2. Запрос висит, план идеальный

Симптом. Вечерний деплой. Через минуту после старта миграции приложение начинает отдавать таймауты на всём, что касается одной таблицы. Не медленно — вообще не отвечает. При этом ручной EXPLAIN того же запроса в psql показывает честные 15 мс.

Первая мысль была неправильной. Мы полезли смотреть планы, статистику, индексы. Потратили минут двадцать. Правильный вопрос был не «почему запрос медленный», а «а он вообще выполняется?».

Не выполнялся. Он стоял в очереди за блокировкой.

Диагностика. Запрос, который стоит держать в закладках:

SELECT a.pid,
       a.state,
       now() - a.xact_start        AS xact_age,
       a.wait_event_type,
       a.wait_event,
       pg_blocking_pids(a.pid)     AS blocked_by,
       left(a.query, 120)          AS query
FROM   pg_stat_activity a
WHERE  a.backend_type = 'client backend'
  AND  (cardinality(pg_blocking_pids(a.pid)) > 0
        OR a.wait_event_type = 'Lock')
ORDER  BY xact_age DESC;

Картина была такая:

 pid  | state               | xact_age | blocked_by | query
------+---------------------+----------+------------+---------------------------
 8123 | idle in transaction | 00:41:12 | {}         | SELECT ... FROM loans ...
 9007 | active              | 00:03:44 | {8123}     | ALTER TABLE loans ADD ...
 9011 | active              | 00:03:41 | {9007}     | SELECT ... FROM loans ...
 9012 | active              | 00:03:40 | {9007}     | SELECT ... FROM loans ...
 ...  |                     |          |            | (ещё ~180 таких)

Читается снизу вверх. Сто восемьдесят обычных SELECT ждут ALTER TABLE. ALTER TABLE ждёт соединение 8123, которое сорок минут висит в состоянии idle in transaction — то есть открыло транзакцию, что-то прочитало и уснуло, не закоммитив.

Почему это кладёт всё. Вот что многие узнают только на проде: очередь блокировок в PostgreSQL честная и упорядоченная. ALTER TABLE запрашивает ACCESS EXCLUSIVE — блокировку, несовместимую вообще ни с чем, даже с SELECT. Пока он ждёт, все запросы, пришедшие после него, встают за ним, хотя друг с другом они прекрасно совместимы.

То есть одно зависшее соединение блокирует ALTER TABLE, а ALTER TABLE блокирует всю таблицу для всех. Причём сам ALTER в нашем случае был безобидный — ADD COLUMN с константным дефолтом, который начиная с PG 11 не переписывает таблицу и отрабатывает за миллисекунды. Ему нужны были эти миллисекунды, а он их не получил, и утянул за собой весь прод.

Откуда взялось idle in transaction. Java-сервис, Spring, @Transactional на методе, внутри которого — вызов внешнего HTTP API с дефолтным таймаутом. Внешний сервис затупил, метод завис, транзакция осталась открытой. Классика: транзакция БД, растянутая на сетевой вызов.

Что сделали.

lock_timeout во всех миграциях. Это дисциплина, а не настройка:

SET lock_timeout = '3s';
ALTER TABLE loans ADD COLUMN risk_grade text;

Теперь миграция, которая не может взять блокировку за три секунды, падает сама и не собирает за собой очередь. Упавшую миграцию перезапускаем — с ретраями и экспоненциальной паузой. Лучше пять неудачных попыток, чем один прод-инцидент.

idle_in_transaction_session_timeout. Глобально выставили в 5 минут, для батчевых ролей — больше. Соединение, забывшее закоммитить, теперь убивается само.

ALTER ROLE app_backend SET idle_in_transaction_session_timeout = '5min';

Убрали HTTP-вызовы из транзакций. Прошли по коду и вынесли все внешние вызовы за границу @Transactional. Где это невозможно архитектурно — перешли на паттерн outbox: пишем событие в таблицу в той же транзакции, отправляем отдельным воркером.

CREATE INDEX CONCURRENTLY для всех индексов на больших таблицах. Обычный CREATE INDEX держит SHARE и блокирует запись на всё время построения — на таблице в 80 миллионов строк это десятки минут.

Алерт на длинные транзакции. Не на «медленные запросы», а именно на xact_start старше минуты и на state = 'idle in transaction' старше 30 секунд. Это оказалось самой полезной метрикой из всех, что мы добавили за проект.

Инцидент 3. Дедлоки в очереди на таблице

Симптом. Enterprise-платформа, домен управления инцидентами. Несколько инстансов сервиса разбирают задачи из таблицы-очереди и обновляют связанные сущности. В логах — deadlock detected, десятки раз в час. Задачи не теряются (ретрай отрабатывает), но латентность плавает, а логи заполнены ошибками, среди которых не видно настоящих.

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

ERROR:  deadlock detected
DETAIL: Process 21534 waits for ShareLock on transaction 998877; blocked by process 21601.
        Process 21601 waits for ShareLock on transaction 998861; blocked by process 21534.
        Process 21534: UPDATE incident SET status = $1 WHERE id = $2
        Process 21601: UPDATE incident SET status = $1 WHERE id = $2
HINT:   See server log for query details.
CONTEXT: while updating tuple (48213,7) in relation "incident"

Два процесса, один и тот же UPDATE, разные строки. Это почти всегда означает одно: обновление нескольких строк в разном порядке.

Причина. Обработчик брал пачку инцидентов и обновлял их в порядке, в котором они пришли из SELECT. А порядок без ORDER BY — не гарантирован: он зависит от плана, от того, что лежит в кэше, от параллельности. Инстанс A взял строки в порядке (10, 25), инстанс B — (25, 10). A залочил 10 и ждёт 25, B залочил 25 и ждёт 10. PostgreSQL через deadlock_timeout (по умолчанию секунда) обнаруживает цикл и убивает одну из транзакций.

Вторая половина проблемы — сама выборка задач. Она выглядела так:

SELECT id FROM incident_queue WHERE status = 'NEW' LIMIT 100 FOR UPDATE;

Без SKIP LOCKED инстансы выбирают одни и те же строки и выстраиваются в очередь друг за другом. Параллелизма нет, зато есть конкуренция за блокировки.

Что сделали.

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

SELECT id
FROM   incident
WHERE  id = ANY($1)
ORDER  BY id           -- порядок обязателен
FOR    UPDATE;

SKIP LOCKED в очереди. Одна строчка, которая превратила конкуренцию в параллелизм:

SELECT id
FROM   incident_queue
WHERE  status = 'NEW'
ORDER  BY created_at
LIMIT  100
FOR    UPDATE SKIP LOCKED;

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

Advisory locks там, где сущность нельзя обрабатывать параллельно. Для операций, где нужна строгая эксклюзивность на уровне бизнес-сущности, а не строки:

SELECT pg_try_advisory_xact_lock(hashtext('incident:' || $1));

Возвращает false вместо ожидания — сервис просто откладывает задачу и берёт следующую.

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

Мониторинг, который надо было настроить до, а не после

Все три инцидента объединяет то, что мы узнавали о них от пользователей, а не от алертов. Итоговый набор:

Длинные транзакции и idle in transaction.

SELECT count(*) FROM pg_stat_activity
WHERE state = 'idle in transaction' AND now() - state_change > interval '30 seconds';

SELECT max(extract(epoch FROM now() - xact_start)) FROM pg_stat_activity
WHERE state <> 'idle';

Ожидание блокировок.

SELECT count(*) FROM pg_stat_activity WHERE wait_event_type = 'Lock';

Алерт при значении больше нуля дольше 15 секунд. Именно эта метрика показала бы второй инцидент в первую минуту.

Мёртвые версии строк. n_dead_tup и процент по горячим таблицам, плюс время последнего автовакуума.

Горизонт xmin. Это тонкий момент: вакуум не может почистить версии строк новее, чем самая старая работающая транзакция. Одно старое соединение — и вакуум работает, отчитывается об успехе, но не удаляет ничего.

SELECT pid, state, age(backend_xmin) AS xmin_age, now() - xact_start AS xact_age
FROM   pg_stat_activity
WHERE  backend_xmin IS NOT NULL
ORDER  BY age(backend_xmin) DESC
LIMIT  10;

Здесь же стоит смотреть неактивные слоты репликации — они держат и WAL, и горизонт вакуума:

SELECT slot_name, active, age(catalog_xmin) AS catalog_xmin_age,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) AS retained_wal
FROM   pg_replication_slots;

Размер таблиц и индексов в динамике. Не абсолютное значение, а производная. Таблица, выросшая на 40% за ночь без пропорционального роста строк — это bloat, и это видно на графике раньше, чем в latency.

Дедлоки. deadlocks из pg_stat_database, алерт на рост счётчика. И log_lock_waits = on с deadlock_timeout = 1s — тогда в лог попадают не только дедлоки, но и просто долгие ожидания блокировок, с указанием, кто кого ждёт.

Чеклист

Что я бы выдал команде, переходящей на PostgreSQL с чего угодно:

1. UPDATE создаёт новую версию строки. Массовые обновления — это bloat, а не «просто запись».

2. Батчи с коммитами вместо одной длинной транзакции. Всегда.

3. fillfactor 80–90 на таблицах с частым UPDATE; следите, чтобы обновляемые колонки не входили в индексы — иначе HOT не работает.

4. Автовакуум настраивается на конкретных таблицах, а не глобально. Дефолт в 20% рассчитан на маленькие таблицы.

5. VACUUM FULL — не инструмент для прода. pg_repack.

6. lock_timeout в каждой миграции. Без исключений.

7. idle_in_transaction_session_timeout на уровне роли приложения.

8. Никаких сетевых вызовов внутри транзакции БД.

9. CREATE INDEX CONCURRENTLY на всём, что больше миллиона строк.

10. Обновляете несколько строк — обновляйте в порядке первичного ключа.

11. Очередь на таблице — только с FOR UPDATE SKIP LOCKED.

12. Одно зависшее соединение способно уронить прод. Алерт на длинные транзакции окупается в первый же месяц.

Первое, что я бы поставил в новом проекте из всего списка — алерт на wait_event_type = 'Lock'. Он стоит десять минут работы и ловит целый класс инцидентов, которые иначе выглядят как «база тормозит, но непонятно почему».