Частичный индекс PostgreSQL для очереди незавершённых задач

В таблице миллионы завершённых задач, а рабочий экран читает только pending. Зачем индексировать для него всю историю? Частичный индекс может хранить только нужную часть строк. На PostgreSQL 14 посмотрим, как задать условие и сортировку, а затем убедимся, что планировщик способен использовать такой индекс.

Таблица задач и частичный индекс

CREATE TABLE jobs (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX jobs_pending_created_idx
ON jobs(created_at, id) WHERE status = 'pending';

SELECT id FROM jobs
WHERE status = 'pending'
ORDER BY created_at, id LIMIT 50;

Порядок включает id, чтобы одинаковое время создания не делало выдачу неоднозначной. Индекс описывает именно ожидающие задачи. Если приложение позднее изменит рабочий статус или начнёт включать в ту же выдачу другой набор состояний, условие индекса придётся пересмотреть.

Условие должно быть узнаваемым

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

Добавление на работающей системе

-- Отдельной командой, вне блока транзакции:
CREATE INDEX CONCURRENTLY jobs_pending_created_live_idx
ON jobs(created_at, id) WHERE status = 'pending';

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

Сравните размер индекса, план чтения и затраты на смену статуса. Если почти вся таблица остаётся pending, заметной экономии может не быть. Частичный индекс особенно уместен, когда очередь активных задач действительно мала по сравнению со всей историей.

Источники и дальнейшее чтение

• PostgreSQL 14: частичные индексы
• PostgreSQL 14: CREATE INDEX