In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. 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. The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. The space it occupies must then be reclaimed for reuse by new rows, to avoid unbounded growth of disk space requirements. But eventually, an outdated or deleted row version is no longer of interest to any transaction. This approach is necessary to gain the benefits of multiversion concurrency control ( MVCC, see Chapter 13): the row version must not be deleted while it is still potentially visible to other transactions. In PostgreSQL, an UPDATE or DELETE of a row does not immediately remove the old version of the row. There are configuration parameters that can be adjusted to reduce the performance impact of background vacuuming - see Section 20.4.4. VACUUM creates a substantial amount of I/O traffic, which can cause poor performance for other active sessions. Generally, therefore, administrators should strive to use standard VACUUM and avoid VACUUM FULL. (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. Also, the standard form of VACUUM can run in parallel with production database operations. VACUUM FULL can reclaim more disk space but runs much more slowly. There are two variants of VACUUM: standard VACUUM and VACUUM FULL. To protect against loss of very old data due to transaction ID wraparound or multixact ID wraparound.Įach of these reasons dictates performing VACUUM operations of varying frequency and scope, as explained in the following subsections.To update the visibility map, which speeds up index-only scans.To update data statistics used by the PostgreSQL query planner.To recover or reuse disk space occupied by updated or deleted rows.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |