Расчет и экономия места в PostgreSQL

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

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

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

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

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

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

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

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

db‹›fiddle здесь
_{Старый sqlfiddle}

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

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

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

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

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

Вы можете проверить фактический размер столбца/строки с помощью функции < strong>pg_column_size().
Некоторые типы занимают больше места в оперативной памяти, чем на диске (сжатый или упакованный формат). Вы можете получить большие результаты для констант (формат ОЗУ), чем для столбцов таблицы, при тестировании одного и того же значения (или строки значений по сравнению со строкой таблицы) с помощью 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 параметр.

Есть несколько других факторов, которые следует учитывать при выборе размера на диске:

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

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

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

Из этой отличной документации: https://www.2ndquadrant.com/en/blog/on-rocks-and-sand/

Для таблицы с именем my_table, которая у вас уже есть или которую вы создаете в процессе разработки, этот запрос даст оптимальный порядок слева направо.

SELECT a.attname, t.typname, t.typalign, t.typlen
FROM pg_class c
JOIN pg_attribute a ON (a.attrelid = c.oid)
JOIN pg_type t ON (t.oid = a.atttypid)
WHERE c.relname = 'my_table'
 AND a.attnum >= 0
ORDER BY t.typlen DESC

Используйте базы данных No-SQL для такого рабочего процесса, потому что они похожи на json и нет ничего похожего на строки, вы бы лучше хранили данные для каждого ключа там, и структура каждого is независима ни от кого другого.

НЕКОТОРЫЕ Примеры: MongoDB, Cassandra.