PostgreSQL database vacuum

In some cases, like a large report archiving or deletion, the Rudder interface will still display the old database size. This is because even if the database has been cleaned as requested, the physical storage backend did not reclaim space on the hard drive, resulting in a "fragmented" database. This is not an issue, as PostgreSQL handles this automatically, and new reports sent by the nodes to Rudder will fill the blanks in the database, resulting in a steady growth of the database. This task is handled by the autovacuum process, which periodically cleans the storage regularly to prevent database bloating.

However, to force this operation to free storage immediately, you can trigger a "vacuum full" operation by yourself, however keep in mind that this operation is very disk and memory intensive, and will lock both the Rudder interface and the reporting system for quite a long time with a big database.

Manual vacuuming using the psql binary. 

# You can either use sudo to change owner to the postgres user, or use the rudder connection credentials.

# With sudo:
sudo -u postgres psql -d rudder

# With rudder credentials, it will ask the password in this case:
psql -u rudder -d rudder -W

# And then, when you are connected to the rudder database in the psql shell, trigger a vacuum:
rudder=# VACUUM FULL;

# And take a coffee.