Жил-был один проект. И была у него база данных. И была в базе таблица для хранения, ну, скажем, котов. Вот такая:
CREATE TABLE cats (
id serial,
cname varchar(20),
ctype varchar(20),
primary key(id)
);
Все довольно просто: у каждого кота есть id, имя и некий тип.
Конечно, у нас были бизнес-требования к котам и их типам. Например, мы точно знали, что у нас есть типы big furry, neko и sudden danger. Предполагали, что могут появиться типы long tail и sleeper-eater. Но мы ожидали, что требования будут меняться. И пока не известно, какие в итоге понадобятся типы. Поэтому использовали тип данных varchar(20).
Спустя долгое время и несколько релизов мы, наконец, составили точный список типов котов. К этому времени в таблице уже было несколько десятков миллионов котов с самыми разными типами, многие из которых устарели. Нужно было навести порядок, привести все значения в таблице в соответствие с новыми требованиями.
Итак, применим инженерный подход:
- построим теорию,
- проверим ее экспериментами,
- разработаем практическое решение на базе теории,
- применим решение и оценим результат.
Строим теорию
Создадим ENUM-тип данных и перечислим в нем допустимые значения. Затем выполним миграцию:
CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail',
'crafty hunter', 'sudden danger', 'sleeper-eater');
ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING ctype::cat_type;
Мы еще не знаем, что в таком виде миграция не сработает. Забыли про уже существующие в таблице недопустимые значения. Узнаем об этом позже, когда попробуем применить миграцию =)
Так мы запретим создание котов с недопустимым значением типа. А еще уменьшим размер таблицы и размер индекса по полю ctype. Размер таблицы не так уж и важен, а вот уменьшение индекса – это хорошо. Мы уже имели дело с индексами, которые не помещались в оперативной памяти. И это, мягко говоря, не очень полезные индексы.
Давайте оценим, какого выигрыша по памяти можно ожидать.
Для хранения значения типа varchar выделяется 1-4 байта на символ (в зависимости от кодировки) и еще 1 или 4 байта на хранение длины строки (подробнее тут www.postgresql.org/docs/current/static/datatype-character.html). В нашем случае это 1 байт на символ (utf8, латинские буквы) и 1 байт на длину строки. Строки длиной 9-14 символов. Будем считать, что в среднем у нас 12 байт на одно значение.
> select pg_column_size('big furry');
10
> select pg_column_size('sleeper-eater');
14
Известно, что значения enum занимают 4 байта независимо от их длины.
> select pg_column_size('big furry'::cat_type);
4
> select pg_column_size('sleeper-eater'::cat_type);
4
Одна строка в таблице занимает:
- 27 байт на заголовок строки,
- 8 байт id,
- 21 байт cname (считаем, что у всех котов имена по 20 символов),
- 12 байт ctype
Итого: 68 байт.
После миграции будет 27 + 8 + 21 + 4 = 60 байт. Разница небольшая, но для 50 млн строк суммарный выигрыш должен быть существенным.
У нас 2 индекса, по id и по ctype. Индекс по id не изменится. Индекс по ctype должен уменьшится. Как устроена память индекса мы не знаем, но ожидаем, что если одно значение уменьшилось в 3 раза, то и индекс уменьшится в 2-3 раза.
Эксперимент №1
Для эксперимента создадим две таблицы:
CREATE TABLE cats1 (
id serial,
name varchar(20),
type varchar(20),
primary key(id)
);
CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail',
'crafty hunter', 'sudden danger', 'sleeper eater');
CREATE TABLE cats2 (
id serial,
name varchar(20),
type cat_type,
primary key(id)
);
Заполним их тестовыми данными:
CREATE SEQUENCE ss;
INSERT INTO cats1 (name, type)
SELECT
substring(md5(random()::text), 0, 20),
(ARRAY['big furry', 'small red', 'long tail',
'crafty hunter', 'sudden danger', 'sleeper eater'])
[nextval('ss') % 5 + 1]
FROM
generate_series(1, 500000);
INSERT INTO cats2 (name, type)
SELECT
substring(md5(random()::text), 0, 20),
((ARRAY['big furry', 'small red', 'long tail',
'crafty hunter', 'sudden danger', 'sleeper eater'])
[nextval('ss') % 5 + 1])::cat_type
FROM
generate_series(1, 500000);
Да, имена у наших котов довольно странные. Но для эксперимента годятся.
Создадим индексы:
CREATE INDEX cats1_index ON cats1(type);
CREATE INDEX cats2_index ON cats2(type);
И посмотрим, сколько памяти они заняли:
SELECT pg_relation_size('cats1') AS table_size,
pg_indexes_size('cats1') AS indexes_size;
SELECT pg_relation_size('cats2') AS table_size,
pg_indexes_size('cats2') AS indexes_size;
Теоретически строки в первой таблице занимают 68 * 500,000 = 34,000,000 байт, во второй таблице 60 * 500,000 = 30,000,000 байт. На практике видим 34,136,064 и 30,121,984 байт. Цифры получились близкие.
Понятно, что таблица устроена сложнее, чем просто 500,000 строк равномерно одна за другой. Там выделяются страницы памяти по 8 Кб. У страниц есть свои заголовки и другая метаинформация. Да и значения в строках как-то выравниваются (подробнее тут www.postgresql.org/docs/9.5/static/storage-page-layout.html).
Но что у нас с индексами?
Функция pg_indexes_size показывает расход памяти суммарно по всем индексам, связанным с таблицей, а не по каждому отдельно. Но это не беда, мы можем вызвать ее до создания индекса по ctype и после. И тогда увидим, что индекс по id занимает 11,255,808 байт, а индексы по ctype для первой таблицы – 15,794,176 байт, а для второй таблицы – 11,255,808 байт.
Заметно меньше, но не в 2-3 раза, как мы ожидали. Почему так?
Эксперимент №2
Создадим несколько простых таблиц, содержащих только один столбец:
CREATE TABLE t_bool (f bool);
CREATE TABLE t_sint (f smallint);
CREATE TABLE t_int (f int);
CREATE TABLE t_bint (f bigint);
CREATE TABLE t_c7 (f char(7));
CREATE TABLE t_c8 (f char(8));
CREATE TABLE t_c9 (f char(9));
CREATE TABLE t_c15 (f char(15));
CREATE TABLE t_c16 (f char(16));
CREATE TABLE t_c20 (f char(20));
Заполним их данными:
INSERT INTO t_bool (f)
SELECT true FROM generate_series(1, 500000);
INSERT INTO t_sint (f)
SELECT 1 FROM generate_series(1, 500000);
...
INSERT INTO t_c7 (f)
SELECT 'abcdefg' FROM generate_series(1, 500000);
...
INSERT INTO t_c20 (f)
SELECT 'abcd efgh abcd efgh ' FROM generate_series(1, 500000);
Создадим индексы:
CREATE INDEX ON t_bool(f);
CREATE INDEX ON t_sint(f);
...
CREATE INDEX ON t_c20(f);
И посмотрим, сколько места занимают таблица и индекс:
| Тип данных |
Байт на одно значение |
Размер таблицы |
Размер индекса |
| bool |
1 |
18,128,896 |
11,255,808 |
| smallint |
2 |
18,128,896 |
11,255,808 |
| int |
4 |
18,128,896 |
11,255,808 |
| bigint |
8 |
18,128,896 |
11,255,808 |
| char(7) |
8 |
18,128,896 |
11,255,808 |
| char(8) |
9 |
22,142,976 |
15,794,176 |
| char(9) |
10 |
22,142,976 |
15,794,176 |
| char(15) |
16 |
22,142,976 |
15,794,176 |
| char(16) |
17 |
26,091,520 |
20,332,544 |
| char(20) |
21 |
26,091,520 |
20,332,544 |
Видим, что размеры таблицы и индекса одинаковые в диапазонах значений 1-8 байт, 9-16 байт и больше 16 байт.
Похоже, что мелкие оптимизации, такие как замена int на smallint, дают мало пользы. Ну разве что в отдельных случаях, когда в одной таблице есть много столбцов, которые можно так оптимизировать.
Замена varchar на enum дает выигрыш, если varchar-значения в среднем больше 8 байт (длиннее 7-ми символов).
Разрабатываем практическое решение
Теперь мы знаем, что ожидать на практике, и готовы реализовать нашу миграцию.
Возвращаемся к нашим котам:
CREATE TABLE cats (
id serial,
cname varchar(20),
ctype varchar(20),
primary key(id)
);
CREATE INDEX c1 ON cats(ctype);
Наполняем таблицу данными так, чтобы в ней были невалидные и NULL-значения.
CREATE SEQUENCE ss;
INSERT INTO cats (cname, ctype)
SELECT
substring(md5(random()::text), 0, 20),
(ARRAY['big furry', 'small red', 'long tail', 'crafty hunter', 'sudden danger',
'sleeper-eater', 'black eye', 'sharp claw', 'neko', NULL])
[nextval('ss') % 10 + 1]
FROM
generate_series(1, 500000);
Пробуем мигрировать:
CREATE TYPE cat_type AS ENUM ('big furry', 'small red', 'long tail',
'crafty hunter', 'sudden danger', 'sleeper-eater');
ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING ctype::cat_type;
И выясняем, что наш наивный ALTER TABLE не работает:
ERROR: invalid input value for enum cat_type: "black eye"
И нужно писать функцию для преобразования типа:
CREATE OR REPLACE FUNCTION cast_to_cat_type(ctype varchar) RETURNS cat_type AS
$$
DECLARE res cat_type;
BEGIN
CASE ctype
WHEN 'big furry' THEN res := 'big furry';
WHEN 'small red' THEN res := 'small red';
WHEN 'long tail' THEN res := 'long tail';
WHEN 'crafty hunter' THEN res := 'crafty hunter';
WHEN 'sudden danger' THEN res := 'sudden danger';
WHEN 'sleeper-eater' THEN res := 'sleeper-eater';
ELSE res := NULL;
END CASE;
RETURN res;
END
$$
LANGUAGE plpgsql;
И пробуем еще раз:
ALTER TABLE cats ALTER COLUMN ctype TYPE cat_type USING cast_to_cat_type(ctype);
На этот раз сработало. Только показывать кому-то такую функцию стыдно. Ой, кажется я только что выдал свою тайную склонность к копипасте =) Тсс, давайте притворимся, что я этого не писал, а вы этого не видели, ок? А я напишу по-другому:
CREATE OR REPLACE FUNCTION cast_to_cat_type(ctype varchar) RETURNS cat_type AS
$$
DECLARE res cat_type;
BEGIN
BEGIN
res := ctype::cat_type;
EXCEPTION WHEN others THEN
res := NULL;
END;
RETURN res;
END
$$
LANGUAGE plpgsql;
Вот это можно смело отправлять на code review.
Оцениваем результат
Что же у нас получилось в итоге? Размеры таблицы и индексов до миграции: 33,038,336 и 26,140,672 байт. После миграции: 28,581,888 и 22,511,616 байт. Учитывая, что в реальной таблице у нас не 500 тысяч записей, а 50 миллионов, выигрыш будет существенный.
Но при некоторых условиях можно выиграть еще больше. Допустим, бизнес не интересуют коты неправильного или неизвестного типа, в запросах они исключаются. Тогда можно исключить их и из индекса.
Используем частичный индекс:
CREATE index CONCURRENTLY c2 ON cats(ctype) WHERE ctype IS NOT NULL;
DROP index c1;
И теперь размер индексов 18,014,208 байт. Тут, конечно, все будет зависеть от того, как много котов у нас оказались неправильными.
Любопытный вопрос, что дальше делать с неправильными котами. Но это уже вопрос к бизнесу, не к разработчику.
Осталось убедиться, что правильные значения в таблицу вставляются, а неправильные не вставляются:
> INSERT INTO cats (cname, ctype) VALUES ('Murzik', 'crafty hunter'), ('Vasjka', 'sleeper-eater');
INSERT 0 2
> INSERT INTO cats (cname, ctype) VALUES ('Sharik', 'big dog');
ERROR: invalid input value for enum cat_type: "big dog"
Все работает как надо.
У нас есть еще парочка интересных историй про миграции, и про то, как трудно жить с большими таблицами. Расскажем об этом в следующий раз.
Юра Жлоба,
Веб-разработчик.
Комментарии (28)
cmdx
01.08.2016 14:18+3Всегда было интересно — что заставляет людей, работающих на PostgreSQL, использовать VARCHAR вместо TEXT?
Wargaming
01.08.2016 15:56+4Между ними нет разницы ни по производительности, ни по занимаемой памяти.
Разница только в декларации намерений. Используя TEXT, мы декларируем намерение хранить «длинные тексты». А используя varchar, мы декларируем намерение хранить «короткие строки». Причем разница между «длинным» и «коротким» субъективна.
Это просто дело вкуса.
Stawros
01.08.2016 14:32А почему не используются справочники типов (табличка с типами котов)? Со справочником легко можно добавить новый тип, лучше контроль целостности (FK), меньше избыточность данных (как в архиваторах — создается словарь (справочник), а значение заменяется краткой ссылкой — полем CAT_TYPE::smallint).
Grebenshikov
01.08.2016 14:51Я не эксперт в области принципов работы субд, но разве енамы не превращаются в те самые краткие ссылки?
Stawros
01.08.2016 14:58В общем то да, но это это фиксированно 4 байта (если уж объем так важен) + enum это же фиксированная последовательность. Что вы будете делать если вам понадобился ещё один тип, которого нет в enum'е? Если нужно добавить атрибуты для типа? Если нужно просто переименовать один тип?
Wargaming
01.08.2016 16:01Если мне понадобится добавить еще один тип, то я сделаю миграцию ALTER TYPE cat_type ADD VALUE ‘some new value’;
Атрибуты для типа за 3 года эксплуатации не понадобились. А иначе мы бы сделали по-другому.
В целом лучше использовать простые вещи, когда они подходят. А сложные брать когда не подходят простые.
Wargaming
01.08.2016 15:58Подразумевается, что список типов котов статичный. Добавлять в него новые типы не нужно.
Контроль целостности обеспечивается самим типом ENUM.
Ну а экономия за счет 2-х байтов smallint вместо 4-х байтов enum, как показал эксперимент, все равно ничего не дает.
heleo
01.08.2016 18:33Пардон. А в чём тогда был смысл? Просто не приняли во внимание что не будет статичным ENUM или были какие то ещё скрытые моменты?
Если уж на то пошло, то FK (ctype) на ctype_table выглядит как более приемлемое решение, так как вписать в ctype несуществующий id будет проблематично. Плюс к тому же создать новый тип в ctype_table без требуемых прав нельзя, что можно в данном контексте считать контролем целостности.Wargaming
02.08.2016 10:13+3Смысл перехода от varchar к enum был в том, чтобы ограничить возможные значения type. Заодно, как бонус, получить уменьшение индекса.
Это можно было бы сделать и так, как вы говорите — вынести список возможных типов в отдельную таблицу. Но такой вариант, очевидно, сложнее. Он требует дополнительных действий и на вставке нового кота (сперва нужно получить id типа по имени типа), и на чтении котов (нужно делать join).
ENUM покрывает наши потребности полностью, и делает это более простым способом. Принцип KISS.
Ну и, опять же, декларация намерений. Создавая таблицу с типами мы декларируем, что типы будут меняться — можно добавлять новые и т. д. Создавая ENUM, мы декларируем, что список типов статичен, и меняться не будет.
insert в таблицу — штатная операция. alter type — нештатная операция. То есть мы подчеркиваем, что таких операций не хотим.
Deosis
02.08.2016 08:33Скорее всего Постгрес выравнивает строки по 8 байт, именно поэтому уменьшение длины строки с 60 до 58 байт не дало выигрыша.
Если дополнительно уменьшить имя на 2 символа, то таблица будет упакована плотнее.
Больше интересно, отличается ли производительность при работе с таблицами (int, smallint, smallint) и (smallint, int, smallint)?Wargaming
02.08.2016 13:43Да, за счет типа меньшего размера в одном столбце трудно получить выигрыш. Нужно несколько таких столбцов.
Что касается производительности, то я предположу, что для значений в рамках одного машинного слова она будет одинаковая.zuborg
03.08.2016 16:32за счет типа меньшего размера в одном столбце трудно получить выигрыш
Если строк много и длина их переменна (ваш случай), то выигрыш все же будет и он будет соответствовать среднему уменьшению длины записи (если средняя длина записи уменьшится с 50байт до 48байт то и таблица уменьшится на ~4%)
maxru
01.08.2016 16:09+1Если вы всё ещё продолжаете гладить котов, я бы не стал делать ALTER на ENUM.
Wargaming
01.08.2016 16:41Мы так делаем. Но будем рады, если вы расскажете, почему это плохо)
maxru
03.08.2016 17:09+1Как мне кажется, эксклюзивная блокировка всех таблиц, которые этот ENUM используют — не лучшее решение для того чтобы поглаживать котов.
Wargaming
08.08.2016 17:40+1В документации на ALTER TYPE ничего не написано про блокировки. И здравый смысл подсказывает, что нет никакой необходимости блокировать какие-либо таблицы при добавлении нового значения в ENUM. Вот при удалении значения, да, блокировки могут понадобится.
ctapnep
02.08.2016 19:37В всей этой миграции несколько смущает моемнт, что у каких-то котов с более неиспользуемыми типами этот типа взяли и заменили на NULL. То есть данные вообще пропали. Может надо было их куда-то в соседнюю таблицу выгрузить? Хотя это, конечно, вопрос к бизнесу, а не к базе как таковой.
zuborg
03.08.2016 16:37Функция pg_indexes_size показывает расход памяти суммарно по всем индексам, связанным с таблицей, а не по каждому отдельно.
SELECT relname, relpages FROM pg_class WHERE relname LIKE '...';
покажет размер (в 8КБ страницах) каждого индекса (и таблиц)
Ivan22
Интересные цифры по размеру индексов. Полезно.
Кстати если типов котов 8 штук как в примере — индекс явно будет неселективный. Тогда уже при фиксированном и неизменном количестве типов логичнее на партицирование перейти.
p.s. да-да, конечно партицирование в постгресе — то еще веселье.
Wargaming
Да, партицирование у нас есть, и там много интересного. Как-нибудь расскажем про это)
Ogiv
А почему было решено не использовать таблицу-справочник с типами котов?
Wargaming
Подразумевается, что список типов котов статичный. В него не будут добавляться новые типы. Так что это ближе к перечисляемому типу, чем к словарю.
Meredian
Про "не будут добавляться" — это хорошо известная сказка :)
Wargaming
Тут дело в том, рассматриваем ли мы добавление нового типа как штатную операцию, или как некое нештатное действие, требующее митингов, согласований, привлечения девопсов и т. д.
gearbox
записался на прием, буду ждать.
Drummer_hard
Анологично