Get faster vacuums

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.