Batch seats exceeded notifications

What does this MR do and why?

Issue: #358080 (closed)

This introduces a new service GitlabSubscriptions::NotifySeatsExceededBatchService that will eventually replace GitlabSubscriptions::NotifySeatsExceededService. The new batch service will run daily via a cron job to alert namespace owners of seat overages that have occurred on their subscription.

Note: The bulk of this MR has not been hooked up yet and will be in a later MR.

query

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13576/commands/47644

SELECT "namespaces"."id", "namespaces"."name", "namespaces"."path", "namespaces"."owner_id", "namespaces"."created_at", "namespaces"."updated_at", "namespaces"."type", "namespaces"."description", "namespaces"."avatar", "namespaces"."membership_lock", "namespaces"."share_with_group_lock", "namespaces"."visibility_level", "namespaces"."request_access_enabled", "namespaces"."ldap_sync_status", "namespaces"."ldap_sync_error", "namespaces"."ldap_sync_last_update_at", "namespaces"."ldap_sync_last_successful_update_at", "namespaces"."ldap_sync_last_sync_at", "namespaces"."description_html", "namespaces"."lfs_enabled", "namespaces"."parent_id", "namespaces"."shared_runners_minutes_limit", "namespaces"."repository_size_limit", "namespaces"."require_two_factor_authentication", "namespaces"."two_factor_grace_period", "namespaces"."cached_markdown_version", "namespaces"."project_creation_level", "namespaces"."runners_token", "namespaces"."file_template_project_id", "namespaces"."saml_discovery_token", "namespaces"."runners_token_encrypted", "namespaces"."custom_project_templates_group_id", "namespaces"."auto_devops_enabled", "namespaces"."extra_shared_runners_minutes_limit", "namespaces"."last_ci_minutes_notification_at", "namespaces"."last_ci_minutes_usage_notification_level", "namespaces"."subgroup_creation_level", "namespaces"."emails_disabled", "namespaces"."max_pages_size", "namespaces"."max_artifacts_size", "namespaces"."mentions_disabled", "namespaces"."default_branch_protection", "namespaces"."unlock_membership_to_ldap", "namespaces"."max_personal_access_token_lifetime", "namespaces"."push_rule_id", "namespaces"."shared_runners_enabled", "namespaces"."allow_descendants_override_disabled_shared_runners", "namespaces"."traversal_ids" FROM "namespaces" WHERE "namespaces"."id" IN (SELECT "gitlab_subscriptions"."namespace_id" FROM "gitlab_subscriptions" WHERE (max_seats_used_changed_at >= '2022-11-28 00:00:00') AND (max_seats_used_changed_at <= '2022-11-29 00:00:00') ORDER BY "gitlab_subscriptions"."max_seats_used_changed_at" ASC, "gitlab_subscriptions"."namespace_id" ASC LIMIT 100)

plan

 Nested Loop  (cost=10.32..369.01 rows=100 width=365) (actual time=552.986..1248.382 rows=100 loops=1)
   Buffers: shared hit=253 read=453 dirtied=33
   I/O Timings: read=1186.140 write=0.000
   ->  HashAggregate  (cost=9.76..10.76 rows=100 width=4) (actual time=541.015..541.334 rows=100 loops=1)
         Group Key: gitlab_subscriptions.namespace_id
         Buffers: shared hit=27 read=173 dirtied=30
         I/O Timings: read=484.773 write=0.000
         ->  Limit  (cost=0.43..8.51 rows=100 width=12) (actual time=30.929..540.643 rows=100 loops=1)
               Buffers: shared hit=27 read=173 dirtied=30
               I/O Timings: read=484.773 write=0.000
               ->  Index Only Scan using index_gitlab_subscriptions_on_max_seats_used_changed_at on public.gitlab_subscriptions  (cost=0.43..12.38 rows=148 width=12) (actual time=30.927..540.522 rows=100 loops=1)
                     Index Cond: ((gitlab_subscriptions.max_seats_used_changed_at >= '2022-11-28 00:00:00+00'::timestamp with time zone) AND (gitlab_subscriptions.max_seats_used_changed_at <= '2022-11-29 00:00:00+00'::timestamp with time zone))
                     Heap Fetches: 203
                     Buffers: shared hit=27 read=173 dirtied=30
                     I/O Timings: read=484.773 write=0.000
   ->  Index Scan using namespaces_pkey on public.namespaces  (cost=0.56..3.58 rows=1 width=365) (actual time=7.064..7.064 rows=1 loops=100)
         Index Cond: (namespaces.id = gitlab_subscriptions.namespace_id)
         Buffers: shared hit=223 read=280
         I/O Timings: read=701.367 write=0.000

recommendations

❗️ Query processes too much data to return a relatively small number of rows. – Reduce data cardinality as early as possible during the execution, using one or several of the following techniques: new indexes, partitioning, query rewriting, denormalization. See the visualization of the plan to understand which plan nodes are the main bottlenecks. <http://momjian.us/main/writings/pgsql/hw_performance/|Show details>
❗️ VACUUM ANALYZE needed – Visibility map(s) for the table(s) involved in the query are outdated. For better performance: 1) run `VACUUM ANALYZE` on them as a one-time solution, 2) perform autovacuum tuning as a long-term permanent solution (tuning "vacuum" part of autovacuum). <https://postgres.ai/#tip-vacuum-analyze-needed|Show details>

statistics

Time: 1.253 s
  - planning: 3.868 ms
  - execution: 1.249 s
    - I/O read: 1.186 s
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 253 (~2.00 MiB) from the buffer pool
  - reads: 453 (~3.50 MiB) from the OS file cache, including disk I/O
  - dirtied: 33 (~264.00 KiB)
  - writes: 0

How to set up and validate locally

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

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.