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))
); 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;

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

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

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


Оставить комментарий

Ваш email нигде не будет показан
Обязательные для заполнения поля помечены *

*

x

Ещё Hi-Tech Интересное!

[Перевод] Создатели ботнета Mirai теперь сражаются с преступностью на стороне ФБР

Три подзащитных студента, стоявшие за ботнетом Mirai – онлайн-инструментом, учинившим разрушения по всему интернету осенью 2016 при помощи мощнейших распределённых атак на отказ от обслуживания – в четверг предстанут перед судом на Аляске и попросят судью вынести новый приговор: они ...

[Из песочницы] RESS — Новая архитектура для мобильных приложений

Вопреки провокационному заголовку, это никакая не новая архитектура, а попытка перевода простых и проверенных временем практик на новояз, на котором говорит современное Android-комьюнити Введение В последнее время стало больно смотреть на то, что творится в мире разработки под мобильные платформы. ...