![](https://habrastorage.org/getpro/habr/upload_files/f89/b5c/7dd/f89b5c7dd52d2baa537d1799428685f6.jpeg)
Достаточно часто при проектировании схемы БД возникает задача сохранить по основной сущности некоторый набор простых второстепенных данных.
Например, это могут быть ФИО сотрудников, принимающих участие во встрече, список приложенных к сообщению файлов или перечень отгружаемых по документу позиций.
Во всех этих случаях мы заранее понимаем, что список этот меняется редко и ни индексировать эти данные, ни искать по ним, ни извлекать отдельно от основной сущности (встречи, сообщения или документа), мы не захотим.
Давайте посмотрим, какие варианты хранения таких данных мы можем использовать в PostgreSQL, и какой из них окажется в разы более эффективным.
Связанная таблица
Этот вариант является "классическим" и наиболее часто используемым - когда второстепенные данные представлены в виде дополнительной таблицы, ссылающейся на основную.
Давайте условимся, что второстепенные данные в нашем примере будут представлены упорядоченным списком из нескольких uuid
:
-- таблица основных данных
CREATE TABLE tblpk(
id
serial
PRIMARY KEY
);
-- таблица второстепенных данных
CREATE TABLE tblfk(
id
integer
REFERENCES tblpk -- эквивалентно tblpk(id), поскольку id - PK
ON DELETE CASCADE
, ord -- порядок записи в списке
integer
, data
uuid
, PRIMARY KEY(id, ord) -- используется и для FK(id)
);
О некоторых проблемах, которые может вызывать подобная структура, если промахнуться с индексами, я уже рассказывал в "PostgreSQL Antipatterns: когда мешает внешний ключ". Но тут у нас все хорошо - на обеих таблицах пришлось создать по индексу (первичному ключу), чтобы функционал foreign keys работал нормально.
Создадим 100K основных сущностей и к ним 400K произвольно связанных случайных записей. Для этого воспользуемся расширением uuid-ossp:
CREATE EXTENSION "uuid-ossp";
INSERT INTO tblpk(id) SELECT generate_series(1, 1e5);
INSERT INTO tblfk
SELECT
id
, row_number() OVER(PARTITION BY id) ord -- фиксируем порядок записей в списке
, data
FROM
(
SELECT
(random() * (1e5 - 1))::integer + 1 id -- случайный ID из [1 .. 100K]
, uuid_generate_v4() "data" -- случайный uuid
FROM
generate_series(1, 4e5)
) T;
-- переупакуем таблицы полностью, чтобы сбалансировались индексы
VACUUM (FULL, ANALYZE, VERBOSE) tblpk, tblfk;
Посмотрим, какой объем занимает такая структура на диске:
SELECT
relname
, pg_total_relation_size(oid) sz -- этот размер включает и все индексы
FROM
pg_class
WHERE
relkind = 'r' AND
relname LIKE 'tbl%';
relname | sz
tblpk | 5890048 -- 5.6MB
tblfk | 29876224 -- 28.5MB
Итак, 34MB у нас заняло хранение в двух связанных таблицах. И вполне понятно почему - каждая запись из списка приносит с собой, как минимум, пару значений (id, ord)
и индекс по ней.
Может, не стоит столько лишнего хранить?..
Массив
Что если весь список хранить прямо в основной таблице - массивом? Тогда ни id
второй раз, ни ord
хранить не придется, и второй индекс не нужен:
CREATE TABLE tblarr(
id
serial
PRIMARY KEY
, list
uuid[] -- тот самый массив
);
Поскольку у некоторых id
могло не оказаться связанных записей, придется их набор взять из оригинальной таблицы:
INSERT INTO tblarr
SELECT
id
, list
FROM
tblpk -- полный список id
LEFT JOIN
(
SELECT
id -- тут не окажется id без записей
, array_agg(data ORDER BY ord) list -- порядок в массиве соответствует исходному
FROM
tblfk
GROUP BY
1
) T
USING(id);
Перепакуем и оценим:
relname | sz
tblarr | 14729216 -- 14.0MB
Почти в 2.5 раза компактнее!
Но давайте оценим, сколько у нас занимает list-поле в первых 10 записях:
SELECT
id
, array_length(list, 1) ln
, pg_column_size(list) sz
FROM
tblarr
ORDER BY
id
LIMIT 10;
id | ln | sz
1 | |
2 | 3 | 69 = 21 + 3 * 16
3 | 6 | 117 = 21 + 6 * 16
4 | 2 | 53 = 21 + 2 * 16
5 | 2 | 53
6 | 2 | 53
7 | 3 | 69 = 21 + 3 * 16
8 | 2 | 53
9 | 4 | 85 = 21 + 4 * 16
10 | 2 | 53
Каждое uuid
-значение занимает 16 байт, а 21 байт "сверху" добавляет заголовочная информация массива о количестве элементов и их типе.
Давайте попробуем убрать этот заголовок.
Строка (text/bytea)
Для этого сохраним весь массив в виде строки. Тут возможны варианты в зависимости от того, насколько сильно хочется упаковать, и насколько сложно потом придется доставать данные.
На примере списка [0, 1, 65535]
мы можем его упаковать:
в строковое представление всего массива:
'{0,1,65535}' - 11 байт
в строку с разделителями:
'0,1,65535' - 9 байт
в двоичную строку:
x'00000001FFFF' - 6 байт
Давайте попробуем применить наиболее компактный вариант упаковки в bytea
:
CREATE TABLE tblstr(
id
serial
PRIMARY KEY
, list
bytea
);
Давайте попробуем "упаковать" наш массив в bytea
. Для этого воспользуемся *_send
-функцией, которая преобразует поле в его хранимое двоичное представление: uuid_send
для uuid
:
SELECT
id
, array_to_string(ARRAY(
SELECT
uuid_send(data)
FROM
unnest(list) data
), '')::bytea list
FROM
tblarr;
Замечу, что чтобы была возможность поклеить bytea как строки, параметр bytea_output должен быть установлен в значение 'escape'
.
Посмотрим, во что превратится запись с 2 uuid
(для удобства разбил на блоки по 8 байт):
...
4 | \035J\366S\032\212D+ -- <
\241~\362f\3216\345\236 -- > uuid #1
\231\220Fc\266 H\177 -- <
\242f}\213\221\032v\025 -- > uuid #2
Такая же функция есть и для всего произвольного массива целиком, array_send
:
SELECT
id
, array_send(list) list
FROM
tblarr;
Но она нам добавит тот самый префикс да еще и информацию о длине каждого поля, чего мы совсем не хотим:
...
4 | \000\000\000\001 -- <
\000\000\000\000
\000\000\013\206
\000\000\000\002
\000\000\000\001 -- > 16 байт префикса
\000\000\000\020 -- размер поля
\035J\366S\032\212D+ -- <
\241~\362f\3216\345\236 -- > uuid #1
\000\000\000\020 -- размер поля
\231\220Fc\266 H\177 -- <
\242f}\213\221\032v\025 -- > uuid #2
В общем, упаковываем в полу-ручном режиме:
INSERT INTO tblstr
SELECT
id
, array_to_string(ARRAY(
SELECT
uuid_send(data)
FROM
unnest(list) data
), '')::bytea list
FROM
tblarr;
relname | sz
tblstr | 12337152 -- 11.8MB
Итого: почти в 3 раза компактнее исходного варианта! Отлично, а как оттуда теперь достать-то данные?
Для этого нам уже понадобится понимать, как представлены данные в нашем контейнере. array_send
как раз это все и сохраняет, а мы на этом - сэкономили:
SELECT
id
, ARRAY(
SELECT
encode(substr(list, pos, 16), 'hex')::uuid -- bytea -> hex -> uuid
FROM
generate_series(1, length(list), 16) pos -- 16 байт/uuid
) uuids
FROM
tblstr
LIMIT 10;
...
4 | {1d4af653-1a8a-442b-a17e-f266d136e59e,99904663-b620-487f-a266-7d8b911a7615}
Перепроверим себя:
SELECT
*
FROM
tblfk
WHERE
id = 4
ORDER BY
ord;
id | ord | data
4 | 1 | 1d4af653-1a8a-442b-a17e-f266d136e59e
4 | 2 | 99904663-b620-487f-a266-7d8b911a7615
Все совпало, и мы молодцы!
Может показаться, что экономия всего в 3 раза не стоит таких сложностей. Но если вам тоже, как и нам, приходится писать в PostgreSQL терабайты данных, то даже "экономия на спичках" может принести существенное снижение не только хранимого объема, но и нагрузки на дисковую подсистему.