PostgreSQL / 系统课时
系统目录与元数据
系统目录与元数据
排查对象定义、权限或运行状态时,不必依赖客户端界面。PostgreSQL 已把表、列、索引、会话和统计信息暴露为可查询的系统目录与视图,本课用几条 SQL 建立一套可重复的元数据检查方法。
学习目标
- 区分
pg_catalog、information_schema与统计视图的用途 - 查询用户对象的类型、估算行数、大小和列定义
- 读取当前会话与表级统计,并判断这些数据的可信边界
选择正确的元数据入口
information_schema 提供标准化视图,适合编写跨数据库或跨 PostgreSQL 版本的通用检查;pg_catalog 暴露 PostgreSQL 自身对象模型,字段更丰富;pg_stat_* 系列则记录运行活动和累计统计。三者解决的问题不同,不应互相替代。
| 入口 | 更适合查询 | 关键特点 |
|---|---|---|
information_schema |
表、列、约束等通用元数据 | 标准化、可移植,但 PostgreSQL 专有细节较少 |
pg_catalog |
OID、对象类型、类型系统、物理属性 | 信息最完整,适合 PostgreSQL 专项诊断 |
pg_stat_* |
会话、扫描、更新、缓存等运行统计 | 会随时间变化,且可能被重置 |
查询对象定义与物理属性
先用标准视图确认有哪些用户表,再用 pg_class 与 pg_namespace 补充 PostgreSQL 专有信息。reltuples 是统计估算值,不等同于精确 COUNT(*);大小函数则会读取当前关系的实际存储占用。
-- 先用标准视图列出用户表,便于编写可移植检查。
SELECT table_schema, table_name, table_type
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
ORDER BY table_schema, table_name
LIMIT 10;
-- 再进入 PostgreSQL 系统目录补充对象类型、估算行数和存储大小。
SELECT
n.nspname AS schema_name,
c.relname AS relation_name,
c.relkind,
c.reltuples::bigint AS estimated_rows,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_catalog.pg_class AS c
JOIN pg_catalog.pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'p', 'm')
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(c.oid) DESC
LIMIT 10;预期结果
table_schema | table_name | table_type
--------------+------------+-----------
public | <TABLE> | BASE TABLE
schema_name | relation_name | relkind | estimated_rows | total_size
-------------+---------------+---------+----------------+-----------
public | <TABLE> | r | <VALUE> | <VALUE>实际对象名、行数估算和大小会因数据库内容不同而变化。若需要列级细节,可继续查询 information_schema.columns;若需要还原视图或索引定义,优先使用 pg_get_viewdef()、pg_get_indexdef() 这类官方辅助函数,而不是自己拼接系统目录字段。
查看当前运行状态
pg_stat_activity 用于观察连接、事务等待和正在执行的 SQL;pg_stat_user_tables 则适合判断表的顺序扫描、索引扫描以及修改量。它们是诊断入口,不是永久审计日志。
-- 当前会话级活动适合定位长查询、等待和连接状态。
SELECT
pid,
usename,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query_preview
FROM pg_catalog.pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start NULLS LAST;
-- 累计表统计用于判断扫描方式与数据修改量。
SELECT
schemaname,
relname,
seq_scan,
idx_scan,
n_tup_ins,
n_tup_upd,
n_tup_del
FROM pg_catalog.pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;预期结果
pid | usename | state | wait_event_type | wait_event | running_for | query_preview
-------+---------+--------+-----------------+------------+-------------+----------------
<PID> | <USER> | active | <VALUE> | <VALUE> | <VALUE> | SELECT ...
schemaname | relname | seq_scan | idx_scan | n_tup_ins | n_tup_upd | n_tup_del
------------+---------+----------+----------+-----------+-----------+----------
public | <TABLE> | <VALUE> | <VALUE> | <VALUE> | <VALUE> | <VALUE><PID>、持续时间和统计计数都属于动态值,实际结果可能不同。查看其他会话的完整 SQL 还受权限影响;普通用户通常只能完整看到自己有权限观察的活动。
判断元数据是否可信
系统目录描述的是当前对象状态,统计视图描述的是当前或累计运行状态,两者都需要结合语义使用。reltuples 可能在大量写入后变旧,统计计数可能因重启、手动重置或版本配置而改变,而权限过滤也可能让视图结果不完整。
编写运维查询时,优先从公开视图和文档化函数取值;只有确实需要 PostgreSQL 专有能力时再直接访问底层目录列。这样既保留诊断深度,也能降低版本升级时依赖内部实现细节的风险。