SonarQube CE 10.4 deleted project data not removed from postgresql database

Setup

I’m having an issue with SonarQube CE 10.4 deployed through zip/.jar. SonarQube is connected to a PostgreSQL 15 database.

Goal

Recover database storage by deleting stale projects in SonarQube. (without having to scan them again to trigger housekeeping)
It seems that after deleting a (stale) project in the SonarQube webUI the data is not being removed from the database. This results in the database ever growing.

Is there a way to definitely remove old/unused/orphaned data from the database that should’ve been removed through deletion of the project in SonarQube?

Tried already

  • Reading the documentation on Housekeeping and database configuration.
  • Reading other questions regarding this issue on this forum and trying those suggestions.
  • Trying to give increased permissions to the database user.
  • Database maintenance tasks (VACUUM FULL, REINDEX, ANALYZE)
  • Deleting 2/3 of the projects that were stale through the SonarQube WebUI
  • Adjusting the Housekeeping settings
  • Looking in logs for error messages

Hi,

Welcome to the community!

How did you perform the deletion? Through the UI?

Also, this thread may help:

 
Ann

Hi Ann,

As mentioned in my post (see quote). We have deleted aprox. 1000 projects with only about 600 remaining. We went from 32M LoC to aprox. 8M LoC and the database size after a VACUUM FULL was not changed by the same ratio. At the moment we’re at a database size of 430GB (almost double after the big cleanup) while the projects (+66) and LoC (+3M) have grown slightly since.

Deleting 2/3 of the projects that were stale through the SonarQube WebUI

Same as the OP in the linked post I’m not a DBA. I will try the suggested fix (link) to see if this will solve anything. We have been performing the DB maintenance tasks like VACUUM regularly.

I will report back after trying the check.

Kind regards,

1 Like

The database maintenance. commands (VACUUM FULL, REINDEX, ANALYZE) worked this time to lower the disk size. Weird how it failed previously.
Maybe it had something to do with the Housekeeping settings together with DB maintenance.

To summarise:

  • Stop sonarqube service
  • Check sonarqube db size with
SELECT pg_size_pretty(pg_database_size('...database name...'));
  • Check TOAST Size with the following query (see linked post):
SELECT *, 
       Pg_size_pretty(total_bytes) AS total, 
       Pg_size_pretty(index_bytes) AS INDEX, 
       Pg_size_pretty(toast_bytes) AS toast, 
       Pg_size_pretty(table_bytes) AS TABLE 
FROM   (SELECT *, 
               total_bytes - index_bytes - Coalesce(toast_bytes, 0) AS 
               table_bytes 
        FROM   (SELECT c.oid, 
                       nspname                               AS table_schema, 
                       relname                               AS TABLE_NAME, 
                       c.reltuples                           AS row_estimate, 
                       Pg_total_relation_size(c.oid)         AS total_bytes, 
                       Pg_indexes_size(c.oid)                AS index_bytes, 
                       Pg_total_relation_size(reltoastrelid) AS toast_bytes 
                FROM   pg_class c 
                       LEFT JOIN pg_namespace n 
                              ON n.oid = c.relnamespace 
                WHERE  relkind = 'r') a 
        WHERE  table_schema = 'public' ORDER  BY total_bytes DESC) a;
  • Perform DB maintenance on the sonarqube database
1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.