Recently, I noticed that the postgresql database partition had been filled up. The general advice is to export data, drop and recreate database and import the data back in, since this would save a lot of time and also reduce the database size comparatively.
Reference: optimize postgresql database size.
Not too sure, if I wanted to go the dump and restore route, so decided to vacuum and reindex instead.
To my surprise, I did regain about 70% of space running the full vacuum and reindexing the database.
-
Full vacuum reduced the database size from 8GB to 4GB.
vacuumdb -afzv | tee /tmp/vacuumdb.log
reindexdb -a | tee /tmp/reindexdb.log
Check the vacuumdb.log for database optimization notices.
It is also advised to reindex prior to vacuuming to further save on vacuum time.