Introduce status column for the security_scans table
What does this MR do and why?
This MR introduces a new column called status
for the security_scans
table along with the logic to start setting it and a background migration to populate the values for the existing records.
Related to #341796 (closed).
Database review
The pre-deployment migration
This migration introduces the new column for the security_scans
table called status
.
rake db:migrate:up
== 20211007113136 AddStatusColumnToSecurityScansTable: migrating ==============
-- add_column(:security_scans, :status, :integer, {:limit=>1, :default=>0, :null=>false})
-> 0.0218s
== 20211007113136 AddStatusColumnToSecurityScansTable: migrated (0.0219s) =====
rake db:rollback
== 20211007113136 AddStatusColumnToSecurityScansTable: reverting ==============
-- remove_column(:security_scans, :status, :integer, {:limit=>1, :default=>0, :null=>false})
-> 0.0080s
== 20211007113136 AddStatusColumnToSecurityScansTable: reverted (0.0142s) =====
The post-deployment migration
This migration schedules background jobs to populate the values of the column introduced by the previous migration for the existing records.
As of writing, we have 11_851_491 records in the security_scans
table. Given the batch size is set as 10K, there will be 1_185 background jobs created by this scheduler migration. As the time interval is set as 2 minutes, the background jobs should be finished in 39 hours.
I didn't add any SQL query or explain result for this migration as the batching will be done without any filtering.
rake db:migrate
== 20211007155221 SchedulePopulateStatusColumnOfSecurityScans: migrating ======
-- Scheduled 0 PopulateStatusColumnOfSecurityScans jobs with a maximum of 10000 records per batch and an interval of 120 seconds.
The migration is expected to take at least 0 seconds. Expect all jobs to have completed after 2021-10-07 17:36:35 UTC."
== 20211007155221 SchedulePopulateStatusColumnOfSecurityScans: migrated (0.0298s)
rake db:rollback
== 20211007155221 SchedulePopulateStatusColumnOfSecurityScans: reverting ======
== 20211007155221 SchedulePopulateStatusColumnOfSecurityScans: reverted (0.0000s)
The background migration
The logic of the background migration is pretty straightforward; it runs update queries to update the status column of the security_scans
records in batches of 500 records at once.
The update query
I can only provide the SQL query for this action as the column is not yet on production.
UPDATE
security_scans
SET
status = (
CASE WHEN ci_builds.status = 'success' THEN
1
ELSE
2
END)
FROM
ci_builds
WHERE
ci_builds.id = security_scans.build_id
AND security_scans.id BETWEEN ? AND ?
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.