Неверный (?) размер таблицы PostgreSQL

У меня есть таблица со столбцами и ограничениями:

height smallint,
length smallint,
diameter smallint,
volume integer,
idsensorfragments integer,
CONSTRAINT sensorstats_idsensorfragments_fkey FOREIGN KEY (idsensorfragments)
  REFERENCES sensorfragments (idsensorfragments) MATCH SIMPLE
  ON UPDATE CASCADE ON DELETE CASCADE

(без первичного ключа). В настоящее время в нем 28 978 112 записей, но размер таблицы, на мой взгляд, слишком велик.

Результат запроса:

select pg_size_pretty(pg_total_relation_size('sensorstats')), pg_size_pretty(pg_relation_size('sensorstats'))

is:

"1849 MB";"1226 MB"

В столбце idsensorfragments есть только один индекс. С помощью простой математики видно, что одна запись занимает ~66,7 Б (?!?!). Кто-нибудь может объяснить мне, откуда эта цифра?

5 столбцов = 2 + 2 + 2 + 4 + 4 = 14 байт. У меня есть один индекс, без первичного ключа. Откуда берутся дополнительные 50 байт на запись?

P.S. Таблица была очищена, проанализирована и переиндексирована.


postgresql storage vacuum
person user1414355    schedule 03.07.2012    source источник

Ответы (2)


arrow_upward
2
arrow_downward

Вы должны посмотреть, как организовано физическое хранилище базы данных, особенно на Макет страницы.

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

  • Заголовок страницы, 24 байта;
  • Заголовок кортежа, 27 байт;
  • «невидимые» версии Tuple;
  • зарезервированное свободное пространство в соответствии с параметрами хранилища таблицы;
  • NULL индикаторный массив;
  • (возможно, пропустил что-то еще).

Расположение физического хранилища меняется между основными выпусками, поэтому вам необходимо выполнить полный дамп/восстановление. В последних версиях pg_upgrade очень помогает в этом процессе.

person vyegorov    schedule 03.07.2012
comment
Вы пропустили массив нулевых индикаторов. (4 столбца OP имеют значение NULL) Я не знаю о выравнивании: лично я бы предпочел выровнять и дополнить шорты до 4-байтовых границ. - person wildplasser; 03.07.2012
comment
Большое спасибо! Я просто думаю, как это решить, потому что предполагаю, что мое приложение будет вставлять прибл. 2 - 5 млн записей в день(!) :D Я, конечно, это агрегирую, но какое-то время мне нужно хранить данные строк. Есть предположения? :) Спасибо! - person user1414355; 03.07.2012
comment
@user1414355 user1414355, вам нужно подумать о разбиении таблицы, так как довольно скоро вы столкнетесь с проблемами производительности. В этом ответе показан пример, а дополнительные сведения приведены в в документах. - person vyegorov; 03.07.2012
comment
Спасибо! Теперь я думаю перейти на облака Amazon со всеми серверами, поэтому я серьезно отнесусь к этому решению. Спасибо дружище еще раз :) - person user1414355; 04.07.2012
comment
@ user1414355, пожалуйста! рекомендуется принимать ответы, чтобы закрыть их. - person vyegorov; 04.07.2012

arrow_upward
0
arrow_downward

Вы выполнили ПОЛНУЮ ОЧИСТКУ или CLUSTER? В противном случае неиспользуемое пространство по-прежнему выделяется для этой таблицы и индекса. Эти операторы перезаписывают таблицу, VACUUM без FULL не перезаписывает.

person Frank Heikens    schedule 03.07.2012
comment
Я сделал ВАКУУМ ПОЛНЫЙ. Сейчас я только добавляю в эту таблицу и ничего не удаляю. - person user1414355; 04.07.2012