Create Ruby Migration To Add An Index To A PostgreSQL Table

The other day I did some optimization work on a PostgreSQL database and at the end needed to create migrations so the changes I was going to make could be deployed on multiple servers. It was my first shot at migrations so I wanted to note for myself and anyone else that may find it useful how to create Ruby migrations for dropping and adding Indexes on PostgreSQL tables. Below you will find the basic concept for creating a migration that adds a Postgres table index and information on removing a table index which is just the reverse of adding.

Generate And Run A Ruby Migration That Adds A Index To A PSQL Table:

1. Generate Blank Migration: First you should generate a blank migration which will add a date timestamp to the beginning of the migration file name. The command below will generate a migration file with the name 20091223181025_index_postgre_sql_table.rb located in the db/migrate folder within your Ruby projects root directory.Generate Blank Ruby Migration:
   bash

   
   

   1. ruby script/generate migration IndexPostgreSQLTable
2. Add PostgreSQL Index Using Migration File: Now open the newly created migration file in your favorite editor. Below is a view of the default migration file followed by the addition of adding one index to a single table in PostgreSQL.Default Ruby Migration File:
   ruby

   
   

   1. class IndexPostgreSqlTable < ActiveRecord::Migration
   2. def self.up
   3. enddef self.down
   4. end
   5. end

   The above displays what the default file will look like. It has two important sections to notice which is where you will be adding code. The self.up is the are you will add the ruby code for the index you want to generate and the self.down should be the reverse of this command so things can be rolled back. Below is an example of adding a single PostgreSQL index on the address and location fields to the servers table.

   Added Ruby Code To Migration File To Generate An Index On A PostgreSQL Table:

   ruby

   
   

   1. class IndexPostgreSqlTable < ActiveRecord::Migration
   2. def self.up
   3. add_index :servers, [:address, :location]
   4. end
   5.  
   6. def self.down
   7. remove_index :servers, [:address, :location]
   8. end
   9. end

   As you can see above the syntax is really simple for adding and removing indexes from PostgreSQL tables. If you wanted a migration that would remove an index simply swap the remove_index and add_index lines in the migration file.

3. Run Migration: Once you are ready to test you need to run the migration using syntax similar to the below logged in as the owner of your Ruby project and from the Ruby projects root directory.Run Migration Using Rake:
   bash

   
   

   1. rake db:migrate RAILS_ENV="production"

   If this is a development environment you may want to change the Rails environment from production to development but that will depend on how you have things set up.

If the migration is successful then check it into your file management system and it can easily be rolled out to all servers. The migrations will obviously need to be run on each server so the changes modify each separate database.