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

Представьте ситуацию. В БД PostgreSQL есть таблица с данными водителей:
id
company_id
name
phone

Внутри одной компании номер телефона водителя должен быть уникален. Для этого заведен индекс уникальности по (phone, company_id).

Затем добавляется возможность удалять водителей из кабинета компании. Удаление реализуем через soft delete: добавляем в табличку поле is_deleted. Все работает корректно.

Проходит время, и от пользователей приходит баг: при добавлении нового водителя возникает ошибка.

В логах видно, что БД ругается из-за нарушения уникальности. Оказалось, что запись с таким номером телефона уже существовала и была удалена.

Перед вставкой записи о новом водителе в коде делали проверку, нет ли уже в этой компании водителя с таким телефоном, но проверка была для неудаленных водителей: WHERE is_deleted = false, поэтому она не находила ранее удаленного водителя (и это правильно).
Для просмотра списка водителей тоже возвращались только неудаленные записи.

А индекс уникальности по (phone, company_id) при добавлении поля is_deleted изменить забыли. Поэтому при попытке добавить ранее удаленного водителя с тем же номером телефона возникала ошибка 😢

Разобрались, сделали, чтобы индекс уникальности тоже учитывал только неудаленные записи:
create unique index uc_drivers_phone_company_id
on drivers(phone, company_id)
where is_deleted is false;

Такой индекс называется частичным.

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

Обратите внимание, что поле из WHERE не обязано быть индексируемым. В примере мы строим индекс по полям phone, company_id, а в предикате используем поле is_deleted.

Когда частичный индекс может пригодиться:

➡️ Как в разобранном выше кейсе, если нужно сделать уникальность по условию.

➡️ Когда в таблице 90% записей "неинтересны" для запросов, можно построить частичный индекс по оставшимся 10%, он будет работать быстрее и занимать в несколько раз меньше места.

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

Если сделать частичный индекс, проиндексировав для поиска оставшиеся 10% неоплаченных счетов, то такой индекс будет занимать значительно меньше места, чем индекс по всем строкам.

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

⚡️ Ещё кейс из практики.

Когда работала в платформе, тоже столкнулась с ситуацией, в которой пригодились частичные индексы для уникальности.

В рамках одной из задач мне нужно было поддержать уникальность связки полей (a, b, c). Я добавила индекс и стала писать тесты. Поле c могло быть null, и в процессе тестирования я обнаружила, что несмотря на индекс могу добавить в таблицу две одинаковые записи со значениями:
a = 1, b = 2, c = null
a = 1, b = 2, c = null

Я знала, что c NULL используется не !=, а IS NULL и IS NOT NULL, так как для PostgreSQL NULL — это не значение, а маркер отсутствия данных. Но оказалось, что с null-значениями еще и уникальный индекс не видит конфликт.

И если мы хотим разрешить только одну такую запись a = 1, b = 2, c = null, то нужно сделать два частичных индекса уникальности:

1️⃣ Уникальность связки (a, b) where c is null
2️⃣ Уникальность связки (a, b, c) where c is not null

Частичные индексы — нечастая история, но могут пригодиться.

Читала, что в некоторых компаниях отказываются от constraints на уровне БД, таких как foreign keys и индексы уникальности, все необходимые проверки существуют только на уровне кода. Поделитесь, пожалуйста, как у вас?

🔗 — у нас созданы foreign keys и индексы уникальности в БД

💅 — у нас все проверки на уровне кода, индексы только для ускорения поиска