From a6a4d387975f9f2a414b813e5035957758829136 Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Sat, 22 Apr 2023 12:11:10 -0700 Subject: [PATCH v2 7/9] Make maintenance.sgml more autovacuum-orientated. Now that it's no longer in its own sect2, shorten the "Vacuuming basics" content, and make it more autovacuum-orientated. This gives much less prominence to VACUUM FULL, which has little place in a section about autovacuum. We no longer define avoiding the need to run VACUUM FULL as the purpose of vacuuming. A later commit that overhauls "Recovering Disk Space" will add back a passing mention of things like VACUUM FULL and TRUNCATE, but only as something that might be relevant in extreme cases. (Use of these commands is hopefully neither "Routine" nor "Basic" to most users). --- doc/src/sgml/maintenance.sgml | 91 +++++++++++++++++------------------ 1 file changed, 44 insertions(+), 47 deletions(-) diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml index 2e18a078a..7476e5922 100644 --- a/doc/src/sgml/maintenance.sgml +++ b/doc/src/sgml/maintenance.sgml @@ -32,11 +32,12 @@ - The other main category of maintenance task is periodic vacuuming - of the database. This activity is discussed in - . Closely related to this is updating - the statistics that will be used by the query planner, as discussed in - . + The other main category of maintenance task is periodic + vacuuming of + the database by autovacuum. Configuring autovacuum scheduling is + discussed in . Autovacuum also updates + the statistics that will be used by the query planner, as discussed + in . @@ -244,7 +245,7 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu - Routine Vacuuming + Autovacuum Maintenance Tasks vacuum @@ -252,24 +253,20 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu PostgreSQL databases require periodic - maintenance known as vacuuming. For many installations, it - is sufficient to let vacuuming be performed by the autovacuum - daemon, which is described in . You might - need to adjust the autovacuuming parameters described there to obtain best - results for your situation. Some database administrators will want to - supplement or replace the daemon's activities with manually-managed - VACUUM commands, which typically are executed according to a - schedule by cron or Task - Scheduler scripts. To set up manually-managed vacuuming properly, - it is essential to understand the issues discussed in the next few - subsections. Administrators who rely on autovacuuming may still wish - to skim this material to help them understand and adjust autovacuuming. + maintenance known as vacuuming, and require + periodic updates to the statistics used by the + PostgreSQL query planner. These + maintenance tasks are performed by the VACUUM and ANALYZE commands + respectively. For most installations, it is sufficient to let the + autovacuum daemon determine when to perform + these maintenance tasks (which is partly determined by configurable + table-level thresholds; see ). - - PostgreSQL's - VACUUM command has to - process each table on a regular basis for several reasons: + The autovacuum daemon has to process each table on a regular basis + for several reasons: @@ -295,35 +292,35 @@ analyze threshold = analyze base threshold + analyze scale factor * number of tu - Each of these reasons dictates performing VACUUM operations - of varying frequency and scope, as explained in the following subsections. + Maintenance work within the scope of items 1, 2, 3, and 4 is + performed by the VACUUM command internally. + Item 5 (maintenance of planner statistics) is handled by the + ANALYZE command internally. Although this + section presents information about autovacuum, there is no + difference between manually-issued VACUUM and + ANALYZE commands and those run by the autovacuum + daemon (though there are autovacuum-specific variants of a small + number of settings that control VACUUM). - - There are two variants of VACUUM: standard VACUUM - and VACUUM FULL. VACUUM FULL can reclaim more - disk space but runs much more slowly. Also, - the standard form of VACUUM can run in parallel with production - database operations. (Commands such as SELECT, - INSERT, UPDATE, and - DELETE will continue to function normally, though you - will not be able to modify the definition of a table with commands such as - ALTER TABLE while it is being vacuumed.) - VACUUM FULL requires an - ACCESS EXCLUSIVE lock on the table it is - working on, and therefore cannot be done in parallel with other use - of the table. Generally, therefore, - administrators should strive to use standard VACUUM and - avoid VACUUM FULL. - - - - VACUUM creates a substantial amount of I/O - traffic, which can cause poor performance for other active sessions. - There are configuration parameters that can be adjusted to reduce the - performance impact of background vacuuming — see + Autovacuum creates a substantial amount of I/O traffic, which can + cause poor performance for other active sessions. There are + configuration parameters that can be adjusted to reduce the + performance impact of background vacuuming. See the + autovacuum-specific cost delay settings described in + , and additional cost + delay settings described in . + + Some database administrators will want to supplement the daemon's + activities with manually-managed VACUUM + commands, which typically are executed according to a schedule by + cron or Task + Scheduler scripts. It can be useful to perform + off-hours VACUUM commands during periods where + reduced load is expected. + Recovering Disk Space -- 2.40.0