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.
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.
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.
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;