15.04.2018 17:43
SanSYS 25 6200 Источник
Postgres поддерживает понятие перечислений (enum)

На скорую руку попытался понять что это для бд и для клиента вообще:

  1. enum — статический упорядоченный набор значений
  2. Значение enum занимает на диске 4 байта
  3. Регистр имеет значение, т. е. 'happy' и 'HAPPY' — не одно и то же
  4. Разные enum сравнивать меж собой нельзя (можно, если привести к общему типу или запилить операторы для них)
  5. Невозможно в колонку перечисляемого типа подсунуть значение, которое отсутствует в самом перечислении

Ок, вроде всё как обычно, только в Postgres

У нас есть ряд таблиц, в которых статусы храним в текстовом виде для удобства чтения глазками
Интересу ради сделал фул вакуум одной из таких таблиц, создал её копию, но статусную колонку заменил на соответствующий enum, что получилось:



Тестовых данных у меня не много, потому разница не сильно заметна



А вот на примере чуть большего объёма данных, но тоже тестовых данных



В любом случае — грубо почти 1 гигабайт экономии, а в проде это наверняка несколько гигов (пусть будет 2, но, конечно, больше)!

Допустим бекапы делаются ежедневно и хранятся 90 дней.

Enum уберёт 180 гигов лишних данных, не так плохо для микрооптимизации в несколько байт.
А в этой табличке аж 9 видов перечислений (их размеры пока не оценивал)

В самой выборке разницы нет (колонка status стала перечисляемого типа)

select date, contragentname, amount, currency, status 
from transactions
where companyid = '208080cd-7426-430a-a5c8-a83f019da923' 
limit 10;

select date, contragentname, amount, currency, status 
from transactions_enum
where companyid = '208080cd-7426-430a-a5c8-a83f019da923' 
limit 10;



Обрати внимание на width в плане выполнения запроса

Код на чтение, при том, менять не приходится совсем (BLToolkit + Npgsql).

Но зависит это только от вашего кода, например у нас на беке .NET и соответствующие enum, а маппит данные BLToolkit, потому мы при отправке запроса на место enum полей подставляем что-то вроде 

(CASE currency WHEN 'NAN' THEN 0 WHEN 'RUR' THEN 1 WHEN 'USD' THEN 2 WHEN 'EUR' THEN 3 WHEN 'CNY' THEN 4 ELSE 0 END) as currency


Потому с чтением проблем нет. А с записью следующая трабла: 

error: column status is of type enum_transaction_status but expression is of type text

Т.к. запрос формируется такой:

update transactions_enum set status = $1::text where id = $2

Для тех кто не понял — явно указывается тип text.

Это очень просто обходится:

CREATE FUNCTION enum_transaction_status_from_str (text) 
returns enum_transaction_status
  AS 'select $1::varchar::enum_transaction_status'
  -- дополнительное приведение к varchar, чтобы не допустить рекурсию
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;

-- создаётся приведение  текста в перечисление 
CREATE CAST (text AS enum_transaction_status) 
WITH FUNCTION enum_transaction_status_from_str(text) 
AS ASSIGNMENT;

Писать case when..then… так себе идея, а с лёту сделать простое чтение не получилось и тут я решил, что BLToolkit это не хорошо и попробовал Dapper.

И без всякой магии и костылей, что хотел читать/писать — то и указал в запросе

using (var conn = new NpgsqlConnection(connString))
{
  conn.Open();

  Dapper.SqlMapper.Execute(conn, 
    "update transactions_enum set status = :status where id = :id",
    new { 
      id, 
      status = ETransactionStatus.Executed.ToString() 
    }
  );

  var tran = Dapper.SqlMapper.QueryFirst<TransactionInStorageFull>(conn, 
    "select id, status from transactions_enum where id = :id",
    new { id }
  );

  Console.WriteLine(tran.Id + " : " + tran.Status.ToString());

  Dapper.SqlMapper.Execute(conn, 
    "update transactions_enum set status = :status where id = :id",
    new { 
      id, 
      status = ETransactionStatus.Deleted.ToString() 
    }
  );

  tran = Dapper.SqlMapper.QueryFirst<TransactionInStorageFull>(conn, 
    "select id, status from transactions_enum where id = :id",
    new { id }
  );

  Console.WriteLine(tran.Id + " : " + tran.Status.ToString());
}



Уже ясно, что enum это круто, потому предлагаю посмотреть, как с ним работать:

  1. Создание

    CREATE TYPE e_contact_method AS ENUM (
 'Email', 
 'Sms', 
 'Phone')
  2. Использование в таблице

    CREATE TABLE contact_method_info (
 contact_name text,
 contact_method e_contact_method,
 value text)
  3. При вставке, обновлении, сравнении не нужно приводить строку к перечислению, достаточно, чтобы строка входила в перечисление (в противном случае — ошибка invalid input value for enum, что является большим плюсом, имхо)

    INSERT INTO contact_method_info 
     VALUES ('Jeff', 'Email', 'jeff@mail.com')
  4. Просмотр всех возможных значений

    select t.typname, e.enumlabel 
 from pg_type t, pg_enum e 
 where t.oid = e.enumtypid and typname = 'e_contact_method';
  5. Добавление новых значений

    ALTER TYPE e_contact_method 
  ADD VALUE 'Facebook' AFTER 'Phone';
  6. Изменение строки на enum в существующей таблице

    ALTER TABLE transactions_enum 
  ALTER COLUMN status 
  TYPE enum_transaction_status 
  USING status::text::enum_transaction_status;

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

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

Немного ссылок:

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


  1. VolCh
    15.04.2018 21:38
    #10756212

    Лично мне излишним в своё время при миграции с MySQL показалось не сами перечисления как таковые (в мускуле они как раз были), а обход строгой типизации постгри тем или иным способом. На уровне ORM просто не получилось, а лезть в «хранимки» очень не хотелось, собственно одна из целей перехода на постгри было избавление от них.


  1. skymal4ik
    15.04.2018 22:42
    #10756246

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


    1. VolCh
      15.04.2018 23:40
      #10756264
      +1

      запись заметно усложнится


    1. kalininmr
      16.04.2018 00:24
      #10756304

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


      1. kalininmr
        16.04.2018 02:20
        #10756382

        в свинге тоже не все так однозначно


        1. kalininmr
          16.04.2018 10:44
          #10756670

          хм… а я не тут это писал. это на другой коммент ответ


    1. voidnugget
      16.04.2018 02:34
      #10756394

      но можно ведь создать отдельную таблицу со статусами

      Если у вас меньше 100 статусов — overkill, так как, в случае с отдельной табличкой, будет храниться больше различных счётчиков для оптимистических/пессимистических блокировок, UNIQUE индекс и последовательность.


      Нормализация нормализацией, но есть размеры структур СУБД, которые, как и законы физики, преодолеть без паранормальных способностей не представляется возможным.


  1. LaXiTy
    16.04.2018 05:58
    #10756422

    В своё время, в MySQL активно использовали тип enum, всем хорош: и места занимает, и контроль данных, и визуально сразу понятно, что за значение. Но стоит захотеть добавить или убрать значение, то надо делать ALTER TABLE, который на миллионах записях мог довольно долго выполняться.
    Решили что проще делать использовать TINYINT и константы в коде. Если очень критично (или есть желание сделать правильно и красиво), то создаём отдельную таблицу-справочник и внешним ключом контролируем, что там может быть только то, что есть в справочнике.


    1. SanSYS Автор
      16.04.2018 05:59
      #10756424

      Хорошо, что у нас postgres
      При добавлении нового значения проблем не заметил вообще


      1. oxidmod
        16.04.2018 08:43
        #10756502

        А при удалении на 500кк-1ккк записей?


        1. SanSYS Автор
          16.04.2018 09:19
          #10756546

          С тиниинтом проще было бы?
          Вообще моветон менять прошлое, имхо
          Мне из енамов пока не приходилось ничего удалять, но да, при обновлении большого количества строк, да ещё если в одном запросе, то проблемы будут, но зачем?


          1. oxidmod
            16.04.2018 09:29
            #10756554
            +1

            Ну у меня был кейс, когда поменялся бизнес-флоу и статусы new / pending слились в один new.
            Я был рад, что не использовал enum


            1. SanSYS Автор
              17.04.2018 06:14
              #10757536

              Кстати, может будет интересно #PostgreSQL. Ускоряем деплой в семь раз с помощью «многопоточки»


              если коротко — как чуваки делают большие update — ранжируют данные на логические части, допустим через ntile, и обновляют частями
              как пример — 50к обновлений по 10 тыс. строк — незаметны локи совсем, и… это быстро, т.к. не в одном запросе, а в несколько
              и многопоточку сделали на гошечке (реально, в нём из коробки таки это удобно)


              короч имейте в виду


  1. Sioln
    16.04.2018 15:25
    #10757100
    +1

    Невозможно в колонку перечисляемого типа подсунуть значение, которое отсутствует в самом перечислении

    Enum же бывает флаговый.
    Типа
    Black=1,
    White=2,
    Yellow=4
    И потом в коде используется значение Black and White = 3 и плюсом к этому удобно работать потом с данными через битовые операторы.


    1. SanSYS Автор
      16.04.2018 17:13
      #10757206

      Спасибо, очень дельное замечание!

      У нас есть пара мест с подобным применением
      И колонки, естественно, числовые
      Конечно можно заюзать массив… строк, енамов (не пробовал), гуидов, но… это уже будет не так удобно, как просто число


  1. Ice2burn
    16.04.2018 17:14
    #10757208

    Не поделитесь, каким инструментарием пользуетесь для работы с postgres? Это SQL Tabs у вас на скриншоте 4 и 5?


    1. SanSYS Автор
      16.04.2018 17:22
      #10757214

      SQL Tabs v0.18.0 — посмотрите, что он умеет. Как правило его юзаю для написания запросов, когда нужно именно писать запросы, смотреть план, исполнять несколько запросов разом

      pgAdmin 1.22.2 — когда хочется просто мышкой покликать (типа топ 100, фильтрануть по значению из колонки..)

      dbForge Studio for PostgreSQL — есть бесплатная версия, плохо дружит с таблицами/колонками «в кавычках».

      Просто пробую всё новое иногда, так у меня не остались — navicat, DataGrip, pgAdmin 4


      1. Ice2burn
        17.04.2018 12:33
        #10758038

        pgAdmin 3 (pgAdmin 1.22.2) очень доволен, стабильнее и быстрее pgAdmin 4. pgAdmin 4 способен привести к взаимоблокировке при выполнении простых скриптов, перемудрили с многопоточностью. А вот что пока не удаётся найти, так это адекватный дебаггер.

        Есть, кстати, интересный проект для поиска ошибок в хранимках и триггерах: plpgsql_check


    1. mgremlin
      16.04.2018 18:34
      #10757312

      Лучше всего — psql :-)
      если про красивые картинки — остановился на DBeaver Community в итоге.


      1. SanSYS Автор
        16.04.2018 19:26
        #10757354

        psql, я уж спецом его не написал ))

        по поводу DBeaver пока только минусы:
        1. слишком монструозен
        2. требует яву! Ну не было её у меня до этого
        3. чтобы качнуть дрова для оракла — потребовалась рега на oracle.com (хотя для пг не запросил и скачал молча)
        4. Коннектит именно к БД, т.е. с лёту не увидел, как именно получить список всех бд в дереве объектов

        Хотя… есть плюсик — всё же комьюнити версия бесплатна и очень мне помогла в коннекте к продовской чужой бд


        1. aleksandy
          16.04.2018 22:13
          #10757366

          слишком монструозен

          Эта монструозность проявляется только на старте.

          требует яву!

          Есть дистрибутивы с предустановленной jre, тупо распаковать архив и запустить.

          потребовалась рега на oracle.com

          Это не вина бобра. Ораклового драйвера нет в публичных репозиториях.

          Коннектит именно к БД

          Опять же это ограничение не бобровое. It is not possible to access more than one database per connection.

          Можно использовать галочку «Show non-default databases» диалоге настройки соединения, чтобы видеть все имеющиеся БД на сервере. А также «Switch default database on access» для переподключения к другой базе при необходимости. Но проще настроить несколько подключений к разным БД.


        1. mgremlin
          17.04.2018 18:42
          #10758834

          Монструозен? Странно — для меня это почти стоп-фактор по жизни, но тут я этого не заметил/замечаю. ява — это большой минус, согласен.
          Дрова для Оракла? У меня ничего такого не было нужно. Или имеется в виду коннектор к ДБ?

          Список дб получить нетрудно в psql :-)


          1. SanSYS Автор
            17.04.2018 18:56
            #10758886

            Да, должно быть это был коннектор, я не уверен в верности терминологии в данной ситуации


          1. aleksandy
            17.04.2018 21:00
            #10759186

            я этого не заметил/замечаю

            Не соглашусь, этого сложно просто не заметить. Не обращать внимания — да, но не замечать невозможно. Не будете же спорить, что запускается бобёр куда как медленнее, нежели pgadmin, например.

            Дрова для Оракла? У меня ничего такого не было нужно.

            Имелся ввиду jdbc-драйвер.


            1. mgremlin
              17.04.2018 21:19
              #10759230

              Специально засек: меньше 10 секунд. Учитывая, что я не перегружаюсь неделями — меня это вполне устраивает. pgadmin мне понравился меньше, прежде всего возможностями работы с результатами запроса. Еще я часто подключаюсь к mysql и maria тоже. И pgadmin у меня постоянно вылетал.

              Но, еще раз, лучший клиент — в консоли. Еще бы там редактировать полегче было…