Don't update `vulnerability_reads` records unnecessarily
requested to merge minac_reduce_the_number_of_unnecessary_tuple_updates_on_vulnerability_reads into master
What does this MR do and why?
We have started updating the vulnerability_reads
records always by Security Report Ingestion set `traversal_ids`, ... (!144618 - merged). This was a mistake as it caused updating the records unnecessarily as well as updating the indices which increased the number of write-ahead logs generated. This is called write amplification and a well-known issue with PostgreSQL.
This MR fixes that behavior. The rationale behind this change can be found in the commit message;
The `traversal_ids` and `archived` attributes are already set for the
`vulnerability_reads` records associated with existing `vulnerability`
records so updating those records unnecessarily creates new tuples in
the heap and causes the indices to be updated associated with the old
tuples due to the MVCC design choice of PostgreSQL.
This causes an increase in the number of write-ahead logs generated.
By not updating the `vulnerability_reads` records unnecessarily, we
will reduce the amount of stress we put on the main DB as well as hot
standbys.
Changelog: performance
EE: true
!144618 (merged)(deployed on February 21st) and the increase on number of tuples updated can be seen here(internal link);
The correlation betweenDatabase review
There is only one additional query introduced by this MR which loads existing vulnerability_reads
records;
SELECT
"vulnerability_reads".*
FROM
"vulnerability_reads"
WHERE
"vulnerability_reads"."vulnerability_id" IN (115564274, 114550727, 114550726, 114550725, 114550724, 114550723, 113048263, 112658927, 112658926, 112658925, 111580082, 110425031, 110425030, 110425029, 110425028, 110425027, 110425026, 110138473, 109778371, 109288034, 109241050, 89413488, 86808184, 79042684, 77964096, 65469685, 61178364, 115605761, 112551168, 112551167, 112113513, 106067469, 102970603, 98154187, 96552093, 88524042, 86884936, 86884935, 77052738, 76546931, 75608344, 75608343, 75608342, 70870344, 67639094, 65204916, 65118494, 65118492, 61721656, 61465135)
https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27658/commands/86283
Edited by Mehmet Emin INAC