Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



>>>>> "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)




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux