0Pricing
PostgreSQL Performance & Query Optimization · Урок

Настройка fillfactor для таблиц с частыми UPDATE

Настройте fillfactor, оставив место для HOT-обновлений и уменьшив перестроение индексов у часто изменяемых строк.

«Настройка fillfactor для таблиц с частыми UPDATE» — бесплатный урок PostgreSQL Performance & Query Optimization на CoddyKit. Это урок 3 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения 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.

Часто задаваемые вопросы

Урок «Настройка fillfactor для таблиц с частыми UPDATE» бесплатный?

Да — полный текст урока «Настройка fillfactor для таблиц с частыми UPDATE» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс PostgreSQL Performance & Query Optimization, подпишись на CoddyKit PRO. Курс PostgreSQL Performance & Query Optimization содержит 4 уроков всего.

Чему я научусь в уроке «Настройка fillfactor для таблиц с частыми UPDATE»?

Настройте fillfactor, оставив место для HOT-обновлений и уменьшив перестроение индексов у часто изменяемых строк. Ты практикуешь PostgreSQL Performance & Query Optimization с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать PostgreSQL Performance & Query Optimization?

Предыдущий опыт не требуется. PostgreSQL Performance & Query Optimization на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 3 из 4.

Сколько времени занимает урок «Настройка fillfactor для таблиц с частыми UPDATE»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке PostgreSQL Performance & Query Optimization?

Да. Каждый урок PostgreSQL Performance & Query Optimization включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

  1. Точная оценка раздувания таблиц и индексов
  2. Освобождение места с помощью pg_repack
  3. Настройка fillfactor для таблиц с частыми UPDATE
  4. Внутреннее устройство TOAST и хранение больших значений
← Назад к PostgreSQL Performance & Query Optimization