simple full text search using postgres on rails

If your app requires full text search, i would highly recommend the out-of-the box capability postgres provides. It is a simple yet powerful solution and removes dependencies on external systems (like solr, sphinx etc) for basic search functionality.

There are 4 parts:

1. Create a search vector column of type of 'tsvector' to represent the data.
2. Create an index on the search vector using 'gin'
3. Search using 'ts_vector'
4. Create a trigger to update the search vector column. (optional)

Table:

My products table looks something like this and i want to search on all the columns:

table name: products
    title              :string
    description  :text
    awards         :text
    keywords     :text

Migration:

class AddSearchVectorToProducts < ActiveRecord::Migration
  def up
    # 1. Create the search vector column
    add_column :products, :search_vector, 'tsvector'

    # 2. Create the gin index on the search vector
    execute <<-SQL
      CREATE INDEX products_search_idx
      ON products
      USING gin(search_vector);
    SQL

    # 4 (optional). Trigger to update the vector column 
    # when the products table is updated
    execute <<-SQL
      DROP TRIGGER IF EXISTS products_search_vector_update
      ON products;
      CREATE TRIGGER products_search_vector_update
      BEFORE INSERT OR UPDATE
      ON products
      FOR EACH ROW EXECUTE PROCEDURE
      tsvector_update_trigger (search_vector, 'pg_catalog.english', title, description, awards, keywords);
    SQL

    Product.find_each { |p| p.touch }
  end

  def down
    remove_column :products, :search_vector
    execute <<-SQL
      DROP TRIGGER IF EXISTS products_search_vector_update on products;
    SQL
  end
end

Your model:

class Product
  def self.search(terms = "")
    sanitized = sanitize_sql_array(["to_tsquery('english', ?)", terms.gsub(/\s/,"+")])
    Product.where("search_vector @@ #{sanitized}")
  end
end

Resources:

Postgres Docs - http://www.postgresql.org/docs/9.1/static/textsearch-intro.html
Texticle Gem - http://tenderlove.github.com/texticle