Use a specialized class for querying events

This changes various controllers to use the new EventCollection class
for retrieving events. This class uses a JOIN LATERAL query on
PostgreSQL to retrieve queries in a more efficient way, while falling
back to a simpler / less efficient query for MySQL.

The EventCollection class also includes a limit on the number of events
to display to prevent malicious users from cycling through all events,
as doing so could put a lot of pressure on the database.

JOIN LATERAL is only supported on PostgreSQL starting with version 9.3.0
and as such this optimisation is only used when using PostgreSQL 9.3 or
newer.

2 files changed, 39 insertions, 2 deletions

diff --git a/db/migrate/20170727123534_add_index_on_events_project_id_id.rb b/db/migrate/20170727123534_add_index_on_events_project_id_id.rb
new file mode 100644
index 00000000000..1c4aaaf9dd6
--- /dev/null
+++ b/db/migrate/20170727123534_add_index_on_events_project_id_id.rb
@@ -0,0 +1,37 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class AddIndexOnEventsProjectIdId < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  COLUMNS = %i[project_id id].freeze
+  TABLES = %i[events events_for_migration].freeze
+
+  disable_ddl_transaction!
+
+  def up
+    TABLES.each do |table|
+      add_concurrent_index(table, COLUMNS) unless index_exists?(table, COLUMNS)
+
+      # We remove the index _after_ adding the new one since MySQL doesn't let
+      # you remove an index when a foreign key exists for the same column.
+      if index_exists?(table, :project_id)
+        remove_concurrent_index(table, :project_id)
+      end
+    end
+  end
+
+  def down
+    TABLES.each do |table|
+      unless index_exists?(table, :project_id)
+        add_concurrent_index(table, :project_id)
+      end
+
+      unless index_exists?(table, COLUMNS)
+        remove_concurrent_index(table, COLUMNS)
+      end
+    end
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 8a2df991f0d..d8e8ef41758 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -530,7 +530,7 @@ ActiveRecord::Schema.define(version: 20170807160457) do
   add_index "events", ["action"], name: "index_events_on_action", using: :btree
   add_index "events", ["author_id"], name: "index_events_on_author_id", using: :btree
   add_index "events", ["created_at"], name: "index_events_on_created_at", using: :btree
-  add_index "events", ["project_id"], name: "index_events_on_project_id", using: :btree
+  add_index "events", ["project_id", "id"], name: "index_events_on_project_id_and_id", using: :btree
   add_index "events", ["target_id"], name: "index_events_on_target_id", using: :btree
   add_index "events", ["target_type"], name: "index_events_on_target_type", using: :btree
 
@@ -546,7 +546,7 @@ ActiveRecord::Schema.define(version: 20170807160457) do
 
   add_index "events_for_migration", ["action"], name: "index_events_for_migration_on_action", using: :btree
   add_index "events_for_migration", ["author_id"], name: "index_events_for_migration_on_author_id", using: :btree
-  add_index "events_for_migration", ["project_id"], name: "index_events_for_migration_on_project_id", using: :btree
+  add_index "events_for_migration", ["project_id", "id"], name: "index_events_for_migration_on_project_id_and_id", using: :btree
   add_index "events_for_migration", ["target_type", "target_id"], name: "index_events_for_migration_on_target_type_and_target_id", using: :btree
 
   create_table "feature_gates", force: :cascade do |t|