Partitioning of sbom_components table
Problem to solve
The sbom_components table will grow bigger as we ingest project SBOMs, import license data from the License Database, and import vulnerability advisories for Continuous Vulnerability Scans. On GitLab.com, depending on what we import from the License DB, we might end up having one DB record per package listed on a supported package registry, that is millions of records.
Having a large number of records in sbom_components might have a negative impact on performance, especially when SELECTing a component by PURL type and name, to perform the following tasks:
- Add or update a vulnerability advisory for a component, when importing from the Advisory Database.
- Add or update license data for a component, when importing from the License DB.
Same for sbom_component_versions.
Proposal
Use list partitioning to have one partition per PURL type. This reduces the index size, and improve the performance of index lookup when SELECTing component with a given PURL type and name, as this is the case in License Scanning and Vulnerability Scanning.