Построение и перестроение глобальных индексов в Pangolin 6.6.0 и выше

Статья описывает изменения механизма построения/перестроения глобальных индексов в СУБД Pangolin версии 6.6.0 и выше, а также особенности выполнения операций с партициями таблиц при наличии глобальных индексов.

Механизм параллельного построения глобальных индексов

Начиная с СУБД Pangolin версии 6.6.0, при построении/перестроении глобальных индексов кроме основного процесса, инициировавшего команду CREATE INDEX или REINDEX INDEX, участвуют параллельные процессы (parallel workers).

Количество параллельных процессов определяется результатом функции:

least(parallel_workers_p,
      max_parallel_workers,
      max_parallel_maintenance_workers)

Где parallel_workers_p — параметр партиционированной таблицы, который задает конкретное количество рабочих процессов для построения глобального индекса. parallel_workers_p задается следующей командой:

ALTER TABLE pgbench_accounts SET (parallel_workers_p = 4);

В этом случае при построении/перестроении глобального индекса на операциях чтения и сортировки кортежей может быть задействовано до 5 процессов (ведущий и 4 параллельных).

Количество параллельных процессов также зависит от размера maintenance_work_mem. Особенности по использованию параметра maintenance_work_mem представлены в разделе «CREATE INDEX» документа «Справочник по языку SQL» официальной документации СУБД Pangolin.

Внимание!

Каждый параллельный процесс должен получить не менее 32 МБ из общего объема maintenance_work_mem, и еще как минимум 32 МБ должны остаться ведущему процессу.

При наличии достаточных ресурсов CPU и отсутствии ограничений со стороны дисковой подсистемы, увеличение max_parallel_maintenance_workers может позволить задействовать больше рабочих процессов и тем самым сократить общее время построения индекса.

Формирование данных без создания постоянных саб-индексов

В процессе параллельного построения/перестроения глобального индекса саб-индексы как постоянные объекты не создаются.

Параллельные процессы читают сегменты партиций таблицы, сортируют наборы данных и сохраняют их в директорию, заданную параметром temp_tablespaces. После этого строится дерево глобального индекса.

Такой подход значительно снижает объем WAL-файлов и сокращает общее время построения/перестроения глобального индекса.

Распределение нагрузки при обработке партиций таблицы

Все параллельные процессы обрабатывают партиции таблицы последовательно.

Партиции таблицы физически состоят из дата-файлов размером 1 ГБ каждый. Параллельные процессы распределяют между собой обработку дата-файлы каждой партиции.

Это исключает ситуацию, при которой один процесс долго обрабатывает одну большую партицию таблицы.

Выполнение операций с партициями таблиц при наличии глобальных индексов

В СУБД Pangolin версии 6.5.2 и выше операции с партициями таблиц (ATTACH, DETACH, TRUNCATE, DROP) при наличии глобальных индексов требуют дополнительной обработки индексов и могут влиять на планы запросов и работу фоновых процессов.

ALTER TABLE … ATTACH PARTITION …

После ввода пользователем команды ALTER TABLE … ATTACH PARTITION … и ее выполнения происходит следующее:

1. В представлении pg_inherits создается запись, фиксирующая связь новой таблицы как партиции партиционированной таблицы.

2. Для партиции создается саб-индекс, выполняется его валидация, после чего в pg_inherits добавляется запись, объявляющая данный саб-индекс «потомком» соответствующего глобального индекса (parent):

    SELECT inhrelid, inhrelid::regclass,
           inhparent, inhparent::regclass
      FROM pg_inherits
     WHERE inhparent = (SELECT oid
                         FROM pg_class
                         WHERE relname = 'global_index_name');

3. После этого управление возвращается пользователю. С этого момента саб-индекс работает как обычный локальный индекс – изменения кортежей партиции таблицы приводят к изменению в индексе.

   Появление саб-индекса влияет на планы выполнения запросов. Если ранее план запроса содержал метод доступа Global Index Scan, то при наличии «потомка» у глобального индекса в плане появляется узел (APPEND) с методом доступа с использованием саб-индекса Index Scan:

   EXPLAIN
   SELECT * FROM test_table WHERE id = 2000000;
   
   APPEND (cost=0.06..16.71 rows=1)
      -> Global Index Scan using test_table__idx on test_table (cost=0.06..4.08 rows=1)
            Index Cond: (id = 2000000)
      -> Index Scan using test_table_p25_id_tableoid_idx on test_table_p25 (cost=0.44..4.46 rows=1)
            Index Cond: (id = 2000000)

   Таким образом, наличие саб-индекса увеличивает времени планирования и выполнения запросов.

4. Далее в зависимости от параметров, саб-индекс может быть объединен с глобальным индексом:

   • вручную с помощью команды ALTER INDEX global_index_name UNITE;
   • автоматически фоновым процессом autounite launcher.

   При выполнении UNITE процесс autounite worker выполняет следующие шаги:

   • создает новый глобальный индекс global_index_name__ccnew;
   • выполняет фазу merge, при которой выполняется объединение текущего глобального индекса и саб-индекса в дерево индекса global_index_name__ccnew;
   • выполняет validate;
   • выполняет swap;
   • удаляет старый глобальный индекс и саб-индекс (drop).

примечание

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

При выполнении ALTER TABLE … ATTACH PARTITION … при наличии глобального индекса осуществляется контроль совпадения имен, порядка и типов столбцов базовой и присоединяемой таблиц.

Внимание!

Не рекомендуется выполнять операциюALTER TABLE … ATTACH PARTITION … под нагрузкой из-за возникновения блокировок.

TRUNCATE TABLE partition_name

Общий принцип выполнения команды TRUNCATE TABLE partition_name схож с механизмом выполнения ALTER TABLE … ATTACH PARTITION …. При выполнении команды:

1. Создается новый сегмент партиции таблицы. Так как OID партиции не меняется (изменяется только relfilenode), для обеспечения правильного результата выполнения запросов выполняется перестроение глобальных индексов партиционированной таблицы.
2. Для партиции создается пустой саб-индекс, не содержащий указателей на кортежи партиции таблицы. Данный саб-индекс через представление pg_inherits привязывается как «потомок» к глобальному индексу.
3. Управление возвращается пользователю.

Далее autounite launcher обнаруживает глобальный индекс с «потомком» и запускает фоновый процесс autounite worker для перестроения глобального индекса. До завершения процесса объединения в планах выполнения запросов могут наблюдаться узлы APPEND.

примечание

Несмотря на то, что после команды TRUNCATE TABLE partition_name созданный саб-индекс пуст (не содержит вхождений), autounite launcher запускает процесс autounite worker для построения нового глобального индекса. Это необходимо для того, чтобы при выполнении SQL-запросов к таблице, до завершения объединения глобального индекса и саб-индекса (merge), поиск записей, относящихся к данной партиции, использовался не текущий глобальный индекс, а саб-индекс.

При большом размере глобального индекса или наличии нескольких глобальных индексоврекомендуется заменить TRUNCATE последовательностью команд:

• ALTER TABLE … DETACH PARTITION …;
• DROP TABLE partition_name;
• CREATE TABLE … AS PARTITION OF … FOR VALUES … (при отсутствии автопартиционирования).

При необходимости выполнить TRUNCATE нескольких секций или при автоматизированном выполнении TRUNCATE можно снизить возможное количество перестроений глобальных индексов с помощью параметров autounite_parent_children_size_ratio и autounite_max_children_count. Подробное описание доступно в разделе «Глобальные индексы и глобальные ограничения на партиционированные таблицы» документа «Администрирование функциональностей» официальной документации СУБД Pangolin.

ALTER TABLE … DETACH PARTITION …

При выполнении команды ALTER TABLE … DETACH PARTITION … значение pg_class.reltuples для отсоединяемой таблицы добавляется к значению pg_stat_all_table.n_dead_tup для записи, соответствующей базовой таблице (таблицы определения). Далее процесс autovacuum удаляет из глобального индекса все записи, tableoid которых не является «потомком» таблицы определения в представлении pg_inherits.

DROP TABLE partition_name

В отличие от операции DETACH, при удалении партиции, значение pg_class.reltuples для отсоединяемой таблицы не добавляется к значению столбца pg_stat_all_table.n_dead_tup записи, соответствующей базовой таблице. Для удаления orphaned вхождений индекса выполните REINDEX INDEX.

Если перестроение глобального индекса невозможно, возможен обходной вариант:

1. Перед удалением партиции таблицы отсоедините партицию с помощью команды ALTER TABLE … DETACH PARTITION ….
2. Дождитесь очистки orphaned entries из индекса процессом autovacuum.
3. После этого выполните DROP TABLE.

примечание

При очистке глобального индекса процессом autovacuum от указателей на кортежи отсоединенной или удаленной партиции, индекс не становится меньше в размере. В некоторых случаях часть страниц глобального индекса может быть полностью освобождена процессом autovacuum. Место, ранее занятое страницей индекса в дата-файле, в дальнейшем будет использовано.

Рекомендации

Несмотря на то, что параметр autounite по умолчанию имеет значение on, рекомендуется выключать его и использовать команду ALTER INDEX … UNITE. Это позволяет в случае возникновения нештатной ситуации избежать автоматического запуска autounite worker фоновым процессом autounite launcher для объединения саб-индексов и глобального индекса.

Если построение/перестроение глобального индекса завершилось ошибкой, необходимо проверить представление pg_class на наличие артефактов – построенных саб-индексов, индексов с суффиксами _ccnew, _ccold, _ccaux. Если не удалить оставшиеся после предыдущего неудачного построения/перестроения саб-индексы и индекс _ccnew и запустить процесс еще раз, будут созданы локальные индексы с 1 в конце имени индекса (например, index_name_ccnew - index_name_ccnew1). При повторных попытках суффиксы будут увеличиваться _ccnew2, _ccnew3 и так далее.

Мониторинг

Для отслеживания процесса построения/перестроения глобального индекса, а также стадии объединения (UNITE), можно использовать следующие SQL-запросы:

SELECT * FROM pg_stat_progress_create_index;

SELECT * FROM pg_stat_progress_unite;

Или:

SELECT s.pid,
       s.datid,
       s.relid AS index_oid,
       s.param1 AS table_oid,
       '(' || s.param2::text || ') ' ||
       CASE s.param2
           WHEN 0 THEN 'initializing'
           WHEN 1 THEN 'create new index'
           WHEN 2 THEN 'merge'
           WHEN 3 THEN 'validate index'
           WHEN 4 THEN 'swap'
           WHEN 5 THEN 'mark dead'
           WHEN 6 THEN 'drop'
           ELSE 'unknown phase'
       END AS phase,
       s.param1::regclass,
       s.param13 AS validated
FROM pg_stat_get_progress_info('UNITE') s
(pid, datid, relid, param1, param2, param3, param4, param5, param6,
 param7, param8, param9, param10, param11, param12, param13,
 param14, param15, param16, param17, param18, param19, param20);

примечание

До версии СУБД Pangolin 6.6.0 SQL-запрос SELECT * FROM pg_stat_progress_create_index возвращает по одной записи для каждого фонового процесса.

Для версий СУБД Pangolin 6.6.0 и выше – SQL-запрос SELECT * FROM pg_stat_progress_create_index возвращает одну запись для всех параллельных процессов, участвующих в построении одного глобального индекса. Также для этих версий стадия UNITE выполняется только при TRUNCATE TABLE partition_name и ALTER TABLE … ATTACH PARTITION ….