pg常用数据字典

pg_stat_activity — 当前会话信息
作用： 查看数据库当前正在干什么。

核心字段：

字段 说明
pid 进程号
usename 用户名
datname 数据库名
state 状态：active / idle / idle in transaction
query 正在执行的 SQL
wait_event / wait_event_type 是否在等待锁或 IO
backend_start / query_start 启动或开始时间

案例操作：
-- 1. 打开两个 psql 会话，A 和 B
-- A:
BEGIN;
UPDATE test SET name = 'aaa' WHERE id = 1; -- 不提交

-- B:
UPDATE test SET name = 'bbb' WHERE id = 1; -- 会被卡住
此时执行：

SELECT pid, usename, state, wait_event_type, wait_event, query
FROM pg_stat_activity
WHERE state <> 'idle';

解读：
会看到 B 的会话在等待锁（wait_event_type='Lock'）。
A 是阻塞者。可以用：
SELECT pg_blocking_pids(<B的pid>);
找出阻塞源。

---

---

pg_stat_all_tables — 所有表的访问统计
作用： 查看系统中所有表的访问频率和修改次数。

核心字段：

字段 含义
seq_scan / seq_tup_read 顺序扫描次数与读取行数
idx_scan / idx_tup_fetch 索引扫描次数与返回行数
n_tup_ins / upd / del 插入、更新、删除次数
n_live_tup / n_dead_tup 活行 / 死行数量
vacuum_time 上次清理时间
案例操作：

CREATE TABLE t_stat (id serial, val int);
INSERT INTO t_stat(val)
SELECT generate_series(1,10000);

SELECT * FROM t_stat WHERE val > 9000; -- 执行几次
UPDATE t_stat SET val = val + 1 WHERE val < 1000;
DELETE FROM t_stat WHERE val > 9999;

SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del, n_dead_tup
FROM pg_stat_all_tables
WHERE relname='t_stat';
解读：

seq_scan > 0：说明全表扫描；
n_dead_tup 高：说明死行多，应 vacuum。

---

---

3️⃣ pg_stat_user_tables — 仅用户表统计
与上一个类似，只过滤掉系统表。

案例：

SELECT relname, seq_scan, idx_scan, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;
说明：
适合 DBA 日常监控哪些表最脏（死行多）。

---

---

5️⃣ pg_stat_all_indexes — 所有索引使用情况
作用： 了解哪些索引常用，哪些没用。

核心字段：

字段 说明
idx_scan 索引被使用的次数
idx_tup_read 从索引读取的元组数
idx_tup_fetch 通过索引返回的元组数
案例：

CREATE INDEX idx_val ON t_stat(val);
SELECT * FROM t_stat WHERE val = 1;
SELECT * FROM t_stat WHERE val > 5000;

SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_all_indexes
WHERE relname='t_stat';
解读：

idx_scan=0 → 该索引未被使用，可考虑删除；
适用于索引健康检查。

---

---

8️⃣ pg_stat_database — 数据库整体性能概览
作用： 按数据库级别聚合事务与缓存命中信息。

核心字段：

字段 含义
numbackends 当前连接数
xact_commit / xact_rollback 提交/回滚次数
blks_read / blks_hit 磁盘读 / 缓存命中次数
tup_returned / tup_fetched 返回行数
案例：

SELECT datname,
numbackends,
xact_commit, xact_rollback,
blks_read, blks_hit,
ROUND(100.0 * blks_hit / (blks_hit + blks_read + 1),2) AS cache_hit_ratio
FROM pg_stat_database;
解读：

命中率低（< 90%）说明内存缓存不够；
回滚多说明事务出错或逻辑问题。

---

🔟 pg_stat_bgwriter — 后台写入进程统计
作用： 查看检查点与后台写入活动。

核心字段：

字段 说明
checkpoints_timed / req 定期与手动触发次数
buffers_checkpoint 检查点写出的缓冲页数
buffers_clean 后台清理写出的页数
buffers_backend 前端进程自己写出的页数
案例：

SELECT checkpoints_timed, checkpoints_req,
buffers_checkpoint, buffers_clean, buffers_backend
FROM pg_stat_bgwriter;
解读：

checkpoints_req 频繁说明写压力大；
优化方法：调大 checkpoint_timeout。

---

---

11️⃣ pg_stat_user_tables
作用：显示用户表的访问统计信息。

常见字段：

字段 含义
relid 表的 OID
relname 表名
seq_scan 顺序扫描次数
seq_tup_read 顺序扫描读取的行数
idx_scan 使用索引扫描的次数
n_tup_ins / upd / del / hot_upd 各类行操作统计
vacuum_count / analyze_count VACUUM 与 ANALYZE 执行次数
案例：

SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;
实战解读：
👉 快速判断哪个表“经常全表扫描”，或“频繁更新”。
当 seq_scan 很高、idx_scan 很低时，说明缺乏索引或 SQL 写得不好。

---

12️⃣ pg_stat_user_indexes
作用：用户表中索引的使用统计。

常见字段：

字段 含义
relname 表名
indexrelname 索引名
idx_scan 索引被使用的次数
idx_tup_read 通过索引读取的行数
idx_tup_fetch 最终取到的行数（命中率）
案例：

SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
实战解读：
👉 找出“没被用过的索引”。
当 idx_scan = 0 时，可能是多余索引，影响写入性能。

---

---