Database Reference
In-Depth Information
Automatic vacuuming
The automatic way is the most effective way to ensure the performance of your database. It
allows you to configure each table or database so you are informed about when the
auto-
vacuum
should be performed. For example:
$ heroku pg:psql --app your-app-name
=> ALTER TABLE invitations SET (autovacuum_vacuum_threshold
= 60);
=> ALTER TABLE invitations SET
(autovacuum_vacuum_scale_factor = 0.4);
The preceding setting sets the time that the
autovacuum
should run in your table. The
threshold value defines the raw number of necessary dead lines and the scale factor defines
the
bloat
factor to run the
autovacuum
process. The default values for these settings
are 50 and 0.2 respectively.
With these two settings you get the real limit through the following formula:
vacuum threshold = autovacuum_vacuum_threshold +
autovacuum_vacuum_scale_factor * number of rows
You can also impose these settings at the database level, through the following settings:
$ heroku pg:psql --app your-app-name
=> SELECT current_database();
current_database
------------------
d6ufnl4ugp8sq1
(1 row)
=> ALTER DATABASE d6ufnl4ugp8sq1 SET vacuum_cost_page_miss =
10;
=> ALTER DATABASE d6ufnl4ugp8sq1 SET vacuum_cost_limit = 200;
=> ALTER DATABASE d6ufnl4ugp8sq1 SET vacuum_cost_page_hit =
1;
=> ALTER DATABASE d6ufnl4ugp8sq1 SET vacuum_cost_page_dirty
= 20;