After recently upgrading to SonarQube 8.9.2 (Enterprise), I next attempted to upgrade our PostgreSQL database from 9.6.19 to 13.3 (major step by step). It’s actually AWS RDS Aurora PostgreSQL. The small staging SonarQube instance I run continued to operate properly after upgrading 9.6 → 10 → 11 → 12 → 13. I went ahead and did the same upgrade for our much larger production instance (~5.5M LoC). In hindsight I should have done the major upgrades at a slower pace but wanted to attempt to minimize the impact of downtime.
After completing the upgrade to 13.3, the database immediately spiked to 100% CPU and remained there. I doubled the size of the RDS instance, and then doubled it again, and again. Still 100% CPU. SonarQube ground to a halt with little resource usage of its own. Downgrading to 12.7 presented the same problem. Downgrading back to 9.6 fixed database CPU usage and resumed normal functioning.
The documentation states that 8.9 LTS supports PostgreSQL 9, 10, 11, 12, and 13 with no caveats. Are there any caveats?
For what it’s worth, the below SQL query appeared to be the primary offender. No errors appeared on the SonarQube instance or RDS database logs.
SELECT
p.uuid as uuid,
p.uuid_path as uuidPath,
p.project_uuid as projectUuid,
p.module_uuid as moduleUuid,
p.module_uuid_path as moduleUuidPath,
p.main_branch_project_uuid as mainBranchProjectUuid,
p.kee as kee,
p.name as name,
p.long_name as longName,
p.description as description,
p.qualifier as qualifier,
p.scope as scope,
p.language as language,
p.root_uuid as rootUuid,
p.path as path,
p.enabled as enabled,
p.copy_component_uuid as copyComponentUuid,
p.private as isPrivate,
p.created_at as createdAt
FROM components p
INNER JOIN components module ON
module.project_uuid = p.project_uuid
and module.uuid = $1
and module.scope='PRJ' AND module.enabled = true
where
p.scope = $2
and p.enabled = true
and
p.module_uuid_path LIKE module.module_uuid_path || '%'