Deploying advanced text searches

Following along the line on a recent post, I have decided to use tsearch2 for postgres as a text search solution to my database. Installation can get tricky, especially when dealing with uncommon locales, but once it is up and running it is very flexible, fast, and customizable.

For instance, here is a select statement that I use to fetch data from my tables based on text keywords:

• select name,email,description from mytable, to_tsquery('free&as&in&beer') as q where description_idx @@ q;

First thing to note: tsearch2 will discard 'as' and 'in' from the query, as instructed by a file called locale.stop. This is interesting to obtain more accurate results out of the search request. Also the dictionary handles the way similar words are grouped in indexes, thus one can say that both 'beers' and beering' are indexed as 'beer' hence refining the expected search results. This is all part of the dictionary setup, which one should setup properly if intends to have a complete tsearch2 installation.

Tsearch2 comes with C and russian locales after a fresh install, so you'll likely run into problems if your system's locale is set to something different, or your database was created using a different encoding (something that cannot be changed afterwards, unless you dump your database, recreate it using a new encoding, and dump data back in). A typical example of such a problem is this message returned by a tsearch2 query:

• ERROR: could not find tsearch config by locale

As I discussed recently on another post, the prefered way of encoding your data in the backend is Unicode. So in my case I decided to create a utf-8 dictionary, tell postgres that's the default encoding/decoding from the database, then force tsearch2 to use this one instead of its default 'english'. There are prebuilt dictionaries here.