{"id":109489,"date":"2026-04-29T13:00:00","date_gmt":"2026-04-29T13:00:00","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=109489"},"modified":"2026-04-28T20:24:57","modified_gmt":"2026-04-28T20:24:57","slug":"fillfactor-in-postgresql-what-it-means-and-when-to-adjust-it","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/fillfactor-in-postgresql-what-it-means-and-when-to-adjust-it\/","title":{"rendered":"What is fillfactor in PostgreSQL – and when should you adjust it?"},"content":{"rendered":"\n

fillfactor<\/code> in PostgreSQL controls how full table or index pages are. The lower the fillfactor<\/code>, the better the performance on update-heavy tables. In this article, learn everything you need to know about fillfactor<\/code> in PostgreSQL – from what it is, to exactly when (and how) you should adjust it.<\/strong><\/p>\n\n\n\n

PostgreSQL<\/a> comes pre-configured with hundreds of various settings. We’ll never want to change (or are even aware of) most of them, but there is one you definitely<\/em> should be familiar with: fillfactor<\/code>.<\/p>\n\n\n\n

First, some background. All database management systems<\/a> (DBMS’) implement ways to avoid conflicts between users simultaneously modifying and accessing the same data. PostgreSQL uses a system called MVCC \u2013 multi-version concurrency control<\/a>. <\/p>\n\n\n\n

MVCC generally outperforms competing systems like lock-based<\/a> protocols, especially in reading or inserting new data. But there is one use case for which MVCC often struggles: heavily-updated tables.<\/p>\n\n\n\n

How table rows are updated in PostgreSQL (and where fillfactor comes in)<\/h2>\n\n\n\n

Some people are surprised to learn that PostgreSQL never directly updates a table row<\/a>. Instead, it writes an entirely new version of the row, leaving the original untouched. Later, when no running queries are referencing the old version, PostgreSQL will delete it. Effectively, the UPDATE<\/code> works like an INSERT<\/code>, followed by a later DELETE<\/code>. <\/p>\n\n\n\n

When updates are frequent or vacuum<\/a> operators are far apart, there may be several different versions of a row still in the table. And because every UPDATE<\/code> is inserting a new row, we must also update the table’s indexes – even when we’re not updating any indexed columns. Treating every update as an INSERT<\/code> is expensive and leads to index fragmentation. This means performance in update-heavy workloads can suffer, sometimes badly – but there’s a solution.<\/p>\n\n\n\n

‘HOT’ updates in PostgreSQL<\/h3>\n\n\n\n

PostgreSQL contains an optimization that can help: a “HOT update”<\/a>. HOT<\/code> — which stands for “Heap-Only Tuple” — is a type of update in which the new row is inserted on the same data page as the existing row, then “chained” to the earlier copy. This allows PostgreSQL to skip updating the table’s indexes. Also, future row accesses can delete the old copies (the entire “HOT chain”) without having to perform a vacuum.<\/p>\n\n\n\n

For a HOT<\/code> update to happen, none of the columns being altered can be indexed. And, most importantly, there must be room in the data page to store the new row. This is where fillfactor<\/code> comes in. By default, PostgreSQL fills a table’s data pages to 100%, leaving no free space (fill factor = 100). But we can lower this default setting on a per-table basis. If we set a value of 80, then 20% of every data page is available for HOT<\/code> updates.<\/p>\n\n\n\n

\n
\n
\n
\n

Get started with PostgreSQL – free book download<\/h3>\n
\n ‘Introduction to PostgreSQL for the data professional’, written by Grant Fritchey and Ryan Booz, covers all the basics of how to get started with PostgreSQL. <\/div>\n <\/div>\n Download your free copy<\/a>\n <\/div>\n <\/div>\n<\/section>\n\n\n

How much free space is best for HOT updates in PostgreSQL?<\/h2>\n\n\n\n

If a lack of free space prevents HOT<\/code> updates from happening in PostgreSQL, why not set the amount of free space even higher? Wouldn’t more room be better? Well, no. Not usually. <\/p>\n\n\n\n

A fill factor of 80 means 20% of the table’s data pages are effectively empty, making your table that<\/em> much larger. This impacts on all operations on that table: reads and inserts included. This is why PostgreSQL defaults to a 100% fillfactor<\/code>: most tables experience many more selects and inserts than they do updates.<\/p>\n\n\n\n

A fillfactor<\/code> below 70 only pays off in the rare case when there are several times more updates against a table than inserts and selects combined. And remember – a SELECT<\/code> that returns 100 rows is more load than one that returns a single row.<\/p>\n\n\n\n

There are also other factors. The more indexes on a table, the more it can benefit by avoiding a HOT<\/code> update. However, if the updates are modifying indexed <\/em>columns, the HOT<\/code> cannot happen. To complicate matters further, HOT<\/code> update rates are affected by row size and the frequency of vacuum operations (among other considerations.)<\/p>\n\n\n\n

The interplay of all these factors makes coming up with a “rule of thumb” for setting the fillfactor<\/code> nearly impossible. Only testing can reveal the best value for each table. Still, it’s not incorrect to say that most frequently-updated tables will benefit from lowering the fillfactor<\/code>.<\/p>\n\n\n\n

One misconception is that fillfactor<\/code> operates like the balance on a scale: lowering it improves update speed only at the cost of reduced select performance. While this is often true, when HOT<\/code> updates are bogging down a server is when tuning the fillfactor<\/code> can also improve SELECT<\/code> performance. Additionally, more HOT<\/code> updates means less index fragmentation, which also helps SELECT<\/code> performance.<\/p>\n\n\n\n

How does PostgreSQL store a database table?<\/h2>\n\n\n\n
\"An<\/figure>\n\n\n\n

How to alter a table’s fillfactor in PostgreSQL<\/h4>\n\n\n\n

fillfactor<\/code> is a table-specific setting. Altering it is easy: to adjust the factor to 80 for table ‘sales’, for instance, execute this statement:<\/p>\n\n\n\n

ALTER TABLE sales SET (fillfactor = 80);<\/code><\/p>\n\n\n\n

Do note that this only<\/em> alters the value for new data – not data already in the table. If you want to see immediate results, you’ll need to dump and reload the table’s data. Otherwise, you’ll only see the results gradually over time (as existing data pages get updated.) For a large table, this may take days or weeks, so bear this in mind when testing your results.<\/p>\n\n\n\n

How to check the effectiveness of fillfactor in PostgreSQL<\/h4>\n\n\n\n

The following query will tell you the total number of updates made to a table, the number of which qualified for a HOT<\/code> update, and the HOT<\/code> update percentage (the ratio of the first two):<\/p>\n\n\n\n

SELECT\n\trelname AS \"Table name\",\n\tn_tup_upd AS \"Total updates\",\n\tn_tup_hot_upd AS \"HOT updates\",\n\tROUND(100.0 * n_tup_hot_upd \/ NULLIF(n_tup_upd,0), 1) AS \"HOT update %\"\nFROM\n\tpg_stat_user_tables\nWHERE\n\trelname = {table_name};<\/pre><\/div>\n\n\n\n
Unless you’ve previously cleared the statistics counters, these values start from when the table was created. It’s therefore recommended you execute the system function pg_stat_reset()<\/code> after resetting the fillfactor<\/code>. Otherwise, this query will contain mostly data from before the setting was changed.<\/p>\n\n\n\n
If you reloaded your table data after lowering the fillfactor<\/code>, you should see an immediate improvement in hit rates. Otherwise, you should expect to see the hit rate climb slowly over time. I can’t stress this latter point enough. For large tables, it may take several weeks before the HOT<\/code> hit rate climbs appreciably.<\/p>\n\n\n\n
When (and how) to use fillfactor for indexes in PostgreSQL<\/h4>\n\n\n\n
Every index in PostgreSQL<\/a> has a fillfactor<\/code> setting. Unlike tables, which default to 100%, indexes default to 90%. The setting affects indexes differently than tables, and the criteria for changing it is also different. <\/p>\n\n\n\n
A lower index fill factor reduces expensive page splits for the index. These splits can occur on both inserts and updates – but only for updates that alter an indexed column. So, when choosing indexes to potentially lower the fillfactor<\/code> on, the ratio we’re interested in is (total insert + index-involved updates) vs .(total select + non-index updates). As before, we measure each value based on the number of rows affected, not just the transaction count.<\/p>\n\n\n\n
Also note that indexes are even more sensitive than tables to the performance implications of lowering the fillfactor<\/code>. In practice, you’ll rarely want to set an index fillfactor<\/code> lower than 80. The syntax for doing this is similar to that of tables: for an index called idx_sales<\/code>, for instance, you would use this command:<\/p>\n\n\n\n
ALTER INDEX idx_sales SET (fillfactor = 80);<\/code><\/p>\n\n\n\n
Note: PostgreSQL contains an optimization for so-called “monotonic inserts”, such as a sequentially-increasing primary key<\/a>. This drastically reduces the number of page splits<\/a>, so you’ll rarely want to consider these indexes for adjustment. Focus on indexes with values that are inserted or updated on a random basis.<\/p>\n\n\n\n
If your table is static (no inserts or updates to indexed columns), you can set its index fillfactor<\/code> to 100 for maximum performance. This packs the index as tightly as possible, removing all wasted space. It even works for tables that receive updates (as long as those updates don’t alter indexed columns.)<\/p>\n\n\n\n
Further optimization: using TOASTing in PostgreSQL<\/h2>\n\n\n\n
If your table has text columns holding lengthy strings, but not quite long enough for the rows to be TOASTed<\/a> (moved by PostgreSQL into separate storage), this can make maintaining free space on a data page difficult. If these text columns aren’t often referenced, you can further improve performance by lowering the table setting ‘toast_tuple_target<\/code>‘.<\/p>\n\n\n\n
Feel free to share your thoughts about PostgreSQL’s fillfactor<\/code> in the comments below!<\/p>\n\n\n\n
\n    
\n        
\n            
\n                
Simple Talk is brought to you by Redgate Software<\/h3>\n                
\n                                            Take control of your databases with the trusted Database DevOps solutions provider. Automate with confidence, scale securely, and unlock growth through AI.                                    <\/div>\n            <\/div>\n                            Discover how Redgate can help you<\/a>\n                    <\/div>\n    <\/div>\n<\/section>\n\n\n
\n    
FAQs: fillfactor in PostgreSQL<\/h2>\n\n                        
1. What is fillfactor in PostgreSQL?<\/h3>\n            
\n                
fillfactor<\/code> in PostgreSQL controls how full table or index pages are. Lower values leave free space for updates.<\/p>\n            <\/div>\n                    
2. What is the default fillfactor in PostgreSQL?<\/h3>\n            
\n                
    \n
  • Tables: 100<\/li>\n
  • Indexes: 90<\/li>\n<\/ul>\n            <\/div>\n                    
    3. Why change fillfactor in PostgreSQL?<\/h3>\n            
    \n                
    Lowering fillfactor<\/code> improves performance on update-heavy tables by enabling HOT updates and reducing index overhead.<\/p>\n            <\/div>\n                    
    4. What are HOT updates in PostgreSQL?<\/h3>\n            
    \n                
    HOT (Heap-Only Tuple) updates store new row versions on the same page, avoiding index updates and improving performance.<\/p>\n            <\/div>\n                    
    5. When should I lower fillfactor in PostgreSQL?<\/h3>\n            
    \n                
      \n
    • Try ~80 for frequently updated tables<\/li>\n
    • Stick to 100 for rare updates<\/li>\n<\/ul>\n            <\/div>\n                    
      6. Can lower fillfactor hurt performance in PostgreSQL??<\/h3>\n            
      \n                
      Yes. It increases table size and can slow reads and inserts if overused.<\/p>\n            <\/div>\n                    
      7. How do I change fillfactor in PostgreSQL?<\/h3>\n            
      \n                
      SQL code:<\/p>\n
      ALTER TABLE table_name SET (fillfactor = 80);<\/code><\/p>\n            <\/div>\n                    
      8. Does fillfactor in PostgreSQL apply to existing data?<\/h3>\n            
      \n                
      No. It only affects new or updated rows unless you rebuild the table.<\/p>\n            <\/div>\n                    
      9. How do I measure results?<\/h3>\n            
      \n                
      Check HOT update rates using pg_stat_user_tables<\/code>.<\/p>\n            <\/div>\n                    
      10. Should I change index fillfactor in PostgreSQL?<\/h3>\n            
      \n                
      Sometimes. You can lower it (e.g., to 80\u201390) for indexes with frequent inserts or updates.<\/p>\n            <\/div>\n                    
      11. What\u2019s the best PostgreSQL fillfactor value?<\/h3>\n            
      \n                
      There\u2019s no universal ‘best’ value – simply test based on your workload.<\/p>\n            <\/div>\n            <\/section>\n\n\n\n
      <\/p>\n","protected":false},"excerpt":{"rendered":"
      Learn how to optimize PostgreSQL performance with fillfactor – including how it affects HOT updates, indexing, and update-heavy workloads.…<\/p>\n","protected":false},"author":343663,"featured_media":105920,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143523,53,143534],"tags":[4168,158978],"coauthors":[159110],"class_list":["post-109489","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-databases","category-featured","category-postgresql","tag-database","tag-postgresql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/109489","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/343663"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=109489"}],"version-history":[{"count":15,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/109489\/revisions"}],"predecessor-version":[{"id":110188,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/109489\/revisions\/110188"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media\/105920"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=109489"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=109489"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=109489"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=109489"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}