Set the FILLFACTOR value for attributes

Edit online

The FILLFACTOR storage parameter specifies how full PostgreSQL database pages should be when a table or index is created or rebuilt.

What it means

FILLFACTOR is a PostgreSQL storage parameter that reserves a percentage of free space on each database page when a table or index is created or rebuilt. The reserved space allows future row updates to occur in place, rather than requiring PostgreSQL to relocate the row to a different page.

The value is expressed as a percentage from 10 to 100.
  • A higher value fills pages more completely, which reduces storage usage but leaves less free space for future updates.
  • A lower value leaves more free space on each page, which reduces page splits and can improve performance for workloads with frequent inserts and updates.
FILLFACTOR value Effect Recommended workload
100 Fills pages completely Read-heavy or mostly static tables and indexes
80-90 Leaves some free space Moderate insert and update activity
50-70 Leaves more free space Write-heavy workloads
Note:

The FILLFACTOR keyword is supported only for PostgreSQL. It is not supported with DB2.

Why it is needed

Without reserved space, an update that no longer fits on its original page forces PostgreSQL to write it to a new page (a page split), increasing I/O and table or index bloat. Setting an appropriate FILLFACTOR value reduces this overhead for attributes that change frequently after creation.

When it is needed

Configure FILLFACTOR for attributes whose values are updated or replaced frequently after entry creation, such as volatile attributes like last login time or mobile number. It is generally not necessary for write-once or rarely updated attributes, where the default value of 100 is usually sufficient.

Impact of setting FILLFACTOR value

A lower FILLFACTOR reduces page splits and bloat for update-heavy attributes but increases the on-disk size of tables and indexes because each page stores fewer rows. Setting the value too low wastes storage, while setting it too high for write-heavy attributes can reintroduce page splits and fragmentation.

Select an initial value

Select an initial value based on how frequently the attribute is expected to change after entry creation:
  • 100 for static or rarely updated attributes
  • 80-90 for attributes with moderate update activity
  • 50-70 for write-heavy attributes
Use these values as a starting point and adjust them based on observed workload patterns.

Determine Tuning requirement

Review table statistics using pg_stat_user_tables or pgstattuple. Persistent bloat above 20% despite regular VACUUM or autovacuum activity may indicate that the FILLFACTOR value must be reduced further. 
SELECT schemaname,relname,n_live_tup,n_dead_tup,CASE WHEN (n_live_tup + n_dead_tup) = 0 THEN 0 ELSE round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 2) END AS dead_pct, last_vacuum,last_autovacuum,last_analyze,pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_stat_user_tables WHERE schemaname = 'idsldap' ORDER BY dead_pct DESC, n_dead_tup DESC