Use optimized query to fill the routes table when running PostgreSQL

2 files changed, 19 insertions, 6 deletions

diff --git a/db/migrate/20161130101252_fill_projects_routes_table.rb b/db/migrate/20161130101252_fill_projects_routes_table.rb
index 4f3fe7b03a9..56ba6fcdbe3 100644
--- a/db/migrate/20161130101252_fill_projects_routes_table.rb
+++ b/db/migrate/20161130101252_fill_projects_routes_table.rb
@@ -8,12 +8,20 @@ class FillProjectsRoutesTable < ActiveRecord::Migration
   DOWNTIME_REASON = 'No new projects should be created during data copy'
 
   def up
-    execute <<-EOF
-      INSERT INTO routes
-      (source_id, source_type, path)
-      (SELECT projects.id, 'Project', concat(namespaces.path, '/', projects.path) FROM projects
-      INNER JOIN namespaces ON projects.namespace_id = namespaces.id)
-    EOF
+    if Gitlab::Database.postgresql?
+      execute <<-EOF
+        INSERT INTO routes (source_id, source_type, path)
+        (SELECT DISTINCT ON (namespaces.path, projects.path) projects.id, 'Project', concat(namespaces.path, '/', projects.path)
+         FROM projects INNER JOIN namespaces ON projects.namespace_id = namespaces.id
+         ORDER BY namespaces.path, projects.path, projects.id DESC)
+      EOF
+    else
+      execute <<-EOF
+        INSERT INTO routes (source_id, source_type, path)
+        (SELECT projects.id, 'Project', concat(namespaces.path, '/', projects.path)
+         FROM projects INNER JOIN namespaces ON projects.namespace_id = namespaces.id)
+      EOF
+    end
   end
 
   def down
diff --git a/db/migrate/20161202152031_remove_duplicates_from_routes.rb b/db/migrate/20161202152031_remove_duplicates_from_routes.rb
index 510796e05f2..a21bde69995 100644
--- a/db/migrate/20161202152031_remove_duplicates_from_routes.rb
+++ b/db/migrate/20161202152031_remove_duplicates_from_routes.rb
@@ -7,6 +7,11 @@ class RemoveDuplicatesFromRoutes < ActiveRecord::Migration
   DOWNTIME = false
 
   def up
+    # We can skip this migration when running a PostgreSQL database because
+    # we use an optimized query in the "FillProjectsRoutesTable" migration
+    # to fill these values that avoid duplicate entries in the routes table.
+    return unless Gitlab::Database.mysql?
+
     select_all("SELECT path FROM #{quote_table_name(:routes)} GROUP BY path HAVING COUNT(*) > 1").each do |row|
       path = connection.quote(row['path'])
       execute(%Q{