PostgreSQL: Short note on VACUUM, VACUUM FULL and ANALYZE

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

VACUUM, VACUUM FULL and ANALYZE: These are the maintenance related commands of PostgreSQL which requires frequent execution because PostgreSQL based on MVCC architecture where every UPDATE and DELETE generates dead rows or dead tuples as an internal fragmentation.

If you don’t about the MVCC, you must visit the below article.
What is MVCC?

VACUUM:

VACUUM reclaims storage occupied by dead tuples.
In the MCVV architecture tuples that deleted which is not physically remove. It presents in a disk until a VACUUM is done.

If you are executing frequent UPDATE/DELETE on your table, you must find the fragmentation and must execute VACUUM on it.
VACUUM does not require an exclusive lock on the table.
Syntax:

1	VACUUM Table_Name;

VACUUM FULL:

Whatever space reclaims by VACUUM that space might require to use for next storage.
VACUUM FULL rewrites the entire table with data and releases all fragmented space of an old table.

We should avoid the VACUUM FULL because it shrinks the whole table and writes everything into the new disk block which requires more resources and disk space to complete this operation.

If you find a huge number of dead rows, you should execute VACUUM FULL on that table.
VACUUM FULL requires an exclusive lock on the table and also require a free disk space as same as your table size.
Syntax:

1	VACUUM FULL Table_Name;

ANALYZE:

After execution of VACUUM or VACUUM FULL, you must execute this command to update the statistics.
ANALYZE updates all require statistics and stores the results in the pg_statistic system catalog.

The Query Planner uses this database statistics information to prepare an efficient query execution plan.
You can also execute VACUUM ANALYZE, which executes the first VACUUM and then executes ANALYZE.
Syntax:

1 2	ANALYZE Table_Name; VACUUM ANALYZE Table_Name;