Re: Full list of operations that constitute a "maintenance" operation?

>>>>> "Lance" == Lance Luvaul <lance(dot)luvaul(at)gmail(dot)com> 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)