Skip to content

Don't update `vulnerability_reads` records unnecessarily

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
The correlation between !144618 (merged)(deployed on February 21st) and the increase on number of tuples updated can be seen here(internal link);

Screenshot_2024-04-19_at_15.57.17

Database 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

Merge request reports