监控 xmin horizon 避免回卷和膨胀

2023-11-10 01:04:03.828 PST [56538] LOG:  automatic vacuum of table "nik.public.t": index scans: 0
  pages: 0 removed, 4480 remain, 4480 scanned (100.00% of total)
  tuples: 0 removed, 1000000 remain, 666667 are dead but not yet removable
  removable cutoff: 784, which was 112449 XIDs old when operation ended
  frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
  index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
  avg read rate: 9.685 MB/s, avg write rate: 0.000 MB/s
  buffer usage: 7281 hits, 1698 misses, 0 dirtied
  WAL usage: 0 records, 0 full page images, 0 bytes
  system usage: CPU: user: 0.11 s, system: 0.02 s, elapsed: 1.36 s

with bits as (
  select
    txid_snapshot_xmin(txid_current_snapshot()) as snapshot_xmin,
    -- 主库客户端会话持有的快照。
    (
      select backend_xmin
      from pg_stat_activity
      where backend_xmin is not null
      order by age(backend_xmin) desc
      limit 1
    ) as data_xmin_pg_stat_activity,
    -- 复制槽为用户表保留的事务 ID。
    (
      select xmin
      from pg_replication_slots
      where xmin is not null
      order by age(xmin) desc
      limit 1
    ) as data_xmin_pg_replication_slots,
    -- 逻辑复制槽为系统目录保留的事务 ID。
    (
      select catalog_xmin
      from pg_replication_slots
      where catalog_xmin is not null
      order by age(catalog_xmin) desc
      limit 1
    ) as catalog_xmin_pg_replication_slots,
    -- 备库反馈到主库的快照视界。
    (
      select backend_xmin
      from pg_stat_replication
      where backend_xmin is not null
      order by age(backend_xmin) desc
      limit 1
    ) as data_xmin_pg_stat_replication,
    -- 预备事务会一直保留事务 ID，直至提交或回滚。
    (
      select transaction
      from pg_prepared_xacts
      where transaction is not null
      order by age(transaction) desc
      limit 1
    ) as data_xmin_pg_prepared_xacts
)
select
  *,
  age(data_xmin_pg_stat_activity) as data_xmin_pg_stat_activity_age,
  age(data_xmin_pg_replication_slots) as data_xmin_pg_replication_slots_age,
  age(catalog_xmin_pg_replication_slots) as catalog_xmin_pg_replication_slots_age,
  age(data_xmin_pg_stat_replication) as data_xmin_pg_stat_replication_age,
  age(data_xmin_pg_prepared_xacts) as data_xmin_pg_prepared_xacts_age,
  greatest(
    age(data_xmin_pg_stat_activity),
    age(data_xmin_pg_replication_slots),
    age(data_xmin_pg_stat_replication),
    age(data_xmin_pg_prepared_xacts)
  ) as data_horizon_age,
  greatest(
    age(data_xmin_pg_stat_activity),
    age(data_xmin_pg_replication_slots),
    age(data_xmin_pg_stat_replication),
    age(data_xmin_pg_prepared_xacts),
    age(catalog_xmin_pg_replication_slots)
  ) as catalog_horizon_age
from bits;