- Vacuum is one of the important feature used by postgreSQL internally to reclaim space used by dead tuple due to MVCC.
- It’s the part of PostgreSQL maintenance activity.
- It reclaim storage by removing obsolete data or tuples from the PostgreSQL database.
- Tuples that are deleted or obsoleted by an update are not physically removed from their table, they remain present until a Vacuum is done.
- Running Vacuum regular is good practice, especially on frequently-updated tables.
VACUUM [FULL] [FREEZE] [VERBOSE] [table_name ];
VACUUM [FULL] [FREEZE] [VERBOSE]
- vacuum < Table_Name> : It cleans specified table dead rows, If you not specify table name only vacuum command then it will process all tables in current databases.
- vacuum command simply remove dead tuple for each page of tuple file.
- It don’t reclaim space only remove dead tuple pointer so that the unused space can be used by the same table.
- Can read data from table while running only vacuum command, No need of EXCLUSIVE lock.
- Removed dead tuple from pages is available to accommodate new records.
VACUUM; // This command will run for all tables
VACUUM EMPLOYEE; // This will only for employee table
- Reclaim dead tuple space and Minimize database file
- Full VACUUM removes dead tuples and defragments
- While Full VACUUM running transactions cannot access tables, takes EXCLUSIVE lock on table.
- It rewrites the entire contents of the table into a new disk file,Need sufficient space to perform activity
VACUUM FULL; // all the tables in current database
VACUUM FULL EMPLOYEE;