Prune web hook logs older than 90 days

This adds a recurring Sidekiq job that removes up to 50 000 old web hook
logs per hour, if they are older than 90 days. This will prevent the
web_hook_logs table from growing indefinitely.

Fixes https://gitlab.com/gitlab-org/gitlab-ce/issues/46120

2 files changed, 27 insertions, 0 deletions

diff --git a/app/workers/all_queues.yml b/app/workers/all_queues.yml
index d06f51b1828..b8b854853b7 100644
--- a/app/workers/all_queues.yml
+++ b/app/workers/all_queues.yml
@@ -20,6 +20,7 @@
 - cronjob:ci_archive_traces_cron
 - cronjob:trending_projects
 - cronjob:issue_due_scheduler
+- cronjob:prune_web_hook_logs
 
 - gcp_cluster:cluster_install_app
 - gcp_cluster:cluster_provision
diff --git a/app/workers/prune_web_hook_logs_worker.rb b/app/workers/prune_web_hook_logs_worker.rb
new file mode 100644
index 00000000000..45c7d32f7eb
--- /dev/null
+++ b/app/workers/prune_web_hook_logs_worker.rb
@@ -0,0 +1,26 @@
+# frozen_string_literal: true
+
+# Worker that deletes a fixed number of outdated rows from the "web_hook_logs"
+# table.
+class PruneWebHookLogsWorker
+  include ApplicationWorker
+  include CronjobQueue
+
+  # The maximum number of rows to remove in a single job.
+  DELETE_LIMIT = 50_000
+
+  def perform
+    # MySQL doesn't allow "DELETE FROM ... WHERE id IN ( ... )" if the inner
+    # query refers to the same table. To work around this we wrap the IN body in
+    # another sub query.
+    WebHookLog
+      .where(
+        'id IN (SELECT id FROM (?) ids_to_remove)',
+        WebHookLog
+          .select(:id)
+          .where('created_at < ?', 90.days.ago.beginning_of_day)
+          .limit(DELETE_LIMIT)
+      )
+      .delete_all
+  end
+end