Text Search on Postgres

reduce your text search dependencies

“Heroku’s documentation lists two ways to get full text indexing working with your Heroku application. They talk about using Ferret and Solr for full text indexes. The Ferret option looks OK, but it requires you to rebuild your indexes every time you push. Solr would work, but it requires an EC2 instance or some third party server. Since my budget is precisely $0, using Solr is out of the picture.

But there is a third option. A very secret option. A devious but fun option. You see, Heroku runs PostgreSQL for each rails application database. They’re running a version new enough (Version 8.3) to have full text index support built in. If we’re willing to throw out database agnosticism, we can take advantage of the database’s indexing capability. [...]” 1

The Pros and Cons

make sure texticle is right for you

Getting Up and Running

add text search to your models in 5 minutes flat

For Rails 3 Apps

  • Inside your Gemfile
  • gem 'texticle', "2.0", :require => 'texticle/rails'

  • To verify, from a terminal
  • bundle install
  • bundle | grep texticle # => Using texticle (…)

Outside of a Rails app

  • require 'activerecord'
  • require 'texticle'
  • ActiveRecord::Base.extend(Texticle)

Search Your Models

forget about LIKE %

Tips and Tricks

get the most out of texticle

Systemwide Search

If you have several models you want to search through in one go, one approach is to use a separate search model. First populate a database view with the models you want to search through:

Now we can write our Search model:

Running Search.new('awesome') will return all authors AND books that have 'awesome' in their name or title.

Limit Searched Columns with Searchable

If you don't wish to search across all your text columns, you an include Searchable with a list of columns to whitelist in searches:

As you can see, _why can't be found.

Scope Searches

It's also possible to limit which columns are searched through judicious use of scopes:


this gem brought to you by the following committers

1Excerpt from Aaron Patterson's article on Full Text Search on Heroku.

2As of Postgres 9.0, homebrew includes the 'contrib' modules. Sadly, they're still missing from Heroku.