Easy full-text search with PostgreSQL and Rails

Solr, Sphinx, ElasticSearch are some of the big players when it comes to full-text search on the web: they’re great products for sure, but you need to add yet another server to your stack if you plan to use one of them.

But if you’re already using (or willing to use) PostgreSQL as your rails app RDBMS then you probably already have all you need: in fact among the many features of PostgreSQL there is full-text search capability as well.

Today I’m going to use the gem pg_search to show how easy is to add a full-text search capability to a basic rails application.

First things first, let’s create a new rails app that uses PostgreSQL:

rails new search_example -d postgresql

Remember to edit the database.yml file with the right username and password values.

We’re now going to add some nifty modules to postgres that allow some matching based on the english dictionary and some fuzzy logic, this can be achieved by creating a migration with this body:

 rails g migration add_contrib_extensions 

  class AddContribExtensions < ActiveRecord::Migration
    def up
      execute 'CREATE EXTENSION pg_trgm;'
      execute 'CREATE EXTENSION fuzzystrmatch;'

We’re now ready to create a couple of models to be searched:

 rails g model article title:string body:text
rails g model gallery title:string 

We want to search both in the body and in the title fields of the tables. Add the gem to the Gemfile:

 gem 'pg_search' 

then of course run


Create the migration for the multi-table search:

 rails g pg_search:migration:multisearch 

and the migration for the dmetaphone support:

rails g pg_search:migration:dmetaphone
rake db:create && db:migrate

At this point we’re ready to configure the models:

class Article
  include PgSearch
  multisearchable against: [:title, :body]

class Gallery
  include PgSearch
  multisearchable against: :title

and we’re almost done. Let’s write an initializer that configures PgSearch to use the postgres modules we added:

 touch config/initializers/pg_search.rb 

and add this code:

PgSearch.multisearch_options = {
  using: {
    tsearch:    {dictionary: 'english'},
    trigram:    {threshold:  0.1},
    dmetaphone: {}

For further explaination please check postgres documentation for tsearch, trigram and dmetaphone modules.

Add some seed data in seed.rb:

Gallery.create title: 'Lolcats'
Article.create title: 'Lolcatz are funny', body: "yes they're indeed"

Active record callbacks will transparently handle the indexing of your models. Ready for a test? Launch the rails console and type:

search = PgSearch.multisearch 'lolcats'
search.map { |s| s.searchable.title }.inspect
#=> ["Lolcatz", "Locats are funny "]

search = PgSearch.multisearch 'lolcts'
search.map { |s| s.searchable.title }.inspect
#=> ["Lolcatz", "Locats are funny "]

Both records were found even if the word “locats” is mispelled in one of the titles, thanks to the optional dictionary statement we passed to the gem configuration in the initializer file.

PostgreSQL full text search modules and the gem pg_search work so well together that we really did need to write just a little code to make it happen. 

Leave a Reply

Please Login to comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.