PostgreSQL Performance & Query Optimization · Ders

Demet Görünürlüğü, xmin ve xmax

Ölü demetlerin neden biriktiğini ve taramaların yavaşladığını anlamak için satır sürümü üst bilgilerini çözümleyin.

1. ders / 413 adım

Demet Görünürlüğü, xmin ve xmax, CoddyKit'te ücretsiz bir PostgreSQL Performance & Query Optimization dersidir. Bu, 4 dersinin 1. 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.

Every Row Carries Hidden Bookkeeping

In PostgreSQL, a row is never just your columns. Each physical row version (a tuple) carries hidden system columns in its header that decide who is allowed to see it.

xmin — the transaction ID that inserted this tuple version.
xmax — the transaction ID that deleted or updated it (0 if still live).
ctid — the physical location (block, offset) of the tuple.

These fields are the foundation of MVCC: multiple versions of the same logical row can coexist on disk, each visible to a different set of transactions.

SELECT xmin, xmax, ctid, * FROM accounts WHERE id = 42;

MVCC: Readers Don't Block Writers

MVCC (Multi-Version Concurrency Control) means an UPDATE does not overwrite data in place. Instead it writes a new tuple version and marks the old one as expired by stamping its xmax.

The benefit: a long-running SELECT keeps reading the old version while a concurrent writer creates a new one. No locks between readers and writers.

The cost: the old version is not erased. It becomes a dead tuple once no transaction can still see it — and dead tuples are exactly what makes scans slow over time.

Reading xmin and xmax Directly

You can inspect the visibility header by selecting the hidden columns explicitly. They are not returned by *, so you must name them.

After an INSERT, xmin holds the inserting transaction's ID and xmax is 0 (meaning: not deleted, fully live).

The txid_current() function shows your current transaction ID, which lets you correlate what you see in xmin.

BEGIN;
INSERT INTO accounts (id, balance) VALUES (42, 100);
SELECT txid_current();          -- e.g. 5012
SELECT xmin, xmax FROM accounts WHERE id = 42;  -- xmin=5012, xmax=0
COMMIT;

An UPDATE Is a Delete Plus Insert

Watch what an UPDATE does to the headers. The original tuple gets its xmax set to the updating transaction, and a brand-new tuple is written with a fresh xmin.

The ctid changes too: the new version lives at a different physical location. The old version still occupies space until vacuum reclaims it.

SELECT ctid, xmin, xmax, balance FROM accounts WHERE id = 42;
-- ctid=(0,1)  xmin=5012  xmax=0    balance=100

UPDATE accounts SET balance = 150 WHERE id = 42;

SELECT ctid, xmin, xmax, balance FROM accounts WHERE id = 42;
-- ctid=(0,2)  xmin=5040  xmax=0    balance=150
-- the old (0,1) tuple now has xmax=5040 and is invisible to new snapshots

Snapshots Decide Visibility

Whether a tuple is visible to you depends on your transaction's snapshot: the set of transactions that had committed when your statement (or transaction, under REPEATABLE READ) began.

The simplified visibility rule for a tuple is:

Its xmin must be committed and visible to your snapshot (the insert happened-before you).
Its xmax must be not committed-and-visible (the delete has not happened from your point of view), or be 0.

This is why two sessions can see different balances for the same id at the same wall-clock moment.

Hint Bits and the txid_status Helper

Checking "is transaction X committed?" on every tuple read would be expensive. PostgreSQL caches the answer in per-tuple hint bits set on first access after commit, so later reads skip the commit-log (clog) lookup.

This is why the first scan after a big write batch can be slower than later ones: it is setting hint bits and dirtying pages. You can query commit status of an xid explicitly:

SELECT txid_status(5012);   -- 'committed', 'aborted', 'in progress', or NULL

Counting Dead Tuples per Table

Dead tuples are old versions whose xmax is committed and older than every active snapshot — nobody can see them anymore, but they still occupy pages.

PostgreSQL tracks an estimate in the statistics views. This is your first stop when a table's scans have quietly gotten slower:

SELECT relname,
       n_live_tup,
       n_dead_tup,
       round(n_dead_tup::numeric
             / NULLIF(n_live_tup + n_dead_tup, 0), 3) AS dead_ratio,
       last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Why Dead Tuples Slow Down Scans

A sequential or index scan must physically visit every tuple on each heap page it reads, then test visibility against the snapshot. Dead tuples are still on the page, so they:

inflate the number of pages a scan must read (table bloat);
cost CPU for visibility checks that always fail;
reduce the effectiveness of index-only scans because the visibility map has fewer all-visible pages.

A table that is 70% dead tuples does roughly 3x the I/O for the same number of live rows. The query plan looks identical; only the buffer counts grow.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM accounts WHERE balance > 1000;
-- Watch 'Buffers: shared hit/read' climb as dead tuples accumulate,
-- even though the row estimate and plan shape stay the same.

The xmin Horizon and Long Transactions

Vacuum can only remove a dead tuple if its xmax is older than the oldest snapshot still in use — the cluster's xmin horizon. A single idle-in-transaction session or a forgotten replication slot pins that horizon back.

The result: dead tuples pile up and vacuum reports work but reclaims nothing, because the rows are technically still potentially visible to that ancient transaction.

Hunt the culprit before it bloats the whole database:

SELECT pid, state, age(backend_xid) AS xid_age,
       age(backend_xmin) AS xmin_age,
       now() - xact_start AS xact_duration, query
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY age(backend_xmin) DESC
LIMIT 5;

Freezing and the 32-bit XID Wraparound

Transaction IDs are 32-bit and wrap around. To keep old tuples permanently visible, vacuum eventually freezes them — conceptually marking xmin as "infinitely in the past" via the committed/frozen hint bits so they are visible to all future snapshots.

If freezing falls behind, the cluster approaches wraparound and PostgreSQL will force an aggressive anti-wraparound vacuum (or refuse new writes). Monitor the age of the oldest unfrozen xid per table:

SELECT relname,
       age(relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(oid)) AS size
FROM pg_class
WHERE relkind = 'r'
ORDER BY age(relfrozenxid) DESC
LIMIT 10;

Putting It Together for Optimization

The practical workflow when scans on a hot, frequently-updated table degrade:

Confirm bloat: high n_dead_tup and dead ratio in pg_stat_user_tables.
Check the horizon: long transactions or stale replication slots in pg_stat_activity / pg_replication_slots.
Tune autovacuum for that table so it runs sooner on high-churn data.

Lowering the scale factor makes autovacuum trigger at a smaller absolute number of dead tuples, keeping pages clean and scans fast.

ALTER TABLE accounts SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 1000
);
-- Autovacuum now triggers at ~2% dead rows + 1000, instead of the 20% default.

Quick Check: Diagnosing Bloat

A high-churn table's queries have slowed even though the plan is unchanged. pg_stat_user_tables shows a high n_dead_tup, but autovacuum runs frequently and the dead-tuple count never drops. What is the most likely root cause?

İnteraktif derste cevapla

Recap: Headers Drive Performance

You now know how row version headers govern both correctness and speed:

xmin stamps the inserting transaction; xmax stamps the deleting/updating one. An UPDATE is a delete-plus-insert that leaves an old version behind.
Your snapshot plus these headers determine visibility — readers never block writers.
Old versions become dead tuples, causing bloat that inflates page reads and slows scans without changing the plan.
Vacuum reclaims them only past the xmin horizon; long transactions and replication slots block that, and freezing prevents XID wraparound.

Diagnose with pg_stat_user_tables and pg_stat_activity, then tune per-table autovacuum to keep hot tables clean.

Başlamak ücretsiz

Yapay zeka eğitmeniyle SQL öğren — ücretsiz

Tarayıcında gerçek kod yaz ve çalıştır, 7/24 yapay zeka eğitmeninden anında yardım al; web'de ya da uygulamada kaldığın yerden devam et.

Kurslar: 22
Dersler: 88

Ücretsiz öğrenmeye başla Tüm kursu görüntüle

Sıkça Sorulan Sorular

“Demet Görünürlüğü, xmin ve xmax” dersi ücretsiz mi?

Evet — “Demet Görünürlüğü, xmin ve xmax” 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.

“Demet Görünürlüğü, xmin ve xmax” dersinde ne öğreneceğim?

Ölü demetlerin neden biriktiğini ve taramaların yavaşladığını anlamak için satır sürümü üst bilgilerini çözümleyin. 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 1. dersidir.

“Demet Görünürlüğü, xmin ve xmax” 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

Demet Görünürlüğü, xmin ve xmax
HOT Güncellemeleri ve Yalnızca Yığın Demeti Zincirleri
Görünürlük Haritası ve Yalnızca Dizin Taramaları
WAL Üretimi ve Yazma Çoğalması

← PostgreSQL Performance & Query Optimization Sayfasına Dön