Precalculate trending projectsprecalculate-trending-projects

This commit introduces a Sidekiq worker that precalculates the list of
trending projects on a daily basis. The resulting set is stored in a
database table that is then queried by Project.trending.

This setup means that Unicorn workers no longer _may_ have to calculate
the list of trending projects. Furthermore it supports filtering without
any complex caching mechanisms.

The data in the "trending_projects" table is inserted in the same order
as the project ranking. This means that getting the projects in the
correct order is simply a matter of:

    SELECT projects.*
    FROM projects
    INNER JOIN trending_projects ON trending_projects.project_id = projects.id
    ORDER BY trending_projects.id ASC;

Such a query will only take a few milliseconds at most (as measured on
GitLab.com), opposed to a few seconds for the query used for calculating
the project ranks.

The migration in this commit does not require downtime and takes care of
populating an initial list of trending projects.

1 files changed, 38 insertions, 0 deletions

diff --git a/db/migrate/20161007133303_precalculate_trending_projects.rb b/db/migrate/20161007133303_precalculate_trending_projects.rb
new file mode 100644
index 00000000000..b324cd94268
--- /dev/null
+++ b/db/migrate/20161007133303_precalculate_trending_projects.rb
@@ -0,0 +1,38 @@
+# See http://doc.gitlab.com/ce/development/migration_style_guide.html
+# for more information on how to write migrations for GitLab.
+
+class PrecalculateTrendingProjects < ActiveRecord::Migration
+  include Gitlab::Database::MigrationHelpers
+
+  DOWNTIME = false
+
+  def up
+    create_table :trending_projects do |t|
+      t.references :project, index: true, foreign_key: { on_delete: :cascade }, null: false
+    end
+
+    timestamp = connection.quote(1.month.ago)
+
+    # We're hardcoding the visibility level (public) here so that if it ever
+    # changes this query doesn't suddenly use the new value (which may break
+    # later migrations).
+    visibility = 20
+
+    execute <<-EOF.strip_heredoc
+      INSERT INTO trending_projects (project_id)
+      SELECT project_id
+      FROM notes
+      INNER JOIN projects ON projects.id = notes.project_id
+      WHERE notes.created_at >= #{timestamp}
+      AND notes.system IS FALSE
+      AND projects.visibility_level = #{visibility}
+      GROUP BY project_id
+      ORDER BY count(*) DESC
+      LIMIT 100;
+    EOF
+  end
+
+  def down
+    drop_table :trending_projects
+  end
+end