Раздутая таблица PostgreSQL на VPS: оценка диска и блокировок перед pg_repack

Вы удалили из таблицы событий всё, что старше квартала. Запрос вернул миллионы удалённых строк, приложение вздохнуло с облегчением, а команда df -h показывает те же цифры, что и до чистки. Диск на VPS по-прежнему заполнен на три четверти. Логика подсказывает, что данных стало меньше, значит, и места должно стать больше. PostgreSQL устроен иначе, и разрыв между «данных меньше» и «файл меньше» как раз определяет, нужна ли вам перепаковка.

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

Две величины, которые легко перепутать

Первая величина — логический объём: сколько живых строк в таблице и сколько места они занимали бы, будь они упакованы плотно. Вторая — физический размер: сколько байт лежит в файлах таблицы, её TOAST-хранилища и индексов. Читатель, пришедший с запросом про раздутую таблицу, обычно видит только вторую и подозревает, что она непропорциональна первой.

Причина в том, как PostgreSQL обращается с изменениями. Когда вы обновляете или удаляете строку, старая версия не стирается мгновенно: она остаётся в странице данных как мёртвая, пока ни одной транзакции она больше не может понадобиться. Дальше работает очистка (VACUUM, в том числе автоматический). Она помечает место под мёртвыми версиями как пригодное для повторного использования. Но «пригодное для повторного использования внутри файла» и «возвращённое операционной системе» — разные вещи. Обычный VACUUM, как правило, возвращает место системе лишь тогда, когда пустые страницы образуют непрерывный хвост файла. Если живые строки разбросаны по всему файлу, а дыры между ними, файл остаётся прежнего размера.

Отсюда два типичных сценария, которые внешне выглядят одинаково.

• Стационарная таблица с постоянными обновлениями. Файл вырос до некоторого размера и дальше почти не меняется: освобождённые дыры заполняются новыми версиями строк. Часть файла — «рабочий запас», и это нормально. Перепаковка здесь сократит файл, а через какое-то время он, возможно, вырастет обратно.
• Одноразовое массовое удаление или переписывание. Было сто миллионов строк, осталось двадцать. Дыры огромны, новые данные заполнят их нескоро или никогда. Файл останется раздутым, потому что нет процесса, который его уплотнит.

Обычная очистка остаётся обязательной гигиеной, но она не заменяет перепаковку во втором случае: она не умеет сдвигать живые строки к началу файла и отдавать диску освободившееся пространство в произвольном месте. Это не повод отказываться от автоочистки, а повод понимать, что именно она делает.

Как увидеть разрыв своими глазами

Начните с простых размеров. Стандартные функции PostgreSQL различают, что именно входит в число:

SELECT pg_size_pretty(pg_relation_size('public.events')) AS heap_only, pg_size_pretty(pg_table_size('public.events')) AS table_with_toast, pg_size_pretty(pg_indexes_size('public.events')) AS indexes, pg_size_pretty(pg_total_relation_size('public.events')) AS total;

Для планирования перепаковки важна последняя колонка: итоговый размер таблицы вместе с индексами и TOAST. Именно эту сумму документация pg_repack берёт за основу при оценке дополнительного места, и именно её нужно сравнивать со свободным пространством тома, где лежит каталог данных.

Затем посмотрите на соотношение живых и мёртвых строк:

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_vacuum FROM pg_stat_user_tables WHERE relname = 'events';

Эти счётчики приблизительные: они оценочные и обновляются статистикой, а не точным пересчётом. Но они показывают тенденцию: если мёртвых строк немного, а файл огромен, значит, раздутие накопилось исторически и дыры уже «схлопнуты» очисткой. Если мёртвых строк много, сначала надо понять, почему автоочистка не успевает или что ей мешает (например, долгая транзакция, удерживающая горизонт видимости).

Для более точной картины используют расширение pgstattuple: оно сканирует таблицу и показывает долю свободного места и мёртвых кортежей. Сканирование читает данные целиком, так что на большой таблице и загруженном диске VPS запускайте его в спокойное время или на копии. Если вы не хотите ставить расширение в рабочую базу, вот грубая прикидка: возьмите число живых строк, умножьте на средний размер строки из pg_stats (колонки avg_width суммарно плюс заголовок кортежа), и сравните с размером файла. Это оценка порядка величины, а не измерение, но она отличает «раздуто вдвое» от «раздуто на пять процентов».

Не забывайте про индексы. Индекс на таблице с массовыми удалениями тоже раздувается, причём иногда сильнее самой таблицы: страницы B-дерева освобождаются хуже, чем кажется. Поэтому смотрите на них отдельно, а не только на итог. Бывает, что 80% проблемы сидит в одном индексе, и тогда разговор о перепаковке всей таблицы заменяется более дешёвым разговором о пересборке одного индекса.

Сколько места нужно на самом деле

Теперь главный вопрос из начала. Официальная документация pg_repack говорит, что полная перепаковка требует свободного места примерно вдвое больше размера таблицы и её индексов. Принцип понятен без подробностей реализации: пока старая таблица продолжает обслуживать запросы, рядом строится новая, плотно упакованная копия вместе с индексами. Старая исчезает только в конце. В какой-то момент на диске лежат обе.

Возьмём условный пример, чтобы почувствовать масштаб. Допустим, таблица с TOAST занимает 9 ГБ, а её индексы — 3 ГБ, итого 12 ГБ. Правило «примерно вдвое» означает запас порядка 24 ГБ. Пусть диск VPS — 80 ГБ, занято 62 ГБ, свободно 18 ГБ. Выходит, места не хватает, хотя интуитивно кажется: «таблица всего 12 ГБ, а свободно 18». Именно на этом споткнётся тот, кто прикинул нужный запас по размеру самой таблицы, а не по формуле из документации.

Дополню картину тем, что документация в этом пункте не расписывает, а вывод следует из устройства PostgreSQL. Во-первых, запас — это не только файлы новой таблицы. Всё, что вы пишете в базу, пишется ещё и в журнал предзаписи (WAL), а при включённой архивации или репликации сегменты задерживаются, пока их не заберут. Массовая перепаковка генерирует много WAL; если каталог WAL лежит на том же томе, запас нужен и под него. Во-вторых, если приложение продолжает писать во время перепаковки, эти изменения тоже нужно где-то накапливать до переноса. Чем больше рабочая запись, тем выше риск, что «примерно вдвое» окажется впритык. Поэтому разумно закладывать не ровно удвоенный размер, а заметный запас сверх него. Сколько именно — зависит от вашей нагрузки; универсальной цифры здесь нет, и придумывать её было бы нечестно.

Что делать, если места не хватает? Есть несколько путей, и они по-разному влияют на систему.

1. Освободить место в другом месте тома. Не удалением чего попало, а осознанно: старые логи приложений, лишние дампы, неиспользуемые базы, ненужные индексы на самой таблице. Последнее особенно полезно: если на таблице есть индекс, который никто не использует, он увеличивает и объём перепаковки, и её длительность.
2. Увеличить диск или перенести базу на тариф побольше. У тарифов MAATRIX дисковые объёмы ступенчатые, от 40 до 320 ГБ NVMe в линейке; но эти цифры описывают конфигурацию, а не гарантию ёмкости под вашу нагрузку, поэтому считайте по своей базе.
3. Подключить второй том или табличное пространство — вариант для тех, кто уже настроил такую схему, и он требует отдельной проверки поддержки этого режима в вашей версии утилиты. Не рассчитывайте на него вслепую.
4. Перепаковывать по частям. Если раздуты не все индексы, возможно, достаточно пересобрать только их. Это дешевле по месту, чем полная перепаковка, но решает другую задачу: таблица останется прежнего размера.

Условия, без которых pg_repack не стартует

До расчёта диска стоит убедиться, что инструмент вообще применим к вашей таблице. В документации названы два требования, которые легко проверить заранее.

Первое — у целевой таблицы должен быть PRIMARY KEY либо подходящий UNIQUE-индекс по столбцу с ограничением NOT NULL. Смысл ограничения понятен из задачи: чтобы потом перенести изменения из старой таблицы в новую, нужно однозначно идентифицировать строки. Таблицы-журналы, созданные «на скорую руку» без ключа, часто попадают именно в эту ловушку. Проверка занимает минуту:

SELECT conname, contype FROM pg_constraint WHERE conrelid = 'public.events'::regclass AND contype IN ('p','u');

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

Второе — права. Утилите нужен владелец таблицы либо суперпользователь. Это означает, что нельзя запустить перепаковку из-под учётной записи приложения, у которой есть только SELECT и INSERT. Если на VPS у вас полный root-доступ и вы сами администрируете PostgreSQL, вопрос решается просто; в окружении с несколькими ролями стоит заранее определить, от чьего имени выполнять работу и как потом вернуть права в прежнее состояние.

Теперь о совместимости. В документации сказано, что конкретные требования опубликованы в документации релиза. Практический вывод: сверяйте не абстрактную пару «pg_repack и PostgreSQL», а три величины. Это основная версия сервера PostgreSQL, версия расширения, созданного в базе, и версия клиентской утилиты, которой вы собираетесь запускать операцию. Расхождение между расширением и утилитой — частая причина отказа на старте. Узнать версию установленного расширения можно из системного каталога:

SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_repack';

Если расширение установлено не было, его придётся создать в целевой базе, и это отдельный шаг, а не часть самой перепаковки. На копии для репетиции пройдите его так же, как пройдёте на рабочей базе, чтобы не обнаружить расхождение версий в день запуска. Если вы только настраиваете сервер под PostgreSQL, пригодится материал про установку и тюнинг PostgreSQL на VPS: там то, что нужно учесть заранее, чтобы такие операции потом не требовали аварийных манёвров.

Что происходит, пока таблица живая

Слово «онлайн» в описании инструмента означает, что реорганизация идёт без удержания эксклюзивной блокировки на всё время обработки. Это не то же самое, что полное отсутствие влияния. Чтобы понять разницу, полезно разделить операцию на части.

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

Короткие блокирующие этапы нужны в начале (подготовка механизма отслеживания изменений) и в конце (переключение и очистка). На переключении старая таблица должна быть заменена новой, а для такой подмены нужны сильные блокировки. Они короткие по замыслу, но «короткие» не значит «мгновенные при любых условиях». Здесь решает то, что происходит в базе вокруг.

Разберём механизм, потому что он важен для практики. Блокировка в PostgreSQL выдаётся в очереди. Если инструмент просит сильную блокировку на таблицу, а на ней висит чья-то давно открытая транзакция, запрос встаёт в очередь. Все последующие запросы к этой таблице, даже обычные чтения, в ряде случаев начинают ждать за ним. Получается эффект пробки: одна долгая транзакция, один ожидающий запрос блокировки — и приложение ощущает «заморозку», хотя инструмент честно онлайн. Поэтому до запуска и во время него смотрите на длительные транзакции:

SELECT pid, state, now() - xact_start AS xact_age, left(query, 80) AS query FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start;

Если вы видите транзакции возрастом в часы (забытый сеанс в консоли, зависший отчёт, ожидающий клиент), сначала разберитесь с ними. Это же улучшает и очистку: долгие транзакции мешают VACUUM убирать мёртвые строки, так что их присутствие, возможно, одна из причин самого раздутия.

Параметры ожидания блокировок и поведение при тайм-ауте в утилите зависят от версии, так что сверьтесь с документацией того релиза, который будете использовать, и не копируйте ключи из чужих заметок. Принцип выбора общий: лучше, чтобы операция отказалась стартовать или завершилась откатом, чем выстроила очередь за собой на рабочей таблице в разгар дня.

Что наблюдать во время запуска

На VPS без развитого мониторинга хватит нескольких простых наблюдений в соседних сессиях:

• свободное место на томе с данными: оно должно убывать плавно и не подходить к краю; заранее решите порог, при котором вы остановите операцию вручную;
• размер каталога WAL и состояние репликации, если она есть;
• очередь блокировок в pg_locks и ожидающие запросы в pg_stat_activity;
• задержки отклика приложения: рост p95 важнее среднего значения.

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

Репетиция на копии: что сравнивать

Всё сказанное выше — рассуждения и документация. Реальные числа по вашей таблице даст только прогон. Репетировать на рабочей базе — плохая идея, поэтому нужна копия с теми же данными. Это не обязательно полная копия всего сервера; важно, чтобы выбранная таблица и её индексы были идентичны по структуре, объёму и распределению значений. Если таблица большая, потребуется отдельный VPS с достаточным диском. Для таких разовых экспериментов удобно, что виртуальную машину можно поднять быстро (в публичном описании MAATRIX указано автоматическое создание VPS в течение пары минут после оплаты), получить root-доступ и NVMe-диск и после теста удалить. Выберите локацию и тариф по размеру копии, помня, что ресурсы тарифа — это конфигурация, а не обещание производительности под вашу нагрузку.

Для чистоты сравнения придерживайтесь простого порядка.

1. Снимите «до». Зафиксируйте размеры (pg_total_relation_size, отдельно таблицы и индексов), статистику живых и мёртвых строк, свободное место на томе.
2. Выберите 3–5 типичных запросов. Не любые, а те, что действительно важны приложению: выборка по диапазону дат, поиск по ключу, агрегат по периоду. Выполните каждый с EXPLAIN (ANALYZE, BUFFERS) несколько раз и запишите медиану времени и число прочитанных буферов. Последний показатель устойчивее к шуму, чем секунды: если после перепаковки запрос читает меньше страниц, это реальный эффект, а не удача кэша.
3. Выполните перепаковку на копии под нагрузкой, имитирующей рабочую, насколько это возможно: хотя бы скриптом, который пишет и обновляет строки в этой же таблице. Без нагрузки вы получите оптимистичное время и не увидите поведения финальной фазы.
4. Снимите «после». Те же размеры, те же запросы, те же условия кэша. Если «до» вы мерили на прогретом кэше, а «после» на холодном, сравнение будет шутливым, а не инженерным. Либо прогревайте оба раза, либо оба раза сбрасывайте кэш.
5. Проверьте остальное. Все ли индексы на месте, нет ли оставшихся служебных объектов после операции, как изменилась оценка планировщика, не нужно ли выполнить ANALYZE.

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

И ещё одна оговорка про репетицию: она хороша настолько, насколько копия похожа на оригинал. Если вы копируете таблицу через логический дамп и загрузку, на копии она окажется уже плотно упакованной, то есть вовсе не раздутой, и перепаковывать будет нечего. Нужна копия, которая сохраняет физическое состояние (например, восстановление из физической резервной копии или клонирование тома), иначе вы замерите разницу между чистым и чистым. Если физическую копию сделать нельзя, можно искусственно воссоздать раздутие: загрузить данные и повторить массовое удаление и обновление, но тогда выводы будут приблизительными, и это нужно помнить.

Не обещать лишнего: чего не следует ждать

Обычная ошибка — решить, что после перепаковки таблица станет меньше в определённое число раз. Реальный результат зависит от того, сколько мёртвого и свободного места она содержала, как были упакованы строки, настройки заполнения страниц (fillfactor) и от самих данных. Может оказаться, что файл уменьшился на десятки процентов, а может — на единицы, и тогда перепаковка не окупила потраченного времени и риска. Именно поэтому предварительная оценка по pgstattuple и репетиция на копии ценнее любых общих цифр.

Вторая ошибка — ожидать, что запросы станут быстрее. Иногда так и бывает: плотнее упакованные страницы значит меньше чтения. Но если запросы и раньше обращались к горячим данным через индексы и всё лежало в кэше, ускорения не будет. Экономия диска — самостоятельная цель, а скорость запросов — побочный эффект, который нужно проверять, а не предполагать.

Третья — думать, что после перепаковки проблема решена навсегда. Если причина раздутия — паттерн работы приложения (частые обновления широких строк, долгие транзакции, слишком редкая автоочистка), раздутие вернётся. Тогда стоит параллельно разобраться с настройками автоочистки таблицы, с долгими транзакциями и с самим паттерном: например, с партиционированием журнальных таблиц, чтобы старые данные удалялись отбрасыванием партиции, а не построчным удалением. Это отдельная тема, но помнить о ней нужно, иначе вы будете перепаковывать ту же таблицу раз в квартал.

Решение: перепаковывать сейчас или сначала освободить запас

Соберём рассуждение в последовательность вопросов. Они проверяются по порядку, и на любом шаге ответ «нет» меняет план.

1. Раздутие реально и неслучайно? Если физический размер превышает ожидаемый логический заметно, а мёртвых строк мало, дыры накопились и не заполняются. Если же файл просто содержит рабочий запас стационарной таблицы, перепаковка даст временный эффект.
2. У таблицы есть первичный ключ или подходящий уникальный индекс по NOT NULL-столбцу? Нет — инструмент неприменим без дополнительной работы.
3. Совпадают версии сервера, расширения и утилиты, и есть права владельца или суперпользователя? Нет — решайте это до дня запуска.
4. Свободного места хватает с запасом сверх «примерно вдвое» от таблицы и индексов, с учётом WAL и записи во время операции? Нет — сначала освободите запас или увеличьте диск. Перепаковка при нехватке места — худший вариант: операция может упасть посередине, а вы будете чинить диск в самый неудобный момент.
5. Нет ли долгих транзакций и выдержит ли приложение короткую задержку на блокирующих этапах? Нет — сначала наведите порядок или выбирайте тихое окно.
6. Репетиция на копии показала приемлемое время, запас по диску и выигрыш, который стоит риска? Да — тогда запускайте на рабочей базе, наблюдая за теми же показателями.

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

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