Долгие транзакции мешают VACUUM

Макс только открыл мониторинг и сделал глоток кофе, как вдруг у него зазвонил телефон.

— Привет, это Сергей из поддержки BI. У нас, с базой что-то не так — инсёрты тормозят.

— Поподробнее. — Макс всё ещё не переключился с текущего графика в Zabbix.

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

— Минуту. — Макс подключился к нужной базе, заглянул в pg_stat_activity, в мониторинг шины.

— Диски, память, проц в порядке, это мы посмотрели. Блокировок тоже нет. — Между тем сообщил Сергей.

Макс открыл pg_stat_user_tables, отсортировал по n_dead_tup (количество “мертвых” строк) и с удивлением посмотрел на верхние записи с значениями в несколько десятков миллионов:

— Ого. А вот и причина.

— Что там? — Оживленно спросил Сергей.

— Да у вас там мильёны мертвых строк. Автовакуум вроде работает, но видимо не справляется. Погоди еще минуту.

Макс запустил вручную VACUUM (VERBOSE, ANALYZE). Ответ из базы был математически точен и бесполезен:

DETAIL: 36000000 dead row versions cannot be removed yet,
oldest xmin: 1411878869

— Ну классика же. Кто-то открыл транзакцию и пошёл домой, — тихо сказал Макс, чувствуя, как кофе становится ещё горче.

Он вывел список активных транзакций:

SELECT
  pid,
  now() - xact_start AS duration,
  state,
  wait_event_type,
  wait_event,
  usename,
  client_addr,
  application_name,
  query
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
  AND pid <> pg_backend_pid()
ORDER BY duration DESC;

Нашёл сразу несколько сессий idle in transaction, висящих уже третий день. Причём все от одной службы. Именно такие сессии легко пропустить, если искать только активно выполняющиеся запросы.

— Почему-то разработчики любят обсуждать утечки памяти, но зато BEGIN без COMMIT их обычно не очень-то волнует! — в сердцах возмутился Макс. Согласовав остановку проблемных сессий, он перезапустил службу и отметил, что как раз три дня назад она была обновлена.

После этого повторный VACUUM отработал как по маслу за несколько секунд.

— Ты что-то сделал? — Радостно сказал Сергей. — У нас все полетело как на ракете.

Макс рассказал ему причины, допил остывший кофе и с философским спокойствием заметил:

— Так и живём: одни пишут BEGIN, другие ищут, кто забыл COMMIT, а база терпит, пока может. Передай разработчикам, что транзакции нужно закрывать вовремя. И настройте мониторинг возраста транзакций, состояния idle in transaction, роста мёртвых версий и отставания реплики. Для страховки можно обсудить подходящий idle_in_transaction_session_timeout, но таймаут не заменяет исправление приложения.