PostgreSQL: The Awesome Table Fillfactor to speedup UPDATE and SELECT statement

PostgreSQL Table FillFactor

This is one of the most important articles for all PostgreSQL Database Professionals because this relates to PostgreSQL database optimization for better performance.

First, You guys have to know what is MVCC and how it manages the different row versions.



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.

How to measure the size of the tuple in PostgreSQL?

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.

Anvesh Patel

Leave a Reply

3 Comments on "PostgreSQL: The Awesome Table Fillfactor to speedup UPDATE and SELECT statement"

Notify of
Sort by:   newest | oldest | most voted

Good way of describing, and fastidious piece of writing to get data
regarding my presentation subject matter, which i am going
to deliver in college.

Venkatesh Naicker

Hi Anvesh. Your post on the fill factor is interesting. You mentioned three i/o for an update with fill factor of 100 (1. delete original tuple, 2. insert new tuple on the new page, 3. update index). Now when there is fill factor of 70, doesn’t it still need to delete original tuple and insert the new tuple (though on the same page)? Is it saving on index update because on the same page? I am a bit lost? Thanks!