GaussDB 系统表与系统视图详解（内网运维版）
1. 系统表视图分类
-
pg_：继承自开源内核的系统表和视图，存储最基础的数据库元数据（如对象 OID、表结构、依赖关系等）。 -
gs_：GaussDB特有的系统表或视图，多用于扩展的内核特性与高级安全审计。 -
pgxc_： 分布式集群 场景下的专属前缀，用于维护多节点拓扑、分布式分布键、数据分片（Slice）等集群级元数据。 -
pgxc_node：查看集群所有节点（CN协调节点、DN数据节点、主备状态）的 IP 和端口。 -
pgxc_class：存储每张表的复制或分布信息，在集中式场景下只能查询表定义。 -
pgxc_slice：对于复杂的Range/List分布表，记录详细的分片区间边界。
为了让
Oracle 背景的 DBA 无缝切换，
GaussDB 提供了一些兼容视图层，其前缀对应关系如下：
-
adm_对应dba_：包含 所有数据库对象的信息；如ADM_TAB_COLUMNS、ADM_TABLES； -
db_对应all_：包含 当前用户能够访问的所有数据库对象的信息；如DB_TAB_COLUMNS、DB_TABLES、DB_ALL_TABLES； -
my_对应user_：包含 当前用户拥有的所有数据库对象的信息。
2. 系统表介绍
2.1
pg_database
记录数据库相关的元信息。
gaussdb=> select oid,datname,datdba,encoding,datcollate,datconnlimit,datcompatibility from pg_database order by oid; oid | datname | datdba | encoding | datcollate | datconnlimit | datcompatibility -------+-----------+--------+----------+------------+--------------+------------------ 1 | template1 | 10 | 0 | C | -1 | A 2 | templatem | 10 | 0 | C | -1 | M 14520 | template0 | 10 | 0 | C | -1 | A 14525 | postgres | 10 | 0 | C | -1 | A (4 rows)
参数说明：
-
oid：内核底层的 唯一数字 ID，对应文件系统中的数据目录名。 -
datname：数据库名称。template开头的是系统内置的模板库。 -
datdba：对应创建或拥有该数据库的 角色 OID （10通常为系统初始化管理员）。 -
encoding：内部编码的枚举值（如0在特定内核中代表SQL_ASCII或继承全局编码）。 -
datcollate：字符串比较和排序的规则。C代表基于 字节流的二进制排序，性能最高。 -
datconnlimit：该数据库允许的最大并发连接。 -1 代表不单独限制 ，受全局max_connections控制。 -
datcompatibility：数据库的兼容模式：A 代表兼容Oracle，M 代表兼容MySQL，B 代表兼容PostgreSQL。
- 查看所有数据库的大小
SELECT datname, pg_size_pretty(pg_database_size(oid)) FROM pg_database; datname | pg_size_pretty -----------+---------------- template1 | 24 MB template0 | 24 MB templatem | 19 MB postgres | 44 MB (4 rows)
- 查看当前数据库大小
SELECT current_database(), pg_database_size(current_database())/1024/1024 AS size_mb; current_database | size_mb ------------------+------------------ postgres | 43.9687538146973
- 查看数据库的最大会话数和实际会话数
SELECT d.datname , d.datconnlimit AS "max_sessions", COUNT(s.pid) AS "current_sessions" FROM pg_database d LEFT JOIN pg_stat_activity s ON d.datname = s.datname GROUP BY d.datname, d.datconnlimit ORDER BY 3 DESC; datname | max_sessions | current_sessions -----------+--------------+------------------ postgres | -1 | 20 templatem | -1 | 0 template0 | -1 | 0 template1 | -1 | 0 (4 rows)
- 监控数据库的 age 和 freeze 触发进度
gaussdb=> SHOW autovacuum_freeze_max_age; autovacuum_freeze_max_age --------------------------- 4000000000 SELECT datname AS "数据库名", age(datfrozenxid64) AS "事务年龄", ROUND( (age(datfrozenxid64)::numeric / 4000000000) * 100, 2 ) AS "Freeze触发进度(%)", CASE WHEN age(datfrozenxid64) > 4000000000 THEN '强制Freeze阈值' WHEN age(datfrozenxid64) > 2000000000 THEN '接近表级Freeze' WHEN age(datfrozenxid64) > 1000000000 THEN '关注' ELSE '正常' END AS "风险等级" FROM pg_database WHERE datname NOT LIKE 'template%' ORDER BY age(datfrozenxid64) DESC; 数据库名 | 事务年龄 | Freeze触发进度(%) | 风险等级 ----------+----------+-------------------+---------- postgres | 16725 | 0.00 | 正常 (1 row)
PG 由于 XID 为 32 位，且 XID 是环形使用，新旧事务差 超过 2³¹ ≈21.47 亿 就有可能分不清谁先谁后存在，所以 age > 21 亿 就存在回卷风险。
GaussDB 采用 64 位事务号，消除了 32 位 XID 的回卷风险；但页面级可见性仍依赖 32 位短 XID，因此在
age(datfrozenxid 64) 达到 40 亿（默认
autovacuum_freeze_max_age）时，内核会触发
aggressive vacuum 进行冻结，因此需监控 Freeze 的触发进度。
2.2 pg_namespace
记录模式相关的元信息
gaussdb=> select * from pg_namespace;
nspname | nspowner | nsptimeline | nspacl | in_redistribution | nspblockchain | nspcollation
----------------------+----------+-------------+------------------------------------+-------------------+---------------+--------------
pg_toast | 10 | 0 | | n | f |
cstore | 10 | 0 | | n | f |
dbe_perf | 10 | 0 | | n | f |
snapshot | 10 | 0 | | n | f |
blockchain | 10 | 0 | | n | f |
prvt_ilm | 10 | 0 | | n | f |
sys | 10 | 0 | | n | f |
pg_catalog | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_ilm_admin | 10 | 0 | | n | f |
public | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
sqladvisor | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
dbe_pldebugger | 10 | 0 | {rdsAdmin=UC/rdsAdmin,=U/rdsAdmin} | n | f |
字段解释如下：
-
nspname：模式名称； -
nspowner：模式的所有者 -
nsptimeline：模式时间线，用于支撑 GaussDB 核心的 闪回技术（Flashback）和时间线管理； -
nspacl：权限列表； -
in_redistribution：是否处于重分布状态； -
nspblockchain：是否为防篡改账本模式； -
nspcollation：模式默认排序规则。
- 查看模式及所属用户
SELECT n.nspname AS schema_name, r.rolname AS owner FROM pg_namespace n JOIN pg_roles r ON r.oid = n.nspowner ORDER BY n.nspname; schema_name | owner ----------------------+----------- blockchain | rdsAdmin cstore | rdsAdmin db4ai | rdsAdmin dbe_application_info | rdsAdmin dbe_compression | rdsAdmin dbe_describe | rdsAdmin dbe_file | rdsAdmin dbe_heat_map | rdsAdmin ... public | rdsAdmin rdsBackup | rdsBackup rdsMetric | rdsMetric rdsRepl | rdsRepl root | root snapshot | rdsAdmin sqladvisor | rdsAdmin sys | rdsAdmin (44 rows)
- 查看所有模式及其拥有的对象数量
SELECT n.nspname AS schema_name, COUNT(c.oid) AS object_count FROM pg_namespace n LEFT JOIN pg_class c ON c.relnamespace = n.oid GROUP BY n.nspname ORDER BY object_count DESC; schema_name | object_count ----------------------+-------------- pg_catalog | 736 dbe_perf | 188 sys | 184 pg_toast | 156 snapshot | 119 information_schema | 66 ...
- 查看当前模式
gaussdb=> show search_path; search_path ---------------- "$user",public (1 row)
- 查看当前数据库，用户和模式
gaussdb=> SELECT current_database(), current_user, current_schema; current_database | current_user | current_schema ------------------+--------------+---------------- postgres | root | root (1 row)
search
path ≠ currentschema，
search_path 是列表，
current_schema 是
最终生效的。
- 查看某个模式下的所有对象
gaussdb=> SELECT c.relname AS table_name, c.relkind AS kind FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'root' ORDER BY c.relname; table_name | kind ------------+------ t | r (1 row)
relkind 的类型有：
- r：普通表
- v：视图
- m：物化视图
- S：序列
- f：外部表
- 判断对象在哪个模式
gaussdb=> SELECT n.nspname AS schema_name FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relname = 't'; schema_name ------------- root (1 row)
- 查看某个模式下所有表及大小
SELECT n.nspname AS schema_name, c.relname AS table_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'root' AND c.relkind = 'r' ORDER BY pg_total_relation_size(c.oid) DESC; schema_name | table_name | size -------------+------------+--------- root | t | 0 bytes (1 row)
2.3 pg_tablespace
记录表空间相关的元信息。
gaussdb=> CREATE TABLESPACE tbs_fast RELATIVE LOCATION 'tablespace/tbs_fast';
CREATE TABLESPACE
-- 表空间位置，relative 表空间不在 base 文件夹里面
[root@gauss1 dn_6001]# pwd
/data/cluster/data/dn/dn_6001
[root@gauss1 dn_6001]# ll pg_location/tablespace/
total 0
drwx------ 3 omm omm 48 Sep 12 08:37 tbs_fast
-- 完整目录如下
[root@gauss1 pgsql_tmp]# pwd
/data/cluster/data/dn/dn_6001/pg_location/tablespace/tbs_fast/PG_9.2_201611171_dn_6001_6002_6003/pgsql_tmp
gaussdb=> select * from pg_tablespace;
spcname | spcowner | spcacl | spcoptions | spcmaxsize | relative
------------+----------+--------+------------+------------+----------
pg_default | 10 | | | | f
pg_global | 10 | | | | f
tbs_fast | 16725 | | | | t
(3 rows)
select pg_relation_filepath('t'::regclass);
字段解释：
-
spcname：表空间名称，系统自带两个表空间； - pg_default：用户表、索引、TOAST 的默认表空间
-
pg_global：全局系统表（如
pg_database、pg_authid） -
spcowner：表空间所有者 oid，10 通常是 初始超级用户。 -
spcacl：访问权限列表 -
spcoptions：参数选项。 -
pcmaxsize：空间配额上限。 -
relative是否为相对路径。
- 查询所有表空间基础信息
SELECT spcname AS tablespace_name, oid AS spcoid, pg_get_userbyid(spcowner) AS owner, spcacl AS acl, spcoptions AS options, CASE WHEN spcmaxsize IS NULL THEN 'UNLIMITED' ELSE pg_size_pretty(spcmaxsize::bigint) END AS max_size, relative, pg_tablespace_location(oid) AS location FROM pg_tablespace; tablespace_name | spcoid | owner | acl | options | max_size | relative | location -----------------+--------+----------+-----+---------+-----------+----------+--------------------- pg_default | 1663 | rdsAdmin | | | UNLIMITED | f | pg_global | 1664 | rdsAdmin | | | UNLIMITED | f | tbs_fast | 17034 | root | | | UNLIMITED | t | tablespace/tbs_fast (3 rows)
- 查询所有表空间下面有多少对象
SELECT t.spcname AS tablespace_name, COUNT(c.oid) AS object_count FROM pg_tablespace t LEFT JOIN pg_class c ON c.reltablespace = t.oid GROUP BY t.spcname ORDER BY object_count DESC; tablespace_name | object_count -----------------+-------------- pg_global | 77 pg_default | 0 tbs_fast | 0 (3 rows)
- 查看当前数据库默认表空间
SELECT datname, t.spcname AS default_tablespace FROM pg_database d JOIN pg_tablespace t ON t.oid = d.dattablespace; datname | default_tablespace -----------+-------------------- template1 | pg_default template0 | pg_default templatem | pg_default postgres | pg_default (4 rows)
postgres 和 业务库，默认表空间为
pg_default 。
- 查看对象实际在哪个表空间下面
SELECT c.relname, n.nspname, t.spcname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace ORDER BY n.nspname, c.relname;
- 查看某表空间下面的大表
SELECT c.relname AS table_name, n.nspname AS schema_name, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_tablespace t ON t.oid = c.reltablespace WHERE t.spcname = 'pg_global' ORDER BY pg_total_relation_size(c.oid) DESC; table_name | schema_name | size -----------------------------------------+-------------+------------ pg_shdepend | pg_catalog | 72 kB pg_authid | pg_catalog | 72 kB pg_resource_pool | pg_catalog | 72 kB pg_workload_group | pg_catalog | 72 kB pg_app_workloadgroup_mapping | pg_catalog | 72 kB pg_database | pg_catalog | 72 kB ....
select oid,spcname,spcowner,spcmaxsize from pg_tablespace;
2.4 pg_class
记录数据库对象及其之间的关系。包括表、索引、序列、视图、物化视图、Toast表等。
- 查询表的存储路径
gaussdb=> select pg_relation_filepath('t'::regclass);
pg_relation_filepath
----------------------
base/14525/16989
(1 row)
- 查看大表（按 relpages 估算）
SELECT c.relname, n.nspname, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace ORDER BY pg_total_relation_size(c.oid) DESC LIMIT 20; relname | nspname | size ----------------------------------+------------+--------- pg_rewrite | pg_catalog | 6304 kB pg_toast_2618 | pg_toast | 5944 kB pg_attribute | pg_catalog | 4264 kB pg_proc | pg_catalog | 4056 kB gs_asp | pg_catalog | 3960 kB ...
- 查看表的事务年龄
SELECT n.nspname, c.relname, age(c.relfrozenxid64) AS xid_age FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' ORDER BY xid_age DESC; nspname | relname | xid_age --------------------+------------------------------------------+--------- db4ai | snapshot | 17088 pg_catalog | gs_client_global_keys | 17088 pg_catalog | gs_encrypted_proc | 17088 pg_catalog | gs_matview_dependency | 17088 pg_catalog | pgxc_slice | 17088 pg_catalog | gs_opt_model | 17088 ...
- 查看 Toast 表
SELECT c.relname AS toast_table, p.relname AS main_table FROM pg_class c JOIN pg_class p ON p.reltoastrelid = c.oid WHERE c.relkind = 't';
- 查看无索引的大表
SELECT n.nspname, c.relname, pg_size_pretty(pg_total_relation_size(c.oid)) AS size FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'r' AND c.relhasindex = false ORDER BY pg_total_relation_size(c.oid) DESC; nspname | relname | size --------------------+-------------------------------+------------ snapshot | tables_snap_timestamp | 136 kB information_schema | sql_features | 96 kB information_schema | sql_sizing | 48 kB information_schema | sql_parts | 48 kB information_schema | sql_packages | 48 kB information_schema | sql_languages | 48 kB information_schema | sql_implementation_info | 48 kB pg_catalog | gs_global_config | 40 kB ...
- 查询某个模式下的所有数据库对象
select oid,relname,relowner,relkind,reltablespace,reltuples,reltoastrelid from pg_class where relnamespace=(select oid from pg_namespace where nspname='root');
- 查询使用
UStore或Astore的表
SELECT n.nspname AS "模式名", c.relname AS "表名", CASE WHEN c.reloptions::text LIKE '%storage_type=USTORE%' THEN 'USTORE' ELSE 'ASTORE' END AS "底层存储引擎" FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND n.nspname NOT LIKE 'pg_%'; 模式名 | 表名 | 底层存储引擎 --------------------+------------------------------------------+-------------- snapshot | tables_snap_timestamp | USTORE snapshot | snapshot | USTORE snapshot | snap_global_os_runtime | USTORE snapshot | snap_global_os_threads | USTORE snapshot | snap_global_instance_time | USTORE ...
- 查询从未执行过
ANALYZE(relpages不准确) 的表
SELECT n.nspname AS "模式名", c.relname AS "表名", c.reltuples AS "统计行数", c.relpages AS "统计页面数" FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND c.relpages = 0 AND n.nspname NOT LIKE 'pg_%' AND n.nspname NOT LIKE 'gs_%' AND n.nspname <> 'public';
- reltuples对应表的行数，即元组数。
- reltoastrelid对应与该表关联的Toast表的oid。
- 查询当前用户下所有表的行数和 comments，需要先
analyze表
select a.relname,a.reltuples,b.description from pg_class a left outer join pg_description b on b.objsubid=0 and a.oid=b.objoid where a.relnamespace=( select oid from pg_namespace where nspname='root' ) and a.relkind='r' order by a.relname;
2.5 pg_attribute
存储了表的所有列信息，是表字段的元数据表。pg class 管“表”，pgattribute 管“表的每一列”。
- 查看某张表的所有字段
SELECT a.attnum, a.attname, t.typname, a.attnotnull, a.atthasdef FROM pg_attribute a JOIN pg_type t ON t.oid = a.atttypid WHERE a.attrelid = 'root.t'::regclass AND a.attnum > 0 ORDER BY a.attnum; attnum | attname | typname | attnotnull | atthasdef --------+---------+---------+------------+----------- 1 | c | int4 | f | f (1 row)
字段说明：
-
attnum：字段在表中的逻辑列号； -
attname：字段名； -
typname：字段数据类型； -
attnotnull：是否 NOT NULL -
atthasdef：是否有 DEFAULT 值
- 查看带有 TEXT / VARCHAR / BYTEA 类型的表
SELECT c.relname, a.attname, t.typname FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid JOIN pg_type t ON t.oid = a.atttypid WHERE t.typcategory = 'S' -- String AND a.attnum > 0 ORDER BY c.relname;
- 查看已删除但是未清理的列
SELECT c.relname, a.attname FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid WHERE a.attisdropped AND c.relkind = 'r';
只有
VACUUM FULL 才能彻底清理。
- 查看表字段数
SELECT c.relname, COUNT(*) AS column_count FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid WHERE a.attnum > 0 GROUP BY c.relname ORDER BY column_count DESC;
- 查看系统列
gaussdb=> SELECT a.attname, a.attnum FROM pg_attribute a WHERE a.attrelid = 'root.t'::regclass AND a.attnum <= 0 ORDER BY a.attnum; attname | attnum --------------+-------- xc_node_hash | -11 xc_node_id | -8 tableoid | -7 cmax | -6 xmax | -5 cmin | -4 xmin | -3 ctid | -1 (8 rows)
ctid、
xmin、
xmax、
cmin、
cmax、
tableoid 为 PostgreSQL 标准 MVCC 系统列，xc
nodeid 是 GaussDB 特有分布式节点 ID，表示 这一行最初是在哪个 CN / DN 上产生的
- ctid = -1，行在物理文件中的位置，类似 Oracle 的
ROWID； - xmin = -3，插入该行的事务 XID，用于 事务可见性判断（MVCC）；
- cmin = -4，插入该行的命令 ID；
- xmax = -5，删除 / 更新该行的事务 XID；
- cmax = -6 ，删除 / 更新该行的命令 ID；
- tableoid = -7，该行所属表的 OID，对应 pg_class.oid；
2.6 pg_index
存储索引相关的元信息，一张索引 = 一行 pg_index。记录的是：索引属于哪张表、是否是主键/唯一索引、使用哪些列、索引是否可用/有效/可见、复制标识（逻辑复制）。
- 查看表的索引是否是主键，是否唯一，是否有效和可见
SELECT t.relname AS table_name, i.relname AS index_name, idx.indisprimary AS is_pk, idx.indisunique AS is_unique, idx.indisusable AS is_usable, idx.indisvalid AS is_valid FROM pg_index idx JOIN pg_class i ON i.oid = idx.indexrelid JOIN pg_class t ON t.oid = idx.indrelid ORDER BY t.relname, idx.indisprimary DESC; table_name | index_name | is_pk | is_unique | is_usable | is_valid ------------------------------------------+---------------------------------------------------------+-------+-----------+-----------+---------- gs_abo_model_statistic | gs_abo_model_statistic_oid_index | f | t | t | t gs_abo_model_statistic | gs_abo_model_statistic_groupkey_index | f | f | t | t gs_abo_model_statistic | gs_abo_model_statistic_modelname_index | f | t | t | t gs_asp | gs_asp_sampletime_index | f | f | t | t gs_auditing_policy | gs_auditing_policy_name_index | f | t | t | t
- 查看无效索引
SELECT t.relname, i.relname FROM pg_index idx JOIN pg_class i ON i.oid = idx.indexrelid JOIN pg_class t ON t.oid = idx.indrelid WHERE idx.indisvalid = false OR idx.indisusable = false;
- 查看主键索引列
SELECT t.relname AS table_name, a.attname AS column_name, row_number() OVER (PARTITION BY t.relname ORDER BY a.attnum) AS key_seq FROM pg_index idx JOIN pg_class t ON t.oid = idx.indrelid JOIN pg_attribute a ON a.attrelid = t.oid WHERE idx.indisprimary AND a.attnum = ANY (idx.indkey) ORDER BY t.relname, key_seq;
- 查看索引大小
SELECT t.relname AS table_name, i.relname AS index_name, pg_size_pretty(pg_relation_size(i.oid)) AS index_size FROM pg_index idx JOIN pg_class i ON i.oid = idx.indexrelid JOIN pg_class t ON t.oid = idx.indrelid ORDER BY pg_relation_size(i.oid) DESC;
- 查看复合索引顺序
SELECT
i.relname AS index_name,
array_agg(a.attname ORDER BY a.attnum) AS columns
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
JOIN pg_attribute a ON a.attrelid = idx.indrelid
WHERE a.attnum = ANY (idx.indkey)
GROUP BY i.relname
ORDER BY i.relname;
index_name | columns
---------------------------------------------------------+--------------------------------------------------------------------------
gs_abo_model_statistic_groupkey_index | {groupkey}
gs_abo_model_statistic_modelname_index | {modelname}
gs_abo_model_statistic_oid_index | {oid}
gs_asp_sampletime_index | {sample_time}
gs_auditing_policy_access_oid_index | {oid}
gs_auditing_policy_access_row_index | {accesstype,labelname,policyoid}
gs_auditing_policy_filters_oid_index | {oid}
gs_auditing_policy_filters_row_index | {policyoid}
gs_auditing_policy_name_index | {polname}
gs_auditing_policy_oid_index | {oid}
gs_auditing_policy_privileges_oid_index | {oid}
gs_auditing_policy_privileges_row_index | {privilegetype,labelname,policyoid}
...
2.7 pg_statistic
- 查看表的统计信息概况
gaussdb=> analyze random_user_data; ANALYZE gaussdb=> SELECT c.relname AS table_name, a.attname AS column_name, s.stanullfrac AS null_frac, s.stawidth, s.stadistinct FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum WHERE c.relname = 'random_user_data' ORDER BY a.attnum; table_name | column_name | null_frac | stawidth | stadistinct ------------------+-------------+-----------+----------+------------- random_user_data | id | 0 | 4 | -1 random_user_data | age | 0 | 4 | 50 random_user_data | username | 0 | 10 | -1 random_user_data | balance | 0 | 6 | -.92024 random_user_data | created_at | 0 | 8 | -1 (5 rows)
- 查看 MCV
SELECT c.relname, a.attname, s.stakind1, s.stavalues1 AS mcv_values, s.stanumbers1 AS mcv_freqs FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum WHERE s.stakind1 = 1 AND c.relname = 'random_user_data';
- 查看直方图
SELECT c.relname, a.attname, s.stakind1, s.stavalues1 AS histogram_bounds FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum WHERE s.stakind1 = 2 AND c.relname = 'random_user_data';
- 查看某列统计信息是否缺失
SELECT c.relname, a.attname, s.stadistinct FROM pg_class c JOIN pg_attribute a ON a.attrelid = c.oid LEFT JOIN pg_statistic s ON s.starelid = c.oid AND s.staattnum = a.attnum WHERE c.relname = 'random_user_data' AND a.attnum > 0 AND s.stadistinct IS NULL;
- 查看某列是否低基数
SELECT c.relname, a.attname, s.stadistinct FROM pg_statistic s JOIN pg_class c ON c.oid = s.starelid JOIN pg_attribute a ON a.attrelid = s.starelid AND a.attnum = s.staattnum WHERE s.stadistinct BETWEEN 1 AND 100 AND c.relname = 'random_user_data'; relname | attname | stadistinct ------------------+---------+------------- random_user_data | age | 50 (1 row)
- 对比 pg stats 与 pgstatistic
SELECT s.schemaname, s.tablename, s.attname, s.n_distinct, t.stadistinct FROM pg_stats s JOIN pg_class c ON c.relname = s.tablename JOIN pg_statistic t ON t.starelid = c.oid JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = s.attname WHERE s.tablename = 'random_user_data';
- 验证
pg_stats是否来自pg_statistic - 排查统计信息不一致
3. 系统视图介绍
3.1 pg_roles
记录角色相关的元信息。初始化用户、以及具有 sysadmin 或 createrole 属性的用户可以查看全部角色的信息。其他用户只能查看自己的信息。
- 查看所有可登录用户
gaussdb=>SELECT rolname, rolcanlogin, rolconnlimit FROM pg_roles WHERE rolcanlogin ORDER BY rolname; rolname | rolcanlogin | rolconnlimit -----------+-------------+-------------- rdsAdmin | t | -1 rdsBackup | t | -1 rdsMetric | t | -1 rdsRepl | t | -1 root | t | -1 (5 rows)
- 查看超级用户
gaussdb=> SELECT rolname FROM pg_roles WHERE rolsuper; rolname ---------- rdsAdmin (1 row)
- 查看系统管理员 / 审计管理员
gaussdb=> SELECT rolname, rolsystemadmin AS sysadmin, rolauditadmin AS auditadmin FROM pg_roles WHERE rolsystemadmin OR rolauditadmin ORDER BY rolname; rolname | sysadmin | auditadmin ----------+----------+------------ rdsAdmin | t | t root | t | f (2 rows)
- 查看具有创建数据库权限的用户
gaussdb=> SELECT rolname FROM pg_roles WHERE rolcreatedb; rolname ---------- rdsAdmin root (2 rows)
- 查看具有创建角色权限的用户
gaussdb=> SELECT rolname FROM pg_roles WHERE rolcreaterole; rolname ---------- rdsAdmin root (2 rows)
- 查看复制角色
gaussdb=> SELECT rolname FROM pg_roles WHERE rolreplication; rolname ---------- rdsAdmin rdsRepl (2 rows)
- 查看账号有效期
gaussdb=> SELECT rolname, rolvalidbegin, rolvaliduntil FROM pg_roles WHERE rolcanlogin ORDER BY rolvaliduntil; rolname | rolvalidbegin | rolvaliduntil -----------+---------------+--------------- rdsAdmin | | rdsBackup | | rdsRepl | | root | | rdsMetric | | (5 rows)
- 查看账号连接数限制，-1 表示无限制
gaussdb=> SELECT rolname, rolconnlimit FROM pg_roles WHERE rolcanlogin AND rolconnlimit != -1 ORDER BY rolconnlimit; rolname | rolconnlimit ---------+-------------- (0 rows)
- 查看角色继承关系
SELECT r.rolname AS role_name, p.rolname AS parent_role FROM pg_roles r JOIN pg_auth_members m ON m.member = r.oid JOIN pg_roles p ON p.oid = m.roleid ORDER BY r.rolname; role_name | parent_role -----------+------------------------ root | gs_role_copy_files root | gs_role_signal_backend root | gs_role_tablespace root | gs_role_replication root | gs_role_account_lock root | gs_role_pldebugger (6 rows)
- 高风险账号汇总
gaussdb=> SELECT rolname, rolsuper, rolsystemadmin, rolauditadmin, rolcreatedb, rolcreaterole FROM pg_roles WHERE rolsuper OR rolsystemadmin OR rolauditadmin ORDER BY rolname; gaussdb-> rolname | rolsuper | rolsystemadmin | rolauditadmin | rolcreatedb | rolcreaterole ----------+----------+----------------+---------------+-------------+--------------- rdsAdmin | t | t | t | t | t root | f | t | f | t | t (2 rows)
3.2 pg_user
pg_user 是
pg_authid 的“用户视角简化视图”，只包含可登录用户，不包含普通角色。
- 查看所有数据库用户
SELECT usename, usesysid, usecreatedb, usesuper FROM pg_user ORDER BY usename; usename | usesysid | usecreatedb | usesuper -----------+----------+-------------+---------- rdsAdmin | 10 | t | t rdsBackup | 16717 | f | f rdsMetric | 16729 | f | f rdsRepl | 16721 | f | f root | 16725 | t | f (5 rows)
- 查看用户及当前会话数
SELECT u.usename, COUNT(a.pid) AS session_count FROM pg_user u LEFT JOIN pg_stat_activity a ON a.usename = u.usename GROUP BY u.usename ORDER BY session_count DESC; usename | session_count -----------+--------------- rdsAdmin | 19 root | 1 rdsRepl | 0 rdsMetric | 0 rdsBackup | 0 (5 rows)
3.3 pg_tables
pg_tables 是系统视图，基于 pg
class + pgnamespace 封装，只展示“表（relkind = 'r'）”，是日常巡检里最常用的表清单视图。
- 查看表及其模式名和表空间
SELECT schemaname, tablename, tablespace FROM pg_tables ORDER BY tablespace, schemaname, tablename; schemaname | tablename | tablespace --------------------+------------------------------------------+------------ pg_catalog | gs_global_config | pg_global pg_catalog | gs_ilm_param | pg_global pg_catalog | gs_ilm_ticker | pg_global pg_catalog | gs_obsscaninfo | pg_global ...
- 查看所有表及行数估算
SELECT t.schemaname, t.tablename, c.reltuples::bigint AS estimate_rows FROM pg_tables t JOIN pg_class c ON c.relname = t.tablename JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.schemaname WHERE t.schemaname NOT LIKE 'pg_%' ORDER BY c.reltuples DESC; schemaname | tablename | estimate_rows --------------------+------------------------------------------+--------------- root | random_user_data | 100000 snapshot | snap_global_shared_memory_detail | 11142 snapshot | snap_global_config_settings | 11076 ...
- 查看所有表及大小
SELECT t.schemaname, t.tablename, pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size, pg_size_pretty(pg_relation_size(c.oid)) AS data_size FROM pg_tables t JOIN pg_class c ON c.relname = t.tablename JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.schemaname ORDER BY pg_total_relation_size(c.oid) DESC; schemaname | tablename | total_size | data_size --------------------+------------------------------------------+------------+----------- - root | random_user_data | 6704 kB | 6680 kB pg_catalog | pg_rewrite | 6304 kB | 248 kB pg_catalog | pg_attribute | 4264 kB | 2880 kB pg_catalog | pg_proc | 4056 kB | 2160 kB pg_catalog | gs_asp | 3960 kB | 3480 kB snapshot | snap_global_config_settings | 3928 kB | 3360 kB ...
3.4 pg_indexes
pg_indexes 是基于
pg_index +
pg_class 封装，用于查看索引定义和属性，是索引巡检中最常用的视图。
select schemaname,tablename,indexname,tablespace,indexdef from pg_indexes where tablename='';
- 查看当前数据库的所有索引
SELECT schemaname, tablename, indexname, tablespace, indexdef FROM pg_indexes ORDER BY schemaname, tablename, indexname;
- 查看某张表的索引
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'random_user_data' ORDER BY indexname;
- 查看所有唯一索引
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE indexdef LIKE '%UNIQUE%' ORDER BY schemaname, tablename;
- 查看所有主键
SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE indexdef LIKE '%PRIMARY%' ORDER BY schemaname, tablename;
- 查看冗余索引（表上索引大于 5 个）
SELECT tablename, string_agg(indexname, ', ') AS indexes, COUNT(*) AS cnt FROM pg_indexes WHERE schemaname NOT LIKE 'pg_%' GROUP BY tablename HAVING COUNT(*) > 5 ORDER BY cnt DESC;
3.5 pg_views
pg_views 是基于
pg_class +
pg_rewrite 封装，用于查看视图定义、属主及所属模式，是视图级元数据审计的核心视图。
- 查看某个模式下的所有视图
SELECT viewname, viewowner FROM pg_views WHERE schemaname = 'root' ORDER BY viewname;
- 查看某个用户拥有的视图
SELECT schemaname, viewname FROM pg_views WHERE viewowner = 'appuser' ORDER BY schemaname, viewname;
- 查看视图定义
SELECT schemaname, viewname, definition FROM pg_views WHERE viewname = 'pg_views';
3.6 pg_locks
数据库实例中当前活跃进程的锁信息。我们先构造一个锁等待场景。
-- Session A BEGIN; UPDATE random_user_data SET balance = balance - 100 WHERE id = 1; -- 获得 ROW EXCLUSIVE（random_user_data 表锁），获得 行级排他锁（tuple lock） -- Session B（新连接） BEGIN; UPDATE random_user_data SET balance = balance + 100 WHERE id = 1; -- 卡住，同一行已经被 Session A 锁定，Session B 必须等 Session A 提交或回滚。
- 查看当前所有锁
SELECT locktype, relation::regclass AS relname, mode, granted, pid FROM pg_locks ORDER BY relation, mode, pid; locktype | relname | mode | granted | pid ---------------+------------------+------------------+---------+----------------- relation | pg_locks | AccessShareLock | t | 140480064517888 relation | random_user_data | AccessShareLock | t | 140478577108736 relation | random_user_data | AccessShareLock | t | 140479813908224 tuple | random_user_data | ExclusiveLock | t | 140479813908224 relation | random_user_data | RowExclusiveLock | t | 140478577108736 relation | random_user_data | RowExclusiveLock | t | 140479813908224 virtualxid | | ExclusiveLock | t | 140477887145728 virtualxid | | ExclusiveLock | t | 140477903927040 virtualxid | | ExclusiveLock | t | 140478577108736 transactionid | | ExclusiveLock | t | 140478577108736 transactionid | | ExclusiveLock | t | 140479813908224 virtualxid | | ExclusiveLock | t | 140479813908224 virtualxid | | ExclusiveLock | t | 140480064517888 transactionid | | ShareLock | f | 140479813908224 (14 rows)
字段解释：
-
locktype：锁的类型。常见的有relation（表级锁）、tuple（行级锁/元组锁）、transactionid（事务所持有的锁）、virtualxid（虚拟事务ID锁） -
relname：对象名称（表名）。如果是事务ID或虚拟事务等非表级资源，此项为空。 -
mode：锁的模式。如AccessShareLock（访问共享锁）、RowExclusiveLock（行排他锁）、ExclusiveLock（排他锁）、ShareLock（共享锁）。 -
granted：锁是否已被授予。t(true) 表示已成功获取并持有，f(false) 表示未被授予、当前进程正处于 挂起等待状态。 -
pid：后台数据库会话的进程 ID。
- 查看正在等待的会话
SELECT a.pid, a.usename, a.application_name, a.client_addr, a.state, l.locktype, l.relation::regclass AS relname, l.mode, l.granted FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid WHERE l.granted = false ORDER BY a.pid; pid | usename | application_name | client_addr | state | locktype | relname | mode | granted -----------------+---------+------------------+-------------+--------+---------------+---------+-----------+--------- 140479813908224 | root | gsql | | active | transactionid | | ShareLock | f (1 row)
-
state(active)：会话当前的状态为active（活跃中），意味着它正在尝试执行 SQL 语句，但由于拿不到锁而卡在了执行阶段。 -
locktype(transactionid)：锁的类型是事务 ID 锁。当一个会话试图修改另一未提交事务修改过的数据行时，它会去请求对端事务 ID 的共享锁。 -
relname(为空)：由于锁的对象是事务 ID（transactionid）而非具体的表结构，因此表名列为空。 -
mode(ShareLock)：申请的锁模式为共享锁。 -
granted(f)：核心判断标志。f(false) 表示 该锁未被授予，即该进程正在挂起等待其他事务释放资源。
- 查看谁阻塞了谁
SELECT blocked.pid AS waiting_pid, blocked.usename AS waiting_user, blocked.query AS waiting_query, blocking.pid AS blocking_pid, blocking.usename AS blocking_user, blocking.query AS blocking_query FROM pg_locks bl JOIN pg_stat_activity blocked ON blocked.pid = bl.pid JOIN pg_locks kl ON kl.locktype = bl.locktype AND kl.database IS NOT DISTINCT FROM bl.database AND kl.relation IS NOT DISTINCT FROM bl.relation AND kl.page IS NOT DISTINCT FROM bl.page AND kl.tuple IS NOT DISTINCT FROM bl.tuple AND kl.pid != bl.pid JOIN pg_stat_activity blocking ON blocking.pid = kl.pid WHERE bl.granted = false AND kl.granted = true; waiting_pid | waiting_user | waiting_query | blocking_pid | blocking_user | blocking_query -----------------+--------------+-------------------------------------------------------------------+-----------------+---------------+-------------------------------------------------------- ----------- 140680632465152 | root | UPDATE random_user_data SET balance = balance + 100 WHERE id = 1; | 140679456941824 | root | UPDATE random_user_data SET balance = balance - 100 WHE RE id = 1; (1 row)
- 查看表级锁
SELECT relation::regclass AS tablename, mode, granted, pid FROM pg_locks WHERE locktype = 'relation' ORDER BY relation, mode; tablename | mode | granted | pid ------------------+------------------+---------+----------------- pg_locks | AccessShareLock | t | 140680544909056 random_user_data | AccessShareLock | t | 140679456941824 random_user_data | AccessShareLock | t | 140680632465152 random_user_data | RowExclusiveLock | t | 140679456941824 random_user_data | RowExclusiveLock | t | 140680632465152 (5 rows)
常见锁模式：
-
ACCESS SHARE：SELECT -
ROW EXCLUSIVE：INSERT / UPDATE / DELETE -
SHARE：CREATE INDEX -
ACCESS EXCLUSIVE：ALTER / DROP / TRUNCATE
- 查看行锁
SELECT relation::regclass AS tablename, page, tuple, mode, granted, pid FROM pg_locks WHERE locktype = 'tuple' ORDER BY relation, page, tuple; ------------------+------+-------+---------------+---------+----------------- random_user_data | 0 | 1 | ExclusiveLock | t | 140680632465152 (1 row)
- 查看锁等待时长
SELECT a.pid, a.usename, a.query, a.xact_start, a.query_start, now() - a.query_start AS wait_duration, l.mode FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid WHERE l.granted = false ORDER BY wait_duration DESC; pid | usename | query | xact_start | query_start | wait_duration | mode -----------------+---------+-------------------------------------------------------------------+-------------------------------+-------------------------------+-----------------+----------- 140680632465152 | root | UPDATE random_user_data SET balance = balance + 100 WHERE id = 1; | 2026-09-12 12:12:05.875665+08 | 2026-09-12 12:12:06.092658+08 | 00:01:25.653838 | ShareLock (1 row)
- 查看某个表的锁情况
SELECT a.pid, a.usename, a.query, l.mode, l.granted FROM pg_locks l JOIN pg_stat_activity a ON l.pid = a.pid WHERE l.relation = 'root.random_user_data'::regclass; pid | usename | query | mode | granted -----------------+---------+-------------------------------------------------------------------+------------------+--------- 140680632465152 | root | UPDATE random_user_data SET balance = balance + 100 WHERE id = 1; | ExclusiveLock | t 140680632465152 | root | UPDATE random_user_data SET balance = balance + 100 WHERE id = 1; | RowExclusiveLock | t 140680632465152 | root | UPDATE random_user_data SET balance = balance + 100 WHERE id = 1; | AccessShareLock | t 140679456941824 | root | UPDATE random_user_data SET balance = balance - 100 WHERE id = 1; | RowExclusiveLock | t 140679456941824 | root | UPDATE random_user_data SET balance = balance - 100 WHERE id = 1; | AccessShareLock | t (5 rows)
3.7 pg_stats
存储在 pg_statistic 系统表里面的单列统计信息，用于查看单列统计信息，是优化器代价估算、执行计划异常排查的核心视图。
- 查找表中未被
ANALYZE收集统计信息的列
SELECT a.attname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace JOIN pg_attribute a ON a.attrelid = c.oid LEFT JOIN pg_stats s ON s.schemaname = n.nspname AND s.tablename = c.relname AND s.attname = a.attname WHERE c.relname = 'random_user_data' AND a.attnum > 0 AND s.attname IS NULL;
- 查看列的统计信息
SELECT attname, null_frac, avg_width, n_distinct, correlation FROM pg_stats WHERE tablename = 'random_user_data' ORDER BY attname;
3.8 pg statactivity
记录当前的活动会话和 SQL 执行信息，最常用的视图。
- 当前活跃 SQL
SELECT pid, usename, datname, application_name, client_addr, query_start, state, query FROM pg_stat_activity WHERE state = 'active' AND pid != pg_backend_pid() ORDER BY query_start; pid | usename | datname | application_name | client_addr | query_start | state | query -----------------+----------+----------+------------------+-------------+-------------------------------+--------+--------------------------------------------------- 140478120851200 | rdsAdmin | postgres | COMMStatusCheck | | 2026-09-12 11:17:50.305856+08 | active | COMMStatusCheck has handled client disconnection. 140477903927040 | rdsAdmin | postgres | workload | | 2026-09-12 11:17:50.335897+08 | active | WLM fetch collect info from data nodes 140478007146240 | rdsAdmin | postgres | PercentileJob | | | active | 140477525391104 | rdsAdmin | postgres | JobScheduler | | | active | 140476865566464 | rdsAdmin | postgres | Asp | | | active | (5 rows)
- 慢查询（超过 5 分钟）
SELECT pid, usename, datname, now() - query_start AS running_for, state, left(query, 200) FROM pg_stat_activity WHERE state = 'active' AND query_start < now() - interval '5 min' ORDER BY query_start; pid | usename | datname | running_for | state | left -----------------+----------+----------+-----------------+--------+--------------------------------------------------- 140478120851200 | rdsAdmin | postgres | 00:32:33.167633 | active | COMMStatusCheck has handled client disconnection. 140477903927040 | rdsAdmin | postgres | 00:32:33.137592 | active | WLM fetch collect info from data nodes (2 rows)
- 查看事务执行时长
SELECT pid, usename, datname, xact_start, now() - xact_start AS xact_age, state, left(query, 200) FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_start; pid | usename | datname | xact_start | xact_age | state | left -----------------+----------+----------+-------------------------------+-----------------+--------+---------------------------------------- 140477903927040 | rdsAdmin | postgres | 2026-09-12 11:50:38.511959+08 | 00:00:05.129548 | active | WLM fetch collect info from data nodes 140477887145728 | rdsAdmin | postgres | 2026-09-12 11:50:43.281933+08 | 00:00:00.359574 | | 140479813908224 | root | postgres | 2026-09-12 11:50:43.641507+08 | 00:00:00 | active | SELECT pid, + | | | | | | usename, + | | | | | | datname, + | | | | | | xact_start, + | | | | | | now() - xact_start AS xact_age,+ | | | | | | state, + | | | | | | left(query, 200) + | | | | | | FROM pg_stat_activity + | | | | | | WHERE xact_start IS NOT NULL + | | | | | | ORDER BY (3 rows)
- idle transaction（是否存在连接泄漏）
SELECT pid,
usename,
client_addr,
xact_start,
now() - xact_start AS idle_in_xact_for,
query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;
- 取消后台 SQL 执行
SELECT pg_cancel_backend(pid);
只中断当前正在执行的 SQL，SQL 被中断后：
- 抛异常：
canceling statement due to user request - 事务仍然开着
- 连接仍然活着
适合：
- 一条 SQL 跑的很慢；
- 应用还要继续用这个连接；
- 连接池里的连接不断掉。
- 直接断连接（回滚事务）
SELECT pg_terminate_backend(pid);
- 直接干掉整个会话进程
- 效果等同于：
- 客户端断开连接
- 服务端回滚事务
- 适合：
- 阻塞源；
- idle in transaction（长时间大量积累）；
- 死锁无法自动解除；
- 连接泄漏；
- 应用已失联。
总结：本期以 GaussDB 集中式集群为背景，系统梳理了
pg_catalog 下核心系统表与系统视图（
pg_class / pg_attribute / pg_locks / pg_stat_activity / pg_stats 等）的使用方式、实战排障案例，为日常巡检、锁阻塞分析提供可复制的 SQL 模板。如果需要 PDF 版本，欢迎私信我。
这里是《实战派 K 8 S&DB》，更多干货敬请关注公众号。如果本文对你有所帮助，欢迎点赞、推荐和转发，也欢迎关注后续文章，一起考证、一起学习
GaussDB 分布式数据库。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31444449/viewspace-3182260/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
