Обсуждение: Full list of operations that constitute a "maintenance" operation?

Lance Luvaul wrote:
> Hi all, I've read on the Postgres documentation for 'maintenance_work_mem' that VACUUM, CREATE INDEX,
> and ALTER TABLE ADD FOREIGN KEY are considered maintenance operations, but are there others?
> For example I use ALTER TABLE ADD COLUMN and ALTER TABLE SET LOGGED in my scripts...
> are they maintenance operations that would cause a maintenance_work_mem-sized chunk of memory
> (or more than 1 such chunk) to be allocated?  Is there a complete list somewhere?  Thanks!

Trawling the source shows that it is used for index operations like CREATE INDEX and REINDEX,
but also for ALTER TABLE ADD FOREIGN KEY, VACUUM and CLUSTER.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

>>>>> "Lance" == Lance Luvaul <lance.luvaul@gmail.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)