PostgreSQL Performance & Query Optimization · 강의

UPDATE가 많은 테이블의 Fillfactor 조정

HOT UPDATE를 위한 공간을 남기도록 fillfactor를 설정하고 자주 수정되는 행의 인덱스 변경을 줄이는 방법을 배웁니다.

레슨 3/413개 단계

UPDATE가 많은 테이블의 Fillfactor 조정은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 3번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 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 튜터와 함께 SQL을(를) 배우세요 — 무료

브라우저에서 실제 코드를 작성하고 실행하며, 24/7 AI 튜터로부터 즉각적인 도움을 받고, 웹이나 앱에서 중단한 부분부터 계속 학습하세요.

코스: 22
레슨: 88

무료로 학습 시작 전체 코스 보기

자주 묻는 질문

“UPDATE가 많은 테이블의 Fillfactor 조정” 강의는 무료인가요?

네 — “UPDATE가 많은 테이블의 Fillfactor 조정” 전체 내용을 이 웹사이트에서 무료로 읽을 수 있습니다. 인터랙티브하게 실습하려면(내장 코드 에디터와 24/7 AI 튜터), CoddyKit PRO로 업그레이드하면 PostgreSQL Performance & Query Optimization 강의 전체를 잠금 해제할 수 있습니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

“UPDATE가 많은 테이블의 Fillfactor 조정”에서 뭘 배우나요?

HOT UPDATE를 위한 공간을 남기도록 fillfactor를 설정하고 자주 수정되는 행의 인덱스 변경을 줄이는 방법을 배웁니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

PostgreSQL Performance & Query Optimization을(를) 시작하는 데 경험이 필요한가요?

사전 경험은 필요하지 않습니다. CoddyKit의 PostgreSQL Performance & Query Optimization은(는) 초급자부터 고급 학습자까지를 위해 구성되어 있으므로, 여기서 시작하거나 처음부터 시작할 수 있으며 자신의 속도대로 진행할 수 있습니다. 이것은 4개 중 3번째 강의입니다.

“UPDATE가 많은 테이블의 Fillfactor 조정” 강의는 얼마나 걸리나요?

대부분의 CoddyKit 강의는 약 5~10분이 소요됩니다. 각 강의는 간결하고 인터랙티브하여 꾸준한 진행이 가능하며, 웹과 앱에서 중단한 부분부터 바로 시작할 수 있습니다.

이 PostgreSQL Performance & Query Optimization 강의에서 코드를 작성하고 실행할 수 있나요?

네. 모든 PostgreSQL Performance & Query Optimization 강의에는 내장 코드 에디터가 포함되어 있으므로, 브라우저에서 바로 실제 코드를 작성하고 실행한 후 즉시 AI 피드백을 받을 수 있습니다 — 로컬 설정이 필요 없습니다.

이 강의의 모든 강의

← PostgreSQL Performance & Query Optimization(으)로 돌아가기