Загадка пустых байтов

Вася ворвался в кабинет Макса с горящими глазами и ноутбуком под мышкой.

— Макс, ты не поверишь! Я нашёл ещё один способ освободить место!

Макс медленно отложил кружку, оценивая Васину одержимость.

— Ну-ка, показывай, что ты там на этот раз нашел.

— Смотри, — Вася развернул ноутбук и ткнул пальцем в экран. — В нашу базу для отчетности сливаются таблицы, которые содержат всякие файлы — PDF-ки, Excel и всякие сканы. Но для формирования отчётов они не нужны! Я уже всем всё написал и согласовал их удаление. Даже занулил уже эти поля через UPDATE SET NULL, но…

— Но размер таблиц не уменьшился? — Макс ухмыльнулся.

— Да! Как так? Я же по сути удалил данные!

Макс развернул к себе экран и быстро набрал запрос:

SELECT pg_size_pretty(pg_relation_size('avent.enclosures')) AS table_size,
       pg_size_pretty(pg_indexes_size('avent.enclosures')) AS indexes_size,
       pg_size_pretty(pg_total_relation_size('avent.enclosures')) AS total_size;

— Вот смотри, — он указал на цифры. — Таблица весит 2 Гб, индексы — 0,5 Гб, а реально места на диске занимает 1300 Гб! Ты занулил bytea-поля, но Postgres не освобождает это место просто так.

— То есть я зря старался? — Вася поник.

— Не совсем. Просто UPDATE, как и DELETE, не уменьшает физический размер таблицы. Postgres помечает старые данные как “мертвые”, но они остаются на диске.

— И что делать?

Макс усмехнулся:

— Есть три пути:

• VACUUM FULL перезапишет таблицу и вернёт место файловой системе, но потребует дополнительное место на время работы и удержит ACCESS EXCLUSIVE-блокировку.

• pg_repack перестроит таблицу с гораздо меньшим временем блокировки, но это отдельное расширение со своими требованиями. Короткие эксклюзивные блокировки в начале и при переключении всё равно нужны.

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

— А если я просто сделаю VACUUM без FULL? У нас же работает автовакуум.

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

Вася задумался:

— Ладно, VACUUM FULL в рабочее время делать рискованно, pg_repack у нас не установлен, перезагрузку базы согласовывать хлопотно… А если я скопирую нужные данные в новую таблицу, а старую дропну?

— Рабочий вариант, — кивнул Макс. — Но простое переименование таблиц не перенаправляет автоматически внешние ключи и представления на новый объект: зависимости привязаны к внутреннему идентификатору таблицы. Сначала составь полный план миграции и отката. Для VACUUM FULL тоже нужно окно, запас диска и оценка влияния на репликацию.

— Понял! — Вася уже лихорадочно строчил запросы.

— Кстати, — Макс поднял бровь, — а ты уверен, что эти файлы в основной системе вообще должны храниться в БД? Может, их лучше в объектное хранилище вынести?

— О! — Вася замер с открытым ртом. — Ты гений!

— Ну, это очевидно, — Макс потягивал кофе. — Но если ты сейчас побежишь это внедрять, то твой начальник сначала убьёт тебя, а потом себя.

— …Потому что это потребует изменений в 100500 сервисах и полгода согласований?

— Бинго.

Вася вздохнул:

— Ладно, начну с VACUUM FULL. Пару терабайт точно выиграю.

Макс одобрительно кивнул:

— Главное — не увлекайся. А то скоро начнёшь сжимать VARCHAR до CHAR и партицировать системные таблицы.

— Эээ… а это плохо?

— Это путь в ад, Вася. В ад.