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 to be used by maintenance operations, such as
CREATE INDEX, and
ALTER TABLE ADD FOREIGN KEY. It defaults to 16 megabytes (
16MB). Since only one of these operations can be executed at a time by a database session, 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.
Note that when autovacuum runs, up to
autovacuum_max_workers times this memory may be allocated, 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 won't necessarily improve performance.