Postgres Enum

    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
    • 0
      Лично мне излишним в своё время при миграции с MySQL показалось не сами перечисления как таковые (в мускуле они как раз были), а обход строгой типизации постгри тем или иным способом. На уровне ORM просто не получилось, а лезть в «хранимки» очень не хотелось, собственно одна из целей перехода на постгри было избавление от них.
      • +2
        Возможно, что-то не так понял, но можно ведь создать отдельную таблицу со статусами, связать их ключами, и сделать вьюху, если прямо хочется смотреть на данные глазками. И нормализация, и ещё меньше места должно занимать.
        • 0
          запись заметно усложнится
          • 0
            тут про немного другую задачу.
            ваше предложение сродни тому, как вместо имен переменным давать просто номера, а имена хранить в отдельном словаре
            • 0
              в свинге тоже не все так однозначно
              • 0
                хм… а я не тут это писал. это на другой коммент ответ
            • +3
              но можно ведь создать отдельную таблицу со статусами

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


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

            • 0
              В своё время, в MySQL активно использовали тип enum, всем хорош: и места занимает, и контроль данных, и визуально сразу понятно, что за значение. Но стоит захотеть добавить или убрать значение, то надо делать ALTER TABLE, который на миллионах записях мог довольно долго выполняться.
              Решили что проще делать использовать TINYINT и константы в коде. Если очень критично (или есть желание сделать правильно и красиво), то создаём отдельную таблицу-справочник и внешним ключом контролируем, что там может быть только то, что есть в справочнике.
              • 0
                Хорошо, что у нас postgres
                При добавлении нового значения проблем не заметил вообще
                • 0
                  А при удалении на 500кк-1ккк записей?
                  • 0
                    С тиниинтом проще было бы?
                    Вообще моветон менять прошлое, имхо
                    Мне из енамов пока не приходилось ничего удалять, но да, при обновлении большого количества строк, да ещё если в одном запросе, то проблемы будут, но зачем?
                    • +2
                      Ну у меня был кейс, когда поменялся бизнес-флоу и статусы new / pending слились в один new.
                      Я был рад, что не использовал enum
                      • 0

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


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


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

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

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

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

                  У нас есть пара мест с подобным применением
                  И колонки, естественно, числовые
                  Конечно можно заюзать массив… строк, енамов (не пробовал), гуидов, но… это уже будет не так удобно, как просто число
                • 0
                  Не поделитесь, каким инструментарием пользуетесь для работы с postgres? Это SQL Tabs у вас на скриншоте 4 и 5?
                  • 0
                    SQL Tabs v0.18.0 — посмотрите, что он умеет. Как правило его юзаю для написания запросов, когда нужно именно писать запросы, смотреть план, исполнять несколько запросов разом

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

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

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

                      Есть, кстати, интересный проект для поиска ошибок в хранимках и триггерах: plpgsql_check
                    • 0
                      Лучше всего — psql :-)
                      если про красивые картинки — остановился на DBeaver Community в итоге.
                      • 0
                        psql, я уж спецом его не написал ))

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

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

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

                          требует яву!

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

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

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

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

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

                          Можно использовать галочку «Show non-default databases» диалоге настройки соединения, чтобы видеть все имеющиеся БД на сервере. А также «Switch default database on access» для переподключения к другой базе при необходимости. Но проще настроить несколько подключений к разным БД.
                          • 0
                            Монструозен? Странно — для меня это почти стоп-фактор по жизни, но тут я этого не заметил/замечаю. ява — это большой минус, согласен.
                            Дрова для Оракла? У меня ничего такого не было нужно. Или имеется в виду коннектор к ДБ?

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

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

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

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

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

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

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

                        Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                        Самое читаемое