Get faster vacuums

posted Nov 5, 2014, 5:30 PM by Sachchida Ojha   [ updated Nov 5, 2014, 6:16 PM ]
The gadget spec URL could not be found
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-# ;
(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

Now set the maintenance_work_mem to 512MB.

sachi=# set maintenance_work_mem = 524288
The gadget spec URL could not be found
The gadget spec URL could not be found