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.
CREATE EXTENSION pg_freespace;
SELECT * FROM pg_freespace('table_name');
Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.
If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.
I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.