This is one of the most important articles for all PostgreSQL Database Professionals because this relates to PostgreSQL database optimization for better performance.
What is Fillfactor and how it impacts to 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 in another page.
There is no extra space to write a new tuple on the same page because default 100 means current page is full.
Now next impact of this default 100 is, related indexes also 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 on deleted tuples, write new tuples in the new pages, and index modifications.
This is not just impact to UPDATE but actually your SELECT query also degrades the performance because of the fragmentation.
How we can 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 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.
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.