- PVSM.RU - https://www.pvsm.ru -
Postgres поддерживает понятие перечислений (enum [1])
На скорую руку попытался понять что это для бд и для клиента вообще:
Ок, вроде всё как обычно, только в 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 [2].
И без всякой магии и костылей, что хотел читать/писать — то и указал в запросе
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 это круто, потому предлагаю посмотреть, как с ним работать [3]:
CREATE TYPE e_contact_method AS ENUM (
'Email',
'Sms',
'Phone')
CREATE TABLE contact_method_info (
contact_name text,
contact_method e_contact_method,
value text)
INSERT INTO contact_method_info
VALUES ('Jeff', 'Email', 'jeff@mail.com')
select t.typname, e.enumlabel
from pg_type t, pg_enum e
where t.oid = e.enumtypid and typname = 'e_contact_method';
ALTER TYPE e_contact_method
ADD VALUE 'Facebook' AFTER 'Phone';
ALTER TABLE transactions_enum
ALTER COLUMN status
TYPE enum_transaction_status
USING status::text::enum_transaction_status;
Некоторым может показаться излишним усложнением введения дополнительных перечислений на уровне БД, но к базе всегда нужно относиться, как к сторонней службе [4]. Тогда ничего непривычного — есть какое-то определение в сторонней службе, у нас точно такое же необходимо завести на беке, просто для удобства, да и фронтенды также себе что-то из этих перечислений уже дублируют
Всё норм, только плюсы, а добавлять новые значения можно и нужно миграциями [5].
Немного ссылок:
Автор: SanSYS
Источник [11]
Сайт-источник PVSM.RU: https://www.pvsm.ru
Путь до страницы источника: https://www.pvsm.ru/postgresql/277863
Ссылки в тексте:
[1] enum: https://postgrespro.ru/docs/postgrespro/10/datatype-enum
[2] Dapper: https://dotnet.libhunt.com/compare-dapper-dot-net-vs-bltoolkit
[3] посмотреть, как с ним работать: http://postgresguide.com/sexy/enums.html
[4] как к сторонней службе: https://12factor.net/ru/backing-services
[5] миграциями: https://12factor.net/ru/admin-processes
[6] postgrespro.ru/docs/postgrespro/10/catalog-pg-enum: https://postgrespro.ru/docs/postgrespro/10/catalog-pg-enum
[7] postgrespro.ru/docs/postgrespro/10/functions-enum: https://postgrespro.ru/docs/postgrespro/10/functions-enum
[8] postgrespro.ru/docs/postgrespro/10/sql-createcast: https://postgrespro.ru/docs/postgrespro/10/sql-createcast
[9] www.npgsql.org/doc/types/enums_and_composites.html: http://www.npgsql.org/doc/types/enums_and_composites.html
[10] 8 Reasons Why MySQL's ENUM Data Type Is Evil: http://komlenic.com/244/8-reasons-why-mysqls-enum-data-type-is-evil/
[11] Источник: https://habrahabr.ru/post/353556/?utm_campaign=353556
Нажмите здесь для печати.