Last Updated: September 09, 2019

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:

Create a search vector column of type of 'tsvector' to represent the data.
Create an index on the search vector using 'gin'
Search using 'ts_vector'
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