0Pricing
PostgreSQL Performance & Query Optimization · 课时

调整高频更新表的填充因子

设置 fillfactor，为 HOT 更新预留空间，并减少频繁修改行带来的索引反复写入。

调整高频更新表的填充因子 是 CoddyKit 上的免费 PostgreSQL Performance & Query Optimization 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 PostgreSQL Performance & Query Optimization 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

本课时的部分内容尚未翻译，以英文显示。

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.

常见问题解答

「调整高频更新表的填充因子」课时是免费的吗？

是的 — 「调整高频更新表的填充因子」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 PostgreSQL Performance & Query Optimization 课程的其余内容，请升级到 CoddyKit PRO。 PostgreSQL Performance & Query Optimization 课程共包含 4 节课。

「调整高频更新表的填充因子」这节课中我会学到什么？

设置 fillfactor，为 HOT 更新预留空间，并减少频繁修改行带来的索引反复写入。 你通过在浏览器中直接运行的动手代码来练习 PostgreSQL Performance & Query Optimization，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 PostgreSQL Performance & Query Optimization 需要有经验吗？

无需任何先前经验。CoddyKit 上的 PostgreSQL Performance & Query Optimization 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

「调整高频更新表的填充因子」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 PostgreSQL Performance & Query Optimization 课中编写并运行代码吗？

能。每节 PostgreSQL Performance & Query Optimization 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 准确测量表膨胀与索引膨胀
  2. 使用 pg_repack 回收空间
  3. 调整高频更新表的填充因子
  4. TOAST 内部机制与大值存储
← 返回 PostgreSQL Performance & Query Optimization