Add index on PostgreSQL table column for in-built full text search in Rails

Applicable for PG 8.4 and above.

class AddIndexesToListings < ActiveRecord::Migration
  def change
    execute <<-SQL
      create index on listings using gin(to_tsvector('english', title));
      create index on listings using gin(to_tsvector('english', description));
    SQL
  end
end

Upgrade Heroku db to new PostgreSQL Dev addon

heroku addons:add heroku-postgresql:dev
heroku pg:promote NEW_DB_COLORFUL_NAME

Configure PostgreSQL full-text search using pg_search gem for partial word match

class Listing < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search_by_title_and_description, against: [:title, :description], using: { tsearch: { prefix: true } }
end

Suppress NOTICE messages from Postgresql when running rake cucumber


# Notice messages spewed
#
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index ...

# Fix
# Add to database config in config/database.yml
#
test:
...
min_messages: WARNING;;


Installing postgresql on CentOS


sudo yum install postgresql postgresql-devel postgresql-server

mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data

sudo su
su postgres

/usr/bin/initdb -D /usr/local/pgsql/data

createuser OR
psql -c "create role ROLE with superuser"


Install postgresql using homebrew on OSX

If there's existing postgres install,

brew rm postgresql --force

Else

brew update
brew install postgres

Then follow instructions on printed by homebrew

initdb /usr/local/var/postgres
cp /usr/local/Cellar/postgresql/9.1.4/homebrew.mxcl.postgresql.plist ~/Library/LaunchAgents/
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start