Какой должен быть размер базы при дампе?

Question

[Андрей]

смотрю размер базы через pretty

postgres=# SELECT pg_size_pretty( pg_database_size( 'soft' ) );
 pg_size_pretty 
----------------
 2524 MB
(1 row)

делаю дамп базы командой

pg_dump -x -O -U postgres -h localhost soft > db_soft.sql

смотрю сколько она весит с помощью du sh * получается 346 мб.

Почему так получается? должен ли дамп базы весить столько же?

Answer 1

[Melkij]

Во-первых, индексы. В sql-дампе любой индекс - это несколько десятков байт запроса на создание индекса. В базе - это развесистая структура, которая может занимать порядочно места. Один индекс обычно небольшой - но у вас же он не один?
Во-вторых, как уже упомянули - бинарные файлы могут быть раздуты из-за delete и update запросов, если плохо настроен автовакуум. Касается как самих таблиц, так и индексов.
Третье: например табличка связей из тройки интов. В pg_dump данные пишутся через copy - один заголовок, следом все строки, значения разделены табуляцией. Итого 3 байта на строку данных оверхеда + строковое представление значения. В реальной таблице - у каждой строки есть заголовок из 23 байт. Плюс выравнивание данных. Итого минимум будет 36 байт на каждую строку. (описание структуры таблицы опять же отдельно хранится) Т.е. текстовый вид внезапно компактнее почти на всём диапазоне значений инта для этой таблички.

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

Comments

[lukashin]

Добавлю: можно поискать "раздутые" (bloat) таблицы и, если они присутствуют, то сделать реорганизацию базы с помощью dump/restore (с простоем) или pg_repack (онлайн).

Answer 2

[Юрий Чудновский]

Я думаю, так получается из-за особенностей работы постгресса с кортежами: при изменении/удалении данных в базу записываются новые данные в новый кортеж, а старый остаётся. При экспорте базы в sql, удалённые данные не попадают в файл, поэтому база "худеет".

Comments

[Андрей]

Не знаю, мы, практически, не удаляем с базы ничего.