Partial Indexes in PostgreSQL: Indexing Only the Hot Rows

Большинство индексов покрывают всю таблицу: каждую строку, каждое значение. Но запросы редко равномерно «размазаны» по данным. Вы постоянно дёргаете заказы со статусом pending, активных пользователей, незакрытые задачи — а исторический хвост из миллионов завершённых строк просто лежит мёртвым грузом. Частичный индекс (partial index) индексирует только то подмножество, которое реально нужно. Результат: индекс меньше, быстрее обновляется и чаще целиком помещается в кэш.

Что такое частичный индекс

Частичный индекс — это обычный индекс с предикатом WHERE. В дерево попадают только строки, удовлетворяющие условию.

-- Полный индекс: каждая строка orders
CREATE INDEX idx_orders_status ON orders (status);

-- Частичный индекс: только «горячие» заказы
CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

Если в таблице 50 млн заказов, но в статусе pending висит лишь несколько тысяч, второй индекс будет на порядки меньше. Вставка завершённого заказа вообще не трогает этот индекс — строка не проходит предикат. Это экономит и место на диске, и время на запись.

Чтобы планировщик использовал такой индекс, условие запроса должно логически совпадать с предикатом индекса (или быть его подмножеством):

-- Использует idx_orders_pending
SELECT id, created_at
FROM orders
WHERE status = 'pending'
ORDER BY created_at;

Почему это быстрее и дешевле

Преимущества вытекают из простой арифметики — индекс физически содержит меньше записей:

• Меньше размер. Меньше страниц на диске, выше шанс, что весь индекс уляжется в shared_buffers.
• Быстрее запись. INSERT/UPDATE строк вне предиката не обновляют индекс вообще.
• Чище статистика. Планировщик оценивает кардинальность по «узкому» подмножеству и реже ошибается.
• Дешевле обслуживание. VACUUM и перестройка идут по меньшему объёму данных.

Классический сценарий — очередь задач. Воркеры читают только неготовые строки:

CREATE INDEX idx_jobs_queue
  ON jobs (priority DESC, created_at)
  WHERE state IN ('queued', 'running');

Даже если в таблице сотни миллионов выполненных задач, индекс очереди остаётся крошечным и горячим.

Подвох: предикат должен быть детерминированным и иммутабельным. Нельзя писать WHERE created_at > now() - interval '7 days' — now() меняется, и «вчерашний» индекс завтра будет покрывать не те строки. Используйте сравнения со статическими значениями: WHERE status = 'pending', WHERE deleted_at IS NULL.

Частичный UNIQUE для soft-delete

Самое мощное применение — частичное UNIQUE-ограничение. Допустим, у пользователей должен быть уникальный email, но вы не удаляете строки физически, а помечаете deleted_at. Обычный UNIQUE (email) запретит зарегистрировать новый аккаунт на email удалённого пользователя.

-- Не сработает: старая удалённая строка блокирует email
CREATE UNIQUE INDEX ON users (email);

-- Уникальность только среди живых строк
CREATE UNIQUE INDEX idx_users_email_active
  ON users (email)
  WHERE deleted_at IS NULL;

Теперь можно держать сколько угодно удалённых строк с одинаковым email, но среди активных (deleted_at IS NULL) email останется уникальным. Тот же приём работает для «один активный по умолчанию на пользователя»:

-- Максимум один основной адрес на пользователя
CREATE UNIQUE INDEX idx_one_primary_address
  ON addresses (user_id)
  WHERE is_primary = true;

Это правило целостности, которое почти невозможно выразить обычным UNIQUE-ограничением без триггеров.

Когда не помогает и различия в СУБД

Частичный индекс — не серебряная пуля. Он бесполезен, если:

• запросы обращаются ко всему диапазону значений колонки, а не к одному подмножеству;
• предикат покрывает большую часть таблицы (выгода от «частичности» исчезает);
• предикат запроса не совпадает с предикатом индекса — планировщик его просто проигнорирует.

Различия между движками:

• PostgreSQL — полноценная поддержка частичных индексов и частичных UNIQUE через CREATE INDEX ... WHERE. Эталонная реализация.
• SQLite — поддерживает тот же синтаксис CREATE INDEX ... WHERE, включая частичный UNIQUE.
• MySQL/InnoDB — частичных индексов нет. Обходные пути: генерируемый столбец плюс индекс по нему, либо отдельная таблица «горячих» строк. Внимание: KEY (col(10)) в MySQL — это префиксный индекс (часть значения), а не частичный.
• SQL Server — есть аналог под названием filtered index: CREATE INDEX ... WHERE.
• ClickHouse — другой класс движка; вместо предикатных индексов используют партиционирование (PARTITION BY) и data-skipping индексы.

Практический рецепт: найдите запрос, который раз за разом фильтрует по одному и тому же узкому условию (status, deleted_at, is_active), и вынесите это условие в WHERE индекса. Проверьте EXPLAIN (ANALYZE), что планировщик подхватил индекс и размер на диске упал. Часто это самое дешёвое ускорение из возможных.