PostgreSQL: Short note on VACUUM, VACUUM FULL and ANALYZE

VACUUM, VACUUM FULL and ANALYZE: These are the maintenance related commands of PostgreSQL which requires to execute frequently because PostgreSQL based on MVCC architecture where every UPDATE and DELETE generates dead rows / data 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 MCVV architecture tuples that are deleted which are not physically removed. 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:

VACUUM FULL:

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

Generally, You should avoid the VACUUM FULL because it shrinks the whole table and writes everything into the new disk block which might require more resources 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:

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 first VACUUM and then executes ANALYZE.
Syntax:

Anvesh Patel

Leave a Reply

2 Comments on "PostgreSQL: Short note on VACUUM, VACUUM FULL and ANALYZE"

Notify of
avatar
Sort by:   newest | oldest | most voted
Advokatska Kancelarija
Guest
Advokatska Kancelarija

A fascinating discussion is worth comment. I think that you need to write more on this subject, it
might not be a taboo subject but generally people do
nnot speak about such issues. To the next! Kind regards!!

Felipe
Guest

Very nice post, really help me to understand better the differences between vaccums.

Reading others posts and they are prety cool.

wpDiscuz