PostgreSQL Performance & Query Optimization · Lesson

Tuning Fillfactor for Update-Heavy Tables

Set fillfactor to leave room for HOT updates and reduce index churn on frequently modified rows.

Why Updates Are Expensive in PostgreSQL

PostgreSQL uses MVCC: an UPDATE never overwrites a row in place. Instead it writes a brand-new row version (tuple) and marks the old one dead.

The new tuple must go somewhere on disk.
If it lands on a different page than the old version, every index on the table must be updated to point at the new location.

On update-heavy tables this index churn becomes a major source of write amplification and bloat. Today's lesson: how fillfactor helps you avoid it.

fillfactor is a per-table (and per-index) storage parameter expressed as a percentage from 10 to 100.

It tells PostgreSQL how full to pack each 8 KB page when inserting rows.
A fillfactor of 100 (the default for tables) packs pages completely full.
A fillfactor of 90 leaves roughly 10% of every page as free space reserved for future updates.

That reserved space is the key to enabling cheaper updates on the same page.

ALTER TABLE orders SET (fillfactor = 90);