0Pricing
PostgreSQL Performance & Query Optimization · Ders

HOT Güncellemeleri ve Yalnızca Yığın Demeti Zincirleri

Güncellemelerin yalnızca yığında kalması ve dizin yazma çoğalmasından kaçınması için şemaları ve dizinleri tasarlayın.

HOT Güncellemeleri ve Yalnızca Yığın Demeti Zincirleri, CoddyKit'te ücretsiz bir PostgreSQL Performance & Query Optimization dersidir. Bu, 4 dersinin 2. dersidir. Aşağıdan dersin tamamını ücretsiz okuyabilir, sonra tarayıcıda yerleşik kod editörü ve 7/24 yapay zeka koçu ile uygulamalı olarak pratik yapabilirsin. Bu, PostgreSQL Performance & Query Optimization öğrenme yolunun bir parçasıdır ve ilerlemeniz web ve CoddyKit uygulaması arasında senkronize olur. PostgreSQL Performance & Query Optimization kursu toplamda 4 dersten oluşur.

Bu dersin bazı bölümleri henüz çevrilmemiş olup İngilizce olarak gösterilmektedir.

The Cost of an Update in PostgreSQL

Because PostgreSQL uses MVCC, an UPDATE does not overwrite a row in place. Instead it writes a brand-new tuple (the new row version) and marks the old one dead. The old version stays until VACUUM reclaims it.

Naively, every new tuple needs a new pointer in every index on the table, even indexes whose columns did not change. With 8 indexes, one logical update means 8 index inserts plus index bloat. This is index write amplification.

  • More WAL written (each index change is logged)
  • More index bloat (dead pointers accumulate)
  • More CPU and I/O per update

This lesson is about a mechanism that lets PostgreSQL skip that index work: HOT updates.

What is a HOT Update?

HOT stands for Heap-Only Tuple. A HOT update creates the new tuple version on the same heap page as the old one and updates no indexes at all.

Two conditions must both hold for an update to qualify as HOT:

  • No indexed column changed. If you touch any column used by any index, HOT is impossible.
  • There is room on the same page for the new tuple version.

When both hold, the old tuple's line pointer is redirected to point at the new tuple, forming a HOT chain. Indexes keep pointing at the original line pointer and never need to be touched.

The HOT Chain Mechanics

Inside a page, each tuple has a line pointer (item ID). On a HOT update:

  • The new tuple gets a fresh line pointer and the HEAP_ONLY_TUPLE flag.
  • The old tuple gets the HEAP_HOT_UPDATED flag, and its t_ctid points forward to the new tuple.
  • Index entries still reference the original line pointer, so a scan follows the chain forward to find the live version.

When VACUUM later runs, it can prune the chain: dead intermediate versions are removed and the root line pointer is converted into a redirect pointer straight to the surviving tuple. This is called HOT pruning and can even happen opportunistically during a normal page read (heap_page_prune).

Inspecting HOT Activity with pg_stat

You can measure how many of your updates are going the HOT path. The view pg_stat_user_tables exposes both the total updated tuples and the subset that were HOT.

A healthy write-heavy table wants n_tup_hot_upd close to n_tup_upd. A low ratio signals that updates are touching indexed columns or that pages are full.

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
ORDER  BY n_tup_upd DESC
LIMIT  20;

Rule #1: Don't Index Volatile Columns

The single most effective way to enable HOT is to avoid indexing columns that change frequently. Every index over a hot-path column forces non-HOT updates whenever that column is written.

Consider a sessions table whose last_seen_at is bumped on every request. If you index it, every heartbeat is a non-HOT update with full index churn.

  • Ask: does this index serve a real query, or is it speculative?
  • Frequently-mutated, low-selectivity columns rarely benefit from a btree anyway.
  • Dropping such an index can instantly flip a workload from 0% HOT to near 100%.
-- Anti-pattern: indexing a column updated on every request
CREATE INDEX idx_sessions_last_seen ON sessions (last_seen_at);

-- Each heartbeat now forces a non-HOT update + index insert:
UPDATE sessions SET last_seen_at = now() WHERE id = 42;

Rule #2: Leave Free Space with fillfactor

HOT needs room on the same page for the new tuple. If the page is packed, the new version spills to another page and the update cannot be HOT.

fillfactor tells PostgreSQL to leave a percentage of each page empty at load time, reserving space for in-page updates. The default for tables is 100 (pack tightly), which is great for append-only data but hostile to HOT on update-heavy tables.

For tables that are updated a lot, a fillfactor of 70-90 creates the headroom HOT needs.

ALTER TABLE sessions SET (fillfactor = 85);

-- Rewrite existing pages so the new fillfactor takes effect:
VACUUM FULL sessions;  -- or CLUSTER / pg_repack for online rewrite

Putting Both Rules Together

The winning recipe for an update-heavy table is to combine both rules: keep indexed columns stable and reserve in-page space.

Here a counters table is bumped constantly. We index only the stable lookup key, never the counter, and we set a generous fillfactor so the repeated updates stay on-page.

  • Index is on key, which never changes → condition 1 satisfied.
  • fillfactor = 80 leaves room → condition 2 satisfied.
  • Result: counter increments are HOT updates with zero index writes.
CREATE TABLE counters (
    key   text PRIMARY KEY,
    hits  bigint NOT NULL DEFAULT 0
) WITH (fillfactor = 80);

-- The hot path: bumps only a non-indexed column
UPDATE counters SET hits = hits + 1 WHERE key = 'page:/home';

Watch Out: Expression and Partial Indexes Still Count

HOT eligibility is decided by whether any indexed column's value changed, not just plain btree columns. This trips people up with:

  • Expression indexes: an index on lower(email) means writing email blocks HOT even if the lowercased value is logically identical.
  • Partial indexes: the indexed column still participates; updating it can break HOT regardless of the WHERE predicate.
  • Included columns (INCLUDE): in PostgreSQL, columns in the INCLUDE clause are also part of the index, so changing them blocks HOT.

PostgreSQL compares the old and new values of every column referenced by every index; if all are unchanged, HOT is allowed.

-- Both of these put `email` into the indexed-column set,
-- so any UPDATE that writes email becomes non-HOT:
CREATE INDEX idx_users_email_ci ON users (lower(email));
CREATE INDEX idx_users_email_inc ON users (id) INCLUDE (email);

Verifying HOT on a Real Update

The pg_stat_user_tables counters are cumulative, so you can take a before/after snapshot around a known workload to prove whether your tuning worked.

Run an update batch, then compare n_tup_hot_upd delta against n_tup_upd delta. If they move together, you are fully HOT; if only n_tup_upd climbs, something is still forcing index updates.

SELECT n_tup_upd, n_tup_hot_upd
FROM   pg_stat_user_tables
WHERE  relname = 'counters';

-- ... run UPDATE counters SET hits = hits + 1 WHERE key = 'page:/home'; x1000 ...

SELECT n_tup_upd, n_tup_hot_upd
FROM   pg_stat_user_tables
WHERE  relname = 'counters';
-- Expect both deltas to be ~1000 for a healthy HOT workload.

HOT and WAL Volume

Reducing index writes directly reduces WAL. A non-HOT update logs the heap change and every index insert; a HOT update logs only the heap change (plus, periodically, a prune record).

On a busy table with several indexes, switching updates to HOT can cut WAL generation substantially, which in turn:

  • Shrinks replication lag on standbys.
  • Reduces checkpoint and background-writer pressure.
  • Lowers archive storage for PITR.

You can quantify WAL per statement with pg_stat_statements (wal_bytes) to see the before/after effect of your fillfactor and index changes.

SELECT query, calls, wal_bytes,
       round(wal_bytes / NULLIF(calls, 0)) AS wal_per_call
FROM   pg_stat_statements
WHERE  query ILIKE 'UPDATE counters%'
ORDER  BY wal_bytes DESC;

When HOT Cannot Save You

HOT is powerful but not universal. It does not help when:

  • You genuinely need to update an indexed column (e.g. a status field that is also a search key) — the index write is unavoidable, though you can sometimes redesign the access path.
  • Pages stay full despite fillfactor because rows grow (variable-length text/JSONB expanding), pushing new versions off-page.
  • Long-running transactions hold back the xmin horizon, preventing HOT pruning so chains and bloat accumulate anyway.

The practical playbook: index only stable columns, set fillfactor on update-heavy tables, keep transactions short so pruning can reclaim space, and measure with n_tup_hot_upd and WAL stats.

Quick Check: Enabling HOT

Test your understanding of what makes an update HOT-eligible.

Recap: Keeping Updates Heap-Only

You now know how to engineer schemas and indexes so updates stay heap-only:

  • HOT update = new tuple on the same page, zero index writes, forming a HOT chain that VACUUM/pruning later collapses.
  • Two conditions: no indexed column changed, and there is free space on the page.
  • Rule 1: don't index volatile/hot-path columns; expression, partial, and INCLUDE columns all count as indexed.
  • Rule 2: set fillfactor (70-90) on update-heavy tables to reserve in-page room.
  • Measure: chase a high n_tup_hot_upd / n_tup_upd ratio and watch wal_bytes drop.
  • Limits: growing rows, genuinely indexed updates, and long transactions can still defeat HOT.

Maximizing HOT is one of the highest-leverage, lowest-risk wins for write-heavy PostgreSQL workloads.

Sıkça Sorulan Sorular

“HOT Güncellemeleri ve Yalnızca Yığın Demeti Zincirleri” dersi ücretsiz mi?

Evet — “HOT Güncellemeleri ve Yalnızca Yığın Demeti Zincirleri” dersin tüm metni burada web'de ücretsiz olarak okunabilir. Etkileşimli olarak pratik yapmak (yerleşik kod editörü ve 7/24 yapay zeka koçu) ve PostgreSQL Performance & Query Optimization kursunun geri kalanını açmak için CoddyKit PRO'ya yükselt. PostgreSQL Performance & Query Optimization kursu toplamda 4 dersten oluşur.

“HOT Güncellemeleri ve Yalnızca Yığın Demeti Zincirleri” dersinde ne öğreneceğim?

Güncellemelerin yalnızca yığında kalması ve dizin yazma çoğalmasından kaçınması için şemaları ve dizinleri tasarlayın. PostgreSQL Performance & Query Optimization ile uygulamalı kodu tarayıcıda doğrudan çalıştırarak pratik yaparsın ve 7/24 yapay zeka koçu dersi çalışırken sorularını yanıtlar.

PostgreSQL Performance & Query Optimization öğrenmeye başlamak için deneyim gerekli mi?

Önceden deneyim gerekmez. CoddyKit'te PostgreSQL Performance & Query Optimization, başlangıçtan ileri seviyeye kadar yapılandırıldığı için buradan başlayabilir veya başından başlayıp kendi hızında ilerleme yapabilirsin. Bu, 4 dersinin 2. dersidir.

“HOT Güncellemeleri ve Yalnızca Yığın Demeti Zincirleri” dersi ne kadar sürer?

Çoğu CoddyKit dersi yaklaşık 5–10 dakika sürer. Her biri kısa ve etkileşimli olduğu için sabit ilerleme yaparsın ve web ile uygulama arasında tam olarak bıraktığın yerden devam edebilirsin.

Bu PostgreSQL Performance & Query Optimization dersinde kod yazıp çalıştırabilir miyim?

Evet. Her PostgreSQL Performance & Query Optimization dersi yerleşik bir kod editörü içerir, bu sayede tarayıcıda gerçek kod yazıp çalıştırabilir ve anlık yapay zeka geri bildirimi alırsın — yerel kurulum gerekli değildir.

Bu kursun tüm dersleri

  1. Demet Görünürlüğü, xmin ve xmax
  2. HOT Güncellemeleri ve Yalnızca Yığın Demeti Zincirleri
  3. Görünürlük Haritası ve Yalnızca Dizin Taramaları
  4. WAL Üretimi ve Yazma Çoğalması
← PostgreSQL Performance & Query Optimization Sayfasına Dön