Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu


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 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.
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.
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.


Popular posts from this blog

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

PostgreSQL Monitoring Tools

PostgreSQL pgBadger