Skip to content Skip to sidebar Skip to footer

Odoo Database Maintenance Using Postgresql Vacuum

Vacuum is the term that used in postgresql as maintenance command to clear up and reclaim unused data and use that freed space for the next saving operation. The easy explanation is, imagine that you have a bookshelf, and it's empty in some row. You are planning to store some books in it, and proceeding to clean it up first from dust and other book that is messed up so you can acquire more space and make it look tidy. The same principal also apply to this vacuum operation. It cleans up every data that you've been deleted from Odoo, which the fact that it is not truly deleted that time. That data is called obsolete data, which only can be deleted and proceed when you are executing this vacuum order to reclaim the row that used, and reserve it for the next saving.

Odoo Database Maintenance - Source

There are some parameter that commonly used when doing maintenance, they are :

  • VACUUM, are the command that used to clear up the obsolete data and reclaim some space to be reserve for the next saving.
  • VACUUM FULL, means that this command will clear up the obsolete data and reclaim some space to be reserve, and every space that available will be returned as reserved to operating system. When executing this, all user and operation will be unable to make a change to database, which means it will be locked and only readable until operation is done. I am not recommending this command to be executed when in working hours.
  • VACUUM VERBOSE, has the same operation like VACUUM but it shows the running progress until it's done.
NOTE : For weekly maintenance, i recommend you to only use VACUUM VERBOSE since it doesn't lock your database for awhile.

To Use Vacuum on Odoo Database, follow there instruction :
  1. Login as root in your linux server;
  2. Type in "su postgresql" without quote;
  3. Type in "psql", also without quote mark;
  4. Connect to your database by typing "\c yourDatabaseName";
  5. And finally, simply type "VACUUM VERBOSE" and let it done.
To verify and check which Odoo table that vacuumed, you can type in this command to see the latest date of which table that has been vacuumed :

SELECT schemaname, relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count  FROM pg_stat_user_tables;