08.12.2022 16:25
Kilor 17 4100 Источник

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

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

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

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

Комментарии (17)


  1. fransua
    08.12.2022 18:49
    #24990954

    Если создать колонку с компрессией LZ4 и хранить массив, разве не будет меньше?


    1. Kilor Автор
      08.12.2022 19:33
      #24991030
      +2

      LZ4 появляется только с PG14, и только для вытесняемых в TOAST данных. Для относительно коротких данных, как в нашем случае, они не будут туда попадать.


  1. stranger1101
    08.12.2022 22:42
    #24991400
    +6

    А для вас правда размер хранимых данных является наиболее важным критерием? Диски вроде бы относительно дешевые в наши дни.

    Что насчёт того как эти конструкции ведут себя при чтении? А какой паттерн чтений? Например, часто ли нужно прочитать это дополнительное поле?

    Я просто веду к тому что денормализация это иногда действительно очень хорошая штука. Вот только делать это ориентируясь исключительно на размер таблицы… Ну такое.


    1. Kilor Автор
      08.12.2022 22:54
      #24991436

      Не "наиболее", но одним из достаточно существенных. И не столько сам объем хранимых данных, сколько пропускная способность системы хранения - есть разница, писать 10Kops или 5Kops.

      То есть как в анекдоте: диск может быть дешевым, объемным и производительным - выберите любые 2 из 3.

      Насчет паттерна чтений - зависит от профиля. У нас, например, в системе упомянутой в статье в последнем абзаце, пишется много больше, чем читается, в силу особенностей мониторинга.


  1. arheops
    09.12.2022 02:40
    #24991660

    А потом вам HR отдел спускает задачу типа «страничка, показывающая где участвовал X.XX» и уже оказыватся что и индексировать и искать надо.
    Кстати, не стоит делать выводы по таблицам в 5Мб размерам. На таблицах в 500 может стать все противоположным. Ну, к примеру, на мелких таблицах индексы ощутимо больший процент места отъедают.


    1. Kilor Автор
      09.12.2022 07:02
      #24991822

      Все-таки, индекс отъедает большую долю в "узкой" таблице вроде варианта-1, а от абсолютного объема практически не зависит - что для 5MB, что для 500MB, доли будут отличаться на десятые процента разве что.

      А насчет изменения требований к БД - справедливо. Но с опытом можно научиться предугадывать вероятность, возникнет ли в данном куске у бизнеса желание расширить функционал.


      1. arheops
        09.12.2022 07:09
        #24991830

        Индекс — логарифмическая(ну, близка к логарифмической) структура и он на больших таблицах относительно меньшего обьема.
        Ну возьмите простой пример, дерево для таблицы из 3х и 5ти строчек. Или дерево из 100 и 1000 позиций, какое будет более сбалансированным? А сбалансированное меньше места занимает.

        В принципе, вы можете это эксперементально проверить. Просто у меня возникает улыбка при виде «вот смотрите 5мб табличка меньше 15мб». Иногда делаешь вроде все, чтоб интуитивно уменьшить размер, а табличка из 100гб становится 130. Еще и скорость операций падает втрое. Приходится откатывать.

        Так вот, если вы это поместите в таблицу — неважно возникнет ли у кого-то такое желание, решить его не представит никакой проблемы. Да и если вы его вынесете как вы делаете, все равно по факту будет lookup, поскольку поле большое. Я не уверен на 100% что сделает постгресс с описанными вами типами, но mysql и oracle гарантированно текст выложат в отдельное место. И у вас гарантированно будет lookup, НО после него еще и парсинг.

        Вообще сама по себе метрика «таблица на диске занимает больше места» никого не волнует. Волнует, сколько при этом она занимает в памяти, есть ли выигрыш или проигрыш в типичных операциях. Иногда даже есть ли выигрыш в одной конкретной, которая realtime и bottleneck, ибо отчеты по большому счету тоже никого не волнуют.


        1. Kilor Автор
          09.12.2022 08:27
          #24991898
          +2

          Индекс — логарифмическая(ну, близка к логарифмической) структура и он на больших таблицах относительно меньшего обьема.

          Индекс - это структура для доступа за O(logN), но его объем растет примерно как O(N) с сохранением доли:

          TRUNCATE TABLE tblpk RESTART IDENTITY CASCADE;
INSERT INTO tblpk(id) SELECT generate_series(1, 1e4);
VACUUM (FULL, ANALYZE, VERBOSE) tblpk;

SELECT relname, pg_relation_size(oid) FROM pg_class WHERE relname LIKE 'tblpk%' AND relkind IN ('r', 'i');
          -- 10K
tblpk      |    368640
tblpk_pkey |    245760 -- 66.7%
-- 100K
tblpk      |   3629056
tblpk_pkey |   2260992 -- 62.3%
-- 1M
tblpk      |  36249600
tblpk_pkey |  22487040 -- 62.0%
-- 10M
tblpk      | 362479616
tblpk_pkey | 224641024 -- 62.0%

          Так вот, если вы это поместите в таблицу — неважно возникнет ли у кого-то такое желание, решить его не представит никакой проблемы.

          Это не совсем так, или даже совсем не так. Классический пример - организация хранения атрибутов объектов в виде EAV - гибко и удобно для разработчика. А потом приходит бизнес с пожеланием сделать по этим данным фасетный поиск - и ой...

          Волнует, сколько при этом она занимает в памяти, есть ли выигрыш или проигрыш в типичных операциях.

          Если мы говорим о most-write, то вообще не интересно, сколько там будет "в памяти", если основная нагрузка (== "типичная операция") - это запись на диск.


          1. arheops
            09.12.2022 09:10
            #24991966

            А почему мы это говорим о most-write если стандартный патерн — 75/25?


            1. Kilor Автор
              09.12.2022 09:26
              #24992014

              Смотря для каких задач стандартный. Например, у нас есть база мониторинга, где мы применили подобный подход замены массивов на строки, и получили профит - там ближе к 10/90.

              Но даже если рассмотреть достаточно большую, не умещающуюся целиком в память, базу с доминирующим чтением, то подобные оптимизации тоже могут быть оправданы.

              Представим, что база работает в режиме append-only, и пишет те самые 25% - тогда именно они и оседают в pagecache операционки. Но пользователи-то хотят видеть в остальные 75% далеко не только свежезаписанные данные, но и архивные из недалекого прошлого. А в кэше-то их уже давно нет - они доступны только на диске. И мы снова возвращаемся к мысли, что для производительности дисковой подсистемы выгодно не только "писать как можно меньший объем", но и "читать как можно меньший объем".


              1. arheops
                09.12.2022 09:29
                #24992036
                +1

                Хороший аналитик может оправдать практически что угодно. Мне не совсем понятно какое это имеет отношение к теме вашей статьи.

                Во первых, ваши данные вообще не хайлоад. Во вторых не совсем DBA, а скорее, только постгрес.
                Ну и выводы из табличек размером в мегабайты, извините, не очень серйозно воспринимаются хоть кем-то кроме джунов.


                1. Kilor Автор
                  09.12.2022 09:42
                  #24992068
                  +1

                  Хороший DBA моделирует на табличке в мегабайт, а думает о терабайтах, хайлоаде и экономии.

                  не совсем DBA, а скорее, только постгрес

                  А разве я где-то предлагал переносить данное решение на другие СУБД? Наоборот, несколько раз в статье подчеркивается, что это решение для PostgreSQL.

                  Неужели оно должно быть обязательным для всех и всегда универсальным рецептом? Или от неуниверсальности оно стало хуже?


  1. shaggyone
    09.12.2022 11:05
    #24992298
    +2

    Мы некоторые связи many-to-many со справочниками, где есть основания полагать, что количество записей в справочнике, пакуем в битовые строки. Из последнего, исходная таблица 10 гигабайт, агрегировали связь со справочником в массив, получили таблицу в размером примерно с гигабайт. Агрегировали эту же связь в виде битовой строки, получили чуть меньше 20 мегабайт.


  1. NeverIn
    09.12.2022 14:53
    #24992928

    А что по скорости доступа, поиска ?


    1. Kilor Автор
      09.12.2022 15:42
      #24993124

      При малом количестве элементов в списке одной записи, это вопрос микросекунд на уровне погрешности измерений.


  1. Val83
    11.12.2022 10:09
    #24996764

    А если сохранить доп. данные в jsonb в основной таблице, gyn индекс для поиска. Насколько объемно и эффективно получится?


    1. Kilor Автор
      11.12.2022 11:51
      #24996920

      У нас по условиям задачи нет необходимости в поиске по значениям в списке.

      Потому что когда такая задача возникает, надо учитывать слишком много дополнительных условий вроде "надо ли искать по нескольким значениям сразу? по И или по ИЛИ?", и это влияет на решение гораздо сильнее, чем схема хранения.