Добрый день, коллеги!

Сегодня я хочу поделиться некоторыми проблемами (и решениями) при создании большого количества индексов и ограничений целостности (констрейнтов) одновременно.

Но для начала хочу порассуждать о том, почему вообще возникли такие вопросы.

При развёртывании новой крупной базы данных с «нуля» (например путём миграции), возникает необходимость построить также большое количество индексов в весьма ограниченное тех. окно. Как известно, процесс построения индекса это не только ценный мех 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).

Продолжение следует...