Régler le facteur de remplissage des tables fortement modifiées

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.

A HOT update (Heap-Only Tuple) happens when:

• None of the updated columns are part of any index, AND
• The new tuple fits on the same page as the old one.

When both hold, PostgreSQL chains the new version to the old one inside the page and skips updating the indexes entirely. No index churn, far less WAL, and the old version can be cleaned up cheaply by HOT pruning.

Leaving free space via a lower fillfactor is what makes the "same page" condition achievable.

You can declare the storage parameter at CREATE TABLE time. This is the cleanest approach because the table is packed correctly from the very first insert.

• Pick a value that reserves enough room for the typical number of in-page row versions between vacuums.
• 90 is a common starting point; 70–80 suits very hot rows.

ALTER TABLE ... SET (fillfactor = N) changes the parameter, but it does not rewrite existing pages. Only newly written pages honor the new value.

To apply it to current data, rewrite the table with VACUUM FULL or CLUSTER (both take an ACCESS EXCLUSIVE lock), or use pg_repack for an online rewrite.

You don't have to guess. pg_stat_user_tables exposes counters that tell you whether your updates are taking the HOT path.

• n_tup_upd — total updated tuples.
• n_tup_hot_upd — how many of those were HOT updates.

A high ratio of n_tup_hot_upd / n_tup_upd means fillfactor and your index design are paying off.

Free space alone is not enough. If an UPDATE touches any indexed column, PostgreSQL must create a new index entry, so the update can never be HOT — even if the new tuple fits on the same page.

• Keep frequently-updated columns (counters, timestamps, status flags) out of indexes where possible.
• Drop indexes you don't actually need; each one is a potential HOT-blocker.

Example: indexing hit_count would defeat the whole purpose of tuning fillfactor on this table.

Lower fillfactor is not free. The trade-offs are:

• Lower fillfactor → more free space per page → more HOT updates, less index churn → but the table occupies more pages, so sequential scans and the buffer cache hold fewer rows per page.
• Higher fillfactor → denser storage, better scan/cache efficiency → but updates spill to new pages, causing index churn and bloat.

Rule of thumb: keep 100 for append-only / read-mostly tables; drop to 70–90 only for genuinely update-heavy ones.

Indexes have their own fillfactor (default 90 for B-tree). Lowering it leaves room in leaf pages so new entries don't force frequent page splits on tables with heavy inserts of monotonically increasing keys.

• For append-only / ever-increasing keys, the default is usually fine.
• For indexes on randomly-distributed keys with churn, a slightly lower index fillfactor can reduce splits.

To see whether a table already has a non-default fillfactor, read reloptions from pg_class. A NULL there means the default (100 for heap, 90 for B-tree) is in effect.

Putting it together for an update-heavy table:

• 1. Confirm the workload is update-heavy and check current n_tup_hot_upd ratio.
• 2. Move hot columns out of indexes; drop unused indexes.
• 3. Set fillfactor (start at 90, lower toward 70 if HOT ratio is still low).
• 4. Rewrite the table (VACUUM FULL / CLUSTER / pg_repack) so existing pages get the new packing.
• 5. Re-measure the HOT ratio and adjust.

Always validate with the stats view — don't tune blind.

You have an update-heavy table whose status column changes constantly, and you've lowered fillfactor to 80 — but n_tup_hot_upd stays near zero. What is the most likely cause?

Key takeaways for tuning fillfactor on update-heavy tables:

• fillfactor reserves free space per page so updated rows can stay on the same page — enabling HOT updates.
• HOT updates skip index maintenance, cutting write amplification and bloat.
• HOT requires both same-page room and no indexed column changed — so keep hot columns out of indexes.
• ALTER TABLE SET (fillfactor=N) only affects new pages; rewrite with VACUUM FULL/CLUSTER/pg_repack to apply it to existing data.
• Measure success with n_tup_hot_upd / n_tup_upd in pg_stat_user_tables and tune iteratively.
• Lower fillfactor trades storage density for fewer updates spilling to new pages — use it only where the workload justifies it.