Как стать автором
Обновить
411.58
Тензор
Разработчик системы СБИС

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

Время на прочтение 4 мин
Количество просмотров 12K

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

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

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

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

Теги:
Хабы:
+14
Комментарии 21
Комментарии Комментарии 21

Публикации

Информация

Сайт
sbis.ru
Дата регистрации
Дата основания
Численность
1 001–5 000 человек
Местоположение
Россия