При реализации паттерна мягкого удаления с использованием булевого флага, почему добавление этого флага в составной индекс иногда ухудшает производительность запросов active-record по сравнению со стратегией частичного индекса, и как отличается оценка селективности планировщика запросов между этими подходами?

Ответ на вопрос

История вопроса

Паттерны мягкого удаления появились как альтернатива жесткому удалению для аудиторских отслеживаний и восстановления данных. Ранние реализации использовали простые булевые флаги или временные метки, но разработчики вскоре столкнулись с ухудшением производительности, когда эти флаги были включены в стандартные индексы B-дерева. Проблема стала актуальной с широким внедрением частичных индексов PostgreSQL и отфильтрованных индексов SQL Server в середине 2000-х годов, которые позволили индексировать только активные записи. Понимание оценки селективности — как планировщик запросов предсказывает процент строк, соответствующих условию — стало критически важным при сравнении полных составных индексов с частичными стратегиями индексации.

Проблема

Когда флаг мягкого удаления (например, is_deleted) добавляется в составной индекс, такой как (is_deleted, user_id, created_at), оптимизатор базы данных может ошибочно рассчитать селективность строк для запросов, фильтрующих WHERE is_deleted = false. Если 90% строк активны, оптимизатор может выбрать последовательное сканирование вместо сканирования индекса, или, наоборот, если распределение искажено, он может неправильно предпочесть индекс. Частичные индексы (WHERE is_deleted = false) хранят только активные строки, гарантируя высокую селективность, но стандартные составные индексы хранят все строки, что приводит к раздутию индекса и неоднозначным оценкам кардинальности, когда статистика не точно отражает распределение мягкого удаления.

Решение

Реализуйте частичные индексы (в PostgreSQL) или отфильтрованные индексы (в SQL Server), которые полностью исключают строки с мягким удалением, в сочетании с отдельными индексами для удаленных данных при необходимости. Для MySQL или баз данных, не имеющих частичной индексации, используйте составной индекс с флагом мягкого удаления в качестве ведущего столбца только если активный набор данных мал; в противном случае разделите таблицу по статусу удаления. Явно анализируйте статистику таблицы после массовых удалений, чтобы предотвратить устаревшие гистограммы. При запросах активных записей используйте точное предикат из определения частичного индекса (WHERE is_deleted = false), чтобы гарантировать, что оптимизатор распознает применимость индекса.

Пример кода

-- PostgreSQL: Частичный индекс только для активных записей
CREATE INDEX idx_active_users_email 
ON users(email) 
WHERE is_deleted = false;

-- SQL Server: Эквивалент отфильтрованного индекса
CREATE INDEX IX_Active_Users_Email 
ON Users(Email) 
WHERE IsDeleted = 0;

-- Запрос, использующий частичный индекс
SELECT * FROM users 
WHERE email = 'alice@example.com' 
AND is_deleted = false;

Ситуация из жизни

Платформа SaaS, управляющая 10 миллионами записей пользователей, столкнулась с серьезными замедлениями в своей административной панели при фильтрации активных пользователей по дате создания. Изначально они использовали составной индекс (is_deleted, created_at) в PostgreSQL, полагая, что это ускорит запросы WHERE is_deleted = false ORDER BY created_at. Однако, когда набор данных увеличился до 80% исторических учетных записей с мягким удалением, запросы начали занимать 8-12 секунд, потому что планировщик недооценил стоимость сканирования раздутого индекса.

Решение A: Поддерживать составной индекс и принудительно использовать его с помощью подсказок. Этот подход использовал SET enable_seqscan = off или подсказки плана запроса, чтобы принудить использование индекса. Хотя это временно улучшило конкретные запросы, это создало долг по поддержке и часто заставляло применять неоптимальные планы для других паттернов доступа при изменении распределения данных. Решение также не решило основную проблему раздувания индекса, вызывающего увеличение объема хранилища и дополнительную нагрузку VACUUM.

Решение B: Создать отдельные частичные индексы для активных и удаленных записей. Реализация CREATE INDEX idx_active_created ON users(created_at) WHERE is_deleted = false уменьшила размер индекса на 80% и позволила планировщику точно оценить 2 миллиона активных строк против 8 миллионов удаленных. Время выполнения запросов снизилось до 40 мс, но потребовалась переработка всех запросов приложений, чтобы гарантировать, что предикат is_deleted = false оставался явным и не был завернут в функции или скрыт за представлениями, затемняющими условие.

Команда выбрала Решение B, потому что оно обеспечивало устойчивую производительность без необходимости поддерживать подсказки запросов. Результатом стало снижение задержки запросов на 95% и устранение периодических проблем с раздутием VACUUM, вызванных прежним чрезмерным составным индексом. Мониторинг подтвердил постоянные времени отклика менее одной секунды для основных сценариев использования панели управления.

Что кандидаты часто упускают

Как наличие значений NULL в столбце временной метки мягкого удаления (используя NULL для активных, временную метку для удаленных) влияет на использование частичных индексов по сравнению с подходами с булевым флагом?

При использовании nullable deleted_at временной метки частичные индексы, такие как WHERE deleted_at IS NULL, сталкиваются с проблемами при обработке NULL индексируемостью в PostgreSQL. В отличие от булевых флагов, где = false является явным и sargable, условия IS NULL требуют, чтобы планировщик признал применимость индекса, что может не сработать, если запрос использует параметризованные операторы, где планировщик не может доказать, что параметр будет NULL. Кроме того, обновления с установкой deleted_at = CURRENT_TIMESTAMP вызывают раздувание индекса в частичном индексе для активных записей, так как строки удаляются из него, в то время как обновления булевых флагов переключают бит, но остаются в рамках полного составного индекса. Nullable подход требует более частых вызовов ANALYZE и тщательного рассмотрения фактора заполнения индекса, чтобы справляться с высокой текучестью изменений статуса удаления.

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

Покрывающие индексы (при использовании клаузулы INCLUDE в PostgreSQL 11+ или SQL Server) добавляя is_deleted, чтобы избежать обращений к таблице, на самом деле ухудшают производительность записи, так как каждая операция мягкого удаления (UPDATE) должна модифицировать несколько структур индекса. Когда пользователь мягко удаляется, база данных должна пометить старую запись индекса как «мертвую» в активном частичном индексе, вставить новую запись в любые индексы удаленных записей и обновить указатели кучи покрытия индекса. Кандидаты часто упускают, что частичные индексы изолируют эту текучесть — только конкретные частичные индексы для активных или удаленных состояний модифицируются — тогда как покрывающие индексы на основной таблице требуют обновления структуры основного индекса независимо от статуса мягкого удаления, создавая амплификацию записи, что влияет на пропускную способность транзакций.

Когда планировщик запросов игнорирует частичный индекс для мягко удаленных данных, даже когда запрос явно фильтрует удаленные записи?

Если частичный индекс определяется как WHERE is_deleted = true для аудиторских запросов, но приложение использует подготовленное выражение с параметром $1 для активных и удаленных запросов, PostgreSQL может закэшировать общий план, который не распознает частичный индекс для конкретного случая true. Это происходит, потому что подготовленные выражения формируют планы до связывания значений параметров, и оптимизатор не может доказать, что $1 = true всегда будет соответствовать предикату индекса. Кандидаты упускают, что динамический SQL или подсказки компиляции (OPTION (RECOMPILE) в SQL Server, выполнение с литеральными значениями в PostgreSQL) требуются, чтобы гарантировать, что планировщик видит конкретное значение и сопоставляет его с предикатом частичного индекса, а не полагается на общие планы, которые по умолчанию приводят к последовательным сканированиям из-за неопределенности значения параметра.