Секционирование таблиц — мощный инструмент PostgreSQL для управления очень большими таблицами с помощью разбиения их на более мелкие части — секции. Основное преимущество достигается, когда запросы содержат условия по ключу секционирования, позволяя СУБД сканировать только необходимые секции (partition pruning), что может кардинально увеличить скорость таких запросов. Кроме того, секционирование упрощает некоторые задачи обслуживания, например быстрое удаление устаревших данных удалением целой секции.

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

Локальные индексы создаются для каждой секции таблицы отдельно. Их плюсы — простота реализации и скорость операций внутри отдельной секции. Но что делать, если нужно обеспечить уникальность значения какого‑то поля во всей секционированной таблице, а не только в одной секции? Или когда требуется выполнить поиск данных, охватывающий сразу несколько секций? Здесь возможности локальных индексов ограничены.

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

1. Ограничение уникальности. Стандартный PostgreSQL позволяет создать уникальный индекс (или ограничение UNIQUE/PRIMARY KEY) для секционированной таблицы, только если этот индекс включает все столбцы ключа секционирования. Если вам нужна уникальность по полю, которое не является частью ключа секционирования (например, email пользователя в таблице, секционированной по дате регистрации), стандартными средствами этого добиться нельзя. Локальные индексы здесь бессильны, так как гарантируют уникальность лишь в пределах своей секции.

2. Производительность запросов по нескольким секциям. Каждый локальный индекс «знает» только о своей секции. Когда запрос затрагивает данные из разных секций, например поиск всех заказов клиента за год в таблице, секционированной по месяцам, PostgreSQL приходится последовательно обращаться к локальным индексам каждой релевантной секции. При множестве секций это может быть неэффективно.

Чтобы решить эти проблемы, в Postgres Pro Enterprise появились глобальные индексы. Они работают на уровне всей секционированной таблицы, обеспечивая как глобальную уникальность, так и оптимизацию запросов, охватывающих несколько секций.

Как работают глобальные индексы?

В отличие от локальных индексов, «привязанных» к каждой секции, глобальный индекс создаётся один раз для родительской секционированной таблицы и охватывает данные всех её секций. Физически он содержит данные индексируемых столбцов из всех секций.

Поиск с помощью глобального индекса Postgres Pro осуществляется в два этапа:

1. Поиск в глобальном индексе. Когда выполняется запрос к секционированной таблице, Postgres Pro сначала обращается к глобальному индексу. Индекс строится по тем столбцам, которые вы указали при его создании. Важный нюанс: в индекс автоматически добавляются столбцы первичного ключа секционированной таблицы в качестве неключевых (аналогично INCLUDE). Эти столбцы хранятся в индексе, но не участвуют в первичном поиске по дереву индекса.

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

Технические детали реализации

• Расширение и метод доступа. Функциональность реализована в расширении pgpro_gbtree. Для создания глобального индекса используется новый метод доступа gbtree. Основой gbtree являются стандартные механизмы B‑tree, но с доработками для работы с секционированными таблицами.

• Создание индекса. Синтаксис создания похож на обычный, но требует наличия первичного ключа у секционированной таблицы и указания USING gbtree:

-- Вначале устанавливаем расширение
CREATE EXTENSION pgpro_gbtree;

-- Пример создания уникального глобального индекса на столбец email
-- (предполагается, что у таблицы my_partitioned_table есть PRIMARY KEY)
CREATE UNIQUE INDEX my_global_unique_index ON my_partitioned_table USING gbtree (email);

• Логика планировщика. Глобальный индекс используется планировщиком только при выполнении двух условий:

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

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

• Виртуальный метод доступа. Для выборки данных через глобальный индекс потребовалось реализовать специальный, «виртуальный» метод доступа для самой секционированной таблицы, которая обычно не участвует в плане как узел выборки данных.

• Размер и структура. Сам глобальный индекс не секционирован. Это единый индекс, содержащий данные всех секций. Поэтому на него распространяется стандартное ограничение PostgreSQL на размер индекса (обычно 32 ТБ). Предполагается, что индекс будет меньше таблицы, но это ограничение сто́ит учитывать.

• Связь с первичным ключом. Глобальный индекс неявно использует первичный ключ таблицы. Поэтому пока существует хотя бы один глобальный индекс, удалить первичный ключ таблицы нельзя (только через CASCADE, что удалит и индекс). Также столбцы первичного ключа не могут входить в состав ключевых столбцов самого глобального индекса (они автоматически добавляются как INCLUDE).

Производительность

Тестирование показывает следующие тенденции:

• Чтение. Поиск по глобальному индексу значительно ускоряет запросы, требующие сканирования многих секций. Скорость сравнима с использованием вспомогательной таблицы‑индекса и может быть в разы выше (в одном из тестов для 100 секций — примерно в 7 раз), чем поиск по локальным индексам при большом количестве секций.

• Запись (INSERT/UPDATE). Операции вставки данных в таблицу с глобальным индексом работают медленнее (в тестах для 100 секций — примерно в 1,6–1,7 раза), чем с обычным локальным B‑tree индексом. Это связано с необходимостью поддерживать уникальность записей внутри самого глобального индекса (даже если он не UNIQUE): перед вставкой требуется поиск дубликата (ключ + PRIMARY KEY) и используется блокировка хэша значения для предотвращения гонок при параллельной вставке.

Операции и обслуживание

• DELETE / DETACH PARTITION. Как и обычные B‑tree индексы, глобальные индексы не удаляют записи немедленно при выполнении DELETE. Записи, соответствующие удалённым строкам или отсоединённым секциям (DETACH PARTITION), остаются в индексе как «мусор». Это нормальное поведение. Глобальный индекс также не перестраивается автоматически при SPLIT/MERGE.

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

• AUTOVACUUM. В настоящее время AUTOVACUUM не поддерживается для глобальных индексов. Основная причина — сложность сбора адекватных метрик для секционированной таблицы в целом, на основе которых автовакуум мог бы принять решение о необходимости очистки глобального индекса. Однако важно отметить, что глобальные индексы не используют механизм MVCC (не хранят версии записей), поэтому они значительно меньше подвержены разрастанию (bloat) по сравнению с обычными B‑tree индексами. Следовательно, потребность в выполнении VACUUM для них возникает гораздо реже, и в некоторых сценариях может отсутствовать вовсе. Поэтому, хотя очистку при необходимости нужно запускать вручную, это обычно не является частой операцией.

Текущие ограничения 

Глобальные индексы выпущены в составе Postgres Pro Enterprise 17.5.1 как экспериментальная функциональность. Существуют следующие ограничения:

• Нельзя использовать опцию CONCURRENTLY при создании или перестроении (REINDEX), как и для обычных индексов.

• Не поддерживаются выражения или предикаты (WHERE) в CREATE INDEX.

• Не поддерживается явное указание столбцов INCLUDE (столбцы PRIMARY KEY добавляются автоматически).

• Столбцы в ключе индекса не могут повторяться.

• Столбцы первичного ключа не могут быть частью ключа индекса.

• Не поддерживаются внешние ключи (FOREIGN KEY), ссылающиеся на уникальный глобальный индекс.

• Таблицу с глобальным индексом нельзя присоединить как секцию к другой секционированной таблице.

• Команда CLUSTER не поддерживается.

• Опция ON CONFLICT неприменима к уникальным глобальным индексам.

Большинство этих ограничений могут быть сняты в будущих версиях по мере развития функциональности и получения обратной связи.

Применение на практике

Несмотря на ограничения, глобальные индексы уже сейчас могут быть незаменимы:

1. Обеспечение глобальной уникальности. Основной сценарий: нужно гарантировать уникальность значения (например, email, номер_договора) по всей секционированной таблице, а не только внутри секции. Глобальный индекс UNIQUE решает эту задачу. Это критично для финансовых систем, CRM, систем учёта.

2. Ускорение запросов по несекционирующим ключам. Если часты запросы с фильтрацией по столбцу, не входящему в ключ секционирования, и эти запросы затрагивают много секций (например, поиск всех заказов клиента по client_id в таблице orders, секционированной по дате), глобальный индекс по client_id может радикально ускорить такие выборки.

3. Упрощение разработки. Разработчику не нужно учитывать структуру секций при написании запросов, в которых фильтрация или поиск выполняются по столбцам с глобальным индексом, — таблица воспринимается как единое целое.

Заключение

Глобальные индексы — важное дополнение к возможностям секционирования в Postgres Pro Enterprise. Они решают давние проблемы с обеспечением глобальной уникальности и производительностью запросов по несекционирующим ключам в больших секционированных таблицах. Текущая реализация является экспериментальной и имеет ряд ограничений, но уже сейчас предоставляет мощный инструмент для определённых сценариев. Мы ожидаем обратную связь от пользователей для дальнейшего развития и улучшения этой функциональности.