On a Rails project I’m currently working on I need to fill the database with test data to have something to play with. Apart from large imports, that’s the time when indexes may slow down operation severely instead of speeding things up. Consider: The indexes are not used, but have to be updated again and again for each new record that is inserted into the database. It is much cheaper, to lift — well, really drop — the indexes during mass operation and recreate them afterwards.

Here’s an example:

namespace :db do
  desc "Populate the database with sample data"
  task :populate => :environment do

    retained_indexes = [
      'index_people_on_lastname_and_firstname',
      { :table => :movies, :columns => :title }
      { :table => 'people', :columns => ['lastname', :firstname] }
    ]

    ActiveRecord::Base.transaction do
      IndexLifter.without_indexes(
        # Only consider indexes on these tables;
        # all tables by default.
        :movies,
        :people,
        # Don't lift these indexes
        :except => retained_indexes,
        # Don't lift unique indexes; default: false.
        :except_unique => true
      ) do
        ActiveRecord::Base.silence do

          # import or generate large amounts of data here

        end
      end
    end
  end
end

Please bear in mind that dropping and creating of indexes is a rather intrusive operation on the structure of your database. You should only perform it while no other users (or processes) are accessing it.

Also, consider that some indexes may be important for the proper function of your database. If you have unique indexes, i.e. indexes that enforce that particular columns or combinations of columns are unique, and if you are handling violations of this constraint in your application code, then you might need to retain these indexes even during data generation.

Getting it

• github
• $ sudo gem install mschuerig-index_lifter

Let your Rails app know about it

In the appropriate place in config/environment.rb add

config.gem "mschuerig-index_lifter", :lib => 'index_lifter'