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 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.
1 VACUUM Table_Name;
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.
1 VACUUM FULL Table_Name;
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.
12 ANALYZE Table_Name;VACUUM ANALYZE Table_Name;
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.