Anormally large PostgreSQL Database size

Hi everyone,

We have recently known that our Sonarqube LTS database, placed in an AWS RDS instance and with PostgreSQL 12 core, has a size of nearly 190 Gb:

If we type in Pgadmin data query:

SELECT pg_size_pretty(pg_database_size(‘<name_of_sonarqubeDB’));

the result is 190 Gb and if we look into the tables size, the table “file_sources” has a size of 52 Gb.

As a workaround, we have deleted manually all the PRs and branches analysis previous to a certain date, and we have manually deleted all the “file_sources” table rows previous to a certain date regarding to the condition above.

After that, we have looked that the size of the database and the “file_sources” table remains the same.
The DBCleaner parameters in our Sonarqube instance are set by default and works well.

¿How can we cleanup the size of the database?

Thanks in advance.

Hey there.

You cannot manually update the SonarQube database. You should consider that your database is corrupted and restore to a version that hasn’t been manually altered.

Do you regularly run a VACUUM on your database to reclaim freed space?

Hi Colin and thanks in advance for your quick answer,

Theorically, in the pgAdmin database properties the AUTOVACUUM property is set to 1, so theorically the AUTOVACUUM operations must be done automatically.
Here are the autovacuum properties of our Sonarqube Postgre database:

autovacuum on
autovacuum_analyze_scale_factor 0.05
autovacuum_analyze_threshold 50
autovacuum_freeze_max_age 200000000
autovacuum_max_workers 3
autovacuum_multixact_freeze_max_age 400000000
autovacuum_naptime 15
autovacuum_vacuum_cost_delay 2
autovacuum_vacuum_cost_limit 200
autovacuum_vacuum_scale_factor 0.1
autovacuum_vacuum_threshold 50
autovacuum_work_mem -1

We don’t know if this properties are OK or not, but are the default properties I think.
Regarding to VACUUM operations, we don’t know exactly if it should be done a VACUUM FULL for all the database or only a VACUUM and what kind of result must throw this command.

Finally, our database is not corrupted at all, so we are sure of this. Neverthless, we’ll be more careful to not update or delete anything in the database directly.

Best regards.