0Pricing
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.

Tuning Fillfactor for Update-Heavy Tables is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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.

What fillfactor Actually Controls

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);

HOT Updates: The Payoff

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.

Setting fillfactor on a New Table

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.
CREATE TABLE session_state (
    session_id   uuid PRIMARY KEY,
    last_seen_at timestamptz NOT NULL,
    hit_count    integer NOT NULL DEFAULT 0,
    payload      jsonb
) WITH (fillfactor = 80);

Changing fillfactor on an Existing Table

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.

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

Confirming a HOT Update Happened

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.

SELECT relname,
       n_tup_upd,
       n_tup_hot_upd,
       round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 1) AS hot_pct
FROM pg_stat_user_tables
WHERE relname = 'session_state';

Indexed Columns Block HOT Updates

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.

-- This index would block HOT updates whenever hit_count changes:
-- CREATE INDEX ON session_state (hit_count);

-- Prefer indexing stable columns instead:
CREATE INDEX idx_session_last_seen ON session_state (last_seen_at);

Choosing a Value: The Trade-off

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.

fillfactor on Indexes Too

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.
CREATE INDEX idx_session_last_seen
    ON session_state (last_seen_at)
    WITH (fillfactor = 80);

Inspecting Current Settings

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.

SELECT relname, reloptions
FROM pg_class
WHERE relname IN ('session_state', 'idx_session_last_seen');

A Practical Tuning Workflow

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.

ALTER TABLE session_state SET (fillfactor = 75);
CLUSTER session_state USING idx_session_last_seen;
ANALYZE session_state;

Quick Check

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?

Recap

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.

Frequently asked questions

Is the “Tuning Fillfactor for Update-Heavy Tables” lesson free?

Yes — the full text of “Tuning Fillfactor for Update-Heavy Tables” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.

What will I learn in “Tuning Fillfactor for Update-Heavy Tables”?

Set fillfactor to leave room for HOT updates and reduce index churn on frequently modified rows. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start PostgreSQL Performance & Query Optimization?

No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Tuning Fillfactor for Update-Heavy Tables” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?

Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Measuring Table and Index Bloat Accurately
  2. Reclaiming Space with pg_repack
  3. Tuning Fillfactor for Update-Heavy Tables
  4. TOAST Internals and Large Value Storage
← Back to PostgreSQL Performance & Query Optimization