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

17 октября 2016

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

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

    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 байта на хранение длины строки (подробнее тут). В нашем случае это 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 Кб. У страниц есть свои заголовки и другая метаинформация. Да и значения в строках как-то выравниваются (подробнее тут).

Но что у нас с индексами?

Функция pg_indexes_size показывает расход памяти суммарно по всем индексам, связанным с таблицей, а не по каждому отдельно. Но это не беда, мы можем вызвать ее до создания индекса по ctype и после. (В psql есть команда \diS+ index_name, которая показывает размер конкретного индекса. Но на момент написания статьи я об этом не знал.) И тогда увидим, что индекс по 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

Тип данных	Байт на одно значение	Размер таблицы	Размер индекса
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"

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