which versions are you using (SonarQube, Scanner, Plugin, and any relevant extension)
SonarQube 10.4 Enterprise
SonarScanner 6.1
how is SonarQube deployed: zip, Docker, Helm
zip
PostgreSQL 15.1
what are you trying to achieve
We are trying to run an analysis on all repositories in the company ~3000 repositories part of an security audit.
To do this we have made some scripts that run analyses in parallel and then we save only the analysis with bad quality gate statuses and delete all the passed statuses.
the issue is the database server does not release the disk space of deleted projects because
as you can see the tables do not hold much data only around 1 Gb
Yet our database is almost full at 50 GB and we can’t seem to reclaim this disk space back even after projects are deleted and we get our reports
Is there any other sort of data stored on the database server that might not be obvious at first (logs or other stuff) we are analyzing a large ammount of code in a short period of time but we dont need to persist it as this is a one time action, what am i missing here, how can we reclaim the database disk ?
I’m not a database expert but in normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables.
Talk with your database admin or review the VACUUM command:
I tried doing a VACUUM FULL but nothing changed regarding the disk space.
We are going to increase the size of the database but i still want to understand what is filling our database since it’s not evident from the data stored in the tables and the discrepancy from around 1 Gb from the screenshot i attached up to 50 Gb on the disk usage is too big.
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;
It will show your total bytes for each table, including TOAST data.
TOAST means The Oversized-Attribute Storage Technique. This is your “extra” size in disk. You will see which tables are bigger and how much space you can free with the VACUUM operation.
Not always you can recover all the space. The only way to full recover the disk space is to “dump” your database and “restore” it so that all the rows are inserted and all the free space is used. If the disk space is the same after restoring from a dump/backup then you can’t do anything. The space in database is real.
