Почему база данных тормозит со временем: VACUUM и статистика

Если запросы к базе с каждым месяцем выполняются всё медленнее, а количество данных и нагрузка растут не так уж сильно — дело часто не в железе и не в индексах, а в накопившемся «мусоре» внутри таблиц и устаревшей статистике, по которой СУБД строит планы запросов. Регулярная внутренняя уборка базы данных — такая же обязательная процедура, как бэкапы, просто менее заметная.

Почему база данных вообще может «зарастать» изнутри?

Когда вы удаляете строку или обновляете значение в таблице, многие СУБД физически не стирают старые данные сразу. Вместо этого они помечают старую версию строки как неактуальную и создают новую. Это сделано ради скорости и одновременной работы множества пользователей: пока один процесс читает старую версию строки, другой уже может писать новую, и они не мешают друг другу.

Такой подход называется многоверсионным управлением параллелизмом (MVCC, multiversion concurrency control) и лежит в основе PostgreSQL и ряда других промышленных СУБД. Это широко известный и хорошо задокументированный механизм, благодаря которому читатели никогда не блокируют писателей.

Что происходит с «неактуальными» версиями строк?

Они остаются лежать внутри файлов таблицы, занимая место на диске и в кэше, пока их не уберёт специальный фоновый процесс. Пока уборки не было, таблица физически «раздувается»: реальных данных в ней может быть, скажем, миллион строк, а на диске лежит объём, как будто их три миллиона. Это явление называют bloat — раздутие таблицы или индекса.

Раздутая таблица — это не только лишнее место на диске. Она замедляет буквально всё: сканирование таблицы читает больше страниц, чем нужно, индексы становятся толще и медленнее, а операции резервного копирования занимают больше времени. Со стороны это выглядит как необъяснимое торможение базы данных при, казалось бы, стабильной нагрузке.

Что такое VACUUM и зачем он нужен?

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

В большинстве современных СУБД эта уборка выполняется автоматически фоновым процессом — его часто называют autovacuum. Он периодически проверяет таблицы и запускает очистку там, где накопилось много «мусора». Проблема в том, что настройки автовакуума по умолчанию рассчитаны на средние сценарии и не всегда справляются с таблицами, в которые данные пишутся и удаляются очень интенсивно.

Что будет, если автовакуум отстаёт или отключён?

Если поток изменений в таблицу больше, чем скорость уборки, «мусор» накапливается быстрее, чем убирается. Со временем это приводит к нескольким неприятным эффектам:

• таблицы и индексы разрастаются, запросы читают больше данных, чем нужно, и выполняются медленнее;
• диск заполняется файлами, которые формально содержат меньше полезных данных, чем занимают места;
• в редких, но серьёзных случаях СУБД может вынужденно перейти в защитный режим, чтобы избежать переполнения внутренних счётчиков версий строк, и заблокировать запись, пока не будет выполнена срочная очистка.

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

При чём тут статистика и почему устаревшая статистика тоже тормозит запросы?

Когда СУБД получает запрос, она не выполняет его «в лоб», а сначала строит план: решает, использовать ли индекс или прочитать всю таблицу целиком, в каком порядке соединять несколько таблиц, сколько строк примерно вернёт каждый этап. Для этого планировщику нужна статистика — приблизительное представление о том, сколько строк в таблице, как распределены значения в столбцах, какие значения встречаются часто, а какие редко.

Эта статистика собирается не постоянно, а периодически — командой ANALYZE, которую в большинстве систем также запускает автовакуум. Если данные в таблице сильно изменились, а статистика не обновилась, планировщик начинает ошибаться: например, решает читать всю таблицу целиком там, где было бы гораздо быстрее использовать индекс, потому что «думает», что подходящих строк много, хотя на самом деле их мало. Внешне это выглядит как внезапное и необъяснимое замедление конкретного запроса, который раньше работал быстро.

Как понять, что базе пора «прибраться»?

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

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

Что можно сделать на практике?

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

Если разбираться со всем этим самостоятельно нет времени или экспертизы, эту рутину берёт на себя управляемая СУБД в облаке — например, база данных в «Клауд АйСи» обслуживается провайдером, включая настройку автообслуживания и мониторинг состояния, так что бизнесу не приходится вручную следить за раздутием таблиц и актуальностью статистики.

Значит, ускорение базы — это не только про индексы?

Именно так. Индексы и оптимизация запросов решают, как быстро найти нужные данные, а регулярное обслуживание — VACUUM и обновление статистики — отвечает за то, чтобы сама «территория», по которой ищет СУБД, оставалась компактной и чтобы планировщик знал о ней правду. Без этой невидимой, но регулярной работы даже идеально спроектированная схема с продуманными индексами со временем начинает буксовать.