>>>>> "Lance" == Lance Luvaul <lance.luvaul@xxxxxxxxx> writes: Lance> Hi all, I've read on the Postgres documentation for Lance> 'maintenance_work_mem' that VACUUM, CREATE INDEX, and ALTER Lance> TABLE ADD FOREIGN KEY are considered maintenance operations, but Lance> are there others? For example I use ALTER TABLE ADD COLUMN and Lance> ALTER TABLE SET LOGGED in my scripts... are they maintenance Lance> operations that would cause a maintenance_work_mem-sized chunk Lance> of memory (or more than 1 such chunk) to be allocated? There are essentially two classes of operations that use maintenance_work_mem: 1. Specific operations: non-full VACUUM (for the deleted tid list), CLUSTER (for sorting the table content), ALTER ... ADD FOREIGN KEY (for the validation query, which might want to use sorts or a hashjoin). (It's not impossible, though it should be rare, for an FK validation to use two chunks of maintenance_work_mem - with the right table sizes and data types, the validation query could plan as a merge anti-join with explicit sorts on both sides.) 2. Anything that (re)builds an index for any reason. This includes a large set of operations: CREATE INDEX and REINDEX are obvious, likewise VACUUM FULL and CLUSTER, but also any form of ALTER TABLE that rewrites the heap (which _includes_ SET LOGGED, but does not include adding a column with no default, or in pg11+ adding a column with a default). It also includes non-concurrent refresh of a materialized view. (TRUNCATE also technically rewrites the table heap, but since the new heap is empty, memory consumption during reindex is not an issue.) ALTER TABLE to add a unique, primary key or exclusion constraint also creates an index to implement the constraint with (unless USING INDEX was used to adopt an existing index) so that counts too. -- Andrew (irc:RhodiumToad)
