PricingLoginStart free
PostgreSQL Performance & Query Optimization · 강의

튜플 가시성, xmin과 xmax

행 버전 헤더를 해석하여 데드 튜플이 누적되고 스캔이 느려지는 이유를 이해합니다.

레슨 1/413개 단계

튜플 가시성, xmin과 xmax은(는) CoddyKit의 무료 PostgreSQL Performance & Query Optimization 강의입니다. 이것은 4개 중 1번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 PostgreSQL Performance & Query Optimization 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. PostgreSQL Performance & Query Optimization 강의에는 총 4개의 강의가 포함되어 있습니다.

이 강의의 일부는 아직 번역되지 않았으며 영어로 표시됩니다.

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?

인터랙티브 레슨에서 답해보세요

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.

무료로 시작

AI 튜터와 함께 SQL을(를) 배우세요 — 무료

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

코스
22
레슨
88
무료로 학습 시작전체 코스 보기

자주 묻는 질문

“튜플 가시성, xmin과 xmax” 강의는 무료인가요?

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

“튜플 가시성, xmin과 xmax”에서 뭘 배우나요?

행 버전 헤더를 해석하여 데드 튜플이 누적되고 스캔이 느려지는 이유를 이해합니다. 브라우저에서 직접 실행하는 실습 코드로 PostgreSQL Performance & Query Optimization을(를) 배우며, 24/7 AI 튜터가 강의를 진행하면서 질문에 답변해줍니다.

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

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

“튜플 가시성, xmin과 xmax” 강의는 얼마나 걸리나요?

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

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

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

이 강의의 모든 강의

  1. 튜플 가시성, xmin과 xmax
  2. HOT UPDATE와 힙 전용 튜플 체인
  3. 가시성 맵과 인덱스 전용 스캔
  4. WAL 생성과 쓰기 증폭
← PostgreSQL Performance & Query Optimization(으)로 돌아가기