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 cybrosys.com |
To check it by yourself, just type in this syntax when you get into
psql.
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_all_tables
ORDER BY n_dead_tup
/ (n_live_tup
* current_setting('autovacuum_vacuum_scale_factor')::float8
+ current_setting('autovacuum_vacuum_threshold')::float8)
DESC
LIMIT 10;
Then you might see, that there's many dead tuples that need to be clean up especially when your autovacuum are turned off.
There are some parameter that commonly used when doing maintenance, they are :
VACUUM
[FULL] [FREEZE] [VERBOSE]- 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 :
- Login as root in your linux server;
- Type in "su postgresql" without quote;
- Type in "psql", also without quote mark;
- Connect to your database by typing "\c yourDatabaseName";
- And finally, simply type "VACUUM VERBOSE" and let it done.
You can also make vacuum being auto, which means AUTOVACUUM will tho this entire job automatically. As default, it's already enabled so to make sure, do the step above until number 4, then type in
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;You can also make vacuum being auto, which means AUTOVACUUM will tho this entire job automatically. As default, it's already enabled so to make sure, do the step above until number 4, then type in
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 :
SHOW autovacuum;