Как использовать уникальные частичные индексы, чтобы избежать условного дублирования
Когда вам нужно иметь дело с параллелизмом, базы данных уникальных записей - ваш друг. Уникальные индексы - лучший способ избежать несогласованности и дублирования данных.
Здесь нет новостей, правда? OK. Уникальные индексы используются большинством разработчиков.
Что, если нам нужен условный уникальный индекс? Одно поле должно быть уникальным на основе значения другого поля.
Все началось с одной реальной потребности в моей работе. Нам нужно было создать функцию переключения. Пользователи могут включать или отключать данную функцию, но нам необходимо отслеживать, когда происходит включение и отключение.
Простое логическое поле не соответствовало нашим потребностям, поэтому мы создали следующую таблицу:
CREATE TABLE toggles ( user_id integer NOT NULL, type varchar(255) NOT NULL, enabled_at timestamp NOT NULL, disabled_at timestamp, );
Когда пользователь включает какую-либо функцию, будет создана новая запись переключения. Если пользователь отключает эту функцию, включенная запись переключения будет обновлена с указанием даты disabled_at.
Одна из наших задач - проектировать программное мышление с высокой степенью согласованности. Этого может никогда не случиться, но что, если пользователь дважды щелкнет по переключателю, и мы сделаем два асинхронных запроса, чтобы включить переключатель? Это тот безумный вопрос, который мы задаем себе во время планирования.
Первое, что пришло нам в голову, это решить проблему такого рода с помощью стратегий блокировки. Но вскоре я понял, что PostgreSQL предлагает нам лучшее решение.
Добро пожаловать в частичные индексы!
Уникальные индексы полезны, чтобы избежать дублирования записей, но нам нужен условный уникальный индекс.
У одного пользователя должен быть включен только один тип переключателя. У него может быть столько отключенных переключателей, сколько ему нужно, но только один должен быть включен одновременно.
Нам нужен уникальный индекс для user_id и тип только тогда, когда disabled_at имеет значение null. Это были частичные индексы.
Частичный индекс - это индекс, построенный по подмножеству таблицы; подмножество определяется условным выражением (называемым предикатом частичного индекса). Индекс содержит записи только для тех строк таблицы, которые удовлетворяют предикату.
Частичные индексы удобны, когда вам нужно отфильтровать определенное подмножество данных. Это индекс, как и любой другой, с областью действия, и индексы могут быть уникальными…;)
Вот как мы создаем частичный индекс:
CREATE UNIQUE INDEX unique_enabled_user_toggle ON toggles (user_id, type) WHERE disabled_at IS NULL;
Вы можете объявить условие, при котором должен применяться индекс, и база данных сделает за нас все волшебство.
Тестирование индекса
Чтобы проверить наше решение, давайте попробуем вставить несколько записей в базу данных:
INSERT INTO toggles (user_id, type, enabled_at) VALUES (1, 'async download', now());
Пользователь включил функцию асинхронной загрузки. Он не может дважды включить один и тот же переключатель. Если мы запустим ту же самую вставку еще раз, мы получим следующую ошибку:
ERROR: duplicate key value violates unique constraint "unique_enabled_user_toggle" DETAIL: Key (user_id, type)=(1, async download) already exists. Query failed PostgreSQL said: duplicate key value violates unique constraint "unique_enabled_user_toggle" Detail: Key (user_id, type)=(1, async download) already exists.
Наш индекс пока работает очень хорошо!
Давайте вставим другой тип переключателя для того же пользователя:
INSERT INTO toggles (user_id, type, enabled_at) VALUES (1, 'reduce bandwith', now());
Тоже норм. Чтобы проверить полную функцию, мы должны попытаться вставить отключенный переключатель, а затем включить тот же переключатель. Мы ожидаем вставить две записи.
INSERT INTO toggles (user_id, type, enabled_at, disabled_at) VALUES (2, 'reduce bandwith', now(), now()); INSERT INTO toggles (user_id, type, enabled_at) VALUES (2, 'reduce bandwith', now());
Это сработало!
Заключение
Частичные индексы очень полезны для достижения производительности, когда вам нужно иметь дело с условными запросами, и их можно использовать, чтобы избежать дублирования при использовании уникальных индексов.
Надеюсь, вам понравилось, и увидимся в следующий раз!
Использованная литература:
Кредиты изображения:
