28.4 膨胀与重建

allocated bytes
  - live payload
  - necessary page/index overhead
  - intentionally reserved fillfactor
  - space likely to be reused soon
  = potentially reclaimable waste

SELECT
  c.oid::regclass AS relation,
  pg_relation_size(c.oid) AS main_bytes,
  pg_table_size(c.oid) AS table_bytes,
  pg_indexes_size(c.oid) AS index_bytes,
  pg_total_relation_size(c.oid) AS total_bytes,
  c.reltuples::bigint AS planner_rows,
  c.relpages
FROM pg_class AS c
WHERE c.oid = 'app.orders'::regclass;

main fork
FSM / VM / init forks
TOAST table and TOAST indexes
user indexes
partition children

1. obsolete tuple not yet vacuumable
2. vacuumable tuple not yet processed
3. processed free space not reused by current workload
4. deliberately reserved page space / unavoidable overhead

CREATE EXTENSION pgstattuple;

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

table_len
tuple_count / tuple_len / tuple_percent
dead_tuple_count / dead_tuple_len / dead_tuple_percent
free_space / free_percent

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

SELECT *
FROM pgstatindex('app.orders_created_at_idx'::regclass);

index_size
tree_level
leaf_pages
empty_pages
deleted_pages
avg_leaf_density
leaf_fragmentation

avg_leaf_density < 70% -> must reindex

SELECT
  schemaname,
  relname,
  indexrelname,
  idx_scan,
  idx_tup_read,
  idx_tup_fetch,
  pg_relation_size(indexrelid) AS bytes
FROM pg_stat_user_indexes
WHERE relid = 'app.orders'::regclass
ORDER BY bytes DESC;

reltuples stale
  -> rows-per-page estimate wrong
  -> SQL bloat formula says 80%

n_dead_tup delayed
  -> dashboard shows vacuum did nothing

partition parent never ANALYZE
  -> planner row estimate wrong
  -> blamed on physical bloat

stats reset
  -> usage looks zero

SELECT
  stats_reset
FROM pg_stat_database
WHERE datname = current_database();

SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_analyze,
  last_autoanalyze,
  analyze_count,
  autoanalyze_count
FROM pg_stat_user_tables
WHERE relname = 'orders';

ANALYZE (VERBOSE) app.orders;

object:
  table: app.orders
  access_method: heap
  partition: false

size:
  heap_bytes: ...
  toast_bytes: ...
  index_bytes: ...
  total_bytes: ...
  growth_7d: ...

contents:
  planner_rows: ...
  cumulative_live: ...
  cumulative_dead: ...
  physical_dead_bytes: ...
  physical_free_bytes: ...

behavior:
  updates_per_hour: ...
  hot_ratio: ...
  expected_reuse_days: ...
  retention_change: ...

holders:
  backend_xmin: ...
  slots: ...
  prepared: ...

confidence:
  stats_reset: ...
  physical_scan_scope: ...
  captured_at: ...

healthy steady state
transient churn
vacuum blocked
vacuum underprovisioned
heap rewrite candidate
index-only rebuild candidate
inconclusive

write a new compact copy
keep old copy until operation completes
swap relation storage
return old storage after success

VACUUM (FULL, VERBOSE, ANALYZE) app.orders;

new heap copy
new indexes
temporary sort/build space
WAL
archive spool
replica retention/replay
filesystem reserve
failure residue/headroom

reclaimable bytes = operation free-space requirement

wait for ACCESS EXCLUSIVE
  -> command execution
      -> dependent work / validation
          -> release

VACUUM FULL waiting
  blocks later queries that conflict with its queued lock

SET lock_timeout = '5s';
SET statement_timeout = '2h';

create shadow table/index
capture concurrent changes
copy base data
replay delta
short final lock/swap
cleanup

pig pg repack mydb --plan
pig pg repack mydb -t app.orders
pig pg repack mydb -j 2

pg_repack version compatibility
extension installed in target database
eligible unique key
disk/WAL/replica budget
lock and timeout
DDL freeze
backup/restore proof
cleanup procedure

which operations continue?
which lock still occurs?
for how long?
what happens under long transactions?

1. clone representative database/table
2. reproduce bloat and statistics
3. run exact tool/version/options
4. measure peak extra disk, WAL, CPU, I/O
5. replay concurrent workload
6. inject interruption before final swap
7. verify cleanup and retry
8. verify backup/restore after rewrite

logical row count/digest
constraints and triggers
indexes valid/ready
grants/owner/comments
replica caught up
archive backlog recovered
plans and latency
autovacuum reloptions
backup chain and restore
temporary objects absent

measured index bloat with low reuse
amcheck or incident evidence indicates index inconsistency
collation/operator-class change requires rebuild
index storage parameter must fully take effect
invalid index recovery

index is large
idx_scan is zero since yesterday's stats reset
query is slow
calendar says monthly reindex

SHOW server_version;
SHOW server_version_num;

REINDEX INDEX app.orders_created_at_idx;

REINDEX INDEX CONCURRENTLY app.orders_created_at_idx;

new transient index
first table scan
second catch-up scan
wait for old snapshots/readers
catalog validity swap
drop old index

BEGIN;
REINDEX INDEX CONCURRENTLY app.orders_created_at_idx;
-- ERROR

SELECT
  pid,
  datname,
  relid::regclass AS table_name,
  index_relid::regclass AS index_name,
  command,
  phase,
  lockers_total,
  lockers_done,
  current_locker_pid,
  blocks_total,
  blocks_done,
  tuples_total,
  tuples_done,
  partitions_total,
  partitions_done
FROM pg_stat_progress_create_index;

building index
waiting for writers before build
waiting for writers before validation
index validation: scanning index
index validation: sorting tuples
index validation: scanning table
waiting for old snapshots
waiting for readers before marking dead
waiting for readers before dropping

SELECT
  n.nspname,
  c.relname,
  c.oid,
  i.indisready,
  i.indisvalid,
  i.indislive,
  pg_relation_size(c.oid) AS bytes
FROM pg_index AS i
JOIN pg_class AS c ON c.oid = i.indexrelid
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE i.indrelid = 'app.orders'::regclass
ORDER BY c.relname;

DROP INDEX app.orders_created_at_idx_ccnew;

SELECT
  conname,
  contype,
  conindid::regclass,
  convalidated
FROM pg_constraint
WHERE conrelid = 'app.orders'::regclass
  AND conindid <> 0;

REINDEX INDEX CONCURRENTLY return code  0
relfilenode changed                      true
same final name count                    1
_ccnew/_ccold artifacts                 0
elapsed on isolated fixture             0.089 s

reindex window
vacuum/freeze window
backup window
bulk load
schema migration

before:
  reason: measured_bloat
  heap_integrity: verified
  index_integrity: ...
  free_disk: ...
  wal_replica_archive_headroom: ...
  long_snapshot_inventory: []
  lock_timeout: ...

after:
  command_success: true
  expected_index_count: ...
  indisready_valid_live: true
  constraints_bound: true
  cc_artifacts: []
  logical_queries_equal: true
  replica_caught_up: true
  size_and_plan_reviewed: true

heap / TOAST / index size separated
stats reset and estimate confidence
physical sample cost and timestamp
future reuse horizon
rewrite benefit in bytes
peak extra disk / WAL / replica budget
lock mode and fail-fast timeout
tool/extension/server compatibility
concurrent progress and snapshot blockers
invalid index cleanup plan
constraint binding
post-change logical and recovery validation