Kilor 8 дек 2022 в 16:25

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

4 мин

15K

Блог компании ТензорPostgreSQL*SQL*Администрирование баз данных*ERP-системы*

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

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

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

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

Теги:

Хабы:

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

Связанная таблица

Массив

Строка (text/bytea)

Публикации

Информация