VACUUM SQL - Language Statements VACUUM Clean and optionally analyze a Postgres database 2001-05-04 VACUUM [ VERBOSE ] [ table ] VACUUM [ VERBOSE ] ANALYZE [ table [ (column [, ...] ) ] ] 1998-10-04 VERBOSE Prints a detailed vacuum activity report for each table. ANALYZE Updates statistics used by the optimizer to determine the most efficient way to execute a query. table The name of a specific table to vacuum. Defaults to all tables. column The name of a specific column to analyze. Defaults to all columns. 1998-10-04 VACUUM The command is complete. NOTICE: --Relation table-- The report header for table. NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0; Tup 1000: Vac 3000, Crash 0, UnUsed 0, MinLen 188, MaxLen 188; Re-using: Free/Avail. Space 586952/586952; EndEmpty/Avail. Pages 0/74. Elapsed 0/0 sec. The analysis for table itself. NOTICE: Index index: Pages 28; Tuples 1000: Deleted 3000. Elapsed 0/0 sec. The analysis for an index on the target table. 1998-10-04 VACUUM reclaims storage occupied by deleted tuples. In normal Postgres operation, tuples that are DELETEd or obsoleted by 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 for more details about its processing. 1998-10-04 We recommend that active production databases be VACUUM-ed nightly, in order to remove expired rows. After copying a large table into Postgres or after deleting a large number of records, 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 Postgres query optimizer to make better choices in planning user queries. The following is an example from running VACUUM on a table in the regression database: regression=> vacuum verbose analyze onek; NOTICE: --Relation onek-- NOTICE: Pages 98: Changed 25, Reapped 74, Empty 0, New 0; Tup 1000: Vac 3000, Crash 0, UnUsed 0, MinLen 188, MaxLen 188; Re-using: Free/Avail. Space 586952/586952; EndEmpty/Avail. Pages 0/74. Elapsed 0/0 sec. NOTICE: Index onek_stringu1: Pages 28; Tuples 1000: Deleted 3000. Elapsed 0/0 sec. NOTICE: Index onek_hundred: Pages 12; Tuples 1000: Deleted 3000. Elapsed 0/0 sec. NOTICE: Index onek_unique2: Pages 19; Tuples 1000: Deleted 3000. Elapsed 0/0 sec. NOTICE: Index onek_unique1: Pages 17; Tuples 1000: Deleted 3000. Elapsed 0/0 sec. NOTICE: Rel onek: Pages: 98 --> 25; Tuple(s) moved: 1000. Elapsed 0/1 sec. NOTICE: Index onek_stringu1: Pages 28; Tuples 1000: Deleted 1000. Elapsed 0/0 sec. NOTICE: Index onek_hundred: Pages 12; Tuples 1000: Deleted 1000. Elapsed 0/0 sec. NOTICE: Index onek_unique2: Pages 19; Tuples 1000: Deleted 1000. Elapsed 0/0 sec. NOTICE: Index onek_unique1: Pages 17; Tuples 1000: Deleted 1000. Elapsed 0/0 sec. VACUUM 1998-10-04 There is no VACUUM statement in SQL92.