Vacuum processing is definitely one of those features which makes Postgres SQL great. It essentially removes dead tuples and freezes transaction ids to ensure the smooth operation of the database.
VACUUM
Two modes are provided for dead tuples removal: Concurrent VACUUM and Full VACUUM. In concurrent VACUUM transactions can still be made while the dead tuples are being removed but in Full VACUUM the table is locked until all the dead tuples are removed. This VACUUM implementation used to be manual until v8 which supports an autovacuum daemon. In v8.4 the visibility map that we referred to in a previous post was introduced. It helps skips pages that do not contain dead tuples making the complete process more efficient.
Concurrent VACUUM
First Block
This block performs freeze processing and removes index tuples that point to dead tuples.
Second Block
In this block the dead tuples are actually removed and FSM and VM are also updated in a page-by-page fashion.
Third Block
It performs the required clean-up and update the statistics.
Full VACUUM
While the concurrent VACUUM removes the dead tuples, it does not help much with the table size reduction hence hindering the performance. This is exactly why you need a full VACUUM. It does both delete dead tuples while also helping in the size reduction.
Freeze processing
Freeze processing freezes old transaction ids. It is a part of VACUUM processing itself. It has two modes: lazy mode and eager mode. Lazy mode scans pages with dead tuples, while eager mode scans all pages regardless of dead tuples.
The PARALLEL option allows parallel processing of vacuuming index and cleaning index phases in the VACUUM command.
The other two important features that make Postgres stand out are the removal of unnecessary clog files as discussed in part 5 and the autovacuum daemon which handles the vacuums.
Top comments (0)