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(*);大小函数则会读取当前关系的实际存储占用。

sql
-- 先用标准视图列出用户表,便于编写可移植检查。
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;

预期结果

text
 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 则适合判断表的顺序扫描、索引扫描以及修改量。它们是诊断入口,不是永久审计日志。

sql
-- 当前会话级活动适合定位长查询、等待和连接状态。
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;

预期结果

text
 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 专有能力时再直接访问底层目录列。这样既保留诊断深度,也能降低版本升级时依赖内部实现细节的风险。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

系统目录与元数据

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通