dynamic current state
  当前 backend、锁、复制、进度

cumulative statistics
  自 reset 以来的 transaction、I/O、WAL、maintenance、statement

pg_stat_activity 当前没有 lock wait
过去五分钟 lock wait 曾导致用户超时

pg_stat_archiver.failed_count = 21
当前归档已经恢复并持续成功

pg_stat_io.read_time 增加
物理磁盘没有等量读取，因为 OS page cache 参与

replica WAL distance = 0
应用仍可能因为路由、事务快照或缓存读到旧结果

n_dead_tup = 0
表仍可能存在已分配但未归还给操作系统的空间

view
  -> source and update path
      -> current/cumulative/estimate/progress
          -> reset and snapshot
              -> independent corroboration
                  -> safe action

SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN (
  'track_activities',
  'track_counts',
  'track_functions',
  'track_io_timing',
  'track_wal_io_timing',
  'stats_fetch_consistency'
)
ORDER BY name;

track_wal_io_timing=off
wal_write_time=0

track_activities       on
track_counts           on
track_functions        all
track_io_timing        on
track_wal_io_timing    off
stats_fetch_consistency cache

SELECT
  backend_type,
  state,
  wait_event_type,
  count(*) AS sessions,
  max(clock_timestamp() - xact_start)
    FILTER (WHERE xact_start IS NOT NULL) AS max_xact_age,
  max(clock_timestamp() - query_start)
    FILTER (WHERE query_start IS NOT NULL) AS max_query_age
FROM pg_stat_activity
GROUP BY backend_type, state, wait_event_type
ORDER BY backend_type, state NULLS LAST, wait_event_type NULLS LAST;

autovacuum launcher / worker
background writer
checkpointer
walwriter / walsender / walreceiver
io worker
slotsync worker
logical replication worker

state = active
  therefore CPU is executing

state=active, wait_event is null
  -> backend 正在运行，或刚好未被采样到等待

state=active, wait_event is not null
  -> SQL 仍是 active，但正在等待

state=idle, wait_event_type=Client
  -> 等客户端发下一条命令

state=idle in transaction
  -> 事务仍开着，可能保留 snapshot/lock/xmin

SELECT
  pid,
  backend_type,
  state,
  wait_event_type,
  wait_event,
  clock_timestamp() - xact_start AS xact_age,
  clock_timestamp() - query_start AS query_age
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state = 'active'
  AND wait_event IS NOT NULL
ORDER BY query_start;

Lock
LWLock
IO
Client
IPC
Activity
Timeout
BufferPin
Extension

Lock
  application transaction contention
  DDL conflicting with queries
  idle in transaction retaining locks

IO
  cache miss
  sequential scan
  checkpoint-related work
  WAL read/write
  extension access

Client
  server waits for client
  slow consumer
  application not reading results

wait type
  -> affected backend/queryid
  -> blocker/resource
  -> user path
  -> corroborating counter/host evidence

BEGIN;
SELECT xact_commit FROM pg_stat_database WHERE datname = current_database();
-- 等待或在其他连接产生工作
SELECT xact_commit FROM pg_stat_database WHERE datname = current_database();
COMMIT;

每次采样使用短事务
不要在长事务里刷新 dashboard 数据
必要时调用 pg_stat_clear_snapshot()
记录采样时间和 stats_fetch_consistency

active transaction
  current activity can show it
  cumulative table/database changes may not yet be flushed

same metric name
  does not imply same counter history

query_age = now - query_start
xact_age  = now - xact_start

SELECT
  pid,
  datname,
  usename,
  application_name,
  state,
  clock_timestamp() - xact_start AS xact_age,
  wait_event_type,
  wait_event,
  backend_xid,
  backend_xmin
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state = 'idle in transaction'
ORDER BY xact_start;

SELECT locktype, mode, granted, count(*) AS locks
FROM pg_locks
GROUP BY locktype, mode, granted
ORDER BY locktype, mode, granted;

SELECT
  a.pid AS waiting_pid,
  a.datname,
  a.usename,
  a.application_name,
  a.wait_event_type,
  a.wait_event,
  clock_timestamp() - a.query_start AS wait_age,
  pg_blocking_pids(a.pid) AS blocking_pids
FROM pg_stat_activity AS a
WHERE cardinality(pg_blocking_pids(a.pid)) > 0
ORDER BY a.query_start;

direct blocker or blocker behind blocker?
transaction or prepared transaction?
DDL, row lock, advisory lock, relation extension?
which user journey?
is blocker making progress?
what is safe to cancel?

waiting backend
  -> direct blocker
      -> root blocker
          -> owner / transaction age / state

pg_stat_database.deadlocks       cumulative
log_lock_waits                   waits beyond deadlock_timeout
deadlock error log               discrete event
application retry/outcome        user semantics

exporter role
  stable, narrow, machine-only

interactive diagnostic role
  time-bounded, reviewed, pg_read_all_stats or narrower

evidence export
  aggregate and redact, no query text/client address

PostgreSQL shared buffers
  -> operating-system page cache
      -> filesystem / block layer
          -> physical or virtual storage

PostgreSQL read()
  may be satisfied by OS cache

shared buffer hit
  does not require an OS read

device read
  may be caused by another process

SELECT
  datname,
  numbackends,
  xact_commit,
  xact_rollback,
  blks_read,
  blks_hit,
  temp_files,
  temp_bytes,
  deadlocks,
  blk_read_time,
  blk_write_time,
  stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL
ORDER BY datname;

backend_type
object
context

reads / read_bytes / read_time
writes / write_bytes / write_time
extends / extend_bytes / extend_time
hits / evictions / reuses
fsyncs / fsync_time

SELECT
  backend_type,
  object,
  context,
  sum(reads) AS reads,
  sum(read_bytes) AS read_bytes,
  sum(read_time) AS read_ms,
  sum(writes) AS writes,
  sum(write_bytes) AS write_bytes,
  sum(write_time) AS write_ms,
  sum(fsyncs) AS fsyncs,
  sum(fsync_time) AS fsync_ms
FROM pg_stat_io
GROUP BY backend_type, object, context
HAVING
  coalesce(sum(reads), 0)
  + coalesce(sum(writes), 0)
  + coalesce(sum(fsyncs), 0) > 0
ORDER BY backend_type, object, context;

read_time rises, read_bytes rises
  -> more work or slower work

read_time per read rises
  -> average operation cost rises, but distribution unknown

read_bytes rises, device reads stable
  -> OS cache may serve more reads

timing is zero and tracking is off
  -> unknown, not free

SELECT *
FROM pg_stat_checkpointer;

num_timed
num_requested
num_done
restartpoints_*
write_time
sync_time
buffers_written
slru_written
stats_reset

requested checkpoint rate rises
  -> workload/config/administrative events may force checkpoints

write_time dominates
  -> checkpoint spreads writes

sync_time spikes
  -> fsync phase or storage path needs correlation

buffers_written rises
  -> more dirty data, not automatically a problem

checkpoint completion
WAL generation
dirty buffer path
storage latency
recovery objective
memory and workload

SELECT
  wal_records,
  wal_fpi,
  wal_bytes,
  wal_buffers_full,
  stats_reset
FROM pg_stat_wal;

SELECT
  archived_count,
  last_archived_wal,
  last_archived_time,
  failed_count,
  last_failed_wal,
  last_failed_time,
  stats_reset
FROM pg_stat_archiver;

历史是否失败过？
  failed_count since reset

最近是否出现新失败？
  increase(failed_count[window])

当前是否推进？
  last success age + WAL generation + archive queue

failed_count           21
last_failed_time       18:57:58Z
last_archived_time     22:27:54Z

pg36:archive_failures:increase15m > 0
and on (cls, ins, ip)
(time() - pg_archiver_finish_time) > 900

SELECT
  application_name,
  state,
  sync_state,
  sent_lsn,
  write_lsn,
  flush_lsn,
  replay_lsn,
  pg_wal_lsn_diff(sent_lsn, replay_lsn) AS sent_replay_gap_bytes,
  write_lag,
  flush_lag,
  replay_lag
FROM pg_stat_replication
ORDER BY application_name;

SELECT
  status,
  sender_host,
  sender_port,
  written_lsn,
  flushed_lsn,
  latest_end_lsn,
  last_msg_send_time,
  last_msg_receipt_time,
  latest_end_time
FROM pg_stat_wal_receiver;

sent - write    network/receiver write path
write - flush   replica flush path
flush - replay  replay/apply path

NULL time lag
  can coexist with zero position gap in an idle/current sample

SELECT
  slot_name,
  slot_type,
  active,
  wal_status,
  safe_wal_size,
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
    AS retained_bytes
FROM pg_replication_slots
ORDER BY slot_name;

vacuum threshold
  base threshold + scale factor * relation tuples

analyze threshold
  base threshold + scale factor * relation tuples

SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  n_mod_since_analyze,
  n_ins_since_vacuum,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze,
  vacuum_count,
  autovacuum_count,
  analyze_count,
  autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC NULLS LAST
LIMIT 50;

dead tuple
  an old row version no longer visible to current snapshots

reusable free space
  vacuum processed space available for future tuples

table bloat
  allocated pages exceed what current data/layout needs

filesystem size
  file blocks currently allocated

updates/deletes create dead versions
  -> vacuum evaluates global visibility horizon
      -> old xmin still needs them
          -> cannot remove
              -> dead tuples/object size grow

SELECT
  pid,
  datname,
  usename,
  application_name,
  state,
  backend_xid,
  backend_xmin,
  clock_timestamp() - xact_start AS xact_age
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

SELECT
  datname,
  age(datfrozenxid) AS xid_age,
  mxid_age(datminmxid) AS multixact_age
FROM pg_database
ORDER BY xid_age DESC;

SELECT
  n.nspname,
  c.relname,
  age(c.relfrozenxid) AS xid_age,
  mxid_age(c.relminmxid) AS multixact_age,
  pg_total_relation_size(c.oid) AS total_bytes
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY xid_age DESC
LIMIT 50;

current age
autovacuum_freeze_max_age
table override
consumption rate
vacuum throughput
blocker
remaining time with uncertainty

SELECT
  pid,
  datid,
  relid,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  num_dead_item_ids,
  max_dead_item_ids
FROM pg_stat_progress_vacuum;

heap_blks_scanned / heap_blks_total

n_mod_since_analyze
last_analyze / last_autoanalyze
table size
query plan estimate vs actual
statistics target
column distribution

SELECT
  n.nspname,
  c.relname,
  pg_relation_size(c.oid) AS heap_bytes,
  pg_indexes_size(c.oid) AS index_bytes,
  pg_total_relation_size(c.oid) AS total_bytes
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY total_bytes DESC
LIMIT 50;

growth rate
retention expectation
free-space horizon
maintenance/rewrite headroom
business volume
owner plan

metric
  aggregate by parent / age band / size band

dashboard
  top-N and drill-down

SQL
  on-demand exact partition inventory

user tables             1
estimated dead tuples   0
max table freeze age    1,050

at captured_at,
the declared sandbox target reported this small current baseline.