Параллельное (конкурентное) создание индексов на секционированных таблицах

Привет, Хабр! Сейчас многие компании активно занимаются вопросом «импортозамещения» и мне выпала задача отвечать за поддержку и миграцию сервиса логирования. Сервис логирования реализован на Spring и после отказа от Oracle использует БД PostgreSQL для хранения логов. В среднем размер таблицы логов составляет ~3Тб. В какой‑то момент потребовалось дополнить некоторые таблицы логов новыми индексами, и я хочу рассказать, с какими проблемами пришлось с толкнуться и как их решали. Важно замечания:

• В статье пример создания индекса основан на использовании самописных процедур (процедуры будут приложены)

• Пример описывает создание индекса на всей таблице base_log (включая уже созданные секции)

Введение

Итак, имеется схема app, где есть табличка с логами «base_log». У таблицы имеется партиционирование по дате — период одна неделя. В эту таблицу непрерывно пишутся логи, а все ddl/dml изменения проливаются Liquibase. Задача — создать индекс по полю operation_uid (идентификатор операции) и start_date (дата начала операции). Создание индекса в лоб заняло бы большое количество времени (простой более двух часов) и такой вариант не подходил, так как пришлось бы остановить сервис. Так в PostgreSQL для создания индекса требуется блокировка SHARE (ShareLock), то рекомендуется использовать конкурентное создание индекса.

Конкурентный индекс (concurrently index) — фоновое создание индекса. Эта процедура (свойство) создания индекса отличается от обычной тем, что она не требует блокирования таблицы, а значит и не блокирует операции записи. С другой стороны, она занимает больше времени и потребляет больше ресурсов. Поэтому рассматривался вариант с конкурентным созданием индекса, однако индекс с таким параметром нельзя создать на секционируемой таблице (см. https://www.postgresql.org/docs/current/ddl‑partitioning.html):

To avoid this, you can use CREATE INDEX ON ONLY the partitioned table, which creates the new index marked as invalid, preventing automatic application to existing partitions. Instead, indexes can then be created individually on each partition using CONCURRENTLY and attached to the partitioned index on the parent using ALTER INDEX … ATTACH PARTITION. Once indexes for all the partitions are attached to the parent index, the parent index will be marked valid automatically.

На официальном сайте предлагается создать индекс по следующему алгоритму:

1. Создать индекс на основную (родительскую) таблицу;

2. Создать конкурентный индекс на каждую секцию;

3. Соединить индекс на секции с индексом на родительской табличке.

По этому плану и решено было действовать, но есть два нюанса:

1. Все изменения проливаются Liquibase равномерно по стендам, поэтому заранее знать количество секций невозможно. Нужна функция, которая достанет все секции, создаст на них конкурентный индекс и соединит с родительской табличкой.

2. К сожалению, в PostgreSQL нельзя сделать конкрутеный индекс через функцию, то есть использовать execute и другие вариации. Текст ошибки:

Error: CREATE INDEX CONCURRENTLY cannot be executed from a function

Поэтому было решено сделать процедуры, которые пролились бы Liquibase, а затем были бы запущены администратором сервера. Итоговые рекомендации по созданию индексов на высоконагруженных таблицах разберем в следующем параграфе.

План по выполнению работ для создания индекса

Итак, в БД были пролиты самописные процедуры с помощью liquibase. Теперь необходимо их запустить в строгом порядке:

Список процедур

1) Процедура установки лока — опционально. Рекомендуется ее использовать, чтобы не зависнуть с ожиданием своей очереди. Полезно в нагруженных таблицах, чтобы словить Deadlock

Комментарий: Процедура для установки явной блокировки на таблицу
create or replace procedure applog.set_lock(
    i_table_name varchar, /* input -- название таблицы в lowercase (без схемы) */
    i_lock_type varchar, /* input -- тип блокировки, возможные значения: ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE, ACCESS EXCLUSIVE */
    i_lock_attempts int /* input -- количество 1/10 секунды, которые надо ждать (-1 в случае бесконечного ожидание, 0 в случае если блокировка не нужна) */
) as
$$
declare
    l_check_exist numeric;
    l_counter     int;
    l_my_sleep    varchar;
    l_schema_name varchar = 'applog'; -- получаем название схемы
begin
    l_counter := 0;
    select count(*) into l_check_exist from information_schema.columns
    where table_schema = l_schema_name
      and table_name = lower($1);

    if l_check_exist != 0 and i_lock_attempts != 0 then
        loop
            begin
                execute 'lock table ' || l_schema_name || '.' || i_table_name || ' in ' || i_lock_type || ' mode nowait';
                raise notice 'The table was successfully locked';
                exit;

            exception
                when sqlstate '55P03' then
                    raise notice 'Retry: %', l_counter;
                    select pg_sleep(0.1) into l_my_sleep;
                    l_counter := l_counter + 1;
                    if l_counter >= i_lock_attempts then
                        raise exception 'Failed to lock table: %', i_table_name;
                    end if;
            end;
        end loop;
    end if;
end;
$$
    language plpgsql;

2) Процедура, которая создает индекс на родительской таблице и запускает процедуру create_index_on_partition, которая генерирует команды для конкурентного создания индексов на секциях

--Комментарий: процедура для создания индекса на секционированных таблицах.
-- Создаем индекс с опцией only для того, чтобы индекс автоматически не создавался на секциях
create or replace procedure applog.create_index_log(
    i_table_name varchar(150), --название таблицы
    i_column_name_1 varchar(150), --название столбца 1
    i_column_name_2 varchar(150)  --название столбца 2
) as
$$
declare
    l_full_index_name         varchar(256);
    l_index_name              varchar(256);
    l_union_column            varchar(356);
    l_union_column_index_name varchar(356);
    l_lock_type               varchar(356);
begin
    -- объединяем колонки, по которым нужно создать индексы
    l_union_column = i_column_name_1 || ',' || i_column_name_2 || '';
    -- объединяем название колонок, чтобы вставить в название индекса
    l_union_column_index_name = i_column_name_1 || '_' || i_column_name_2 || '';
    l_index_name = i_table_name || '_';
    -- собираем название индекса и проверяем существует ли он уже
    l_full_index_name = l_index_name || l_union_column_index_name || '_idx';
    if (select count(*) from pg_indexes where schemaname = 'applog' and lower(indexname) = lower(l_full_index_name)) = 0 then
        -- устанавливаем лок на таблицу
        l_lock_type = 'exclusive';
        call applog.set_lock(i_table_name,l_lock_type, 5);
        -- создаем индекс только на основную таблицу (не на секции)
        execute 'create index ' || l_full_index_name || ' on only applog.' || i_table_name || ' (' || l_union_column || ')';
    end if;
    -- вызываем процедуру, которая создает индексы на секциях основной таблицы
    call applog.create_index_on_partition(i_table_name, l_union_column, l_union_column_index_name);
end;
$$
    language plpgsql;

3) Процедура, которая соединяет индекс на секции с индексом на основной табличке

--Комментарий: процедура объединения основного индекса с партициями
create or replace procedure applog.attach_index_on_partition(
    i_table_name varchar(150), --название таблицы
    i_union_column_index_name varchar(150) --список столбцов через "_"
) as
$$
declare
    l_table_indexes        record;
    l_sql_query            varchar(256);
    l_full_index_name      varchar(256);
    l_full_index_name_part varchar(256);
    l_index_name           varchar(256);
begin
    l_index_name = substr(i_table_name, length('applog..')) || '_';
    -- получаем индексы на секциях и соединяем с индексом на основной таблице
    FOR l_table_indexes IN select distinct (tablename) as partition_name, substring(tablename from length(l_index_name)+1) as prefix
                           from pg_indexes
                           where schemaname = 'applog'
                             and tablename like l_index_name||'%'
                           order by tablename
        loop
            l_full_index_name = l_index_name || i_union_column_index_name || '_idx';
            l_full_index_name_part = l_index_name || l_table_indexes.prefix || '_' || i_union_column_index_name || '_idx';
            l_sql_query = 'alter index applog.' || l_full_index_name || ' attach partition applog.' || l_full_index_name_part || '';
            execute l_sql_query;
        end loop;
end;
$$
    language plpgsql;

4) Процедура, которая генерирует команды для конкурентного создания индексов на секциях

--Комментарий: процедура создания индекса на всех партициях
create or replace procedure applog.create_index_on_partition(
    i_table_name varchar(150), --название таблицы
    i_union_column varchar(150), --список столбцов через запятую
    i_union_column_index_name varchar(150) --список столбцов через "_"
) as
$$
declare
    l_table_indexes   record;
    l_sql_query       varchar(256);
    l_full_index_name varchar(256);
    l_index_name      varchar(256);
begin
    l_index_name = i_table_name || '_';
    -- ищем все секции осн. таблицы и создаем на каждой индекс
    FOR l_table_indexes IN select distinct (tablename) as partition_name, substring(tablename from length(l_index_name)+1) as prefix
                           from pg_indexes
                           where schemaname = 'applog'
                             and tablename like l_index_name||'%'
                           order by tablename
        loop
            l_full_index_name = l_index_name || l_table_indexes.prefix || '_' || i_union_column_index_name || '_idx';
            l_sql_query = 'create index concurrently ' || l_full_index_name || ' on applog.' || l_table_indexes.partition_name ||
                          ' (' ||
                          i_union_column || ')';
            raise notice '%;',l_sql_query;
            l_full_index_name = '';
        end loop;
end;
$$
    language plpgsql;

Итог

Надеюсь, данный материал поможет кому‑то при создании индекса в нестандартной ситуации. Когда я столкнулся с проблемой, на просторах интернетах не нашел готового решения.