alexpcMaxim Boguk,
Ну откровенно говоря, мы не знали что это переделанный Postgres он везде отображается и ведет себя как PostgreSQL. Поэтому прошу меня извинить, если что было не точно сказано. Что такое форк я не знаю, поэтому не мог заранее сказать.
Спасибо большое за ответы!! Благодарю, что направили куда необходимо смотреть.
В общем таблицы с узкими строками и большим количеством строк в PostgreSQL могут быть неожиданно дорогими.
Поэтому (хоть это и нарушение разнообразных нормальных форм) зачастую полезно (если есть возможность) группировать данные в одну строку (с использованием массивов для списков и/или json для более сложных структур).
Вот базовый тестовый пример:
есть таблица в миллион строк (2 поля integer pk + float value) созданная следующим образом:
create table table1 as select id, random() as val from generate_series(1,1000000) as g(id);
alter table table1 add primary key(id);
ее размер получается 55Mb
теперь предположим что у нас есть задача добавить для каждой записи поддержку расширяемого в будущем количества флагов флагов.
Для примера возьмем 16 флагов с номерами от 0 до 15. И опять же для примера возмьем шанст того что каждый из флагов установлен в 50%.
какие у нас есть варианты:
=========================================================================
вариант номер один (самый простой и самый часто применяемый):
create table table1_flags (id serial primary key, table1_id integer references table1(id), flag_id smallint);
create unique index table1_flags_uniq on table1_flags(table1_id, flag_id);
заполняем:
insert into table1_flags (table1_id, flag_id) select id, flag_id from table1 join generate_series(0,15) as g(flag_id) on random()>0.5;
получилось около 8M строк как и ожидалось...
размер таблицы table1_flags: 667 MB
т.е. добавление поддержки флагов нам обошлось в 667 байт !!!! на строку в table 1 (т.е. мы легким движением руки увеличили базу больше чем в 10 раз).
==========================================================================
варинат номер один с плюсом (чуть получше но почему то когда делают many to many постоянно зачем то добавляют искуственный PK):
учитываем что искуственный PK в этой задаче нам не нужен так как парал table1_id+flag_id прекрасно его заменяет
create table table1_flags (table1_id integer references table1(id), flag_id smallint, primary key (table1_id, flag_id));
заполняем:
insert into table1_flags (table1_id, flag_id) select id, flag_id from table1 join generate_series(0,15) as g(flag_id) on random()>0.5;
получилось около 8M строк как и ожидалось...
размер таблицы table1_flags: 500 MB
т.е. добавление поддержки флагов нам обошлось в 500 байт на строку... чуть лучше но всеравно рост в 10 раз... печально
==========================================================================
Вариант номер два (в принципе неплохой когда 16 флагов но становится ужасным когда флагов станет 100 да и alter table не добавляют радости)
Делаем по boolean полю на флаг:
create table table1 as select id, random() as val, case when random()>0.5 then true else false end as flag_0, case when random()>0.5 then true else false end as flag_1, case when random()>0.5 then true else false end as flag_2, case when random()>0.5 then true else false end as flag_3, case when random()>0.5 then true else false end as flag_4, case when random()>0.5 then true else false end as flag_5, case when random()>0.5 then true else false end as flag_6, case when random()>0.5 then true else false end as flag_7, case when random()>0.5 then true else false end as flag_8, case when random()>0.5 then true else false end as flag_9, case when random()>0.5 then true else false end as flag_10, case when random()>0.5 then true else false end as flag_11, case when random()>0.5 then true else false end as flag_12, case when random()>0.5 then true else false end as flag_13, case when random()>0.5 then true else false end as flag_14, case when random()>0.5 then true else false end as flag_15 from generate_series(1,1000000) as g(id);
alter table table1 add primary key(id);
Размер таблицы стал 71MB... т.е. добавление флагов в виде полей обошлось нам в 16MB или всего 16байт на строку (как и ожидалось).
==========================================================================
Вариант номер два с плюсом (пытаемся сэкономить на том что NULL занимает 1бит а не 1 байт даже для boolean и храним false как NULL... полезный фокус когда надо место экономить)
Делаем по boolean полю на флаг:
create table table1 as select id, random() as val, case when random()>0.5 then true else NULL::boolean end as flag_0, case when random()>0.5 then true else NULL::boolean end as flag_1, case when random()>0.5 then true else NULL::boolean end as flag_2, case when random()>0.5 then true else NULL::boolean end as flag_3, case when random()>0.5 then true else NULL::boolean end as flag_4, case when random()>0.5 then true else NULL::boolean end as flag_5, case when random()>0.5 then true else NULL::boolean end as flag_6, case when random()>0.5 then true else NULL::boolean end as flag_7, case when random()>0.5 then true else NULL::boolean end as flag_8, case when random()>0.5 then true else NULL::boolean end as flag_9, case when random()>0.5 then true else NULL::boolean end as flag_10, case when random()>0.5 then true else NULL::boolean end as flag_11, case when random()>0.5 then true else NULL::boolean end as flag_12, case when random()>0.5 then true else NULL::boolean end as flag_13, case when random()>0.5 then true else NULL::boolean end as flag_14, case when random()>0.5 then true else NULL::boolean end as flag_15 from generate_series(1,1000000) as g(id);
alter table table1 add primary key(id);
Размер таблицы стал 69MB... т.е. добавление флагов нам обошлось в 14MB или 14байт на строку.... чуть лучше
==========================================================================
Вариант номер 3... задумались о том что всетаки когда станет 100 флагов станет грустно в такой реализации и решили использовать массив флагов ( smallint[]) который легко расширяется на любое количество флагов
(
http://www.postgresql.org/docs/9.3/interactive/arrays.html ):
create table table1 as select id, random() as val, array_agg(flag_id)::smallint[] from generate_series(1,1000000) as g1(id) join generate_series(0,15) as g2(flag_id) on random()>0.5 group by id;
alter table table1 add primary key(id);
Размер таблицы получился 93MB что конечно хуже заметно чем 69MB и добавило нам не 14байт на строку а 38байт на строку но зато позволило расширять количество флагов настолько насколько нам удобно...
Кстати этот метод стал бы сравним с методом два если бы было выставлено не 50% флагов а 10% (или меньше) флагов...
Я обычно рекомендую для такой задачи именно этот метод (кроме экстремальных случаев где надо каждый байт экономить).
===========================================================================
Вариант номер 4: пробуем скрестить расширяемость третьего подхода и экономичность второго... для экстремальных случаев когда строк очень много и байты надо экономить.
Используем тип bit string
http://www.postgresql.org/docs/9.3/interactive/datatype-bit.html +
http://www.postgresql.org/docs/9.3/interactive/functions-bitstring.html
create table table1 as select *, random() as val from (select id, sum(1::integer<<flag_id)::bit(16) as flags from generate_series(1,1000000) as g1(id) join generate_series(0,15) as g2(flag_id) on random()>0.5 group by id) as _t;
alter table table1 add primary key(id);
Размер таблицы получился 63MB т.е. обошлось оно нам в 8байт на строку... вот он святой грааль :))
===========================================================================
Внимание: Если флагов или потенциальных значений сотни (а тем более тысячи и больше) а реально выставлено из них 1-2-3 на строку - тогда вариант три будет выигрывать у всех других вариантов.
Т.е. в общем надо учитывать специфику базы и если надо экономить размер - думать как именно лучше хранить данные.
Надеюсь это будет полезно не только автору топика но и другим читателям форума.
--Maxim Boguk
www.postgresql-consulting.ru
