Parameter type: Integer
Default value: 16MB
Range: 1024kB to 2097151kB
Minimum scope of effect: Per session
When value changes take effect: Immediate
Required authorization to activate: Session user
Specifies the maximum amount of memory used by maintenance operations such as
CREATE INDEX, and
ALTER TABLE ADD FOREIGN KEY. It defaults to 16MB. Since a database session can execute only one of these operations at a time, and an installation normally doesn't have many of them running concurrently, it's safe to set this value significantly larger than
work_mem. Larger settings might improve performance for vacuuming and for restoring database dumps.
When autovacuum runs, you can allocate up to
autovacuum_max_workers times, so be careful not to set the default value too high.
A good rule of thumb is to set this to about 5% of system memory but not more than about 512MB. Larger values don't necessarily improve performance.