PostgreSQL. Как правильно хранить котов или история одной миграции

История взята из реального проекта. Но поскольку реальный проект слишком скучный (и под NDA), в этой статье используется упрощенный пример.

Жил-был один проект. И была у него база данных. И была в базе таблица для хранения, ну, скажем, котов. Вот такая:

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"

Все работает как надо.

У нас есть еще парочка интересных историй про миграции, и про то, как трудно жить с большими таблицами. Расскажем об этом в следующий раз.

Юра Жлоба,
Веб-разработчик.