In today’s article, we will provide important insights on dead rows, vacuum operations, FSM, and VM in PostgreSQL.<\/p>\n
When performing UPDATE or DELETE operations on a table in PostgreSQL, the modified or deleted data is still retained.<\/p>\n
For example, when you execute an UPDATE operation on a table, the existing data is hidden, and a new value is inserted instead.<\/p>\n
In the case of a DELETE operation, the record to be deleted is marked as deleted, but it remains on disk rather than being physically removed.<\/p>\n
Similarly, in an UPDATE operation, the old record is hidden to prevent its usage, and a new version of the data is inserted into the table. However, the hidden (old) data continues to occupy space on disk.<\/p>\n
This phenomenon, where old data remains on disk, is referred to as “dead rows.”<\/p>\n
Relationship Between Dead Rows and Vacuum<\/h3>\n
Since dead rows remain on disk, they consume unnecessary space. As a result, when executing queries, these dead rows are also scanned, increasing query execution time and cost.<\/p>\n
To prevent unnecessary scanning and high costs, PostgreSQL periodically performs a VACUUM operation. This process cleans up dead rows, freeing disk space and improving query performance.<\/p>\n
VACUUM<\/h3>\n
I had mentioned dead rows earlier. The VACUUM process marks dead rows so they can be reused.<\/p>\n
Since this marking process does not place an exclusive lock, other operations can continue without interruption.<\/p>\n
During this process, only a small number of dead rows are physically removed, so it does not significantly reduce disk usage.<\/p>\n
However, it ensures that dead rows are kept ready for reuse. This process can be performed automatically using the AUTOVACUUM feature.<\/p>\n
ANALYZE<\/h3>\n
VACUUM ANALYZE= VACUUM+ANALYZE<\/p>\n
When queries are executed, they create a lifecycle that starts when the query begins and ends when it completes, aiming to retrieve results in the fastest way possible. In PostgreSQL, this lifecycle is managed by the planner.<\/p>\n
The ANALYZE command updates statistics for either the entire table or a specific table, ensuring optimized query performance.<\/p>\n
VACUUM ANALYZE helps improve query efficiency by updating table statistics and removing dead rows, preventing operations on deleted or updated data. This process ensures that queries return results more quickly.<\/p>\n
However, this operation may cause a read lock on the table.<\/p>\n
VACUM FULL<\/h3>\n
The VACUUM FULL command places an exclusive lock on the table, preventing any operations until the process is complete.<\/p>\n
Unlike other vacuum types, VACUUM FULL removes dead rows from the table or database and updates statistics.<\/p>\n
Because of this, it provides greater space savings and performance improvements compared to other vacuum methods.<\/p>\n
During VACUUM operations, you can use the VERBOSE option to get detailed output about the actions being performed.<\/p>\n
Additionally, VACUUM cleans up the FSM (Free Space Map) and VM (Visibility Map) files that physically store table data.<\/p>\n
FSM (Free Space Map):<\/strong> keeps track of free space within a table. When new data is inserted, PostgreSQL checks FSM to determine where the new data can be written.<\/p>\n
If there is available space in FSM, the new data is written there; otherwise, a new page is created.<\/p>\n
You can use the following query to get more detailed information about FSM:<\/p>\n
Select*from pg_freespace(table_name)<\/pre>\n
VM (Visibility Map):<\/strong> is updated during UPDATE and DELETE operations to track which data is visible or invisible.<\/p>\n
It helps PostgreSQL determine which pages contain only live tuples, improving query performance by avoiding unnecessary scans of dead rows.<\/p>\n
You can use the following query to get more detailed information about VM:<\/p>\n
Select*from pg_visibilty(table_name)\r\n<\/pre>\n
You can check when tables were last analyzed and what actions autovacuum has performed using the following query:<\/p>\n
SELECT relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze FROM pg_stat_user_tables;<\/pre>\n