NielsKSchjoedt · June 3, 2013 07:44 · Jun 3, 2013
diff --git a/reindex_concurrenty.rb b/reindex_concurrenty.rb
@@ -0,0 +1,70 @@
+class DailyReindexingJob
+
+  #
+  # Reindexes for performance reasons
+  #
+  def perform
+    tables_to_be_reindexed.each do |tbl|
+      indexes_for(tbl).each do |idx|
+
+        idx_name = idx['relname']
+
+        tmp_new_idx_name = random_index_name
+        tmp_old_idx_name = random_index_name
+
+        raise "The two tmp_idx names '#{tmp_new_idx_name}' and '#{tmp_old_idx_name}' may not be the same!" if tmp_new_idx_name == tmp_old_idx_name
+
+        create_statement = idx['pg_get_indexdef'].gsub('INDEX ', 'INDEX CONCURRENTLY ').gsub(idx_name, tmp_new_idx_name)
+
+        begin
+          create_index(create_statement)
+          rename_indexes(idx_name, tmp_old_idx_name, tmp_new_idx_name)
+        ensure
+          drop_indexes([tmp_old_idx_name, tmp_new_idx_name])
+        end
+      end 
+    end
+    return true
+  end
+
+  private
+
+  def tables_to_be_reindexed
+    [
+      "advert_candidate_collector_statuses", 
+      "failed_adverts",
+      "adverts",
+      "cars"
+    ].freeze
+  end
+
+  def indexes_for table
+    ActiveRecord::Base.connection.execute("SELECT relname, pg_get_indexdef(indexrelid) FROM pg_index JOIN pg_class ON pg_class.oid = pg_index.indexrelid WHERE indisprimary = false AND indrelid ='#{current_schema}.#{table}'::regclass;")
+  end
+
+  def random_index_name
+    Array.new(32) { (rand(122-97) + 97).chr }.join
+  end
+
+  def current_schema
+    "public"
+  end
+
+  def create_index create_statement
+    ActiveRecord::Base.connection.execute(create_statement)
+  end
+
+  def rename_indexes idx_name, tmp_old_idx_name, tmp_new_idx_name
+    ActiveRecord::Base.transaction do
+      ActiveRecord::Base.connection.execute("ALTER INDEX #{idx_name} RENAME TO #{tmp_old_idx_name}")
+      ActiveRecord::Base.connection.execute("ALTER INDEX #{tmp_new_idx_name} RENAME TO #{idx_name}")
+    end
+  end
+
+  def drop_indexes index_names
+    index_names.each do |name|
+      ActiveRecord::Base.connection.execute("DROP INDEX CONCURRENTLY IF EXISTS #{name}")
+    end
+  end
+
+end