PostgreSQL 核心原理：如何查询 `pg_class`、`pg_attribute` 等元数据？（系统目录表）

在 PostgreSQL 中，一切皆对象——表、索引、列、函数、类型、约束、操作符……这些数据库对象的定义并非存储在神秘的二进制结构中，而是以普通关系表的形式，明文存放在一组被称为“系统目录表”（System Catalog Tables）的特殊表中。

其中，pg_class、pg_attribute、pg_type、pg_namespace等是最核心的元数据表。掌握它们的结构与查询方法，就等于拿到了 PostgreSQL 内核的“地图”，不仅能实现动态 SQL、自动化运维、权限审计，还能深入理解数据库的内部工作机制。

本文将带你从零构建对系统目录的认知体系，详解关键系统表的字段含义、关联关系、典型查询场景，并提供大量可直接复用的 SQL 模板。

---

一、什么是系统目录表？——PostgreSQL 的“元数据仓库”

1.1 基本概念

• 系统目录表（System Catalogs）是 PostgreSQL 内置的一组特殊表，用于存储数据库集群的元数据（metadata）。
• 它们位于pg_catalogschema中（默认在搜索路径中，可省略前缀）。
• 所有系统表都以pg_开头，如pg_class,pg_attribute,pg_proc。
• 用户不能直接 INSERT/UPDATE/DELETE 这些表（需通过 DDL 语句间接修改）。
• 系统表本身也是“表”，可通过普通 SQL 查询。

💡 你可以把pg_catalog看作数据库的“DNA”，记录了所有对象的“基因信息”。

1.2 系统目录 vs 信息模式（information_schema）

特性	pg_catalog（系统目录）	information_schema
标准	PostgreSQL 特有	SQL 标准（跨数据库兼容）
信息量	✅ 极其丰富（含内部 OID、存储参数等）	⚠️ 有限（仅标准字段）
性能	✅ 直接查系统表，速度快	⚠️ 视图封装，可能慢
适用场景	深度运维、内核开发、高级诊断	跨库工具、通用元数据查询

✅建议：在 PostgreSQL 环境中，优先使用pg_catalog；仅当需要跨数据库兼容时才用information_schema。

1.3 性能提示

• 系统表很小，通常全在 shared_buffers 中，查询极快
• 避免在 OLTP 事务中频繁查系统表（虽快，但非必要）
• 使用pg_get_*系列函数（如pg_get_expr,pg_get_ruledef）比手动解析更可靠

1.4 系统目录的能力与依赖的系统表

能力	依赖的系统表
动态生成 DDL	pg_class,pg_attribute,pg_constraint
自动化索引优化	pg_stat_user_indexes,pg_index
权限审计	pg_auth_members,pg_roles
复杂依赖分析	pg_depend
类型系统扩展	pg_type,pg_cast

掌握pg_catalog，你就拥有了“透视”PostgreSQL 的能力。

---

二、核心系统目录表详解

以下是最常用的 6 张系统表，构成元数据查询的“黄金三角”。

---

2.1pg_class—— 所有“关系”的总账本

作用：存储表（table）、索引（index）、序列（sequence）、视图（view）、物化视图（materialized view）等“关系型对象”的元数据。

1、关键字段：

字段	类型	说明
oid	oid	对象唯一标识（主键）
relname	name	对象名称（如'users'）
relnamespace	oid	所属 schema 的 OID（引用pg_namespace.oid）
relkind	char	对象类型：r=普通表,i=索引,S=序列,v=视图,m=物化视图,c=复合类型,t=TOAST 表
relowner	oid	所有者（引用pg_authid.oid）
reltuples	float4	估算行数（用于 planner）
relpages	int4	估算数据页数
relhasindex	bool	是否有索引
relfilenode	oid	物理文件节点号（通常 = oid，但VACUUM FULL/CLUSTER后会变）
reltoastrelid	oid	对应的 TOAST 表 OID（若存在）

2、示例查询：

-- 查找所有用户表（排除系统表）SELECTrelname,relkind,reltuplesFROMpg_classWHERErelkind='r'ANDrelnamespace=(SELECToidFROMpg_namespaceWHEREnspname='public');-- 查看某表的物理文件名（用于定位数据文件）SELECTpg_relation_filepath('users');-- 返回 base/16384/16389

---

2.2pg_attribute—— 列（属性）的详细档案

作用：存储每个表/视图/复合类型的每一列的定义。

⚠️ 注意：一张表有 N 列，则pg_attribute中有 N 条记录。

1、关键字段：

字段	类型	说明
attrelid	oid	所属关系的 OID（引用pg_class.oid）
attname	name	列名
atttypid	oid	数据类型 OID（引用pg_type.oid）
attlen	int2	类型长度（-1=变长，如 text；4=int4）
attnum	int2	列序号（从 1 开始；<0 为系统列如ctid,xmin）
attstattarget	int4	统计信息采样目标
attbyval	bool	是否按值传递（小类型如 int4 为 true）
attstorage	char	存储策略：p=plain,e=external,m=main,x=extended
attnotnull	bool	是否 NOT NULL
atthasdef	bool	是否有默认值
attidentity	char	标识列：a=always,d=by default,''=否
attgenerated	char	生成列：s=stored,''=否

2、示例查询：

-- 获取 users 表的所有列定义SELECTa.attnameAScolumn_name,t.typnameASdata_type,a.attlen,a.attnotnull,a.atthasdefFROMpg_attribute aJOINpg_type tONa.atttypid=t.oidJOINpg_class cONa.attrelid=c.oidWHEREc.relname='users'ANDa.attnum>0-- 排除系统列ANDNOTa.attisdropped;-- 排除已删除列（ALTER DROP 后标记为 dropped）

---

2.3pg_type—— 数据类型的百科全书

作用：定义所有数据类型，包括内置类型（int4, text）、数组、枚举、复合类型、域（domain）等。

1、关键字段：

字段	类型	说明
oid	oid	类型 OID（主键）
typname	name	类型名（如'int4','text'）
typnamespace	oid	所属 schema
typlen	int2	固定长度（-1=变长）
typbyval	bool	是否按值传递
typtype	char	类型类别：b=基础类型,c=复合类型,d=域,e=枚举,p=伪类型,r=范围
typcategory	char	更粗粒度分类（如 ‘N’=数值, ‘S’=字符串）
typarray	oid	对应的数组类型 OID（如 int4 → int4[]）
typelem	oid	元素类型（对数组/范围有效）

2、示例查询：

-- 查看所有自定义枚举类型SELECTtypname,enumlabelFROMpg_type tJOINpg_enum eONt.oid=e.enumtypidWHEREt.typtype='e'ORDERBYt.typname,e.enumsortorder;

---

2.4pg_namespace—— Schema 的注册中心

作用：存储所有 schema（命名空间）的信息。

1、关键字段：

字段	类型	说明
oid	oid	schema OID
nspname	name	schema 名称（如'public','pg_catalog'）
nspowner	oid	所有者

所有对象（表、函数等）都属于某个 schema，通过relnamespace/pronamespace等字段关联。

---

2.5pg_index—— 索引的元数据

作用：存储索引与被索引表的关系及索引特性。

1、关键字段：

字段	类型	说明
indexrelid	oid	索引自身的 OID（引用pg_class.oid）
indrelid	oid	被索引的表 OID（引用pg_class.oid）
indkey	int2vector	被索引的列 attnum 数组（如{1,3}表示第1、3列）
indisunique	bool	是否唯一索引
indisprimary	bool	是否主键
indisexclusion	bool	是否排除约束索引
indpred	pg_node_tree	部分索引谓词（如WHERE status = 'active'）

2、示例查询：

-- 查看 users 表的所有索引及列SELECTi.relnameASindex_name,a.attnameAScolumn_name,ix.indisunique,ix.indisprimaryFROMpg_index ixJOINpg_class iONix.indexrelid=i.oidJOINpg_class tONix.indrelid=t.oidJOINpg_attribute aONa.attrelid=t.oidANDa.attnum=ANY(ix.indkey)WHEREt.relname='users';

---

2.6pg_depend与pg_shdepend—— 对象依赖图谱

作用：记录对象之间的依赖关系（如视图依赖表，函数依赖类型）。

• pg_depend：数据库内依赖
• pg_shdepend：共享对象依赖（如角色、表空间）

关键字段（pg_depend）：

字段	说明
classid	依赖者所属系统表 OID（如pg_class）
objid	依赖者 OID
refclassid	被依赖者所属系统表 OID
refobjid	被依赖者 OID
deptype	依赖类型：n=normal,a=auto（自动创建，如索引）,i=internal,p=pin（不可删）

🔍 通过此表可实现“级联删除分析”、“影响范围评估”。

---

三、系统目录表的关联关系图

💡 查询时，通常以pg_class为中心，连接pg_namespace（定位 schema）、pg_attribute（获取列）、pg_index（获取索引）。

---

四、高频实用查询模板（可直接复制）

4.1 获取表结构（类似\d table）

SELECTa.attnum,a.attnameAScolumn_name,format_type(a.atttypid,a.atttypmod)ASdata_type,CASEWHENa.attnotnullTHEN'NOT NULL'ELSE''ENDASnot_null,pg_get_expr(d.adbin,d.adrelid)ASdefault_value,col_description(a.attrelid,a.attnum)AScommentFROMpg_attribute aLEFTJOINpg_attrdef dONa.attrelid=d.adrelidANDa.attnum=d.adnumJOINpg_class cONa.attrelid=c.oidJOINpg_namespace nONc.relnamespace=n.oidWHEREc.relname='your_table_name'ANDn.nspname='public'ANDa.attnum>0ANDNOTa.attisdroppedORDERBYa.attnum;

✅ 使用format_type()自动处理varchar(50)、numeric(10,2)等带参数类型。

---

4.2 查找所有外键约束

SELECTtc.table_schema,tc.table_name,tc.constraint_name,kcu.column_name,ccu.table_nameASforeign_table_name,ccu.column_nameASforeign_column_nameFROMinformation_schema.table_constraints tcJOINinformation_schema.key_column_usage kcuONtc.constraint_name=kcu.constraint_nameJOINinformation_schema.constraint_column_usage ccuONccu.constraint_name=tc.constraint_nameWHEREtc.constraint_type='FOREIGN KEY'ANDtc.table_schema='public';

或使用系统表（更高效）：

SELECTconnameASfk_name,cl.relnameAStable_name,a.attnameAScolumn_name,cf.relnameASref_table,af.attnameASref_columnFROMpg_constraint ctJOINpg_class clONct.conrelid=cl.oidJOINpg_class cfONct.confrelid=cf.oidJOINpg_attribute aONa.attrelid=cl.oidANDa.attnum=ct.conkey[1]JOINpg_attribute afONaf.attrelid=cf.oidANDaf.attnum=ct.confkey[1]WHEREct.contype='f';

---

4.3 查找未使用索引（潜在可删）

SELECTschemaname,tablename,indexname,idx_scanAStimes_usedFROMpg_stat_user_indexesWHEREidx_scan=0ORDERBYschemaname,tablename,indexname;

结合pg_index.indisunique = false可排除唯一索引。

---

4.4 查看表的存储参数（如 fillfactor）

SELECTreloptionsFROMpg_classWHERErelname='your_table';-- 返回: {"fillfactor=80"}

---

4.5 获取函数定义

SELECTproname,proargtypes::regtype[]ASarguments,prorettype::regtypeASreturn_type,prosrcASsource_codeFROMpg_procWHEREproname='your_function'ANDpronamespace=(SELECToidFROMpg_namespaceWHEREnspname='public');

---

五、安全与权限注意事项

1. 默认所有用户可读pg_catalog

   • 出于兼容性考虑，PUBLIC有SELECT权限
   • 若需隐藏元数据，可REVOKE SELECT ON pg_catalog.pg_class FROM PUBLIC;（但可能破坏应用）

2. 敏感信息

   • 函数源码（prosrc）、默认值表达式可能包含密码或业务逻辑
   • 建议限制非 DBA 用户访问

3. 不要直接修改系统表

   • 即使有权限，直接UPDATE pg_class会导致 catalog corruption
   • 必须通过 DDL（CREATE,ALTER,DROP）操作

---

延伸学习：

• 官方文档：System Catalogs
• 命令行工具：\d+,\df+,\dt+底层均查询系统目录
• 源码位置：src/include/catalog/下各.h文件定义了系统表结构