In normal PostgreSQL operation, tuples that are deleted or obsoleted by an update are not physically removed from their table.

They remain present until a VACUUM is done. Therefore it’s necessary to do VACUUM periodically, especially on frequently-updated tables.

VACUUM :
-> Garbage-collect and optionally analyze a database.
-> Reclaims space and makes it available for re-use for that alone not to the operating system.
-> Its fast compared to Vacuum Full.
VACUUM ANALYZE FULL: 
-> performs a VACUUM and then an ANALYZE for each selected table.
-> Reclaim more space, but takes much longer and exclusively locks the table.
VERBOSE:-> Prints a detailed vacuum activity report for each table.

-> When it is specified, VACUUM emits progress messages to indicate which table is currently being processed. Various statistics about the tables are printed as well.

ANALYZE:

Updates statistics used by the planner to determine the most efficient way to execute a query.

Eg: Vacuum (verbose,analyze) temp;

Note:

1. PostgreSQL includes an “autovacuum” facility which can automate routine vacuum maintenance

2. If we are not specifying any table name then all the tables in the current database get vacuumed.

VACUUM Progress Reporting:

Whenever Vacuum  is running, the pg_stat_progress_vacuum view will contain one row for each backend (including autovacuum worker processes) that is currently vacuuming.

Wait monitoring:

-> Previously DBA’s use pg_stat_activity to provide information about what’s currently happening on the database.

-> This returns information about each connection in the cluster, including the queries being run, which users are connected to which database, when the connection was made, whether it’s active or idle, and various other bits of information.

2 new columns:

wait_event_type:Lock

wait_event:tuple/Write-Ahead Log

-> By using these columns we can determine on which the connection  is waiting for.