We have migrated to new Google sites!
Post date: Nov 06, 2014 1:30:27 AM
The amount of memory pg uses for vacuuming by default is super low. You can increase it and thus dramatically speed up vacuuming by doing something like:
How to find current setting?
sachi=# show maintenance_work_mem
sachi-# ;
maintenance_work_mem
----------------------
64MB
(1 row)
sachi=# select name, setting, unit from pg_settings where name='maintenance_work_mem';
name | setting | unit
----------------------+---------+------
maintenance_work_mem | 65536 | kB
(1 row)
Specifies the maximum amount of memory (in kilobytes) to be used in maintenance operations, such as VACUUM or CREATE INDEX. Since only one of these operations can be executed at a time by a database session, and an installation normally doesn't have very many of them happening concurrently, it's safe to set this value significantly larger than work_mem. Larger settings may improve performance for vacuuming and for restoring database dumps.
Set Classifications
master
session
reload
Now set the maintenance_work_mem to 512MB.
sachi=# set maintenance_work_mem = 524288