This article is half-done without your Comment! *** Please share your thoughts via Comment ***
This is one of the most important articles for all PostgreSQL Database Professionals because it relates to PostgreSQL database optimization
First, You guys have to know what is MVCC and how it manages the different row versions.
What is Fillfactor and how it impacts the performance?
The Fillfactor is a table storage parameter and for the performance aspect this is the most important parameter.
The Fillfactor for a table is a percentage between 10 and 100. 100 is the default for a complete packing.
Because of this default 100 value, when one UPDATE is performed, the old tuple is marked as deleted and a new tuple is inserted into another page.
There is no extra space to write a new tuple on the same page because default 100 means write data in page till its full.
Now, next impact of this default 100 is, related indexes of newly updated records are modified because it has to point to the new page location.
If you notice here, for one UPDATE actually there are three operations, first mark deleted tuples, write new tuples in the new pages, and index modifications.
This is not just about the UPDATE but actually the performance of SELECT query also will degrade because of the internal fragmentation.
How can we improve the performance?6
The default Fillfactor is 100 and it is better, but not in all situations.
When your table has frequent updates, this is not a better solution because it requires more CPU and IO for different data page operations which actually degrade the performance.
The solution is to first measure the size of the tuple and if tuple size is not that much bigger, we can reduce the value of default Fillfactor.
How to change the current value of Table Fillfactor in PostgreSQL?
When we reduce the default value of Fillfactor, each page has some free space and new tuple which is generated by UPDATE can be stored on the same page.
Now, index page does not require any modification because new tuple is on the same page.
