Vacuum-Analyze-Vacuum Full - Maintenance Activities in PostgreSQL: Performance

 

Maintenance Activities in PostgreSQL:

we are going to cover following questions and its answers

How to check vacuum details?

#] select schemaname, relname, last_vacuum from pg_stat_user_tables;

#] select pg_relation_filepath('scott.emp');

output:-

pg_relation_filepath

----------------------

base/16403/16390

#] VACUUM VERBOSE scott.emp;

#] select pg_relation_filepath('scott.emp');

base/16403/16390

If you run VACUUM, there will be no changes in the file.

How to run VACUUM on whole DB level?

#] VACUUM VERBOSE;

How to analyze table? 

#] ANALYZE scott.emp;

#] explain select * from scott.emp;

How to run VACUUM FULL on table level?

#] VACUUM FULL scott.emp;

#] explain select * from scott.emp;

How to check 'AUTOVACUUM is ON or OFF ?

postgres=# show autovacuum;

autovacuum

----------

on

Note:- If anyone trying to access that table automatically autovacuum operation will be cancelled.

If keep on operations happening on any table 'autovacuum' operation will not workout.

that's why we always run Manually.

If you disable 'autovacuum', you should be very active on maintaince activities.

'AUTOVACUUM' should run on table:-

1. If table size grows %

2. If number of inserts/deletes/updates happened

Q) How to log autovacuum?

Now after knowing how to turn on/off the autovacuum, you might want to ask how can I print out the log information of autovacuum?

To do that, you can go to postgresql.conf file again, find the ‘log_autovacuum_min_duration’ parameter:

log_autovacuum_min_duration = 1000

Q) When to run autovacuum vacuum/analyze?

Vacuuming will be required when the number of dead tuples exceeds a certain threshold, more specifically it depends on the equation below:

num_of_dead_tuple >= autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * number of tuples