I ran into a situation today where I had 10 long running background processes that were getting bogged down because we were missing an index. The app is running on Heroku on a shared database, so I don’t have the luxury of using psql to add the needed index. However, I wanted to figure out a way to add the index without restarting the long-running processes.
My solution was to log in via the console, and create the migration by hand.
| heroku run console --app <your app name> | |
| irb(main):002:0* class AddIndexToSearches < ActiveRecord::Migration | |
| irb(main):003:1> def change | |
| irb(main):004:2> add_index :searches, :search_string, :quiet => true | |
| irb(main):005:2> end | |
| irb(main):006:1> end | |
| => nil | |
| irb(main):007:0> t = AddIndexToSearches.new | |
| => #<AddIndexToSearches:0x00000006a240a8 @name="AddIndexToSearches", @version=nil, @connection=nil> | |
| irb(main):008:0> t.change | |
| -- add_index(:searches, :search_string) | |
| (91.8ms) SELECT distinct i.relname, d.indisunique, d.indkey, t.oid | |
| FROM pg_class t | |
| INNER JOIN pg_index d ON t.oid = d.indrelid | |
| INNER JOIN pg_class i ON d.indexrelid = i.oid | |
| WHERE i.relkind = 'i' | |
| AND d.indisprimary = 'f' | |
| AND t.relname = 'searches' | |
| AND i.relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname = ANY (current_schemas(false)) ) | |
| ORDER BY i.relname | |
| (207509.6ms) CREATE INDEX "index_searches_on_search_string" ON "searches" ("search_string") | |
| -> 207.6028s | |
| => #<PGresult:0x00000006c81328> | |
| irb(main):009:0> | |
| irb(main):010:0* |
Once complete, I added a new migration within the source code that has the option :quiet => true (_updated: see note below), so that all environments/developers get the manually created index that resides in production. Had I just done a git push to heroku, the background processes would have restarted. The results of adding the needed index can be seen in our New Relic stats below:
Note: I thought :quiet was a valid option for add_index, but it is actually a patch that you will need to add for your specific database. The following article describes a patch for MySQL. See mine for PostGreSQL.
| module ActiveRecord::ConnectionAdapters::SchemaStatements | |
| def add_index_with_quiet(table_name, column_names, options = {}) | |
| quiet = options.delete(:quiet) | |
| add_index_without_quiet table_name, column_names, options | |
| rescue | |
| raise unless quiet and $!.message =~ /^Index name '.*' on table '#{table_name}' already exists/i | |
| puts "Failed to create index #{table_name} #{column_names.inspect} #{options.inspect}" | |
| end | |
| alias_method_chain :add_index, :quiet | |
| end |
