Changes for optimizing expiring worker

What does this MR do and why?

Solves #432518 (closed)

Changes for optimizing expiring worker

We are reducing batch size of expiring worker and querying PersonalAccessTokens table instead of User table since indexing is coming to work and query execution is much faster

Changelog: changed

Screenshots or screen recordings

For OLD User Query with batch size 1000 and no filter

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/25991/commands/81812

Time: 22.472 s
- planning: 1.396 s
- execution: 21.077 s
- I/O read: 20.620 s
- I/O write: 0.000 msShared buffers:
- hits: 2099 (~16.40 MiB) from the buffer pool
- reads: 19315 (~150.90 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0

Query Execution after changes for Personal Access Token LOOP execution

SELECT "personal_access_tokens"."user_id" FROM "personal_access_tokens" WHERE "personal_access_tokens"."impersonation" = FALSE AND (revoked = false AND expire_notification_delivered = false AND expires_at >= CURRENT_DATE AND expires_at <= '2024-02-15') LIMIT 100

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26068/commands/82124

Runtime -

Time: 108.510 ms
  - planning: 1.806 ms
  - execution: 106.704 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 7161 (~55.90 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Query plan for internal loop where we are fetching all the tokens for related user

SELECT "personal_access_tokens".* FROM "personal_access_tokens" WHERE "personal_access_tokens"."user_id" = 1 AND "personal_access_tokens"."impersonation" = FALSE AND (revoked = false AND expire_notification_delivered = false AND expires_at >= CURRENT_DATE AND expires_at <= '2024-02-15')

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26068/commands/82082

Time: 12.135 ms
  - planning: 2.392 ms
  - execution: 9.743 ms
    - I/O read: 9.541 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 0 from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Query plan for finding users with user_ids

Number of user ids can be maximum 100 in this query

SELECT "users".* FROM "users" WHERE "users"."id" IN (1, 70, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110)

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26068/commands/82125

Time: 555.302 ms
  - planning: 456.330 ms
  - execution: 98.972 ms
    - I/O read: 97.979 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 123 (~984.00 KiB) from the buffer pool
  - reads: 25 (~200.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Query plan for update all query

UPDATE "personal_access_tokens" SET "expire_notification_delivered" = TRUE WHERE "personal_access_tokens"."user_id" = 1 AND "personal_access_tokens"."impersonation" = FALSE AND (revoked = false AND expire_notification_delivered = false AND expires_at >= CURRENT_DATE AND expires_at <= '2024-02-15')

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/26068/commands/82083

Time: 2.056 ms
  - planning: 1.957 ms
  - execution: 0.099 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 3 (~24.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Before	After

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.