Вычисление и сохранение пространства в PostgreSQL

Ответ 1

Я не вижу ничего, чтобы получить (и что-то потерять) при хранении нескольких числовых полей в массиве.

Размер каждого числового типа четко документирован, вы должны просто использовать тип наименьшего размера, совместимый с вашим желаемым разрешением диапазона; и это обо всем, что вы можете сделать.

Я не думаю (но я не уверен), если для столбцов вдоль строки требуется некоторое выравнивание по байтам, в этом случае переупорядочение столбцов может изменить используемое пространство - но я не думаю поэтому.

Кстати, есть накладные расходы на исправление для строки, 23 байта.

Ответ 2

"Колонна тетриса"

На самом деле, вы можете что- то сделать, но это требует более глубокого понимания. Ключевое слово - выравнивание выравнивания. Каждый тип данных имеет определенные требования к выравниванию.

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

CREATE TABLE t (
    e int2    -- 6 bytes of padding after int2
  , a int8
  , f int2    -- 6 bytes of padding after int2
  , b int8
  , g int2    -- 6 bytes of padding after int2
  , c int8
  , h int2    -- 6 bytes of padding after int2
  , d int8)

Чтобы сохранить 24 байта на строку, используйте вместо этого:

CREATE TABLE t (
    a int8
  , b int8
  , c int8
  , d int8
  , e int2
  , f int2
  , g int2
  , h int2)   -- 4 int2 occupy 8 byte (MAXALIGN), no padding at the end

SQL Fiddle.

Как правило, если сначала поставить 8-байтовые столбцы, а затем 4-байтовые, 2-байтовые и 1-байтовые столбцы, вы не ошибетесь.

boolean, uuid и некоторые другие типы не требуют выравнивания выравнивания. text, varchar и другие типы "varlena" (переменной длины) обычно требуют выравнивания "int" (4 байта на большинстве машин). Но на самом деле нет выравнивания выравнивания в формате диска (в отличие от RAM). Я проверен во многих тестах. В конце концов, я нашел объяснение в примечании в исходном коде:

Также обратите внимание, что мы допускаем нарушение номинального выравнивания при хранении "упакованных" варен;

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

Вы можете проверить фактический размер столбца/строки с помощью функции pg_column_size().
Некоторые типы занимают больше места в оперативной памяти, чем на диске (сжатый или "упакованный" формат). Вы можете получить больший результат для констант (формат RAM), чем для столбцов таблицы, когда тестируете одно и то же значение (или строку значений и строку таблицы) с помощью pg_column_size().

Наконец, некоторые типы могут быть сжаты или "поджарены" (сохранены вне строки) или и то, и другое.

Накладные расходы на кортеж (ряд)

4 байта в строке для указателя элемента - без учета вышеуказанных соображений.
И как минимум 24 байта (23 + заполнение) для заголовка кортежа. Руководство по разметке страницы базы данных:

Существует заголовок фиксированного размера (занимающий 23 байта на большинстве компьютеров), за которым следует необязательное нулевое растровое изображение, необязательное поле идентификатора объекта и пользовательские данные.

Для заполнения между заголовком и пользовательскими данными вам нужно знать MAXALIGN на вашем сервере - обычно 8 байтов в 64-битной ОС (или 4 байта в 32-битной ОС). Если вы не уверены, посмотрите pg_controldata.

Запустите следующее в вашем двоичном каталоге Postgres, чтобы получить окончательный ответ:

./pg_controldata /path/to/my/dbcluster

Руководство:

Фактические пользовательские данные (столбцы строки) начинаются со смещения, указанного в t_hoff, которое всегда должно быть кратным расстоянию MAXALIGN для платформы.

Таким образом, вы обычно получаете оптимальный объем хранения, упаковывая данные в кратные 8 байт.

В приведенном вами примере нечего получить. Уже плотно упаковано. 2 байта заполнения после последнего int2, 4 байта в конце. Вы можете объединить отступ до 6 байтов в конце, что ничего не изменит.

Накладные расходы на страницу данных

Размер страницы данных обычно составляет 8 КБ. На этом уровне тоже есть некоторые издержки: слишком большие остатки, чтобы вместить другой кортеж, и, что более важно, мертвые строки или процент, зарезервированный с FILLFACTOR параметра FILLFACTOR.

Существует несколько других факторов, влияющих на размер диска:

• Сколько записей я могу хранить в 5 МБ PostgreSQL на Heroku?
• Разве использование NULL в PostgreSQL все еще не использует растровое изображение NULL в заголовке?
• Настройка PostgreSQL для производительности чтения

Типы массивов?

С типом массива, который вы оценивали, вы добавили бы 24 байта для этого типа. Плюс, элементы массива занимают место как обычно. Нечего там приобретать.