DependencyTrack / DependencyTrack/dependency-track

Database performance improvements with improved indexes for table "VULNERABLESOFTWARE"

Open
#6,904 1 comment 0 reactions 0 assignees View on GitHub
enhancement
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.