TSearchable – Postgres Text Search on Rails
## 50,000 articles, title and body indexed
## sphinx via ultrasphinx plugin
>> search = Ultrasphinx::Search.new(:query => "apple")
>> Benchmark.realtime { search.run }
=> 0.27561092376709
>> search = Ultrasphinx::Search.new(:query => "cat AND dog")
>> Benchmark.realtime { search.run }
=> 0.225758075714111
>> search = Ultrasphinx::Search.new(:query => "apple OR pear")
>> Benchmark.realtime { search.run }
=> 0.224870184998115
## postgres native text search via tsearchable
>> Benchmark.realtime { Article.find_by_text_search("apple") }
=> 0.156562089920044
>> Benchmark.realtime { Article.find_by_text_search("cat AND dog") }
=> 0.154358863830566
>> Benchmark.realtime { Article.find_by_text_search("apple OR pear") }
=> 0.164685010910034
Postgres Text Searching and Rails
As of Postgresql 8.3, text searching is now a native feature. Previous versions of Postgres required a “tsearch” extension, which was quite popular, and worked quite well.
As a fan of Postgres, I wanted to use its text search in a project I was working on. We had recently upgraded to 8.3, and knew the perks of tsearch now being a native feature. No socket trickery, ongoing support, etc. I found a great plugin called acts_as_tsearch which supported text search in < = 8.2, but didn't quite work with 8.3. It also had quite a few features that I didn't need. So, using acts_as_tsearch as a resource, I whipped up tsearchable, which met my needs. See the README for the list of available features.
The benchmarks shown above are obviously rough. They are against a production data set of approximately 50,000 records, with one varchar and one text field indexed. This benchmark was run on my local machine, with tons of other random processes running, and each search was run cold. Some thorough benchmarks on Sphinx and Postgres Text Search would be great however, as the 8.3 integration of tsearch was quite an impressive feat.
We are running the tsearchable in production across a few high profile sites, and it works great. The 8.3 install hums and search is super fast. Thanks to Florent Monbillard for his trigger, suggestions, and other contributions as well. There are a lot of features to be added, and a lot of love would do the plugin good… so, if you’re running 8.3, feel free to use and contribute !
5 Comments
Good job on this plugin. Any plans to maintain it and turn it into a gem? I would like to use it with Heroku which at this point uses Postgresql 8.3.5 but it has a limited amount of options when it comes to full text searching (acts_as_ferret and solr – hosted on a another server).
Hey Andrei! I haven’t had the need to extend it into a gem just yet, but it would definitely be a nice improvement. Would you still be able to use it on Heroku as a plugin?
Thanks for the write up, I’m trying to figure out how to employ fts across multiple tables, could you please explain what the best method and strategy to follow.
Hey Dylan! Nice post! Yes you can use it on heroku! I have hosted a very simple app to check if it does work.
You can create new data here:
And search the same using tsearch here:
You can also check out this thread from the heroku google group:
Hey Dylan! Nice post! Yes you can use it on heroku! I have hosted a very simple app to check if it does work.
You can create new data here:
You can create new data here:
And search the same using tsearch here
You can also check out this thread from the heroku google group: