Всем привет,
Описываются базовые возможности, плюсы и минусы использования встроенного полнотекстового поиска СУБД Postgre на основе опыта его практического использования.
При разработке приложений, особенно web-приложений, в 95% возникает задача выбрать системы для управления как структурированным контентом, так и неструктурированными (текстовая информация произвольной структуры), а также данными мультимедиа (выходит за рамки данной статьи).
Архитектор приложения задается вопросом: совместить эти данные под управлением одной СУБД, либо же взять отдельное специализированное средство для каждого вида информации.
Существуют проверенные временем инструменты для индексирования и поиска неструктурированных текстовых данных — Django, Sphinx, Lucene, на Хабре есть хорошие авторские статьи на эту тему.
Преимущество в том, что это отдельная система и она приспособлена для своей задачи максимально хорошо.
Но есть и архитектурный минус такого решения — ведь структурная и описательная часть данных чаще всего связаны между собой, а следовательно придется сконструировать комбинированные запросы.
Рассмотрим на примере
Есть задача учета кандидатов-соискателей, которые присылают свои резюме в текстовом формате. Была изначально поставлена задача находить информацию по кандидатам на основании их навыков и практического опыта владения этими навыками.
Создаем реляционную модель (упрощенно):
рекрутеры принимают резюме, изучают их, заполняют наши таблички, после чего запрос вида «дай мне список кандидатов с опытом C (но не C++) не менее 2 года и не старше 100 лет» задаются примерно следующим образом:
SELECT candidate.name, skill.name, candidate_skill.expericence, candidate.phone, candidate.email FROM candidate, candidate_skill, skill
WHERE ( year(candidate.dob) > (year(now()) — 100) ) AND candidate.candidate_id = candidate_skill.candidate_id AND
candidate_skill.experience > 2 AND candidate_skill.skill_id = skill.skill_id AND skill.name = 'C' ORDER BY candidate_skill.experience DESC;
Всех находит, все довольны.
Затем случается неожиданность — заказчик из Тмутаракани требует специалиста с опытом работы на C, но чтобы опыт был обязательно в Тмутаракани.
У нас тексты резюме проиндексированы в системе Shpinx, например, так что мы можем быстро найти всех кандидатов с опытом работы в Тмутаракани, но при поиске по C уже возникнут сложности (попадут C++, C-Sharp и всякое другое C). Младшему рекрутеру светит вручную перелопатить многие тысячи кандидатов с опытом работы в Тмутаракани, чтобы найти у кого есть там опыт 2 года на C (сам такое видел не раз).
Но не обязательно — если изначально была благоразумно выбрана СУБД Postgre, выручит текстовая колонка Resume: text в таблице Candidate — туда рекрутеры с самого начала тупо копипастили текст резюме, на всякий случай.
Надо заставить систему искать по текстовому полю. Что сделать:
1. Postgre устанавливается без словарей русского языка, поэтому их нужно отдельно скачать например тут. Если база данных (что скорей всего) в UTF-8 — придется еще переконвертировать в utf-8, вот так:
iconv -f koi8-r -t utf-8 < ru_RU.aff > russian.affix
iconv -f koi8-r -t utf-8 < ru_RU.dic > russian.dict
Получившиеся файлы скопировать в подпапку tsearch_data папки, где у вас установлена Postgre.
2. создать словарь и конфигурацию для русского языка:
CREATE TEXT SEARCH DICTIONARY russian_ispell (Template = ispell, DictFile = russian, AffFile = russian, StopWords = russian );
CREATE TEXT SEARCH CONFIGURATION ru ( Copy = russian );
Для полноты
ALTER TEXT SEARCH CONFIGURATION ru ALTER MAPPING FOR hword, hword_part, word WITH russian_ispell, russian_stem;
ALTER TEXT SEARCH CONFIGURATION ru ALTER MAPPING FOR asciihword, asciiword, hword_asciipart WITH english_ispell, english_stem;
После этого уже можно получить результат, доработав наш запрос
SELECT Candidate.Name, Skill.Name, Candidate_Skill.Expericence, Candidate.Phone, Candidate.Email FROM Candidate, Candidate_Skill, Skill
WHERE ( year(Candidate.DoB) > (year(now()) — 100) ) AND Candidate.Candidate_id = Candidate_Skill.Candidate_id AND
Candidate_Skill.Experience > 2 AND Candidate_Skill.Skill_id = Skill.Skill_id AND Skill.Name = 'C' AND
to_tsvector('ru',Candidate.Resume) @@ to_tsquery('ru','Тмутаракань')
ORDER BY Candidate_Skill.Experience DESC;
3. Операция to_tsvector трудоемкая, и каждый раз при запросе конвертировать всё резюме для каждой строчки нерационально, создание индекса типа GIN по полю Resume, сконвертированному в тип данных tsvector, решит этот вопрос.
Для этого сначала создаем поле fulltext: tsvector в таблице Candidate, затем создаем триггер, который будет заполнять это поле на основании значения поля Resume при создании или изменении записи Candidate — не будем загромождать кодом, всё стандартно — в триггере присвоить fulltext := to_tsvector('ru',NEW.resume)
Затем создаем индекс по полю Fulltext:
CREATE INDEX candidate_fulltext ON candidate USING gin(fulltext);
GIN — специальный тип индекса, для данных типа tsvector и для массивов (всего в Postgre 9.3 уже 5 разных типов индексов, подробно можно узнать изучив доку Postgre).
Запрос тогда принимает вид:
SELECT Candidate.Name, Skill.Name, Candidate_Skill.Expericence, Candidate.Phone, Candidate.Email FROM Candidate, Candidate_Skill, Skill
WHERE ( year(Candidate.DoB) > (year(now()) — 100) ) AND Candidate.Candidate_id = Candidate_Skill.Candidate_id AND
Candidate_Skill.Experience > 2 AND Candidate_Skill.Skill_id = Skill.Skill_id AND Skill.Name = 'C' AND
Candidate.fulltext @@ to_tsquery('ru','Тмутаракань')
ORDER BY Candidate_Skill.Experience DESC;
Вот и всё — запрос выдаст точнейший из возможных результат по заданному критерию.
Плюсы и минусы
Главный плюс очевиден — компактность и точность запросов за счет комбинирования структурных и полнотекстовых критериев. Ради него и сыр-бор.
Еще один плюс — в установке и поддержке Sphinx уже нет необходимости.
Поиск по готовому индексу GIN очень быстрый, в сложных запросах Postgre умеет его «склеивать» с другими индексами (хотя сам GIN составным быть не может).
Минусы тоже очевидны
— необходимо содержать дополнительное поле tsvector (немаленькое), нужен триггер, нужен дополнительный индекс. Потери производительности при вставке и обновлении записей будут довольно ощутимыми.
— дополнительная нагрузка на сервер Postgre
На одной чаше весов у нас 1) простота, точность и скорость запросов SQL 2) минус 1 система в ландшафте,
на другой — 1) несколько дополнительных объектов в схеме БД, 2) замедление производительности запросов DML.
Если есть сомнения, что это будет хорошо работать — не стоит тогда сразу сносить Sphinx. Но мы свой снесли уже очень давно, всё прекрасно без сбоев под нагрузкой работает.
Автор: eugenebartosh
