NAME
VACUUM - 垃圾收集以及可选地分析一个数据库
SYNOPSIS
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
DESCRIPTION 描述
VACUUM 回收已删除元组占据的存储空间。 在一般的 PostgreSQL 操作里， 那些已经 DELETE 的元组或者被 UPDATE 过后过时的元组是没有从它们所属的表中物理删除的； 在完成 VACUUM 之前它们仍然存在。 因此我们有必须周期地运行 VACUUM， 特别是在常更新的表上。
如果没有参数，VACUUM 处理当前数据库里每个表， 如果有参数，VACUUM 只处理那个表。
VACUUM ANALYZE 先执行一个 VACUUM 然后是给每个选定的表执行一个 ANALYZE。 对于日常维护脚本而言，这是一个很方便的组合。参阅 ANALYZE [analyze(7)] 获取更多有关其处理的细节。
简单的 VACUUM （没有FULL） 只是简单地回收空间并且令其可以再次使用。这种形式的命令可以和对表的普通读写并行操作， 因为没有请求排他锁。VACUUM FULL 执行更广泛的处理，包括跨块移动元组，以便把表压缩到最少的磁盘块数目里。 这种形式要慢许多并且在处理的时候需要在表上施加一个排它锁。
FREEZE 是一种特殊用途的选项，它导致元组尽可能快地标记为"冻结(frozen)"， 而不是等到它们已经相当老的时候才标记。如果在同一个数据库上没有其它运行着的事务的时候完成这个命令， 那么系统就保证在数据库里的所有元组都是"冻结(frozen)"的， 因此不会有事务 ID 重叠的问题，而和数据库未清理的时间没有关系。 我们不建议把 FREEZE 用做日常用途。我们用它的***目地是准备和用户定义的模板数据库联接的时候， 或者是其它完全是只读的， 不会等到日常维护性 VACUUM 操作的数据库。 参阅 Chapter 21 ``Routine Database Maintenance'' 获取细节。
PARAMETERS 参数
- FULL
选择"完全"清理，这样可以恢复更多的空间， 但是花的时间更多并且在表上施加了排它锁。- FREEZE
选择激进的元组"冻结"。- VERBOSE
为每个表打印一份详细的清理工作报告。- ANALYZE
更新用于优化器的统计信息，以决定执行查询的最有效方法。- table
要清理的表的名称（可以有模式修饰）。缺省时是当前数据库中的所有表。- column
要分析的具体的列/字段名称。缺省是所有列/字段。
OUTPUTS 输出
如果声明了 VERBOSE，VACUUM 发出过程信息， 以表明当前正在处理那个表。各种有关这些表的统计也会打印出来。
NOTES 注意
我们建议在经常VACUUMM（清理）（至少每晚一次）生产数据库， 以保证不断地删除失效的行。尤其是在增删了大量记录之后， 对受影响的表执行 VACUUM ANALYZE 命令是一个很好的习惯。这样做将更新系统目录为最近的更改，并且允许 PostgreSQL 查询优化器在规划用户查询时有更好的选择。
我们不建议日常使用 FULL 选项，但是可以在特殊情况下使用。 一个例子就是在你删除了一个表的大部分行之后，希望从物理上缩小该表以减少磁盘空间占用。VACUUM FULL 通常要比单纯的 VACUUM 收缩更多表的尺寸。
EXAMPLES 例子
下面是一个在 regression （蜕变）数据库里某个表上执行 VACUUM的一个例子：
regression=# VACUUM VERBOSE ANALYZE onek; INFO: vacuuming "public.onek" INFO: index "onek_unique1" now contains 1000 tuples in 14 pages DETAIL: 3000 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.08u sec elapsed 0.18 sec. INFO: index "onek_unique2" now contains 1000 tuples in 16 pages DETAIL: 3000 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.07u sec elapsed 0.23 sec. INFO: index "onek_hundred" now contains 1000 tuples in 13 pages DETAIL: 3000 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.08u sec elapsed 0.17 sec. INFO: index "onek_stringu1" now contains 1000 tuples in 48 pages DETAIL: 3000 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.09u sec elapsed 0.59 sec. INFO: "onek": removed 3000 tuples in 108 pages DETAIL: CPU 0.01s/0.06u sec elapsed 0.07 sec. INFO: "onek": found 3000 removable, 1000 nonremovable tuples in 143 pages DETAIL: 0 dead tuples cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.07s/0.39u sec elapsed 1.56 sec. INFO: analyzing "public.onek" INFO: "onek": 36 pages, 1000 rows sampled, 1000 estimated total rows VACUUM
COMPATIBILITY 兼容性
SQL 标准里没有 VACUUM 语句。
SEE ALSO 参见
vacuumdb [vacuumdb(1)]
#p#
NAME
VACUUM - garbage-collect and optionally analyze a database
SYNOPSIS
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ table ] VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ]
DESCRIPTION
VACUUM reclaims storage occupied by deleted tuples. In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done. Therefore it's necessary to do VACUUM periodically, especially on frequently-updated tables.
With no parameter, VACUUM processes every table in the current database. With a parameter, VACUUM processes only that table.
VACUUM ANALYZE performs a VACUUM and then an ANALYZE for each selected table. This is a handy combination form for routine maintenance scripts. See ANALYZE [analyze(7)] for more details about its processing.
Plain VACUUM (without FULL) simply reclaims space and makes it available for re-use. This form of the command can operate in parallel with normal reading and writing of the table, as an exclusive lock is not obtained. VACUUM FULL does more extensive processing, including moving of tuples across blocks to try to compact the table to the minimum number of disk blocks. This form is much slower and requires an exclusive lock on each table while it is being processed.
FREEZE is a special-purpose option that causes tuples to be marked ``frozen'' as soon as possible, rather than waiting until they are quite old. If this is done when there are no other open transactions in the same database, then it is guaranteed that all tuples in the database are ``frozen'' and will not be subject to transaction ID wraparound problems, no matter how long the database is left unvacuumed. FREEZE is not recommended for routine use. Its only intended usage is in connection with preparation of user-defined template databases, or other databases that are completely read-only and will not receive routine maintenance VACUUM operations. See the chapter called ``Routine Database Maintenance'' in the documentation for details.
PARAMETERS
- FULL
- Selects ``full'' vacuum, which may reclaim more space, but takes much longer and exclusively locks the table.
- FREEZE
- Selects aggressive ``freezing'' of tuples.
- VERBOSE
- Prints a detailed vacuum activity report for each table.
- ANALYZE
- Updates statistics used by the planner to determine the most efficient way to execute a query.
- table
- The name (optionally schema-qualified) of a specific table to vacuum. Defaults to all tables in the current database.
- column
- The name of a specific column to analyze. Defaults to all columns.
OUTPUTS
When VERBOSE is specified, VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.
NOTES
We recommend that active production databases be vacuumed frequently (at least nightly), in order to remove expired rows. After adding or deleting a large number of rows, it may be a good idea to issue a VACUUM ANALYZE command for the affected table. This will update the system catalogs with the results of all recent changes, and allow the PostgreSQL query planner to make better choices in planning queries.
The FULL option is not recommended for routine use, but may be useful in special cases. An example is when you have deleted most of the rows in a table and would like the table to physically shrink to occupy less disk space. VACUUM FULL will usually shrink the table more than a plain VACUUM would.
EXAMPLES
The following is an example from running VACUUM on a table in the regression database:
regression=# VACUUM VERBOSE ANALYZE onek; INFO: vacuuming "public.onek" INFO: index "onek_unique1" now contains 1000 tuples in 14 pages DETAIL: 3000 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.08u sec elapsed 0.18 sec. INFO: index "onek_unique2" now contains 1000 tuples in 16 pages DETAIL: 3000 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.00s/0.07u sec elapsed 0.23 sec. INFO: index "onek_hundred" now contains 1000 tuples in 13 pages DETAIL: 3000 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.08u sec elapsed 0.17 sec. INFO: index "onek_stringu1" now contains 1000 tuples in 48 pages DETAIL: 3000 index tuples were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.09u sec elapsed 0.59 sec. INFO: "onek": removed 3000 tuples in 108 pages DETAIL: CPU 0.01s/0.06u sec elapsed 0.07 sec. INFO: "onek": found 3000 removable, 1000 nonremovable tuples in 143 pages DETAIL: 0 dead tuples cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 0.07s/0.39u sec elapsed 1.56 sec. INFO: analyzing "public.onek" INFO: "onek": 36 pages, 1000 rows sampled, 1000 estimated total rows VACUUM
COMPATIBILITY
There is no VACUUM statement in the SQL standard.
SEE ALSO
vacuumdb [vacuumdb(1)]
