VACUUM in PostgreSQL
In simple terms, VACUUM is a process of cleaning DEAD rows/tuples from the database.
Why use VACUUM in PostgreSQL?
PostgreSQL does not make use of the IN-PLACE update mechanism. This mechanism means whenever DELETE operations are performed on rows, mark these deleted rows as DEAD instead of removing rows physically. Similarly, it works with the UPDATE operation marks the old existing record as DEAD, and inserts the new row.
So, there will be always DEAD rows present in the database. In order to reclaim unused space from DEAD rows, we make use of VACUUM.
Types of VACUUM
Plain VACUUM: Frees up space for re-use.
Syntax: VACUUM [tablename];
Full VACUUM: Frees up more space than plain vacuum and locks the database table during the process.
Syntax: VACUUM FULL [tablename];
Full VACUUM and ANALYZE: Performs full vacuum, analysis, and Updates statistics used by the planner to determine the most efficient way to execute a query.
Syntax: VACUUM FULL ANALYZE [tablename];
Full VACUUM, ANALYZE, and VERBOSE: Performs full vacuum, analysis, and display action for each activity.
Syntax: VACUUM (FULL, ANALYZE, VERBOSE) [tablename];
Let’s consider an example to understand practically more on VACUUM.
PostgreSQL extension pg_freespacemap can help us in understanding the available free space after the dead tuple.
Query to check the number of pages and the average ratio of Freespace.
SELECT COUNT(*) AS npages, round(100 * AVG(avail)/8192 ,2) AS average_freespace_ratio FROM pg_freespace(‘medm’);
After running VACUUM medm;
After running VACUUM FULL medm;
So, this was all about manual VACCUM. We also have an option for auto VACUUM. To read about auto VACUUM
Links for reference:
Do clap, if find useful🙂. Do share your experience with using VACUUM in databases.
Keep learning and keep sharing knowledge.✌
