DBA: хранение списков — таблица, массив, строка?

Достаточно часто при проектировании схемы БД возникает задача сохранить по основной сущности некоторый набор простых второстепенных данных.

Например, это могут быть ФИО сотрудников, принимающих участие во встрече, список приложенных к сообщению файлов или перечень отгружаемых по документу позиций.

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

Давайте посмотрим, какие варианты хранения таких данных мы можем использовать в 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 терабайты данных, то даже «экономия на спичках» может принести существенное снижение не только хранимого объема, но и нагрузки на дисковую подсистему.