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 Интересное!

«Чтение на выходные»: 25 материалов для начинающих любителей винила

Сегодня мы подобрали материалы из «Мира Hi-Fi» специально для начинающих и всех, кто хотел бы познакомиться с экосистемой винила, подобрать проигрыватель и разобраться с настройкой. Фото Best Picko / CC Выбор проигрывателя Назад к винилу — вперед к настоящему звуку. ...

[Перевод] Оптимизация рендеринга сцены из диснеевского мультфильма «Моана». Часть 2

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