Creating GIN Index for database columns in rails Migration.

Hi,

Indexing the database columns plays an important role in database query optimisation. Indexing also increases the application performance.

While working with full text search, I recommend to use the GIN Index. It will speed-up the full-text search.


GIN (Generalised Inverted Index)-based index. While creating Column as GIN Index we need to make that column as tsvector type.

Here is the full stack view of GIN Index : http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html


Coming rails migration we can create GIN Index by simple commands as below

Create migration file:

> rails g migration add_ginindex_products

class AddGinindexProductions < ActiveRecord::Migration
  def up
    execute "CREATE INDEX products_gin_question ON products USING GIN(to_tsvector('english', question))"
  end

  def down
    execute "DROP INDEX products_gin_question"
  end
end


> rake db:migrate

The above migration will create the GIN Index for the products table.

Thank You,
Uma Mahesh.