Добрый день, коллеги!
Сегодня я хочу поделиться некоторыми проблемами (и решениями) при создании большого количества индексов и ограничений целостности (констрейнтов) одновременно.
Но для начала хочу порассуждать о том, почему вообще возникли такие вопросы.
При развёртывании новой крупной базы данных с «нуля» (например путём миграции), возникает необходимость построить также большое количество индексов в весьма ограниченное тех. окно. Как известно, процесс построения индекса это не только ценный мех IO, но и довольно большое количество CPU при достаточно производительной дисковой подсистеме. Чем больше ядер вы сможете задействовать — тем быстрее пойдёт процесс (в общем случае утверждение, конечно, спорное, но в моём случае обоснованное и проверенное).
На пути «параллелизации» всего и вся возникают разнообразные проблемы, про 3 такие проблемы я и хочу рассказать сегодня.
Итак, случай 1: «Функциональный» индекс
Известно, что Postgres умеет «параллелить» читающую часть создания индекса (и не умеет пишущую). Но в некоторых случаях даже читающая часть может не «параллелиться». Давайте рассмотрим простейший тестовый пример.
Создадим тестовую табличку достаточно большого размера, чтобы успеть поймать эффекты,
create table public.test_fidx AS select g id, repeat('a',100) f1, repeat('b',300) f2, repeat('cd',500) filler from pg_catalog.generate_series(1, 20000000) g;
и функцию для использования в индексе. Заметим, что функция простейшая и объявлена как immutable.
CREATE OR REPLACE FUNCTION "public"."ftest"(a text, b text ) RETURNS text IMMUTABLE LANGUAGE plpgsql AS $function$ begin return a || '.' || b; end; $function$ ;
Попробуем построить индекс, используя данную функцию:
create index test_fidx_ix1 on public.test_fidx ("public"."ftest"(f1,f2));
В другой сессии подсмотрим, что происходит:
SELECT now() - a.query_start as duration, p.phase, round(p.blocks_done / nullif(p.blocks_total::numeric,0) * 100, 2) AS "% read done", round(p.tuples_done / nullif(p.tuples_total::numeric,0) * 100, 2) AS "% write done", a.query, p.blocks_total, p.blocks_done, p.tuples_total, p.tuples_done, ai.schemaname, ai.relname, ai.indexrelname FROM pg_stat_progress_create_index p left JOIN pg_stat_activity a ON p.pid = a.pid LEFT JOIN pg_stat_all_indexes ai on ai.relid = p.relid AND ai.indexrelid = p.index_relid order by 2 desc; duration |phase |% read done|% write done|query |blocks_total|blocks_done ---------------+------------------------------+-----------+------------+------------------------------------------------------------------------+------------+----------- 00:00:11.559213|building index: scanning table| 6.89| |create index test_fidx_ix1 on public.test_fidx ("public"."ftest"(f1,f2))| 4000000| 275693
Видно, что мы находимся в читающей стадии построения индекса (фаза building index: scanning table)
select a.pid, now()-query_start as dur,wait_event,state, substring(query, 1,160) AS query from pg_stat_activity a where 1=1 and query like 'create%' order by query_start ; pid |dur |wait_event |state |query | -------+---------------+------------+------+------------------------------------------------------------------------+ 1303866|00:00:11.559213|DataFileRead|active|create index test_fidx_ix1 on public.test_fidx ("public"."ftest"(f1,f2))|
И видно, что процесс идёт в один поток. Хотя таблица и получающийся индекс довольно большие для выполнения читающей части параллельно (было проверено на простых индексах)
Видно, что построение индекса НЕ ПАРАЛЛЕЛИТСЯ.
Что же можно сделать?
В синтаксисе CREATE FUNCTION (ссылка) есть клауза PARALLEL { UNSAFE | RESTRICTED | SAFE }
Давайте попробуем поменять определение нашей функции: добавим аналогичную, но с клаузой PARALLEL SAFE.
CREATE OR REPLACE FUNCTION "public"."ftest_p"(a text, b text ) RETURNS text immutable parallel SAFE --!!!! LANGUAGE plpgsql AS $function$ begin return a || '.' || b; end; $function$ ;
И опять создадим индекс:
create index test_fidx_ix2 on public.test_fidx ("public"."ftest_p"(f1,f2));
Подсмотрим опять, что происходит (в другой сессии):
pid |dur |wait_event|state |query | -------+---------------+----------+------+--------------------------------------------------------------------------+ 1618020|00:00:10.489583| |active|create index test_fidx_ix2 on public.test_fidx ("public"."ftest_p"(f1,f2))| 1618021|00:00:10.489583| |active|create index test_fidx_ix2 on public.test_fidx ("public"."ftest_p"(f1,f2))| 1618022|00:00:10.489583| |active|create index test_fidx_ix2 on public.test_fidx ("public"."ftest_p"(f1,f2))| 1618023|00:00:10.489583| |active|create index test_fidx_ix2 on public.test_fidx ("public"."ftest_p"(f1,f2))| 1303866|00:00:10.489583| |active|create index test_fidx_ix2 on public.test_fidx ("public"."ftest_p"(f1,f2))| duration |phase |% read done|% write done|query |blocks_total|blocks_done ---------------+------------------------------+-----------+------------+--------------------------------------------------------------------------+------------+----------- 00:00:10.489583|building index: scanning table| 20.06| |create index test_fidx_ix2 on public.test_fidx ("public"."ftest_p"(f1,f2))| 4000000| 802557
Видно, что в данном случае создание индекса замечательно «параллелится», возникает 4 дочерних читающих процесса + координатор.
Резюме: при создании функций, используемых для создания индексов, не забывайте указывать PARALLEL SAFE (если они, конечно, SAFE).
Продолжение следует...
