Optimize PostgreSQL Database Size

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
• Reindex then reduced further to 2.5GB.
  
  reindexdb -a | tee /tmp/reindexdb.log
• Total of 5.5GB space reduced with a full vacuum and reindex of the database.
  
• Check the vacuumdb.log for database optimization notices.
  
• It is also advised to reindex prior to vacuuming to further save on vacuum time.