Vacuum
Vacuum
Vacuum database:
(postgres@host)_$: vacuumdb <database>
Check when the last vacuum was done:
(postgres@host)_$: psql database-devel
database-devel=# SELECT relname, last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY relname;
database-devel=# VACUUM list;
database-devel=# VACUUM ANALYZE list;
database-devel=# VACUUM ANALYZE VERBOSE list;
database-devel=# VACUUM FULL list; -- Very costly
Autovacuum
Check autovacuum is active:
(postgres@host)_$: psql database-devel
database-devel=# show autovacuum;
autovacuum
------------
on
(1 row)
You can automate it with cron:
(postgres@host)_$: crontab -e
...
# m h dom mon dow command
0 9 * * * /usr/bin/vacuumdb --analyze database-devel