FTS5 в SQLite: полнотекстовый поиск без лишних сервисов

Поиск по своим текстам обычно начинают с LIKE '%слово%', а заканчивают отдельным поисковым сервисом, потому что LIKE — это полный скан таблицы, и на десятках тысяч строк он становится заметен. Между этими крайностями есть ступень, которую пропускают: FTS5 уже встроен в SQLite, не требует ни установки, ни процесса, ни сети, и даёт индекс с ранжированием.

У него есть ровно одна серьёзная проблема для русского текста, и её нужно понимать до того, как вы построите на нём поиск.

#Что понадобится

• SQLite со сборкой, включающей FTS5. Практически все современные сборки его включают; проверяется одной строкой (шаг 1).
• Таблица с текстом, по которому вы хотите искать. FTS5 не хранит данные вместо неё — он строит индекс поверх.
• Понимание, что стемминга для русского в комплекте нет. Это не настройка, которую забыли включить.

Отдельного расширения ставить не нужно: в отличие от векторного поиска, полнотекстовый — часть библиотеки. Разница принципиальная, и она объясняет, почему в Persona именно он выбран режимом по умолчанию.

#Шаг 1. Проверить наличие FTS5

python -c "import sqlite3; c=sqlite3.connect(':memory:'); c.execute('create virtual table t using fts5(x)'); print('ok')"

Напечаталось ok — можно работать. Ошибка no such module: fts5 означает урезанную сборку; лечится заменой сборки SQLite, а не настройкой.

#Шаг 2. Создать индекс поверх существующей таблицы

Самая важная развилка здесь — режим content. FTS5 умеет хранить копию текста у себя (проще, но данные дублируются) либо работать как внешний индекс над вашей таблицей. Второй вариант почти всегда правильный.

CREATE VIRTUAL TABLE IF NOT EXISTS chat_message_fts USING fts5(
    content,
    content='chat_message',
    content_rowid='id',
    tokenize='unicode61 remove_diacritics 2'
);

content='chat_message' говорит: текст живёт в таблице chat_message, здесь только индекс. content_rowid='id' связывает строки индекса с первичным ключом исходной таблицы. Выигрыш — нет дублирования текста и нет второго места, где данные могут разойтись с реальностью.

Плата — синхронизацию придётся вести самому.

#Шаг 3. Триггеры синхронизации

Внешний индекс не обновляется автоматически. Три триггера закрывают вставку, удаление и правку:

CREATE TRIGGER IF NOT EXISTS chat_message_ai AFTER INSERT ON chat_message
BEGIN
    INSERT INTO chat_message_fts(rowid, content) VALUES (new.id, COALESCE(new.content, ''));
END;

CREATE TRIGGER IF NOT EXISTS chat_message_ad AFTER DELETE ON chat_message
BEGIN
    INSERT INTO chat_message_fts(chat_message_fts, rowid, content)
    VALUES ('delete', old.id, COALESCE(old.content, ''));
END;

CREATE TRIGGER IF NOT EXISTS chat_message_au AFTER UPDATE ON chat_message
BEGIN
    INSERT INTO chat_message_fts(chat_message_fts, rowid, content)
    VALUES ('delete', old.id, COALESCE(old.content, ''));
    INSERT INTO chat_message_fts(rowid, content) VALUES (new.id, COALESCE(new.content, ''));
END;

Удаление из внешнего индекса записывается как специальная вставка со служебным значением 'delete' — синтаксис непривычный, но это единственный корректный способ: FTS5 нужно знать, какой именно текст был у удаляемой строки, чтобы вычесть его из индекса.

Отсюда важное практическое следствие, о котором забывают при чистке данных: если вы удаляете строки в обход триггера — например, каскадом от родительской таблицы, — индекс останется с записями об удалённом. В Persona поэтому сообщения при удалении аккаунта удаляются явным DELETE FROM chat_message, а не «за компанию» через каскад от сессии; это записано прямо в комментарии app/auth/account_delete.py как причина такого решения.

Разовое наполнение индекса по уже накопленным строкам:

INSERT INTO chat_message_fts(chat_message_fts) VALUES ('rebuild');

Команда идемпотентна: перестроить индекс можно в любой момент, это дешёвая операция восстановления, если что-то разъехалось.

#Шаг 4. Запрос с ранжированием

SELECT m.id, m.content, bm25(chat_message_fts) AS rank
  FROM chat_message_fts
  JOIN chat_message m ON m.id = chat_message_fts.rowid
 WHERE chat_message_fts MATCH ?
 ORDER BY bm25(chat_message_fts)
 LIMIT 40

Две вещи, на которых спотыкаются.

bm25() в SQLite отрицательный, и меньше — значит релевантнее. Проверяется за минуту: на восьми документах запрос по редкому слову даёт около −1.57, по слову, встречающемуся в четверти документов, — около −0.93. Поэтому сортировка идёт по возрастанию. Если вы напишете DESC, поиск не сломается — он будет молча возвращать наименее релевантное.

MATCH и bm25() требуют имени таблицы, а не псевдонима. Попытка написать fts.MATCH или bm25(fts) через алиас даёт ошибку «no such column». Это ограничение синтаксиса, а не ошибка запроса.

#Шаг 5. Главная проблема — русская морфология

FTS5 умеет стемминг только для английского (tokenize='porter'). Для русского в стандартной поставке нет ни стемминга, ни лемматизации. Токенизатор unicode61 просто режет текст по не-буквенным символам и приводит к нижнему регистру.

Практически это значит: «лендинг» не находит «лендингом». Проверяется на трёх строках:

Первые две строки дают рабочий обход: дописывать звёздочку к каждому токену запроса. Префиксный поиск покрывает окончания, потому что в русском они дописываются в конце. Persona так и делает — функция _fts_expr в app/chat/sessions.py очищает токен от небезопасных символов, приводит к нижнему регистру и добавляет *.

У приёма есть чёткая граница, видная в третьей строке таблицы: префикс расширяет запрос вперёд, а не назад. Запрос «отпуска» не найдёт «отпуск», потому что искомое короче запроса. То есть обход работает, когда пользователь вводит основу слова, и не работает, когда он вводит словоформу длиннее той, что в тексте. Практический вывод: обрезайте длинные токены запроса до основы либо принимайте эту потерю осознанно.

Четвёртая и пятая строки — отдельный неприятный сюрприз. Настройка remove_diacritics 2 снимает диакритику с латиницы, но ё и е остаются разными токенами. Регистр при этом сворачивается нормально: «ЁЛКА» находит «ёлка». Если ваши тексты пишутся то с ё, то без — а в русской переписке так всегда, — вы будете терять попадания. Рабочее решение одно: нормализовать ё в е и при записи в индекс, и в запросе.

#Шаг 6. Подсветка найденного

SELECT snippet(chat_message_fts, 0, '[', ']', '…', 16)
  FROM chat_message_fts WHERE chat_message_fts MATCH ?

Аргументы: номер колонки, обёртки до и после совпадения, многоточие для обрезанных краёв, число токенов в окне. Обёртки задавайте текстовые, а не HTML-теги, если результат потом идёт в шаблон, — иначе вы отдаёте наружу неэкранированную разметку.

#Проверка результата

1. Индекс наполнен. SELECT count(*) FROM chat_message_fts; — число должно совпадать с числом строк в исходной таблице. Расхождение означает, что часть данных попала в базу мимо триггеров.
2. Поиск быстрее скана. Сравните время MATCH-запроса и того же поиска через LIKE '%…%' на своём объёме. Если разница незаметна, у вас слишком мало данных, чтобы FTS5 окупился, — и это нормальный результат.
3. Морфология не режет выдачу. Возьмите десять реальных запросов и проверьте, находится ли нужное при вводе словоформы, отличной от той, что в тексте. Если нет — вы не включили префиксный поиск.

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

#Что может пойти не так

Индекс расходится с таблицей. Причина почти всегда одна — запись в обход триггеров: массовая вставка, каскадное удаление, ручная правка. Лечится командой rebuild, но лучше найти путь записи, который триггер обошёл.

Запрос падает на пользовательском вводе. FTS5 имеет собственный синтаксис: кавычки, NOT, NEAR, дефисы и двоеточия трактуются как операторы. Пользовательскую строку нужно чистить перед подстановкой, а не полагаться на параметризацию: параметр защищает от SQL-инъекции, но не от синтаксической ошибки FTS-выражения. В Persona токены очищаются регулярным выражением и приводятся к нижнему регистру именно поэтому — слова AND, OR, NOT в нижнем регистре перестают быть операторами.

Пустой результат при непустом запросе. Проверьте, не отфильтровали ли вы все токены: короткие слова длиной в одну букву обычно отбрасываются, и запрос из них даёт пустое выражение. Правильное поведение в этом случае — не выполнять MATCH, а откатиться на LIKE, иначе пользователь получит ошибку вместо результата.

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

#Частые вопросы

#Чем FTS5 отличается от LIKE?

LIKE '%слово%' перебирает все строки и не использует индекс. FTS5 строит инвертированный индекс и ищет по нему, поэтому время поиска почти не растёт с объёмом. Плюс к этому FTS5 даёт ранжирование, а LIKE — только факт совпадения. Краткое определение и границы применимости — в справке про FTS5.

#Как работает bm25 и почему он отрицательный?

Формула взвешивает документы по редкости слов запроса и по длине документа: чем реже слово и чем короче документ, тем выше вес. Реализация в SQLite возвращает значение с обратным знаком, чтобы обычная сортировка по возрастанию давала релевантное первым. Разбор самой формулы — в справке про bm25.

#Можно ли подключить нормальный русский стеммер?

Технически да — через собственный токенизатор, но это уже сборка расширения, то есть тот самый внешний компонент, ради отсутствия которого выбирали FTS5. Для большинства задач префиксный поиск плюс нормализация ё закрывают проблему дешевле.

#Насколько FTS5 хватает по объёму?

На персональных и командных архивах — с большим запасом; узким местом раньше станет что угодно другое. Где начинается граница и как её мерить, разобрано в материале про производительность FTS5 на миллионах строк.

#Подходит ли FTS5 для поиска по распознанному тексту со скриншотов?

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

#Как это встроено в память ассистента?

Полнотекстовый индекс — источник кандидатов для блока памяти, который подкладывается в промпт перед ответом. Общая схема слоёв описана в статье о том, как работает память у ИИ.

#Вывод

Берите FTS5, если данные уже лежат в SQLite и вам нужен поиск с ранжированием без второго сервиса, — он окупается почти сразу и не добавляет ни одной внешней зависимости. Не берите, если вам нужен настоящий морфологический разбор русского: префиксный обход закрывает окончания, но не приставки, не чередования и не однокоренные слова. И обязательно нормализуйте ё перед записью в индекс — иначе половина попаданий будет теряться молча, без единой ошибки в логах.