Организация полнотекстовая поиск в postgresql. Использование полнотекстового индексирования и поиска в PostgreSQL. Плюсы и минусы

16.03.2019

Windows 7

Перевод

FreeBSD хороша для серверов, но не для настольных компьютеров

У FreeBSD есть полнофункциональная звуковая подсистема c низкой задержкой, а смешивание в ядре позволяет нескольким приложениям воспроизводить звуки одновременно (с независимыми настройками громкости) без дополнительных настроек. Дефолтные настройки включают в себя X.org и настройки рабочего стола, такие как KDE или GNOME, это так же просто как выбрать метапакет в зависимости от того, какой вы предпочитаете.

Если даже это кажется слишком сложным, PC-BSD - полнофункциональная настольная система, созданная поверх FreeBSD с простым в использовании установщиком и опцией коммерческой поддержки.

FreeBSD использует модель закрытого развития

FreeBSD развивают более 400 разработчиков по всему миру, все они имеют полный доступ ко всей системе и данным этой ОС. Третьи лица так же зачастую фиксят родные патчи. Если Вы хотите увидеть количество исправленных патчей, вы можете поискать "Submitted by" в логах фиксации.

Нет никаких жестких рамок для FreeBSD. Решения приняты людьми готовыми сделать работу. Если все же возникают споры, то они разрешаются группой разработчиков, которые избираются каждые два года. Обязательным критерием, по которому избираются разработчики, является обязательное исправление или доработка кода проекта в прошлых годах.

FreeBSD - Просто OS X без красивого GUI интерфейса

Это такой же миф про OS X, как и про FreeBSD: OS X просто FreeBSD с красивым графическим интерфейсом. Эти две операционных системы действительно совместно используют часть кода, например большинство утилит пользовательского пространства и библиотека C по OS X получены из версий FreeBSD. Часть этого кода развивается в разное время и в разных направлениях, например FreeBSD 9.1 позднее включила стек C++ и компилятор, который первоначально были разработаны для OS X сотрудниками Apple. Так же есть и кардинально разные детали.

Ядро XNU, которое используется на OS X включает в себя несколько подсистем из более старых версий FreeBSD, но в основном считаются независимой реализацией. Но все же за счет своего сходства продукты реализованные на OS X значительно проще адаптировать под FreeBSD. Например libdispatch и libc ++ были записаны для OS X и работали на FreeBSD раньше, чем на любой другой ОС.

В FreeBSD все необходимо компилировать с исходного кода

Коллекция портов FreeBSD представляет собой очень мощный способ установки программного обеспечения, что позволяет настраивать параметры для различных сторонних программ и библиотек. Однако, это не единственный способ установки программного обеспечения на FreeBSD. Всегда можно установить программное обеспечение из бинарных пакетов. Проект pkgng добавил новый формат пакета и инструмент управления пакетами, обеспечив современный набор инструментов для двоичного управления.

Вы можете установить pkgng из портов (ports-mgmt/pkg) на более старых версиях FreeBSD. Она включена по умолчанию на FreeBSD 9.1 и более поздние версии.

FreeBSD это UNIX из 90-х (или 80-ых)

FreeBSD - линейный потомок исходного UNIX через Распределение программного обеспечения Беркли, но она продолжала развиваться отдельно. За последние несколько лет мы видели, что ZFS становится значительно производительней: поддержка 10 ГБ, 40 ГБ и 100 ГБ канала, улучшеная звуковая подсистема, поддержка 802.11n и других улучшений.

Это не означает, что FreeBSD отказалась от своих UNIX корней. Есть много причин, почему UNIX системы стали популярными. Они включают в себя свободно распространяемую систему, которую легко портировать на новые платформы, набор простых инструментов и ядро, которое хорошо зарекомендовало себя на различных платформах. FreeBSD поддерживает эти традиции.

Весь хороший код в FreeBSD родом из Solaris

FreeBSD импортировала две функции высокого профиля из OpenSolaris: DTrace и ZFS. Обе теперь хорошо поддерживаются FreeBSD. ZFS, в частности, находится в центре внимания многих разработчиков FreeBSD, в том числе тех, которые используются iXsystems, компанией, которая поддерживает развитие FreeNAS и продает коммерческие NAS устройства на базе FreeBSD. Разработчики FreeBSD также работают в тесном сотрудничестве с разработчиками Illumos, одной из веток с открытым исходным кодом Solaris, для улучшения обеих этих функций.

Несмотря на преимущества ZFS, это по-прежнему относительно небольшая часть общей системы. ZFS и DTrace составляют менее чем 4% кода в ядре, которое равно почти 10% кода в основной системе. Если считать, что только 0.4% FreeBSD хороши, то система не приобрела бы такой популярности.

FreeBSD не имеет драйверов

Это проблема, с которой сталкиваются все операционные системы, - даже новые версии Windows. Большую часть времени пользователи не заботятся об общем количестве драйверов, только если драйверы уже установлены по умолчанию. Есть некоторые упущения с точки зрения поддержки драйверов, но FreeBSD поддерживает широкий спектр сетевых карт (в том числе 802.11n чипсеты), большинство звуковых карт AMD, Intel и графические процессоры NVIDIA.

Поддержка аппаратных средств - это та часть реализации, которая требует постоянной доработки, потому что нельзя просто сказать производителям аппаратных средств, чтобы они подождали пару лет, пока разработчики ПО их догонят. Обеспечение поддержки новых устройства занимает время для настройки, хотя некоторые производители сами обеспечивают драйверы, например Nvidia обеспечивает драйверы для их GPU и Intel для новейших сетевых контроллеров. Другие производители обеспечивают помощь в разработке драйверов FreeBSD, включая Broadcom, JMicron, HP, Mellanox, Chelsio и Solarflare. Если Вы знаете устройства, которые не поддерживаются FreeBSD, лучше сообщить про это разработчикам и производителям устройств. Как правило, лучшим толчком для решения проблемы со стороны производителей - сообщение о том, что их клиенты не могут использовать их продукты.

FreeBSD 4.x лучшее из всего что было

4.x релиз был самым стабильным и FreeBSD гордились тем, что они смогли реализовать такой продукт. Многие пользователи продолжали использовать его на протяжении многих лет. Серия 5.x вышла во время перехода к оптимизации многопоточности. Эта включало замену одной блокировки вокруг ядра с рядом меньших блокировок, которые используются отдельными подсистемами. Это требовало большого куска работы, что не могло не привести к некоторым ошибкам. 5.x поставлялась с двумя реализациями поточной обработки, а это еще больше осложняло ситуацию. Первые два выпуска в серии 5.x были отмечены «только разработчикам», но 5.2 была нацелена на более широкую аудиторию и не оправдала ожиданий пользователей системы FreeBSD. Ряд крупных пользователей решили не менять серию 4.x.

Серия 5.x была болезненным уроком для проекта. В серии 6.x восстановили стабильность выпуска 4.x., а в серии 7.x восстановили производительность одного процессора. Во время выпуска серии 8.x можно было увидеть ряд опубликованных третьими лицами тестов, которые продемонстрировали лучшую масштабируемость FreeBSD на многопроцессорных системах, чем любая другая операционная система.

Все эти релизы имели значительное количество улучшений, такие как улучшенная звуковая подсистема, ZFS, DTrace, ведение журналов UFS и многое другое, но стабильность и производительность остались ключевыми задачами системы FreeBSD.

Недостатки программного обеспечения FreeBSD

Коллекция FreeBSD в настоящее время содержит более 26 000 единиц программного обеспечения. Трудно сравнить это число с другими репозиториями, потому что программы разделены по-другому (например, GCC порт в FreeBSD устанавливает программы и библиотеки, которые разделены между 6-10 пакетами в Debian, в зависимости от версии GCC), но большинство вещей Вы все же можете там найти. Одной из причин, по которой пользователи выбирают FreeBSD является тот факт, что набор портов обеспечивает определенную, относительно неясную часть программного обеспечения, в котором она нуждается, в то время как другие системы этого не делают.

Большая часть программного обеспечения в наборе портов работает исходно на FreeBSD. Большая часть программного обеспечения с открытым исходным кодом - агностик относительно ОС и требует, чтобы минимальная модификация была скомпилирована и работала на FreeBSD. Есть исключения такие как Valgrind, которые требуют детального понимания системы. Проприетарное программное обеспечение может быть более серьезной проблемой. Некоторые разработчики, такие как Opera, обеспечивают FreeBSD своим исходным кодом.

Другое программное обеспечение должно работать в режиме эмуляции. Например, бинарные файлы Linux могут работать на уровне Linux ABI, где системные вызовы Linux переводятся на их эквиваленты FreeBSD. Единственный изъян - немного увеличенная нагрузка системных вызовов; обычно сложно измерить различие в производительности между выполнением программ Linux на Linux и на FreeBSD: в некоторых случаях программы работают быстрее на FreeBSD, чем на Linux из-за более эффективных реализаций базовых вызовов. Например, версия Linux Flash-плагина может работать, используя NSPluginWrapper на уровне Linux ABI с собственным веб-браузером.

Аналогичное решение существует для запуска Windows приложений.

FreeBSD не поддерживает виртуализацию

FreeBSD 9 работает как гость Xen (domU) и на x86, и на x86-64, включая Amazon EC2. Благодаря работе, проделанной совместно с Microsoft, NetApp и Citrix, FreeBSD можно запускать на Hyper-V гипервизора Microsoft. FreeBSD 11 будет включать Dom0 поддержку управления домена.

FreeBSD также поддерживает VirtualBox и в качестве гостя и в качестве хоста. Вы можете найти гостевые дополнения VirtualBox, а затем и сам гипервизор в наборе портов. FreeBSD 10 также работает в качестве хоста операционной системы для гипервизора BSD, давая множество возможностей для запуска FreeBSD виртуальных машин на базе FreeBSD.

И наконец, если Вам не требуется полная виртуализация, то для выполнения изолированных пространств пользователя FreeBSD (или даже пространств пользователя Linux, используя уровни Linux ABI) на одном ядре FreeBSD Вы можете использовать подсистему контейнеров. Контейнеру можно даже предоставить их собственный независимый сетевой стек и т.д., и таким образом, одна машина может использоваться, чтобы эмулировать целый парк машин.

Лицензия BSD способствует взаимному сотрудничеству

Пока разработчики кода FreeBSD не подадут на вас иск о нарушении авторских прав, Вы вольно можете его использовать. Но, если верить утверждениям самих разработчиков, такого не произойдет.

Некоторые компании, почти наверняка возьмут наш код, изменят его и никогда ничего не предоставят взамен.

Рассмотрим в качестве примера случай двух крупнейших интернет-компаний: Google и Yahoo! Прежде их внутренняя инфраструктура основывалась на операционной системе GPL, в то время как более поздние версии уже используют FreeBSD. Поскольку Google не распределяет их измененную операционную систему, они могут сохранить к примеру GoogleFS в частной собственности. В таких случаях как этот, где программное обеспечение разработано для внутреннего использования, по лицензионному соглашению компания не обязана раскрывать свои доработки для разработчиков FreeBSD.

Существуют, однако, некоторые проблемы с соединением: например, нельзя использовать библиотеку с лицензией GPL, если уже используется BSD.

На протяжении многих лет не мало компаний внесло значительный вклад в FreeBSD. И вызвано это не только чувством альтруизма, ведь поддержка любого проекта, который быстро развивается, очень дорогое удовольствие.

По русификации операционной системы FreeBSD прошло довольно много времени, которое вносит в нашу жизнь и в администрирование некоторые коррективы. Об этих изменениях и пойдёт речь ниже…

После недавнего обновления до FreeBSD 11-RELEASE обнаружил, что некоторые настройки по русификации серверной ОС не работают. В логах ошибок при загрузке появились сообщения о невозможности загрузки кодовой страницы и т.д.

Как оказалось, во FreeBSD начиная с релиза 10.1, внедрили новый драйвер консоли – vt (newcons). До этого же момента использовался драйвер консоли syscons . Про новый драйвер (который теперь внедрили в ядро GENERIC и сделали драйвером системной консоли по умолчанию – отсюда и ошибки) вы можете почитать .

Драйвер vt(4) может работать как в текстовом, так и в графическом режиме VGA , благодаря последнему и реализована поддержка UTF -8 в консоли (чем мы в последствии и воспользуемся). Также реализована интеграция с KMS видео драйверами для переключения между Xorg и виртуальными терминалами.

Приступим к выполнению локализации (все операции производились на FreeBSD 11.0-RELEASE -p2 ). Дадим команду для выявление всех доступных русских локалей в системе:

# locale -a | grep ru ru_RU.CP1251 ru_RU.CP866 ru_RU.ISO8859-5 ru_RU.KOI8-R ru_RU.UTF-8

Будем использовать локаль ru_RU.UTF-8 .

1. Русификация локальной консоли (данный пункт не требуется и не влияет на работу удаленных text-mode эмуляторов терминалов, таких как putty или openssh)

Все доступные русские keymaps теперь находятся по пути:

# ls -Al /usr/share/vt/keymaps/ | grep ru -r--r--r-- 1 root wheel 16228 11 окт. 13:43 ru.kbd -r--r--r-- 1 root wheel 16234 11 окт. 13:43 ru.shift.kbd -r--r--r-- 1 root wheel 16232 11 окт. 13:43 ru.win.kbd

Выбирайте из этого для указания в /etc/rc.conf , исходя из следующего:

keymap=“ru” — переключение на русский клавишей CAPS Lock;
keymap=“ru.shift” — переключение на русский клавишей CAPS Lock, но верхний ряд на клавиатуре by default будет цифровой;
keymap=“ru.win” — переключение на русский клавишами CTRL +SHIFT.

Фонты находятся по пути:

# ls -Al /usr/share/vt/fonts/ total 92 -r--r--r-- 1 root wheel 8452 12 авг. 2015 gallant.fnt -r--r--r-- 1 root wheel 36408 11 окт. 13:43 vgarom-16x32.fnt -r--r--r-- 1 root wheel 8742 12 авг. 2015 vgarom-8x14.fnt -r--r--r-- 1 root wheel 9864 12 авг. 2015 vgarom-8x16.fnt -r--r--r-- 1 root wheel 5384 12 авг. 2015 vgarom-8x8.fnt -r--r--r-- 1 root wheel 5400 12 авг. 2015 vgarom-thin-8x16.fnt -r--r--r-- 1 root wheel 2704 12 авг. 2015 vgarom-thin-8x8.fnt

vgarom-8×8.fnt - матрица 8×8 с поддержкой русских символов;
vgarom-8×14.fnt - матрица 8×14 с поддержкой русских символов;
vgarom-8×16.fnt - матрица 8×16 с поддержкой русских символов (используется по умолчанию);
vgarom-16×32.fnt - матрица 16×32 с поддержкой русских символов (для wide мониторов).

Таким образом, в файле /etc/rc.conf наши настройки должны выглядеть примерно так:

Nano -w /etc/rc.conf ... keymap="ru.win" font8x16="vgarom-8x16" #можно не указывать...

Если вы используете ядро GENERIC , для однозначного включения нового драйвера консоли внесите в /boot/loader.conf следующее:

# echo "kern.vty=vt" >> /boot/loader.conf

Или же при сборке своего собственного ядра закомментируйте строки с vga и sc , оставив относящееся к vt без изменения:

Nano -w /usr/src/sys/amd64/conf/MYKERNEL ... #device vga # VGA video card driver # syscons is the default console driver, resembling an SCO console #device sc # vt is the new video console driver device vt device vt_vga device vt_efifb ...

Тип эмулируемого терминала для UTF -8 консоли только xterm ! Так что /etc/ttys в части касающейся должен выглядеть так:

# less /etc/ttys | grep xterm # For virtual consoles, the correct type is typically xterm. ttyv0 "/usr/libexec/getty Pc" xterm on secure ttyv1 "/usr/libexec/getty Pc" xterm on secure ttyv2 "/usr/libexec/getty Pc" xterm on secure ttyv3 "/usr/libexec/getty Pc" xterm on secure ttyv4 "/usr/libexec/getty Pc" xterm on secure ttyv5 "/usr/libexec/getty Pc" xterm on secure ttyv6 "/usr/libexec/getty Pc" xterm on secure ttyv7 "/usr/libexec/getty Pc" xterm on secure ttyv8 "/usr/local/bin/xdm -nodaemon" xterm off secure

2. Локализация login shells и использование login class

При использовании в русификации login класса необходимо привести настройки класса “russian” в файле /etc/login.conf к следующему виду:

Nano -w /etc/login.conf ... russian|Russian Users Accounts:\ :charset=UTF-8:\ :lang=ru_RU.UTF-8:\ :tc=default: ...

После чего обязательно перестройте хеш-базу командой:

# cap_mkdb /etc/login.conf

Теперь осталось перевести необходимых пользователей на этот новый класс командой:

# pw usermod -n max -L russian

Проверить сделанное можно командой vipw:

# vipw ... max:passwd:0:0:russian:0:0:User Name:/home/max:/usr/local/bin/bash ...

Для настройки русификации в shell (я использую bash), в файл /etc/profile внесите следующие строки:

# nano -w /etc/profile LANG=ru_RU.UTF-8; export LANG MM_CHARSET=UTF-8; export MM_CHARSET

В домашнем каталоге пользователей создаём файл.login_conf и заносим туда следующие строки:

Me:My Account:\ :charset=UTF-8:\ :setenv=LC_ALL=ru_RU.UTF-8:\ :lang=ru_RU.UTF-8:

Вот, в принципе, и всё.

Проверяем вывод locale:

# locale LANG=ru_RU.UTF-8 LC_CTYPE="ru_RU.UTF-8" LC_COLLATE="ru_RU.UTF-8" LC_TIME="ru_RU.UTF-8" LC_NUMERIC="ru_RU.UTF-8" LC_MONETARY="ru_RU.UTF-8" LC_MESSAGES="ru_RU.UTF-8" LC_ALL=ru_RU.UTF-8

И какой-нибудь простенькой команды:

Забавно, что на момент написания статьи handbook ещё не был поправлен…

Часто, для подсобных нужд, бывает нужно использовать хоть плохенький но все-таки полнотекстовый поиск по БД. Понятно что, «серьезные пацаны» используют для этого всякие Sphinx и ElasticSearch , но у этих штук есть одна премерзкая особенность: их надо настраивать, загонять в них данные и вообще мониторить их потребности. Да и если у вас в базе всего полсотни тысяч записей — использовать эти «махины» не сильно то удобно. Особенно если не хотите выходить за пределы Django.

Выход есть и он достаточно простой. Любая современная база данных (Mongo — ага, lol) имеет в себе встроенный полнотекстовый поиск. Не шибко умный, зато «изкоробки». Так как MySQL последние несколько лет не работал (и слава богу) расскажу на примере PostgreSQL. Начнем с версии. Вообще для постгреса использование свежих версий не «прихоть админа», а реальная необходимость. Связано это с достаточно активной деятельностью направленной на улучшение работы и вводу новых фич в движек. Так что я советую проводить эксперименты, да и вообще работать с самой свежей версией СУБД.

Итак, приступим.

Допустим у нас есть какая то Django-модель:

Class Element(models.Model): class Meta: db_table = "products" parent = models.ForeignKey(Section, null=True, blank=True) title = models.CharField(max_length=200) description = models.TextField(null=True, blank=True) article = models.CharField(max_length=200, null=True, blank=True) def __unicode__(self): return "%s/%s" % (self.parent.title, self.title)

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

Для начала разберем как это делается на чистом SQL:

SELECT *, ts_rank(to_tsvector("russian", title||desctription), to_tsquery("russian", "шампунь|против|перхоти")) as rank FROM "Products" WHERE to_tsvector("russian", title||desctription) @@ to_tsquery("russian", "шампунь|против|перхоти") ORDER BY rank DESC LIMIT 20 OFFSET 0;

В этом запросе главными командами являются to_tsvector и to_tsquery. Первая команда переводит строки которые лежат в БД в дикт вида: слово:вес,…

Наглядно:

Функция to_tsquery нормализует введенные слова и приводит их к типу tsquery. Так-же есть функция plainto_tsquery, которая принимает на вход просто строку и приводит ее так-же как to_tsquery, но без необходимости заранее разбивать фразу. Надо заметить, что если в случае с to_tsquery можно самим указать логическое условие, я данном случае ИЛИ (|), то plainto_tsquery разбивает строку с условием И (&).

Так-же в запросе участвует оператор @@ которые как раз сравнивает tsvector и tsquery. Вообще есть достаточно много функций и операторов для полнотекстового поиска, так что курим .

Все это будет работать из коробки, даже с не настроенными индексами (про них ниже). Но. Мы то ленивые, писать сырые запросы не хотим и вообще считаем это «низкосистемным злом» (sarcasm).

Добрый дядя Джангонафт облегчил задачу интеграции FTS в Django. Для этого устанавливаем модуль

Pip install djorm-ext-pgfulltext

и несколько изменяем код нашей модельки:

From djorm_pgfulltext.models import SearchManager from djorm_pgfulltext.fields import VectorField from django.db import models class Element(models.Model): class Meta: db_table = "products" parent = models.ForeignKey(GroupSection, null=True, blank=True) title = models.CharField(max_length=200) description = models.TextField(null=True, blank=True) article = models.CharField(max_length=200, null=True, blank=True) search_index = VectorField() def __unicode__(self): return "%s/%s" % (self.parent.title, self.title) search_manager = SearchManager(fields=("title", "description"), config="pg_catalog.russian", search_field="search_index", auto_update_search_field=True)

По сути все изменение заключается в том, что мы добавили search_index — которое является тем самым tsvector для записи в БД и добавили новый менеджер запросов в конструктор которого передали следующие параметры:

fields — массив полей из которых будет строиться tsvector,
config — указывает postgresql с каким словарем мы хотим работать,
search_field — поле в которм у нас лежат данные которые являются уже подготовленным tsveсtor, собранный из указанных в fields полей,
auto_update_search_field — флаг который заставляет пересоздаваться search_field при изменении записи.

Если взглянуть на структуру таблицы, то мы увидим одно дополнительное поле — search_index, в котором уже лежит tsvector. Это сделано для оптимизации, Postgres умеет работать с уже подготовленными векторами и не тратить в пустую ресурсы на выполнение to_tsvector(‘russian’, title||desctription) для каждой строки БД.

Осталось понять как этот полнотекстовый запрос собственно сделать в нашем коде. Тут проще не бывает.

Всем привет,

Описываются базовые возможности, плюсы и минусы использования встроенного полнотекстового поиска СУБД PostgreSQL на основе опыта его практического использования.

При разработке приложений, особенно web-приложений, в 95% возникает задача выбрать системы для управления как структурированным контентом, так и неструктурированными (текстовая информация произвольной структуры), а также данными мультимедиа (выходит за рамки данной статьи).
Архитектор приложения задается вопросом: совместить эти данные под управлением одной СУБД, либо же взять отдельное специализированное средство для каждого вида информации.

Существуют проверенные временем инструменты для индексирования и поиска неструктурированных текстовых данных — Sphinx, Lucene, на Хабре есть хорошие авторские статьи на эту тему.
Преимущество в том, что это отдельная система и она приспособлена для своей задачи максимально хорошо.
Но есть и архитектурный минус такого решения — ведь структурная и описательная часть данных чаще всего связаны между собой, а следовательно придется сконструировать комбинированные запросы .

Рассмотрим на примере

Есть задача учета кандидатов-соискателей, которые присылают свои резюме в текстовом формате. Была изначально поставлена задача находить информацию по кандидатам на основании их навыков и практического опыта владения этими навыками.
Создаем реляционную модель (упрощенно):

Рекрутеры принимают резюме, изучают их, заполняют наши таблички, после чего запрос вида «дай мне список кандидатов с опытом C (но не C++) не менее 2 года и не старше 100 лет» задаются примерно следующим образом:

WHERE (year(candidate.dob) >
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 (сам такое видел не раз).

Но не обязательно — если изначально была благоразумно выбрана СУБД PostgreSQL, выручит текстовая колонка Resume: text в таблице Candidate — туда рекрутеры с самого начала тупо копипастили текст резюме, на всякий случай.

Надо заставить систему искать по текстовому полю. Что сделать:

1. PostgreSQL устанавливается без словарей русского языка, поэтому их нужно отдельно скачать например . Если база данных (что скорей всего) в uft-8 — придется еще переконвертировать словари из koi8-r в 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 папки, где у вас установлена PostgreSQL.

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 >
to_tsvector("ru",Candidate.Resume) @@ to_tsquery("ru","Тмутаракань")

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 и для массивов (всего в PostgreSQL 9.3 уже 5 разных типов индексов, подробно можно узнать изучив доку PostgreSQL).

Запрос тогда принимает вид:
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 очень быстрый, в сложных запросах PostgreSQL умеет его «склеивать» с другими индексами (хотя сам GIN составным быть не может).

Минусы тоже очевидны
— необходимо содержать дополнительное поле tsvector (немаленькое), нужен триггер, нужен дополнительный индекс. Потери производительности при вставке и обновлении записей будут довольно ощутимыми.
— дополнительная нагрузка на сервер PostgreSQL
— Postgre не позиционирует полнотекстовый поиск как основную фишку — все таки это была, есть и (насколько видно вперед) будет хорошая RDBMS+, поэтому если Вашему приложению нужны возможности полнотекстового поиска и индексирования с передовых рубежей науки — обратите внимание все-таки на специализированные продукты.

На одной чаше весов у нас 1) простота, точность и скорость запросов SQL 2) минус 1 система в ландшафте,
на другой — 1) несколько дополнительных объектов в схеме БД, 2) замедление производительности запросов DML 3) неспециализированный движок.

Если есть сомнения, что это будет хорошо работать — не стоит тогда сразу сносить Sphinx. Но мы свой снесли уже очень давно, всё прекрасно без сбоев под нагрузкой работает.
Результат прекрасно себя оправдает, если запросы на полнотекстовый и комбинированный поиск составляют не более 30% от общего числа запросов. Внимательно изучите возможности полнотекстового поиска PostgreSQL — если потенциально вам недостаточно описанных в документации функций — тогда конечно надо рассматривать другие продукты, потому что, еще раз отмечу, PostgreSQL не является специализированной для полнотекстового индексирования и поиска системой и может уступать по возможностям более специализированным продуктам.

Всем привет,

Описываются базовые возможности, плюсы и минусы использования встроенного полнотекстового поиска СУБД Postgre на основе опыта его практического использования.

Существуют проверенные временем инструменты для индексирования и поиска неструктурированных текстовых данных - 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) >
candidate_skill.experience > 2 AND candidate_skill.skill_id = skill.skill_id AND skill.name = "C" ORDER BY candidate_skill.experience DESC;

Всех находит, все довольны.

Затем случается неожиданность - заказчик из Тмутаракани требует специалиста с опытом работы на 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.

После этого уже можно получить результат, доработав наш запрос

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 >
to_tsvector("ru",Candidate.Resume) @@ to_tsquery("ru","Тмутаракань")

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 (немаленькое), нужен триггер, нужен дополнительный индекс. Потери производительности при вставке и обновлении записей будут довольно ощутимыми.

На одной чаше весов у нас 1) простота, точность и скорость запросов SQL 2) минус 1 система в ландшафте,
на другой - 1) несколько дополнительных объектов в схеме БД, 2) замедление производительности запросов DML.

Если есть сомнения, что это будет хорошо работать - не стоит тогда сразу сносить Sphinx. Но мы свой снесли уже очень давно, всё прекрасно без сбоев под нагрузкой работает.