FSM, VISIBILITY MAP and VACUUM

Update: Heikki ' s slides is here!

Heikki Linnakangas gave a presentation this past Sunday at FOSDEM on the improved Free Space map (FSM), which tracks UN Used space inside the database, and new visibility map, a bitmap which would indicate which data pages can be skipped Durin G A partial VACUUM. This performance enhancement would affect all users of the upcoming 8.4 software release. You can see that the new FSM implementation looked like back in October Fromdepesz ' s blog.

Despite Heikki ' s modest claim during the talk that the performance tests were inconclusive, the consensus among Postgres C Ontributors is, this feature would result a substantial improvement in the performance of VACUUM for tables so is LA Rge, but has few UPDATEs.

The new free space map and Visibility maps (in 8.4) and Autovacuum (enabled by default starting in version 8.2) is huge ad Ministrative usability improvements to version 8 of Postgres. Prior to version 8.1, VACUUM had to be scheduled outside of the database system. Autovacuum have been part of the core Postgres distribution for over a years, and is tunable via several global Configura tion parameters.

The visibility map enables partial vacuums-meaning that VACUUM no longer have to examine every a tuple to update the FSM. The new FSM implementation eliminates the configuration parameters, effectively automating a formerly manual configuration Process.

the new FSM are stored on disk in seperate files inside of $PGDATA/base/, and are cached in Shared_buffers. The result is, the max_fsm_* configuration parameters is no longer in 8.4 -postgres was able to track and ad Just this data structure without user intervention.

A few critical features of the new FSM are:

* Now a binary tree structure
* Constructed using 1 byte per heap page
* The top level shows the maximum amount of contiguous space available
* The data structure is auto-repairing and can being reconstructed from the bottom

Previously, every time that VACUUM is run, the free space map had to is reconstructed from scratch. Now, individual nodes in the map is updated (aka "Retail" updates).

Visibility Map is a bitmap of the heap pages which tracks which tuples on pages was visible to transactions, and therefore Not available for vacuuming.

Previously, when VACUUM ran, it *had*-look at every tuple in a table, because there is no information about which page s May is not having been updated since the last VACUUM. With the visibility map, VACUUM would now is able to perform partial scans of table data, skipping pages which is marked a s fully visible. Partial scans means fewer I/O operations for VACUUM, and happier database administrators.

Note: The old article, saw the study.

1. Prior to vacuuming, the FSM will be refactored, and now the individual nodes will be updated (this should refer to version 8.4).

2, before you see some max_fsm_* parameters, the original after 8.4 has not.

Reference:

http://www.chesnok.com/daily/2009/02/10/fsm-visibility-map-and-new-vacuum-awesomeness/

FSM, VISIBILITY MAP and VACUUM