Prune Sonarqube DB

SonarQube Server / Community Build
1

Which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension)
Sonarqube 7.9.2 Enterprise Edition
Scanner version 4.3.0.2102
DB: Postgres v10.x (AWS RDS)

What are you trying to achieve
During the recent upgrade from v6.7.5 to v7.9.2, it was decided that we really needed to prune our SQ server and hence bringing the Postgres DB to a smaller size. Our current DB size is 164GB, this was only achieved by VACUUM FULL as the DB was at 250GB straight after upgrade. Here are the heavy tables in desc order:
relation | total_size
---------------------------±-----------
live_measures | 81 GB
file_sources | 66 GB
projects | 11 GB
issue_changes | 2455 MB
issues | 1703 MB
project_measures | 1333 MB
ce_activity | 100 MB
ce_scanner_context | 48 MB
snapshots | 26 MB
events | 25 MB
ce_task_characteristics | 13 MB

So, its apparent that live_measures and file_sources tables need to be pruned. Can you please shed some light as to what gets written in here? We have over 500 developers and over 300 active projects with over 15 different languages. Basically I am looking for a way to do some quick clean-up and then implement this an as automation to keep it clean and concise going forward.
Are there any automation scripts that already exist in order to do carry out house-keeping jobs?

What have you tried so far to achieve this
I have tried to understand the SQ DB structure and ploughing through Sonar community posts but I couldn’t find any.

Thank you in advance.

2

Live Measures stores all the latest measures for all components. File Sources stores the source code and related metadata (again, only latest version).
You can’t delete data from these tables without breaking SonarQube. If you really need to reduce the disk use you’ll have to delete projects or branches.

3

@dmeneses, thanks so much for the prompt reply. I will have to work with our developers to do this gradually.
Do you know whether there are any maintenance / house-keeping jobs exist on Open Source that could help us get this done much sooner. I don’t want to “re-invent the wheel”, I am sure some of our colleagues have already achieved what I am trying to do here.

Thanks again.

4

I don’t know of any and I doubt there are any tools like that.

160GB sounds reasonable for 300 active projects. Just so that I understand, you have some limits on how much disk you can use?

5

We don’t have a hard limit on space. Its just we upgraded to v7.9.2 last week and had issue with CE engine where it took significantly longer to create quality gate reports than the previous versions (6.7.5). We have iOS and Android projects, some took over an hour. After a lot of reading and being a Sys Admin in the past we’ve nailed down the issue - we had to run ANALYZE and VACUUM FULL, this indeed resolved the issues. Vacuum took about 3 hours so we want to do some clean-up so the DB is free of unwanted data (VACUUM takes less time) and optimal so it functions with speed.