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

Частичный индекс (Partial Index)

Частичный индекс — это индекс в БД, который индексирует не всю таблицу, а только подмножество строк, удовлетворяющих определённому условию WHERE. Это мощная оптимизация для часто встречающихся сценариев.

Базовая идея

Обычный индекс индексирует все строки:

CREATE INDEX idx_users_active ON users(is_active);

Частичный индекс индексирует только те, что соответствуют условию:

CREATE INDEX idx_users_active ON users(is_active) 
WHERE is_active = true;

Практические примеры

Пример 1: Активные пользователи

CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  username VARCHAR(255),
  email VARCHAR(255),
  is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMP
);

CREATE INDEX idx_users_active ON users(username, email)
WHERE is_active = true;

SELECT * FROM users WHERE is_active = true AND username = 'john';

Пример 2: Мягкое удаление

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  amount DECIMAL,
  status VARCHAR(50),
  deleted_at TIMESTAMP NULL,
  created_at TIMESTAMP
);

CREATE INDEX idx_orders_active ON orders(created_at)
WHERE deleted_at IS NULL;

SELECT * FROM orders 
WHERE deleted_at IS NULL 
AND created_at > NOW() - INTERVAL '1 day';

Пример 3: Незавершённые платежи

CREATE TABLE payments (
  id BIGINT PRIMARY KEY,
  transaction_id VARCHAR(255),
  amount DECIMAL,
  status VARCHAR(50),
  created_at TIMESTAMP
);

CREATE INDEX idx_payments_pending ON payments(transaction_id, created_at)
WHERE status IN ('pending', 'processing');

Преимущества

1. Меньший размер индекса

   • Индекс занимает меньше места на диске
   • Помещается в памяти более эффективно
   • Быстрее создаётся и обновляется

2. Лучше performance

   • Поиск по меньшему индексу намного быстрее

3. Меньше write-overhead

   • При вставке/обновлении индекс обновляется реже
   • Не нужно обновлять индекс для удалённых записей

Недостатки

1. Не помогает в некоторых запросах

   • Если индекс нацелен на is_published = true
   • Запрос WHERE is_published = false индекс не использует

2. Усложнение с временем

   • Когда логика меняется, индекс может стать неактуальным
   • Нужна хорошая документация

3. Может не использоваться в некоторых случаях

Когда использовать

Частичные индексы отлично подходят когда:

• Много строк, но используется небольшая их часть
• Есть часто используемое условие (статус, флаг активности)
• Индекс становится очень большим
• Write-performance критичен
• Нужно экономить RAM

Не подходят когда:

• Нужен универсальный индекс для разных условий
• Условие часто меняется
• Сложный WHERE, трудно выразимый индексом

PostgreSQL пример

CREATE INDEX ON users(id) WHERE active;

CREATE INDEX ON articles(created_at DESC) 
WHERE published AND deleted_at IS NULL;

CREATE INDEX ON transactions(amount) 
WHERE amount > 1000 AND status = 'completed';

В Java приложениях

Это создаётся в миграции:

CREATE INDEX idx_user_email_active ON users(email)
WHERE deleted_at IS NULL;

CREATE INDEX idx_order_date_recent ON orders(created_at DESC)
WHERE status != 'cancelled';

В коде Java ничего не меняется:

@Query("SELECT u FROM User u WHERE u.email = :email AND u.deletedAt IS NULL")
Optional<User> findActiveUserByEmail(@Param("email") String email);

Но БД будет использовать наш быстрый partial index.

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

Пример улучшения при частичном индексе:

• Таблица 10M строк, 100K активных

  • Обычный индекс: 500MB
  • Частичный индекс: 5MB (100x меньше)

• Поиск активного пользователя

  • Обычный индекс: 0.5ms
  • Частичный индекс: 0.01ms (50x быстрее)

• Вставка новой строки

  • Обычный индекс: 50μs
  • Частичный индекс: 5μs (10x быстрее)

Реальные сценарии

Soft delete в e-commerce системе:

• Миллионы заказов в истории
• Но активно работаем только с последних 2 лет
• Частичный индекс на deleted_at IS NULL даст огромный выигрыш

CMS с публикацией:

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

SaaS система с планами:

• Индексируем только активные подписки
• Отменённые не интересуют при поиске
• Большая разница в размере и скорости

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