Vacuum processing in PosgtreSQL is a maintenance process that helps to facilitate persistent operations.
The main tasks of vaccuum processing are to remove dead tuples and freeze transaction ids which are no longer active.
Two different modes are provided by vacuum processing to remove dead tuples, namely:
- Concurrent VACUUM
- Full VACUUM
Concurrent VACUUM, which is often simply referred simply as VACUUM, works by removing dead tuples for each page of the table file but meanwhile other transactions can still read the table while the process in running.
On the other hand, Full VACUUM works by removing dead tuples and also defragments the whole file but while Full VACUUM is running, the table cannot be accessed by other transactions.
Work on improving the functionality of vacuum has been rather slow compared to other functions despite it being essential in PostgreSQL. It is a costly and expensive process because vacuum processing involves scanning the whole table.
When it comes to freezing old tax ids, it removes unnecessary parts of the clog that are not being used actively if possible.
In the first block, freeze processing is performed and index tuples that point to old tuples are removed. Firstly, a target table is scanned by PostgreSQL to build a list of dead tuples and freeze old tuples if possible. This list is then stored in maintenance_work_mem in the local memory area.
After the scanning is done, index tuples are removed by postgreSQL by pointing to the deadtuples list. Internally, this process is known as the "cleanup stage" and needless to say, this is an expensive process.
Next, in the second block, dead tuples are removed and both the FSM and VM are updated page-by-page.
Lastly, the third block performs a cleanup after the indexes have been deleted and it also updates both the system catalogs and statistics that are related to the vacuum processing for each of the target tables.
Top comments (0)