• 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.

Syntax

VACUUM [FULL] [FREEZE] [VERBOSE] [table_name ];

OR

VACUUM [FULL] [FREEZE] [VERBOSE]

Vacuum

  1. 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.
  2.  vacuum command simply remove dead tuple for each page of tuple file.
  3. It don’t reclaim space only remove dead tuple pointer so that the unused space can be used by the same table.
  4. Can read data from table while running only vacuum command, No need of EXCLUSIVE lock.
  5. 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

VACUUM FULL

  1. Reclaim dead tuple space and Minimize database file
  2. Full VACUUM removes dead tuples and defragments
  3.  While Full VACUUM running transactions cannot access tables, takes EXCLUSIVE lock on table.
  4. 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;