PostgreSQL Logo

ANALYZE – PostgreSQL Database Maintenance

ANALYZE collects statistics about the database, i.e. contents of the tables and updates all statistics to pg_statistic system catalog.
Resulting Query planner uses pg_statistic system catalog to find efficient query execution plan.
ANALYZE acquiring only READ LOCK on target table, so that PostgreSQL can run parallel on same table.

Syntax:

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

Parameters:

Plain ANALYZE: Examines all tables in database.

ANALYZE table_name: Examines only provided table. In case of provides list of columns than ANALYZE only examines provided columns.

VERBOSE: Provides progress messages of the ANALYZE.

AutoVacuum daemon is take care of automatic analyzing of tables when they are first loaded to database, and throughout regular change in operations. When AutoVacuum is not configured than its recommended activity to perform ANALYZE as database maintenance task.

To simulate environment, Consider following examples on ANALYZE:

Ex 1 : Plain ANALYZE on Postgres default database.

Plain ANALYZE

Plain ANALYZE

 

Ex 2 : ANALYZE with VERBOSE on Postgres default database.

ANALYZE with VERBOSE

ANALYZE with VERBOSE

 

Ex 3 : ANALYZE specific table with VERBOSE on Postgres default database.

ANALYZE specific table with VERBOSE

ANALYZE specific table with VERBOSE

Note: Table demo is created for only to demonstrate above ANALYZE execution, No rows inserted into it. Thats why message tab shows 0 live rows and 0 dead rows.

Cheers!!

PostgreSQL Logo

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.