28.1 死元组与可见性

old tuple
  xmax <- updating transaction
  t_ctid -> new tuple location

new tuple
  xmin <- updating transaction
  values <- new values

T1 BEGIN ISOLATION LEVEL REPEATABLE READ
T1 SELECT row                 -- snapshot S1

T2 UPDATE row
T2 COMMIT

T3 SELECT row                 -- sees new version
T1 SELECT row                 -- still sees old version

SELECT
  ctid,
  xmin::text,
  xmax::text,
  id,
  revision
FROM maint.churn
WHERE id = 42;

WITH updated AS (
  UPDATE t SET payload = 'new'
  WHERE id <= 100
  RETURNING id
), deleted AS (
  DELETE FROM t
  WHERE id <= 100
  RETURNING id
)
SELECT ...;

UPDATE count       40,000
DELETE count       10,000
same-command count 60,000
next-command count 50,000

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  n_tup_ins,
  n_tup_upd,
  n_tup_del,
  n_tup_hot_upd,
  n_tup_newpage_upd,
  last_vacuum,
  last_autovacuum,
  vacuum_count,
  autovacuum_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

CREATE EXTENSION pgstattuple;

SELECT *
FROM pgstattuple('app.orders'::regclass);

running transaction snapshot
backend_xmin
idle in transaction
logical replication slot xmin/catalog_xmin
standby feedback
prepared transaction

remove no-longer-needed intermediate tuple data
convert root line pointer to redirect
compact page free space
preserve chain reachability for indexes

n_dead_tup decreased before autovacuum

update indexed column
update expression-index referenced column
page has no room
wide row grows
fillfactor leaves too little reserve
write pattern moves working set to packed pages

SELECT
  relname,
  n_tup_upd,
  n_tup_hot_upd,
  n_tup_newpage_upd,
  round(
    100.0 * n_tup_hot_upd / nullif(n_tup_upd, 0),
    2
  ) AS hot_pct,
  round(
    100.0 * n_tup_newpage_upd / nullif(n_tup_upd, 0),
    2
  ) AS newpage_pct
FROM pg_stat_user_tables
WHERE n_tup_upd > 0
ORDER BY n_tup_upd DESC;

n_tup_hot_upd      15,000
n_tup_newpage_upd  25,000

HOT gain
base table footprint
cache residency
scan cost
insert density
rewrite cost

VACUUM table
VACUUM (ANALYZE) table
VACUUM (FREEZE) table
VACUUM (INDEX_CLEANUP OFF) table

CREATE EXTENSION pg_freespacemap;

SELECT
  count(*) AS pages,
  sum(avail) AS reusable_bytes,
  max(avail) AS largest_page_free_bytes
FROM pg_freespace('app.orders'::regclass);

bit = 1 -> 条件必须为真
bit = 0 -> 条件可能不真，也可能尚未被 vacuum 证明

all_visible = 0

CREATE EXTENSION pg_visibility;

SELECT *
FROM pg_visibility_map_summary('app.orders'::regclass);

SELECT * FROM pg_check_visible('app.orders'::regclass);
SELECT * FROM pg_check_frozen('app.orders'::regclass);

UPDATE / DELETE
  |
  v
old row versions --------> snapshot horizon
  |                             |
  | page-local                  | when safe
  v                             v
prune / HOT chain          VACUUM heap scan
  |                             |
  +------ reusable page space --+--> FSM
                                |
                                +--> index cleanup
                                +--> VM all-visible/all-frozen
                                +--> relfrozenxid / relminmxid
                                +--> optional ANALYZE

minimum live footprint
  + space consumed between vacuum cycles
  = stable relation plateau

normal VACUUM always shrinks files     false
normal VACUUM never shrinks files      false

SELECT
  pg_relation_size('app.orders')       AS heap_bytes,
  pg_indexes_size('app.orders')        AS index_bytes,
  pg_total_relation_size('app.orders') AS total_bytes;

SELECT *
FROM pgstattuple('app.orders');

SELECT sum(avail)
FROM pg_freespace('app.orders');

baseline heap                 61,440,000 bytes
after churn heap              87,040,000 bytes
after holder-blocked vacuum   87,040,000 bytes
after release + freeze        87,040,000 bytes

dead tuples:
  with old snapshot           50,000
  after release               0

FSM reusable:
  final                       51,920,000 bytes

progress samples   128
phases             initializing, scanning heap
dead tuples        still 50,000

progress samples   171
phases             scanning heap, vacuuming indexes, vacuuming heap
dead tuples        0
all-frozen pages   10,625

Will the table reuse the space within the retention horizon?

one-time purge
tenant offboarding
retention shortened
schema removed wide columns
index permanently overgrown
filesystem headroom endangered

object: app.orders
live_bytes: ...
estimated_rewrite_bytes: ...
extra_disk_required: ...
wal_generated_estimate: ...
replica_replay_headroom: ...
archive_headroom: ...
lock_mode: ...
long_transaction_wait: ...
duration_estimate: ...
rollback: ...
backup_and_restore_proof: ...

oldest backend_xmin is unexplained
replication slot consumer ownership unknown
prepared transaction ownership unknown
free disk cannot hold rewrite
backup exists but restore untested
replica/archive headroom insufficient
lock queue begins to grow
suspected structural corruption

logical live rows
cumulative dead estimate
physical tuple/free-space sample
heap / index / total bytes
HOT / new-page update ratio
VM all-visible / all-frozen
oldest holder
last vacuum / autovacuum
write and growth rate
expected future reuse