Есть одно распространённое заблуждение, которое мешает многим разработчикам, работающим с PostgreSQL: достаточно настроить VACUUM или просто запускать VACUUM, и база данных будет оставаться в хорошем состоянии. Мёртвые кортежи будут удаляться. Идентификаторы транзакций — переиспользоваться, а пространство — освобождаться. И база данных будет счастливо жить дальше.
Но здесь есть пара неприятных моментов, о которых многие не знают. Первый из них заключается в том, что VACUUM вводит вас в заблуждение, когда дело касается индексов.
Анатомия хранения данных
Когда вы удаляете строку в PostgreSQL, она всего лишь помечается как «мёртвый кортеж». Для новых транзакций она уже невидима, но физически всё ещё остаётся на месте. И только когда завершатся все транзакции, которые на неё ссылаются, VACUUM сможет действительно её удалить, освободив место в куче — то есть в табличном хранилище.
Чтобы понять, почему для таблиц и индексов это работает по-разному, нужно представить, как PostgreSQL на самом деле хранит данные.
Данные таблицы живут в куче — наборе страниц по 8 КБ, где строки размещаются в любом месте, куда помещаются. Встроенного порядка там нет. Когда вы выполняете INSERT, PostgreSQL находит страницу с достаточным количеством свободного места и помещает строку туда. Удаляете строку — появляется пустота. Вставляете новую — она может занять это место, а может и не занять: вполне возможно, что она поместится совсем в другом месте.
Именно поэтому запрос SELECT * FROM users без ORDER BY сначала может возвращать строки в одном порядке, а после нескольких обновлений — в, казалось бы, случайном, и со временем этот порядок может снова меняться. Куча похожа на тетрис. Строки падают туда, где есть свободное место, а после удаления оставляют зазоры.
Когда запускается VACUUM, он удаляет эти мёртвые кортежи и уплотняет оставшиеся строки внутри каждой страницы. Если страница целиком становится пустой, PostgreSQL может полностью вернуть её в оборот.
И хотя индексы на первый взгляд представляют собой тот же самый набор страниц по 8 КБ, устроены они иначе. Индекс B-дерева должен поддерживать отсортированный порядок — в этом и состоит смысл его существования, и именно поэтому запрос вида WHERE id = 12345 работает так быстро, хотя при некоторых условиях даже индексы могут игнорироваться. PostgreSQL может выполнить двоичный поиск по дереву вместо того, чтобы просматривать все возможные строки. Подробнее об основах B-деревьев и о том, за счёт чего они быстры, можно почитать отдельно.
Но если именно устройство индексов делает их быстрыми, то в этом же заключается и их главное ограничение. PostgreSQL может размещать строки там, где есть свободное место, но не может просто взять и сдвинуть записи на страницах индекса так, чтобы заполнить их максимально плотно.
VACUUM может удалять мёртвые записи из индекса. Но он не перестраивает B-дерево. Когда VACUUM обрабатывает кучу, он может уплотнять строки внутри страницы и возвращать в оборот пустые страницы. У кучи нет требования сохранять порядок — строки могут лежать где угодно. А страницы B-дерева? Они жёстко привязаны к своей структуре. Да, VACUUM может удалить из них мёртвые записи.
Многие разработчики считают, что VACUUM одинаково работает со всеми страницами — неважно, страницами кучи или индексными страницами. Он ведь должен удалять мёртвые записи, верно?
Да. Но вот чего он не делает: он не перестраивает B-дерево.
Что VACUUM действительно делает
Удаляет указатели на мёртвые кортежи из страниц индекса
Помечает полностью пустые страницы как пригодные для повторного использования
Обновляет карту свободного пространства
Чего VACUUM делать не умеет:
Объединять разреженные страницы друг с другом (может работать только с полностью пустыми страницами)
Уменьшать глубину дерева
Освобождать пустые, но всё ещё связанные со структурой страницы
Менять физическую структуру B-дерева
Если куча — это тетрис, то в ней пустоты могут снова заполняться. А B-дерево — это отсортированная книжная полка. VACUUM может убрать книги, но не может сдвинуть оставшиеся, чтобы убрать пустые места. И при каждом проходе вам приходится идти мимо этих пустых полок.
Эксперимент
Давайте перейдём к практике: создадим таблицу, заполним её, удалим большую часть данных и посмотрим, что произойдёт.
CREATE EXTENSION IF NOT EXISTS pgstattuple; CREATE TABLE demo (id integer PRIMARY KEY, data text); -- вставляем 100 000 строк INSERT INTO demo (id, data) SELECT g, 'Row number ' || g || ' with some extra data' FROM generate_series(1, 100000) g; ANALYZE demo;
На этом этапе наш индекс в хорошем состоянии. Зафиксируем исходную точку:
SELECT relname, pg_size_pretty(pg_relation_size(oid)) as file_size, pg_size_pretty((pgstattuple(oid)).tuple_len) as actual_data FROM pg_class WHERE relname IN ('demo', 'demo_pkey');
relname | file_size | actual_data -----------+-----------+------------- demo | 7472 kB | 6434 kB demo_pkey | 2208 kB | 1563 kB
Теперь удалим часть данных — если точнее, 80%, причём где-то из середины:
DELETE FROM demo WHERE id BETWEEN 10001 AND 90000;
Цель — смоделировать типичный сценарий из реальной жизни: политику хранения данных, массовую очистку или последствия неудачной миграции данных.
VACUUM demo; SELECT relname, pg_size_pretty(pg_relation_size(oid)) as file_size, pg_size_pretty((pgstattuple(oid)).tuple_len) as actual_data FROM pg_class WHERE relname IN ('demo', 'demo_pkey');
relname | file_size | actual_data -----------+-----------+------------- demo | 7472 kB | 1278 kB demo_pkey | 2208 kB | 313 kB
Размер таблицы заметно сократился, а индекс остался прежним. Теперь у вас 20 000 строк, которые индексируются структурой, изначально рассчитанной на 100 000.
Но обратите внимание на важный момент: хотя объём actual_data уменьшился примерно до 1,3 МБ, что соответствует оставшимся 20 000 строк, значение file_size для индекса по-прежнему равно 2208 kB. VACUUM очистил данные о мёртвых кортежах, но не возвращает место операционной системе и не уплотняет структуру индекса. Он лишь помечает страницы как пригодные для повторного использования внутри PostgreSQL.
Примечание автора: в исходной версии статьи размер таблицы был указан без выполнения VACUUM. Я упустил это в процессе написания статьи, который временами бывает довольно утомительным. Большое спасибо моим читателям за то, что заметили.
Этот эксперимент, конечно, довольно-таки экстремальный, но он хорошо демонстрирует саму проблему.
Понимание состояний страниц
Листовые страницы имеют несколько состояний:
Полная страница (>80% плотности) — страница содержит много записей индекса и эффективно использует пространство. При чтении каждой страницы размером 8 КБ возвращается значительный объём полезных данных. Это оптимальное состояние.
Частично заполненная страница (40–80% плотности) — есть некоторое количество пустого места, но эффективность всё ещё приемлемая. Обычно встречается на границах дерева или после умеренных изменений данных. Поводов для беспокойства нет.
Разреженная страница (<40% плотности) — в основном пустая. Вы читаете страницу в 8 КБ ради нескольких записей. Стоимость ввода-вывода (I/O) такая же, как у полной страницы, но полезной информации вы получаете значительно меньше.
Пустая страница (0% плотности) — не содержит ни одной «живой» записи, но при этом остаётся частью структуры дерева. Чистые накладные расходы. При диапазонном сканировании вы можете прочитать такую страницу и не получить вообще ничего полезного.
О параметре fillfactor
Вы можете задаться вопросом, может ли здесь помочь fillfactor. Это параметр, который применяется как к куче, так и к листовым страницам и определяет, насколько плотно PostgreSQL заполняет страницы при записи данных. Значение по умолчанию для индексов B-дерева — 90%. Это означает, что 10% пространства на каждой листовой странице остаётся свободным для будущих вставок.
CREATE INDEX demo_index ON demo(id) WITH (fillfactor = 70);
Меньшее значение fillfactor (например, 70%) оставляет больше свободного места, что может уменьшить количество разделений страниц, когда вы вставляете данные в середину индекса. Это полезно для таблиц со случайными вставками по индексируемому столбцу или при частых обновлениях значений индексных колонок.
Но если вы внимательно читали раздел про устройство хранения, становится ясно: это не решает проблему раздувания индексов. Более того, может её усугубить. Если вы задаёте низкий fillfactor, а затем удаляете большую часть строк, вы изначально получаете больше страниц и повышаете вероятность того, что вместо частично заполненных страниц у вас окажется больше разреженных.
Fillfactor для листовых страниц — это инструмент оптимизации под обновления и вставки. Это не решение проблемы раздувания, вызванного удалениями или обновлениями индексных колонок.
Почему оптимизатор ошибается
Оптимизатор запросов PostgreSQL оценивает стоимость выполнения, опираясь на физические статистики, включая количество страниц в индексе.
EXPLAIN ANALYZE SELECT * FROM demo WHERE id BETWEEN 10001 AND 90000;
QUERY PLAN -------------------------------------------------------------------------------------------------------------------- Index Scan using demo_pkey on demo (cost=0.29..29.29 rows=200 width=41) (actual time=0.111..0.112 rows=0 loops=1) Index Cond: ((id >= 10001) AND (id <= 90000)) Planning Time: 1.701 ms Execution Time: 0.240 ms (4 rows)
Хотя выполнение почти мгновенное, важно заглянуть «под капот». Оптимизатор ожидал 200 строк, а получил ноль. Он прошёл по структуре B-дерева в поисках данных, которых там нет. Для одного запроса с прогретым кэшем это не проблема. Но под боевой нагрузкой, когда выполняются тысячи запросов и страницы не находятся в кэше, вы снова и снова платите за ввод-вывод впустую.
Если копнуть глубже, обнаруживается куда более серьёзная проблема.
SELECT relname, reltuples::bigint as row_estimate, relpages as page_estimate FROM pg_class WHERE relname IN ('demo', 'demo_pkey');
relname | row_estimate | page_estimate -----------+--------------+--------------- demo | 20000 | 934 demo_pkey | 20000 | 276
Значение relpages вычисляется как физический размер файла, делённый на размер страницы (8 КБ). PostgreSQL обновляет его во время VACUUM и ANALYZE, но оно отражает фактический размер файла на диске, а не объём полезных данных внутри. Наш индекс по-прежнему занимает 2,2 МБ (276 страниц по 8 КБ), хотя большая часть этих страниц пустая.
Оптимизатор видит 276 страниц на 20 000 строк и делает вывод, что на страницу приходится очень мало строк. В такой ситуации он может решить: «индекс слишком разреженный — лучше выполнить последовательное сканирование». И это уже проблема.
«Но подождите, — скажете вы, — разве ANALYZE не исправляет статистику?»
И да, и нет. ANALYZE обновляет оценку количества строк — он больше не считает, что у вас 100 000 строк, а понимает, что их 20 000. Но он не уменьшает значение relpages, потому что оно отражает физический размер файла на диске. А ANALYZE не может это изменить.
Теперь у оптимизатора есть точная оценка числа строк, но совершенно неточная оценка числа страниц. Полезные данные фактически укладываются всего примерно в 57 страниц, но оптимизатор этого не знает.
cost = random_page_cost × pages + cpu_index_tuple_cost × tuples (видно в выводе EXPLAIN)
При раздувшемся индексе:
количество страниц завышено (276 вместо примерно 57)
стоимость на страницу умножается в том числе на пустые страницы
итоговая расчётная стоимость искусственно завышается
Полый индекс
Проблему с индексом можно рассмотреть ещё глубже, если заглянуть во внутреннюю статистику:
SELECT * FROM pgstatindex('demo_pkey');
-[ RECORD 1 ]------+-------- version | 4 tree_level | 1 index_size | 2260992 root_block_no | 3 internal_pages | 1 leaf_pages | 57 empty_pages | 0 deleted_pages | 217 avg_leaf_density | 86.37 leaf_fragmentation | 0
Стоп, что? avg_leaf_density равна 86%, и на первый взгляд всё выглядит совершенно здоровым. В этом и ловушка. Из-за «полого» индекса — мы ведь удалили 80% данных прямо из середины — у нас осталось 57 хорошо заполненных листовых страниц, но при этом сам индекс всё ещё содержит 217 удалённых страниц.
Именно поэтому одного показателя avg_leaf_density недостаточно: он может ввести в заблуждение. Плотность используемых страниц выглядит отлично, но 79% файла индекса — это мёртвый груз.
Самый простой способ заметить раздувание индекса — сравнить его фактический размер с ожидаемым.
SELECT c.relname as index_name, pg_size_pretty(pg_relation_size(c.oid)) as actual_size, pg_size_pretty((c.reltuples * 40)::bigint) as expected_size, round((pg_relation_size(c.oid) / nullif(c.reltuples * 40, 0))::numeric, 1) as bloat_ratio FROM pg_class c JOIN pg_index i ON c.oid = i.indexrelid WHERE c.relkind = 'i' AND c.reltuples > 0 AND c.relname NOT LIKE 'pg_%' AND pg_relation_size(c.oid) > 1024 * 1024 -- only indexes > 1 MB ORDER BY bloat_ratio DESC NULLS LAST;
index_name | actual_size | expected_size | bloat_ratio ------------+-------------+---------------+------------- demo_pkey | 2208 kB | 781 kB | 2.8
Значение bloat_ratio, равное 2,8, означает, что индекс почти в три раза больше ожидаемого размера. Всё, что выше 1,8–2,0, уже заслуживает внимания.
Мы отфильтровали только индексы больше 1 МБ, потому что раздувание у маленьких индексов обычно не так важно. Подстройте этот порог под свою среду: для крупных баз данных, например, вас могут интересовать только индексы больше 100 МБ.
Но здесь важно сделать БОЛЬШОЕ предупреждение: функция pgstatindex(), которую мы использовали выше, физически читает весь индекс целиком. Для индекса размером 10 ГБ это будет 10 ГБ ввода-вывода (I/O). Не запускайте её для всех индексов на боевом сервере, если не очень хорошо понимаете, что делаете.
REINDEX
Как же на практике решить проблему раздувания индекса? Прямолинейный вариант — REINDEX, потому что эта команда пересобирает индекс с нуля.
REINDEX INDEX CONCURRENTLY demo_pkey ;
После этого можно снова проверить состояние индекса:
SELECT * FROM pgstatindex('demo_pkey');
-[ RECORD 1 ]------+------- version | 4 tree_level | 1 index_size | 466944 root_block_no | 3 internal_pages | 1 leaf_pages | 55 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 89.5 leaf_fragmentation | 0
И ещё раз посмотрим на размеры:
SELECT relname, pg_size_pretty(pg_relation_size(oid)) as file_size, pg_size_pretty((pgstattuple(oid)).tuple_len) as actual_data FROM pg_class WHERE relname IN ('demo', 'demo_pkey');
relname | file_size | actual_data -----------+-----------+------------- demo | 7472 kB | 1278 kB demo_pkey | 456 kB | 313 kB
Наш индекс уменьшился с 2,2 МБ до 456 КБ — сокращение на 79%, хотя после предыдущих шагов это уже не выглядит неожиданностью.
Как вы, возможно, заметили, мы использовали CONCURRENTLY, чтобы избежать блокировки с исключительным доступом. Этот режим доступен начиная с PostgreSQL 12+. Формально его можно не указывать, но по сути единственная веская причина так поступить — плановые технические работы, когда нужно ускорить перестроение индекса.
pg_squeeze
Если посмотреть выше на file_size наших отношений, то мы смогли вернуть дисковое пространство для затронутого индекса — всё-таки мы выполняли REINDEX, — но пространство таблицы обратно операционной системе не вернулось.
Именно здесь и проявляет себя pg_squeeze. В отличие от альтернатив, основанных на триггерах, pg_squeeze использует логическое декодирование, а значит, оказывает меньшее влияние на работающую систему. Он в онлайн-режиме перестраивает и таблицу, и все её индексы, с минимальными блокировками:
CREATE EXTENSION pg_squeeze; SELECT squeeze.squeeze_table('public', 'demo');
Исключительная блокировка требуется только на финальной стадии подмены, и её длительность можно настроить. Более того, pg_squeeze рассчитан на регулярную автоматизированную обработку: можно зарегистрировать таблицы и позволить инструменту самостоятельно выполнять обслуживание, когда уровень раздувания достигает заданных порогов.
pg_squeeze имеет смысл использовать, когда раздулись и таблица, и индексы, либо когда вам нужно автоматизированное управление. REINDEX CONCURRENTLY проще в случаях, когда вмешательство требуется только индексам.
Есть и более старый инструмент — pg_repack. Для более подробного сравнения средств борьбы с раздуванием см. статью The Bloat Busters: pg_repack vs pg_squeeze.
VACUUM FULL — ядерный вариант
VACUUM FULL переписывает таблицу целиком вместе со всеми индексами. Да, он действительно решает проблему полностью, но есть серьёзное «но»: для этого требуется блокировка с исключительным доступом (ACCESS EXCLUSIVE lock), которая на всё время выполнения полностью блокирует и чтение, и запись. Для большой таблицы это может означать часы простоя.
В целом в продакшене этого лучше избегать. Вместо него разумнее использовать pg_squeeze: результат тот же, но без такого простоя.
Когда пора действовать, а когда расслабиться
Прежде чем бросаться выполнять REINDEX на всём подряд, стоит понять, в каких случаях раздувание индекса действительно имеет значение.
B-деревья расширяются и сжимаются вместе с вашими данными. При случайных вставках в индексируемые столбцы — UUID, хеш-ключи и так далее — разделение страниц происходит постоянно. Эффективность индекса временами может проседать, а затем естественным образом стабилизироваться где-то в диапазоне 70–80% в зависимости от циклов работы вашей системы. Это не раздувание. Это дерево находит свою естественную форму под ваши данные.
Раздувание, которое мы показали в примере — 57 полезных страниц, утопающих в 217 удалённых, — это крайний случай. Он возник из-за удаления 80% непрерывного диапазона данных. В обычной повседневной работе вы с таким, скорее всего, не столкнётесь.
Когда нужно действовать сразу:
после массового DELETE: например, из-за политики хранения данных, очистки по GDPR или неудачной очистки после миграции
если
bloat_ratioпревысил 2,0 и продолжает растиесли планы запросов внезапно начинают предпочитать последовательное сканирование по столбцам, для которых есть индекс
если размер индекса явно несоразмерен числу строк
Но в большинстве случаев паниковать не нужно. Достаточно еженедельно следить за ситуацией. Если коэффициент раздувания индексов стабильно растёт и выходит за предупреждающие пороги, запланируйте REINDEX CONCURRENTLY на период низкой нагрузки.
Раздувание индекса не выглядит аварией ровно до того момента, пока ею не становится. Знайте тревожные признаки, держите под рукой нужные инструменты и не позволяйте молчаливому VACUUM вводить вас в заблуждение, будто всё в порядке.
Заключение
VACUUM критически важен для PostgreSQL. Запускайте его. Дайте autovacuum делать свою работу. Но важно понимать его ограничения: он очищает мёртвые кортежи, а не структуру индекса.
Реальность обслуживания PostgreSQL такова: с раздуванием кучи VACUUM справляется вполне неплохо, а вот раздувание индексов требует явного вмешательства. Нужно понимать, когда с индексами действительно что-то не так, а когда они просто естественно «дышат» вместе с данными, и в какой момент пора запускать REINDEX.
VACUUM справляется с раздуванием кучи. Раздувание индексов — уже ваша задача. Важно видеть разницу.
Если вы сталкивались с ситуацией, когда «всё вроде обслуживается, а база всё равно деградирует», значит, упираетесь уже не в команды, а в понимание внутренних механизмов PostgreSQL. На продвинутом уровне это работа с индексами, статистикой, отказоустойчивыми кластерами и реальным поведением системы под нагрузкой. Именно такие практические задачи разбираются на курсе «Администрирование PostgreSQL. Экспертный уровень».
Готовы к серьезному обучению? Пройдите вступительный тест и узнаете, есть ли пробелы в знаниях по PostgreSQL и подойдет ли вам программа курса.
А чтобы узнать больше о формате обучения и задать вопросы преподавателям, приходите на бесплатный демо-урок 26 марта в 20:00. Поговорим о том, как PostgreSQL превращает текст запроса в план выполнения и результат, и почему решения планировщика напрямую влияют на производительность. Записаться на урок.
