Remove trigram indexes for "ci_runners"remove-ci-runner-trigram-indexes

These indexes are only used when you search for runners in the admin
interface. This operation is so rarely used that it does not make sense
to slow down every update in order to update the GIN trigram indexes.

Removing these indexes should speed up queries such as those used for
updating the last contact time of CI runners. Locally the timings of
this query were reduced from ~50 ms to ~25 ms:

    UPDATE ci_runners SET updated_at = now(), contacted_at = now();

3 files changed, 29 insertions, 3 deletions

diff --git a/CHANGELOG b/CHANGELOG
index 42d32e53685..a317b69941b 100644
--- a/CHANGELOG
+++ b/CHANGELOG
@@ -36,6 +36,7 @@ v 8.11.0 (unreleased)
   - Retrieve rendered HTML from cache in one request
   - Fix renaming repository when name contains invalid chararacters under project settings
   - Upgrade Grape from 0.13.0 to 0.15.0. !4601
+  - Trigram indexes for the "ci_runners" table have been removed to speed up UPDATE queries
   - Fix devise deprecation warnings.
   - Update version_sorter and use new interface for faster tag sorting
   - Optimize checking if a user has read access to a list of issues !5370
diff --git a/db/migrate/20160810102349_remove_ci_runner_trigram_indexes.rb b/db/migrate/20160810102349_remove_ci_runner_trigram_indexes.rb
new file mode 100644
index 00000000000..0cfb637804b
--- /dev/null
+++ b/db/migrate/20160810102349_remove_ci_runner_trigram_indexes.rb
@@ -0,0 +1,27 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class RemoveCiRunnerTrigramIndexes < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  # Disabled for the "down" method so the indexes can be re-created concurrently.
+  disable_ddl_transaction!
+
+  def up
+    return unless Gitlab::Database.postgresql?
+
+    transaction do
+      execute 'DROP INDEX IF EXISTS index_ci_runners_on_token_trigram;'
+      execute 'DROP INDEX IF EXISTS index_ci_runners_on_description_trigram;'
+    end
+  end
+
+  def down
+    return unless Gitlab::Database.postgresql?
+
+    execute 'CREATE INDEX CONCURRENTLY index_ci_runners_on_token_trigram ON ci_runners USING gin(token gin_trgm_ops);'
+    execute 'CREATE INDEX CONCURRENTLY index_ci_runners_on_description_trigram ON ci_runners USING gin(description gin_trgm_ops);'
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 71980a6d51f..b795eeaa23c 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -11,7 +11,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 20160804150737) do
+ActiveRecord::Schema.define(version: 20160810102349) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "plpgsql"
@@ -301,10 +301,8 @@ ActiveRecord::Schema.define(version: 20160804150737) do
     t.boolean  "locked",       default: false, null: false
   end
 
-  add_index "ci_runners", ["description"], name: "index_ci_runners_on_description_trigram", using: :gin, opclasses: {"description"=>"gin_trgm_ops"}
   add_index "ci_runners", ["locked"], name: "index_ci_runners_on_locked", using: :btree
   add_index "ci_runners", ["token"], name: "index_ci_runners_on_token", using: :btree
-  add_index "ci_runners", ["token"], name: "index_ci_runners_on_token_trigram", using: :gin, opclasses: {"token"=>"gin_trgm_ops"}
 
   create_table "ci_services", force: :cascade do |t|
     t.string   "type"