Skip to content

Introduce status column for the security_scans table

Mehmet Emin INAC requested to merge 341796_add_status_column_to_security_scans_table into master

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.

Edited by Mehmet Emin INAC

Merge request reports