VACUUM – PostgreSQL Database Maintenance

VACUUM Performs key role in PostgreSQL database maintenance task for performance gain.

Key Benefits to perform VACUUM periodically is to Reclaim storage space occupied by dead tuples. When delete or obsolete performed, tuples are not remove physically i.e. dead tuples. With VACUUM we can reclaim that occupied space.

Syntax:

VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table [ (column [, ...] ) ] ]

Plain VACUUM:

  • VACUUM process all tables.
  • Reclaim and make space available for reuse.
  • No exclusive lock on table, operates in parallel with normal reading and writing.
  • Reclaimed extra space reused for the table instead of return back to OS.(in most cases)
  • Recommended on daily basis.

FULL:

  • Exclusive lock on table while VACUUM.
  • Rewrites whole table into a new disk file with no extra space.
  • Reclaimed extra space return back to OS.
  • Slower than Plain VACUUM.
  • Recommended weekly not for daily used,

FREEZE:

  • Selects aggressive “freezing” of tuples.

VERBOSE:

  • Provides progress messages of the VACUUM.

ANALYZE:

  • Enhance execution of query by determining most efficient way by updating statistics used by planner.

Notes:

  • User need to be table owner or Super user to perform VACUUM.
  • After large amount of DML operations, It’s highly recommended to perform VACUUM ANALYZE in order to update system catalogs, It allows query planner to determine best future plan for query execution.
  • If large amount of update OR delete transactions suppose to perform on table, It’s good idea to FULL VACUUM that table immediately for performance point of view.
  • VACUUM increases number of I/O during activity, which may be culprit for poor DB performance for the session, So you can use Cost-based Vacuum Delay feature.

Examples:

Ex 1: Plain VACUUM on PostgreSQL default database.

Plain VACUUM

Plain VACUUM

 

Ex 2 : Plain VACUUM with Verbose on PostgreSQL default database.

Plain Vacuum with Verbose

Plain Vacuum with Verbose

 

Ex 3 : FULL VACUUM on PostgreSQL default database.

VACUUM Full

VACUUM Full

 

Ex 4 : FULL VACUUM with Verbose on PostgreSQL default database.

Vacuum Full with Verbose

Vacuum Full with Verbose

 

Ex 5 : FULL VACUUM with ANALYZE and Verbose on PostgreSQL default database.

Vacuum Full with Analyze and Verbose

Vacuum Full with Analyze and Verbose

 

Ex 6 : VACUUM on specific table with ANALYZE and verbose, In my case demo table is created to simulate example only.

VACUUM Table

VACUUM Table

 

This is how the vacuum is beneficial for postgreSQL database maintenance activity.

Leave a Reply