Skip to content

Implement `security_findings` partition management

Mehmet Emin INAC requested to merge 351524_implement_security_findings_partition_managements into master

What does this MR do and why?

As the security_findings table is finally partitioned, we are now implementing the logic to automatically create the necessary partitions and detach the no further necessary ones.

The idea is that we want to create a list of partitions each of which contains around 10 gigabytes of data and we want to detach the old partitions if all the related security_scans of that partition are older than 3 months and purged.

Rationale behind visualized

security_findings_partitioning__1_

Database review

New queries introduced by this MR

Selecting the `table_size` of the partition 
select pg_table_size('gitlab_partitions_dynamic.security_findings_1');

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12011/commands/42606

Selecting the last finding from the partition 
SELECT
    "security_findings".*
FROM
    "security_findings"
WHERE
    "security_findings"."partition_number" = 1
ORDER BY
    "security_findings"."id" DESC
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12011/commands/42607

Related to Implement retention period for Security::Findin... (#351524 - closed)

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