This article is half-done without your Comment! *** Please share your thoughts via Comment ***
In this post, sharing a small note on one of the internal architecture things of PostgreSQL.
Each Table and Index has an FSM which used to keep track of available space in the relation.
It stores all free space related information in an alongside primary relation and that relation starts with the file node number plus the suffix _fsm.
If you do not know about the MVCC (Multiversion Concurrency Control), please visit below an article.
Because of MVCC architecture, PostgreSQL generates dead tuples on each update and delete action.
Why VACUUM requires in MVCC because VACUUM removes dead row versions in tables and marks the space available for future reuse.
The VACUUM process also updates the Free Space Map and using VACUUM FULL we can recover those free spaces.
The FSM pages are organized as a tree.
The bottom level FSM pages store the free space available on each page, using one byte to represent each such page.
The upper levels aggregate information from the lower levels.
We can also examine free space pages using pg_freespace module.
|
1 2 |
CREATE EXTENSION pg_freespace; SELECT * FROM pg_freespace('table_name'); |
