PostgreSQL的autovacuum 与 vacuum full

首先要了解 vacuum 与  vacuum all的区别：

vacuum 就是进行扫除，找到那些旧的“死”数据，把它们所知的行标记为可用状态。但是它不进行空间合并。

vacuum full，就是除了 vacuum，还进行空间合并，因此它需要lock table。

而 autovacuum，可以理解为 定时自动进行  vacuum 。

对于有大量update 的表，vacuum full是没有必要的，因为它的空间还会再次增长，所以vacuum就足够了。

所以说： standard VACUUMs often enough to avoid needing VACUUM FULL

并且：    The autovacuum daemon attempts to work this way, and in fact will never issue VACUUM FULL. 

http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html#AUTOVACUUM

The usual goal of routine vacuuming is to do standard VACUUMs often enough to avoid needing VACUUM FULL. The autovacuum daemon attempts to work this way, and in fact will never issue VACUUM FULL. In this approach, the idea is not to keep tables at their minimum size, but to maintain steady-state usage of disk space: each table occupies space equivalent to its minimum size plus however much space gets used up between vacuumings. Although VACUUM FULL can be used to shrink a table back to its minimum size and return the disk space to the operating system, there is not much point in this if the table will just grow again in the future. Thus, moderately-frequent standard VACUUM runs are a better approach than infrequent VACUUM FULL runs for maintaining heavily-updated tables.

因此：

Moderately-frequent standard VACUUM runs are a better approach than infrequent VACUUM FULL runs for maintaining heavily-updated tables.

 关于 VACUUM FULL：

VACUUM FULL requires 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 FULL要写新表、新文件的:

The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

对于 大量 update/delete 的表，普通的vacuum可能是不合适的，此时需要VACUUM FULL：

(有点前后矛盾,可能是指数据变更量更大的情况)

Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. If you have such a table and you need to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it. All these options require exclusive lock.

 

本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/p/3272620.html，如需转载请自行联系原作者