Skip to content

Reduce DB load when resetting CI minute notifications

What does this MR do?

Resetting namespace notifications used to hit every single table row. This MR changes it so it only updates the ones that need to be updated to reduce database load.

Query

UPDATE "namespaces" SET "last_ci_minutes_notification_at" = NULL, "last_ci_minutes_usage_notification_level" = NULL WHERE "namespaces"."id" BETWEEN 11000000 AND 12000000 AND "namespaces"."id" >= 11135000 AND "namespaces"."id" < 11136000 AND ("namespaces"."last_ci_minutes_notification_at" IS NOT NULL OR "namespaces"."last_ci_minutes_usage_notification_level" IS NOT NULL)

Execution plan

ModifyTable on public.namespaces  (cost=0.43..1409.13 rows=1 width=371) (actual time=1181.656..1181.657 rows=0 loops=1)
   Buffers: shared hit=4501 read=863 dirtied=281
   I/O Timings: read=1148.573
   ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.43..1409.13 rows=1 width=371) (actual time=36.347..591.102 rows=66 loops=1)
         Index Cond: ((namespaces.id >= 11000000) AND (namespaces.id <= 12000000) AND (namespaces.id >= 11135000) AND (namespaces.id < 11136000))
         Filter: ((namespaces.last_ci_minutes_notification_at IS NOT NULL) OR (namespaces.last_ci_minutes_usage_notification_level IS NOT NULL))
         Rows Removed by Filter: 904
         Buffers: shared hit=343 read=500 dirtied=11
         I/O Timings: read=580.469

Summary

Time: 1.184 s
  - planning: 2.314 ms
  - execution: 1.182 s (estimated* for prod: 0.021...1.181 s)
    - I/O read: 1.149 s
    - I/O write: N/A

Shared buffers:
  - hits: 4501 (~35.20 MiB) from the buffer pool
  - reads: 863 (~6.70 MiB) from the OS file cache, including disk I/O
  - dirtied: 281 (~2.20 MiB)
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel/sessions/2530/commands/7790

Related #323069 (closed)

Does this MR meet the acceptance criteria?

Conformity

Edited by Matija Čupić

Merge request reports