DependencyTrack / DependencyTrack/dependency-track
Database performance improvements with improved indexes for table "VULNERABLESOFTWARE"
- Dominant language
- Java
- Stars
- 4.2k
- Forks
- 811
- Avg merge
- 8h 39m
- Merged PRs (30d)
- 237
Description
### Current Behavior
Hi,
Me and my team are running 30 Dependency Track (DT) instances with database sizes up to ~45 GB.
Depenendency Track version: 4.14.3
Database: PostgreSQL 15.8
Our CI pipelines report new projects to those DT instances and get errors from time to time which we traced back to database performance issues.
So with the help of our database admins we took a closer look at the executed queries using [pgBadger](http://pgbadger.darold.net/).
As of now we did the following and were able to improve the execution times of the most time consuming queries significantly:
Execution times:
- Before: ~198.2s
- After: ~1.2s
Database schema changes:
```
create index concurrently on "VULNERABLESOFTWARE"("VERSIONENDEXCLUDING","PURL_NAME");
create index concurrently on "VULNERABLESOFTWARE"("VERSIONENDEXCLUDING", "VERSION");
create index concurrently on "VULNERABLESOFTWARE"("VERSION");
drop index concurrently if exists "VULNERABLESOFTWARE_VERSION_VERSIONENDEXCLUDING_idx";
```
Most time consuming query according to pgBadger:
```
SELECT 'org.dependencytrack.model.VulnerableSoftware' AS "DN_TYPE",
"A0"."CPE22",
"A0"."CPE23",
"A0"."EDITION",
"A0"."ID",
"A0"."LANGUAGE",
"A0"."OTHER",
"A0"."PART",
"A0"."PRODUCT",
"A0"."PURL",
"A0"."PURL_NAME",
"A0"."PURL_NAMESPACE",
"A0"."PURL_QUALIFIERS",
"A0"."PURL_SUBPATH",
"A0"."PURL_TYPE",
"A0"."PURL_VERSION",
"A0"."SWEDITION",
"A0"."TARGETHW",
"A0"."TARGETSW",
"A0"."UPDATE",
"A0"."UUID",
"A0"."VENDOR",
"A0"."VERSION",
"A0"."VERSIONENDEXCLUDING",
"A0"."VERSIONENDINCLUDING",
"A0"."VERSIONSTARTEXCLUDING",
"A0"."VERSIONSTARTINCLUDING",
"A0"."VULNERABLE"
FROM "VULNERABLESOFTWARE" "A0"
WHERE "A0"."PURL_TYPE" = 'deb'
AND "A0"."PURL_NAME" = 'linux-gke'
AND "A0"."PURL_NAMESPACE" = 'ubuntu'
AND "A0"."VERSION" IS NULL
AND "A0"."VERSIONENDEXCLUDING" = '5.15.0-1107.113'
AND "A0"."VERSIONENDINCLUDING" IS NULL
AND "A0"."VERSIONSTARTEXCLUDING" IS NULL
AND "A0"."VERSIONSTARTINCLUDING" IS NULL FETCH NEXT ROW ONLY;
```
We haven't looked into DT v5 yet, but maybe the suggestions are also applicable to DT v5 in a slightly adjusted way?
### Proposed Behavior
I'm expected to see improvements in the error rate during reporting of new projects to DT and the overall database performance in terms of query execution times, CPU usage and IO usage (because of less used buffers).
### Checklist
- [x] I have read and understand the [contributing guidelines](https://github.com/DependencyTrack/dependency-track/blob/main/CONTRIBUTING.md#filing-issues)
- [x] I have checked the [existing issues](https://github.com/DependencyTrack/dependency-track/issues) for whether this enhancement was already requested
Contributor guide
Research direction
Start by reviewing the VULNERABLESOFTWARE schema and the slow query identified with pgBadger, then compare the proposed concurrent indexes with the existing index named in the issue. Check how database schema changes are managed for Dependency-Track and whether the changes apply to version 5. Done means validated query, CPU, I/O, and reporting improvements without harming migration or write behavior.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql
- Domain
- databases, performance
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Active
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100