Со временем таблицы и индексы в postgresql начинают занимать на диске больше места, чем реально нужно для хранимых данных. Это явление называют раздуванием, или bloat. Из-за него запросы читают лишние страницы, индексы перестают помещаться в кеш, а место на диске тает без видимой причины. В этом уроке разберем, откуда берется bloat, как его измерить через pgstattuple, и как привести таблицы и индексы в порядок без остановки рабочей базы - с помощью REINDEX CONCURRENTLY и CREATE INDEX CONCURRENTLY.
Как это работает
Главная причина bloat - механика версионирования строк (MVCC). При UPDATE или DELETE postgresql не стирает старую версию строки сразу. Он помечает ее как устаревшую, но физически она остается в файле, пока на нее ссылается хоть одна активная транзакция. Очистку мертвых версий выполняет vacuum: он освобождает место внутри страниц, чтобы его переиспользовали новые строки. Но vacuum, как правило, не возвращает место операционной системе - таблица просто перестает расти, имея внутри пустоты.
Если таблицу интенсивно обновляют, а autovacuum не успевает или настроен слабо, пустот накапливается много. Файл таблицы становится разреженным: полезных данных мало, а страниц много. То же самое с индексами. B-tree индекс хранит ссылки на версии строк, и при частых обновлениях его страницы заполняются неравномерно, остаются полупустыми после удалений и расщеплений. Раздутый индекс - частая причина того, что один и тот же запрос со временем замедляется, хотя данных будто бы столько же.
Лечение делится на два уровня. Легкий случай - обычный VACUUM, который держит bloat в узде, переиспользуя дыры. Тяжелый случай, когда дыр накопилось критически много, требует физической перезаписи. Для таблиц это VACUUM FULL или pg_repack, для индексов - REINDEX. И VACUUM FULL, и обычный REINDEX берут тяжелую блокировку и останавливают работу с объектом. Поэтому для боевой базы, которая не может позволить себе простой, есть варианты с CONCURRENTLY: они строят новую копию объекта рядом, а блокировку берут лишь на доли секунды в самом конце.
SQL и примеры
Сначала измерим раздувание. Размер объекта смотрим встроенными функциями, а точную долю мусора - расширением pgstattuple.
Код: Выделить всё
CREATE EXTENSION IF NOT EXISTS pgstattuple;
-- Размер таблицы перелетов с индексами и без
SELECT pg_size_pretty(pg_total_relation_size('bookings.flights')) AS total,
pg_size_pretty(pg_relation_size('bookings.flights')) AS heap_only;
-- Доля мертвого пространства в таблице
SELECT table_len, tuple_count, dead_tuple_count,
round(dead_tuple_percent::numeric, 2) AS dead_pct,
round(free_percent::numeric, 2) AS free_pct
FROM pgstattuple('bookings.ticket_flights');
Для индексов есть более легкая функция pgstatindex: она не читает индекс целиком, а оценивает заполненность листовых страниц.
Код: Выделить всё
-- Оценим раздувание индекса по номеру билета
SELECT round(avg_leaf_density::numeric, 1) AS leaf_density_pct,
leaf_pages, leaf_fragmentation
FROM pgstatindex('bookings.tickets_pkey');
Теперь сама перестройка. Базовый вариант блокирует таблицу на запись (а REINDEX TABLE - и на чтение нужного индекса):
Код: Выделить всё
REINDEX INDEX bookings.tickets_pkey; -- один индекс
REINDEX TABLE bookings.bookings; -- все индексы таблицы
Код: Выделить всё
REINDEX INDEX CONCURRENTLY bookings.tickets_pkey;
REINDEX TABLE CONCURRENTLY bookings.ticket_flights;
Код: Выделить всё
CREATE INDEX CONCURRENTLY idx_tf_fare
ON bookings.ticket_flights (fare_conditions);
Код: Выделить всё
SELECT schemaname, relname AS table, indexrelname AS index,
idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
Частые грабли
- VACUUM FULL и обычный REINDEX берут ACCESS EXCLUSIVE LOCK - это полная остановка работы с таблицей. На боевой базе их запускают только в окно обслуживания.
- CREATE INDEX CONCURRENTLY и REINDEX CONCURRENTLY нельзя выполнять внутри блока транзакции (BEGIN...COMMIT) - команда упадет с ошибкой.
- Если CONCURRENTLY прервался, остается невалидный индекс (поле indisvalid = false). Запросы его не используют, но место он занимает. Найдите такой через pg_index и удалите вручную: DROP INDEX CONCURRENTLY.
- REINDEX CONCURRENTLY временно требует места под две копии индекса. На забитом под завязку диске он не отработает.
- Долгая транзакция или зависший replication slot держат xmin и не дают vacuum чистить мертвые строки. Сначала разберитесь с долгими транзакциями, иначе bloat вернется сразу после REINDEX.
- pgstattuple на огромной таблице читает ее целиком и создает нагрузку. Для индексов берите более легкий pgstatindex, а для приблизительной оценки - pgstattuple_approx.
- Перестройка индекса не лечит раздувание самой таблицы (heap). Для heap нужен VACUUM FULL или pg_repack, REINDEX тут не поможет.
Цель - своими глазами увидеть bloat и убрать его без блокировки.
- Создайте тестовую таблицу: CREATE TABLE t AS SELECT g AS id, md5(g::text) AS v FROM generate_series(1, 500000) g; и индекс CREATE INDEX t_id_idx ON t(id);
- Посмотрите исходный размер: SELECT pg_size_pretty(pg_total_relation_size('t')); и плотность индекса через pgstatindex('t_id_idx').
- Сгенерируйте раздувание: UPDATE t SET v = md5(random()::text); выполните этот UPDATE 3-4 раза подряд.
- Снова замерьте размер и pgstatindex - плотность листьев должна заметно упасть, а размер вырасти.
- Перестройте индекс без блокировки: REINDEX INDEX CONCURRENTLY t_id_idx; и сравните плотность - она вернется к 90 процентам.
- Запустите VACUUM (VERBOSE) t; и прочитайте, сколько мертвых строк было удалено.
- Для сравнения вычистите heap через VACUUM FULL t; и посмотрите, как упал общий размер таблицы.
- Почему обычный VACUUM обычно не уменьшает размер файла таблицы на диске?
- Чем REINDEX CONCURRENTLY отличается от REINDEX по характеру блокировок?
- Какое расширение и какая функция дадут точную долю мертвого пространства в таблице?
- Что произойдет, если выполнение CREATE INDEX CONCURRENTLY прервется на середине, и как это исправить?
- Почему долгая открытая транзакция мешает борьбе с bloat?
- В каком случае REINDEX бесполезен и нужен VACUUM FULL или pg_repack?
