PostgreSQL Vacuum

In today’s article, we will provide important insights on dead rows, vacuum operations, FSM, and VM in PostgreSQL.

When performing UPDATE or DELETE operations on a table in PostgreSQL, the modified or deleted data is still retained.

For example, when you execute an UPDATE operation on a table, the existing data is hidden, and a new value is inserted instead.

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.

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.

This phenomenon, where old data remains on disk, is referred to as “dead rows.”

Relationship Between Dead Rows and Vacuum

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.

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.

VACUUM

I had mentioned dead rows earlier. The VACUUM process marks dead rows so they can be reused.

Since this marking process does not place an exclusive lock, other operations can continue without interruption.

During this process, only a small number of dead rows are physically removed, so it does not significantly reduce disk usage.

However, it ensures that dead rows are kept ready for reuse. This process can be performed automatically using the AUTOVACUUM feature.

ANALYZE

VACUUM ANALYZE= VACUUM+ANALYZE

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.

The ANALYZE command updates statistics for either the entire table or a specific table, ensuring optimized query performance.

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.

However, this operation may cause a read lock on the table.

VACUM FULL

The VACUUM FULL command places an exclusive lock on the table, preventing any operations until the process is complete.

Unlike other vacuum types, VACUUM FULL removes dead rows from the table or database and updates statistics.

Because of this, it provides greater space savings and performance improvements compared to other vacuum methods.

During VACUUM operations, you can use the VERBOSE option to get detailed output about the actions being performed.

Additionally, VACUUM cleans up the FSM (Free Space Map) and VM (Visibility Map) files that physically store table data.

FSM (Free Space Map): 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.

If there is available space in FSM, the new data is written there; otherwise, a new page is created.

You can use the following query to get more detailed information about FSM:

VM (Visibility Map): is updated during UPDATE and DELETE operations to track which data is visible or invisible.

It helps PostgreSQL determine which pages contain only live tuples, improving query performance by avoiding unnecessary scans of dead rows.

You can use the following query to get more detailed information about VM:

You can check when tables were last analyzed and what actions autovacuum has performed using the following query: