Чем это объясняется? Существует такая вещь, как выравнивание данных CPU, и от нее зависит низкоуровневый размер структуры данных. Осознанный выбор порядка столбцов дает возможность оптимизировать размер данных. Не верите? Давайте попробуем:
test=# CREATE TABLE t_test (
i1 int,
i2 int,
i3 int,
v1 varchar(100),
v2 varchar(100),
v3 varchar(100)
);
CREATE TABLE
В этом примере 6 столбцов. 3 целочисленных столбца один за другим и 3 столбца varchar, также один за другим. Добавим в эту таблицу 10 миллионов строк:
test=# INSERT INTO t_test SELECT 10, 20, 30, 'abcd', 'abcd', 'abcd'
FROM generate_series(1, 10000000);
INSERT 0 10000000
Общий размер таблицы — 574 МБайт:
test=# SELECT pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
574 MB
(1 row)
Попробуем изменить расположение этих столбцов. В следующем примере после столбца varchar идет столбец integer. Это повторяется трижды:
test=# CREATE TABLE t_test (
v1 varchar(100),
i1 int,
v2 varchar(100),
i2 int,
v3 varchar(100),
i3 int
);
CREATE TABLE
Теперь добавим 10 миллионов строк…
test=# INSERT INTO t_test SELECT 'abcd', 10, 'abcd', 20, 'abcd', 30
FROM generate_series(1, 10000000);
INSERT 0 10000000
… и таблица изрядно увеличится:
test=# SELECT pg_size_pretty(pg_relation_size('t_test'));
pg_size_pretty
----------------
651 MB
(1 row)
Данные в таблице не изменились – просто они специально подобраны для иллюстрации этого эффекта. Если бы я написал “abc” вместо “abcd”, мы не увидели бы разницы в размере, но строка из 4 символов уже не помещается в буфере меньшего размера.
Вывод
Важный вывод, который можно сделать из этого эксперимента – упаковка одинаковых типов данных рядом друг с другом определенно имеет смысл. Кроме того, я обнаружил, что имеет смысл паковать столбцы integer в начале таблиц. Часто это может прибавить еще пару процентов к производительности, просто из-за того, что структура данных немного меньше, чем если этого не делать.
От переводчика:
Автор статьи: Hans-Jurgen Schonig. Оригинал доступен по ссылке.
Комментарии (14)
homm
10.01.2017 14:58+3Бесполезная статья. «Столбцы нужно упаковывать». Ну ок. А как?
tuffnatty
10.01.2017 15:40+2Статья несколько сумбурная, но мне показался важным и неожиданным сам факт, на который указывает автор — что в дисковом формате данных используется data alignment.
Как паковать — как правило, пересозданием таблицы. Есть текст https://wiki.postgresql.org/wiki/Alter_column_position, в котором также упоминается, что physical layout can be optimized by putting fixed size columns at the start of the table, но без подробностей.
nvv
10.01.2017 17:39Сколько раз повторялся эксперимент, есть ли влияние упреждающего выделения места/страниц БД и др. параметров базы, такие как кодировка?
tuffnatty
10.01.2017 18:39Ну, поведение вполне стабильно и естественно, но явным образом не описано в документации, или я не нашел — вот только есть тут (искать typalign) и в вики немного.
tuffnatty
10.01.2017 18:49Но еще у автора поста есть книга, в которой делается предположение, что может влиять тип CPU.
baza906
10.01.2017 18:09Справедливо только для Postgre? Есть у кого информация?
fifthsound
11.01.2017 01:29+1В Oracle 12c второй вариант получился на 2мегабайта больше (320мб и 322мб).
Timosha
11.01.2017 14:20+3немного мякотки
( select encode((select substring(get_raw_page('t_test2', 'main', 0) from (lp_off + t_hoff + 1) for (lp_len - t_hoff))), 'hex') as user_data from heap_page_items(get_raw_page('t_test2', 'main', 0)) as h limit 1 ) union all ( select encode((select substring(get_raw_page('t_test', 'main', 0) from (lp_off + t_hoff + 1) for (lp_len - t_hoff))), 'hex') as user_data from heap_page_items(get_raw_page('t_test', 'main', 0)) as h limit 1 )
и мы получаем как физически выглядят данные в тупле
t_test : 0a000000 14000000 1e000000 0b61626364 0b61626364 0b61626364
t_test2: 0b616263 64000000 0a000000 0b616263 64000000 14000000 0b616263 64000000 1e000000
в случае когда varchar'ы идут подряд не производится дополнительного выравнивания
Melkij
Как внезапно кончилась статья. Я-то думал, дальше пойдёт описание заголовка строки в 23 байта, битовая маска NULL-значений, описание по какой границе данные выравниваются (хотя бы касательно только x86_64) и т.д.
Немножко по этой теме было на pgday'16, вот этот доклад начиная с 34 слайда. (выложено ли видео доклада — честно, не в курсе)
Об этом любопытно знать. Но проблема — добавить новое поле можно только в конец списка полей. В итоге табличка всё равно будет кучкой разных полей и заново разметить её можно только перезаписью всей таблицы, что весьма неудобно.
afiskon
Я бы еще добавил к возможным подходам ZSON.
В случае с текстовыми данными нужно учитывать, что в TOAST уже есть LZ-подобное сжатие, поэтому все большие объемы данных и так жмутся. На выравнивании можно выиграть еще 1-2 процента от силы.
Melkij
Если говорить именно об экономии места, то есть ещё не самый очевидный вариант: вместо много строк (23 байта заголовок каждой строки, сжатия нет) упаковать все похожие строки в массив (можно и массив композитных типов использовать! Константный заголовок на весь массив, остальное — чистые данные). И массив пойдёт в toast и дополнительно будет сжиматься. И по этому всё ещё можно адекватно искать.
tuffnatty
ZSON — идея неплоха, но, к сожалению, работает только с JSONB-столбцами, а еще мне показалось, что 16 битов маловато.
Сжатие TOAST в постгресе практически никакое, в погоне за процессорными тактами был выбран алгоритм, эффективно сжимающий только совсем однообразные данные. Основная надежда тут — что появится сжатие на уровне страниц, да еще желательно с pluggable алгоритмами
В синтетическом примере в статье автор выигрывает на выравнивании 12%, а не 1-2.
Итого — это неплохая прибавка к пенсии, когда остальные варианты оптимизации уже применены.