What Is Fill Factor and When to Use It

PostgreSQL stores table data on 8KB pages. By default, FILLFACTOR is set to 100, which means PostgreSQL fills each page until it is completely full before moving to the next one. This is the right choice for read-only tables. It is often the wrong choice for everything else.

When you update a row in PostgreSQL, you are not modifying it in place. You are writing a new version of the row and marking the old version as dead. If the new version fits on the same page as the old version, PostgreSQL calls this a HOT update (Heap Only Tuple). HOT updates are fast because they do not require an index update. You just write the new row version and mark the old one dead.

If the new version does not fit on the same page, PostgreSQL has to write it to a different page. Now you have two versions of the row on two pages, and the index still points to the old page. The index gets updated to point to the new page. This creates index churn, bloat, and makes vacuum work harder to clean up the dead versions and the old index entries.

If your table gets many updates and you have fill factor at 100%, you are basically guaranteeing that most updates will not fit on the same page.

How to Use It

Lower the fill factor on write-heavy tables to leave room for updates:

ALTER TABLE big_table SET (fillfactor = 80);
VACUUM FULL big_table;

The VACUUM FULL is necessary to rewrite the table and actually create the space. A regular vacuum does not move rows around. VACUUM FULL locks the table, which is expensive, which is why this is a one-time operation during maintenance, not something you do casually.

For a table that gets lots of updates, 70-80% is usually right. For a table that mostly gets inserts and rarely updates, leave it at 100. For a table that gets updated a little bit, 90% is often a reasonable middle ground.

The cost is straightforward: lower fill factor means more pages, which means more storage, and potentially more I/O if you are scanning the table sequentially. On a table that is read a lot, this matters. On a table that is written to a lot and read by specific index lookups, it usually does not.

How to Check If It Is Working

Query the pg_stat_user_tables view:

SELECT
  schemaname,
  relname,
  seq_scan,
  idx_scan,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum
FROM pg_stat_user_tables
WHERE schemaname != 'information_schema'
ORDER BY n_dead_tup DESC;

If n_dead_tup is consistently high, it means updates are creating dead row versions faster than vacuum can clean them up. That is a sign that fill factor is too high.

You can also check the actual page usage:

SELECT * FROM pgstattuple('big_table');

This scans the entire table and reports how many bytes are live data, how many are dead, and how fragmented the storage is. High dead ratio means you are wasting space. High fragmentation means updates are scattered across pages.

If both are high, lower fill factor.

The Tradeoff

Lower fill factor trades storage and sequential scan performance for faster updates and less vacuum overhead. On a small table, this is not worth the storage cost. On a large write-heavy table, it is usually worth it.

I have seen systems where fill factor on the right tables dropped the time spent in autovacuum from minutes down to seconds. I have also seen people set it to 50 because they thought lower was always better and ended up with tables that took up twice the storage for no real benefit.

The default of 100 assumes your table is mostly read and occasionally written. If that assumption is wrong for your table, change it. If it is right, leave it alone.