Exclude Project Bots from Deactivation in DeactivateDormantUsersWorker

What does this MR do and why?

According to #340346 (closed), project_bot users should not be deactivated automatically by the DeactivateDormantUsersWorker.

This MR excludes project_bot users from deactivation.

Issue: #340346 (closed)

Database Migrations

Up

$ bin/rails db:migrate
== 20211027203921 RemoveIndexForDormantUsers: migrating =======================
-- transaction_open?()
   -> 0.0000s
-- indexes(:users)
   -> 0.0112s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_on_id_and_last_activity_on_for_non_internal_active"})
   -> 0.0087s
-- execute("RESET statement_timeout")
   -> 0.0006s
== 20211027203921 RemoveIndexForDormantUsers: migrated (0.0286s) ==============

== 20211027203950 AddUpdatedIndexForDormantUsers: migrating ===================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:users, [:id, :last_activity_on], {:where=>"state = 'active' AND (users.user_type IS NULL OR users.user_type = 4)", :name=>"index_users_on_id_and_last_activity_on_for_active_human_service", :algorithm=>:concurrently})
   -> 0.0085s
-- add_index(:users, [:id, :last_activity_on], {:where=>"state = 'active' AND (users.user_type IS NULL OR users.user_type = 4)", :name=>"index_users_on_id_and_last_activity_on_for_active_human_service", :algorithm=>:concurrently})
   -> 0.0084s
== 20211027203950 AddUpdatedIndexForDormantUsers: migrated (0.0186s) ==========

$ 

Index after migration:

"index_users_on_id_and_last_activity_on_for_active_human_service" btree (id, last_activity_on) WHERE state::text = 'active'::text AND (user_type IS NULL OR user_type = 4)

Down

$ STEP=2 bin/rails db:rollback   
== 20211027203950 AddUpdatedIndexForDormantUsers: reverting ===================
-- transaction_open?()
   -> 0.0000s
-- indexes(:users)
   -> 0.0107s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:users, {:algorithm=>:concurrently, :name=>"index_users_on_id_and_last_activity_on_for_active_human_service"})
   -> 0.0036s
-- execute("RESET statement_timeout")
   -> 0.0006s
== 20211027203950 AddUpdatedIndexForDormantUsers: reverted (0.0173s) ==========

== 20211027203921 RemoveIndexForDormantUsers: reverting =======================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:users, [:id, :last_activity_on], {:where=>"state = 'active' AND (users.user_type IS NULL OR users.user_type IN (NULL, 6, 4))", :name=>"index_users_on_id_and_last_activity_on_for_non_internal_active", :algorithm=>:concurrently})
   -> 0.0074s
-- add_index(:users, [:id, :last_activity_on], {:where=>"state = 'active' AND (users.user_type IS NULL OR users.user_type IN (NULL, 6, 4))", :name=>"index_users_on_id_and_last_activity_on_for_non_internal_active", :algorithm=>:concurrently})
   -> 0.0034s
== 20211027203921 RemoveIndexForDormantUsers: reverted (0.0122s) ==============

$ 

Index after rollback:

"index_users_on_id_and_last_activity_on_for_non_internal_active" btree (id, last_activity_on) WHERE state::text = 'active'::text AND (user_type IS NULL OR (user_type = ANY (ARRAY[NULL::integer, 6, 4])))

Index creation

Index creation took about 37 seconds in database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1635370971298600

Database Performance

SQL before change:

UPDATE "users"
SET "state" = 'deactivated'
WHERE "users"."id" IN (
  (SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (6, 4)) AND (last_activity_on <= '2021-07-27') LIMIT 200)
  UNION
  (SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" IN (6, 4)) AND "users"."last_activity_on" IS NULL LIMIT 200)
  LIMIT 200
)

Query plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7036/commands/24892

In database lab: https://gitlab.slack.com/archives/CLJMDRD8C/p1635196319180100

Summary:

Time: 2.853 s
  - planning: 7.126 ms
  - execution: 2.845 s
    - I/O read: 2.705 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 14628 (~114.30 MiB) from the buffer pool
  - reads: 4278 (~33.40 MiB) from the OS file cache, including disk I/O
  - dirtied: 2747 (~21.50 MiB)
  - writes: 21 (~168.00 KiB)

SQL after change:

UPDATE "users"
SET "state" = 'deactivated'
WHERE "users"."id" IN (
  (SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" = 4) AND (last_activity_on <= '2021-07-29') LIMIT 200)
  UNION
  (SELECT "users"."id" FROM "users" WHERE ("users"."state" IN ('active')) AND ("users"."user_type" IS NULL OR "users"."user_type" = 4) AND "users"."last_activity_on" IS NULL LIMIT 200)
  LIMIT 200
)

Query plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/7081/commands/25047

Database lab link: https://gitlab.slack.com/archives/CLJMDRD8C/p1635371033299400

Summary:

Time: 1.157 s
  - planning: 7.567 ms
  - execution: 1.149 s
    - I/O read: 1.063 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 14317 (~111.90 MiB) from the buffer pool
  - reads: 4324 (~33.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 2834 (~22.10 MiB)
  - writes: 23 (~184.00 KiB)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.