postgres--系统视图，表

IT艺术家-rookie

已于 2024-10-08 11:34:31 修改

阅读量529

点赞数 4

分类专栏：数据库技术文章标签：云原生数据库

于 2024-09-10 17:58:56 首次发布

本文链接：https://blog.csdn.net/newbieJ/article/details/142092671

版权

数据库技术专栏收录该内容

10 篇文章 0 订阅

订阅专栏

pg_stat_all_tables

累积统计系统-监控数据库活动
pg_stat_all_tables是一个系统视图，它为当前数据库中的每个表（包括TOAST表）提供一行数据。显示了关于特定表的访问统计信息，比如访问次数等。pg_stat_user_tables和pg_stat_sys_tables这两个视图包含了与pg_stat_all_tables相同的信息pg_stat_user_tables仅显示用户创建的表的统计信息，而pg_stat_sys_tables仅显示系统表的统计信息。

relid oid: 表的唯一标识符（OID）。

schemaname name: 表所属的模式（schema）的名称。

relname name: 表的名称。

seq_scan bigint: 在此表上启动的顺序扫描次数。

last_seq_scan timestamp with time zone: 基于最近的事务停止时间，最后一次在此表上进行的顺序扫描的时间。

seq_tup_read bigint: 通过顺序扫描获取的行数。

idx_scan bigint: 在此表上启动的索引扫描次数。

last_idx_scan timestamp with time zone: 基于最近的事务停止时间，最后一次在此表上进行的索引扫描的时间。

idx_tup_fetch bigint: 通过索引扫描获取的行数。

n_tup_ins bigint: 插入的总行数。

n_tup_upd bigint: 更新的总行数。包括在n_tup_hot_upd和n_tup_newpage_upd中计数的行更新，以及剩余的非HOT更新。

n_tup_del bigint: 删除的总行数。

n_tup_hot_upd bigint: HOT更新的行数。这些更新不需要在索引中创建后继版本。

n_tup_newpage_upd bigint: 行更新的数量，其中后继版本被放置到新的堆页面上，留下带有指向不同堆页面的t_ctid字段的原始版本。这些总是非HOT更新。

n_live_tup bigint: 估计的存活行数。

n_dead_tup bigint: 估计的死亡行数。

n_mod_since_analyze bigint: 自上次分析此表以来估计的修改行数。

n_ins_since_vacuum bigint: 自上次对此表进行清理（vacuum）以来估计的插入行数。

last_vacuum timestamp with time zone: 手动清理（不包括VACUUM FULL）此表的最后时间。

last_autovacuum timestamp with time zone: 自动清理守护进程清理此表的最后时间。

last_analyze timestamp with time zone: 手动分析此表的最后时间。

last_autoanalyze timestamp with time zone: 自动分析守护进程分析此表的最后时间。

vacuum_count bigint: 手动清理（不包括VACUUM FULL）此表的次数。

autovacuum_count bigint: 自动清理守护进程清理此表的次数。

analyze_count bigint: 手动分析此表的次数。

autoanalyze_count bigint: 自动分析守护进程分析此表的次数。

这些统计信息可以帮助数据库管理员了解表的使用情况，优化查询性能，以及计划维护任务，如清理和分析。

TOAST 表

TOAST（The Oversized-Attribute Storage Technique）是 PostgreSQL 中的一种机制，用于处理大字段（如大文本或大二进制数据）的存储，以提高性能并优化存储空间。

TOAST 的特点：

存储超大字段：当某个列的值超过一定大小（通常为 2KB），PostgreSQL 会自动将其存储到一个 TOAST 表中。
简化主表：大字段被移动到 TOAST 表中，主表只存储一个指向 TOAST 数据的指针，从而减小主表的大小，提高查询性能。
压缩和分块：TOAST 支持对存储的数据进行压缩，进一步减少占用的存储空间，并可以将大字段分块存储，以便更高效地管理。

TOAST 表的使用情况：
适用于大文本字段（如 TEXT 或 BYTEA 类型）。
在查询时，PostgreSQL 会自动处理 TOAST 表的读取和写入，用户无需显式管理。
查询 TOAST 表：
要查看某个表的 TOAST 表，您可以使用以下 SQL 查询：

SELECT relname FROM pg_class WHERE relkind = 't';

结论：
TOAST 机制使 PostgreSQL 能够高效地处理大字段，优化存储和性能，确保在处理大型数据时不会影响整体数据库性能。

pg_stat_database视图

pg_stat_database视图会为集群中的每个数据库包含一行数据，此外还会包含一行用于共享对象，以展示数据库级别的统计信息。简单来说，这个视图用于存储和显示数据库性能和状态的相关信息，帮助数据库管理员监控和分析数据库的运行状况。

这段内容描述的是 PostgreSQL 数据库中的 pg_stat_database 视图。这个视图提供了关于数据库性能和状态的统计信息。下面是对每个列的解释：

datid: 数据库的OID（对象标识符），如果是共享关系的对象，则为0。
datname: 数据库的名称，如果是共享对象，则为NULL。
numbackends: 当前连接到数据库的后端（backend）数量，如果是共享对象，则为NULL。这是该视图中唯一反映当前状态的列；所有其他列返回自上次重置以来累积的值。
xact_commit: 在此数据库中已提交的事务数量。
xact_rollback: 在此数据库中已回滚的事务数量。
blks_read: 在此数据库中读取的磁盘块数量。
blks_hit: 磁盘块已在缓冲区缓存中找到的次数，因此不需要读取（这只包括PostgreSQL缓冲区缓存中的命中，不包括操作系统的文件系统缓存）。
tup_returned: 通过顺序扫描获取的行数和通过索引扫描返回的索引条目数。
tup_fetched: 通过索引扫描获取的行数。
tup_inserted: 通过查询在此数据库中插入的行数。
tup_updated: 通过查询在此数据库中更新的行数。
tup_deleted: 通过查询在此数据库中删除的行数。
conflicts: 由于与恢复冲突而取消的查询数量。（冲突仅在备用服务器上发生；详情请参阅 pg_stat_database_conflicts。）
temp_files: 通过查询在此数据库中创建的临时文件数量。所有临时文件都被计数，无论创建临时文件的原因（例如，排序或哈希），以及无论 log_temp_files 设置如何。
temp_bytes: 通过查询写入临时文件的总数据量。所有临时文件都被计数，无论创建临时文件的原因，以及无论 log_temp_files 设置如何。
deadlocks: 在此数据库中检测到的死锁数量。
checksum_failures: 在此数据库（或共享对象）中检测到的数据页面校验和失败数量，如果未启用数据校验和，则为NULL。
checksum_last_failure: 检测到最后一次数据页面校验和失败的时间，如果未启用数据校验和，则为NULL。
blk_read_time: 后端在此数据库中读取数据文件块所花费的时间，以毫秒为单位（如果启用了 track_io_timing，则为零）。
blk_write_time: 后端在此数据库中写入数据文件块所花费的时间，以毫秒为单位（如果启用了 track_io_timing，则为零）。
session_time: 数据库会话在此数据库中花费的时间，以毫秒为单位（注意，只有当会话状态发生变化时，统计信息才会更新，因此如果会话长时间空闲，这段时间不会被包括在内）。
active_time: 在此数据库中执行SQL语句所花费的时间，以毫秒为单位（这对应于 pg_stat_activity 中的 active 和 fastpath function call 状态）。
idle_in_transaction_time: 在此数据库中处于事务中时空闲所花费的时间，以毫秒为单位（这对应于 pg_stat_activity 中的 idle in transaction 和 idle in transaction (aborted) 状态）。
sessions: 建立到此数据库的会话总数。
sessions_abandoned: 由于与客户端的连接丢失而终止的数据库会话数量。
sessions_fatal: 由于致命错误而终止的数据库会话数量。
sessions_killed: 由于操作员干预而终止的数据库会话数量。
stats_reset: 这些统计信息上次重置的时间。

share_buffer

shared_buffers 是 PostgreSQL 的一个配置参数，用于控制分配给共享内存中缓存数据的内存量。这个区域用于加速数据访问，通过存储频繁访问的数据块，减少对磁盘的读取。

关键点：

目的：存储多个数据库会话共享的数据，提升性能，减少磁盘 I/O。
默认值：通常默认值较小（例如 128MB），对于较大的数据库或工作负载，需要增大此值。
建议：通常建议将 shared_buffers 设置为系统内存的 25%左右，但这可能因工作负载和系统配置而异。
修改设置：
- 编辑 postgresql.conf 文件：
```
shared_buffers = 2GB  # 示例值
```
- 重启 PostgreSQL 使更改生效。
监控：可以通过 PostgreSQL 的统计视图（如 pg_stat_bgwriter 和 pg_stat_database）监控 shared_buffers 的效果。

示例配置

编辑 postgresql.conf：
```
shared_buffers = 2GB
```
重启 PostgreSQL 服务：
```
sudo systemctl restart postgresql
```

总结

合理设置 shared_buffers 对于提高 PostgreSQL 性能非常重要，特别是对于读取较多的应用程序。在进行更改后，务必监控性能表现。

pg_stat_database中如何分析share_buffer的表现

在 PostgreSQL 中，可以通过 pg_stat_database 视图分析 shared_buffers 的表现。以下是一些关键指标和分析方法：

关键指标

blks_hit: 这个计数器表示从 shared_buffers 中读取的块的数量。高值意味着数据多数都在缓存中。
blks_read: 这个计数器表示从磁盘读取的块的数量。低值显示缓存性能好，数据很少需要从磁盘加载。

分析方法

命中率计算:
- 命中率是 blks_hit 与 (blks_hit + blks_read) 的比率：
  $blks_hit blks_hit + blks_read \text{命中率} = \frac{\text{blks\_hit}}{\text{blks\_hit} + \text{blks\_read}}$
- 较高的命中率（如 95% 以上）通常表明 shared_buffers 配置合理，缓存效果良好。
监测时间段:
- 定期监控这些指标的变化，比如每分钟或每小时记录一次，观察趋势。
使用 pg_stat_statements:
- 可以结合 pg_stat_statements 来分析特定查询的命中情况，查看哪些查询频繁从磁盘读取数据。

示例查询

你可以运行以下 SQL 查询来获取数据库的 blks_hit 和 blks_read 数据：

SELECT datname,
       blks_hit,
       blks_read,
       (blks_hit * 100.0 / (blks_hit + blks_read)) AS hit_ratio
FROM pg_stat_database
WHERE datname = 'your_database_name';

总结

通过分析 pg_stat_database 中的 blks_hit 和 blks_read，可以评估 shared_buffers 的表现。如果命中率较低，可以考虑增加 shared_buffers 的值或优化查询。

pg_index

SELECT
    c.relname AS index_name,
    i.indisclustered AS is_clustered
FROM
    pg_index i
JOIN
    pg_class c ON c.oid = i.indexrelid
WHERE
    i.indrelid = 'your_table_name'::regclass;

indisclustered 列的值为 true 表明该索引是聚簇索引。

pg_index表包含了索引的部分信息，而其他大部分信息则存储在pg_class表中。下面是对pg_index表中各列的解释：

indexrelid: 这是当前索引在pg_class中的OID（对象标识符）。
indrelid: 这是索引所属的表在pg_class中的OID。
indnatts: 索引中总共有多少列，包括键列和包含的属性列。
indnkeyatts: 索引中键列的数量，不包括任何包含的列。
indisunique: 如果为真，则表示这是一个唯一索引。
indnullsnotdistinct: 仅用于唯一索引。如果为假，则索引会将NULL值视为不同的（默认PostgreSQL行为）。如果为真，则索引会将NULL值视为相等。
indisprimary: 如果为真，则表示这个索引是表的主键。
indisexclusion: 如果为真，则表示这个索引支持一个排除约束。
indimmediate: 如果为真，则在插入时立即执行唯一性检查。
indisclustered: 如果为真，则表示表最后一次是在这个索引上进行聚集的。
indisvalid: 如果为真，则表示索引当前对查询有效。如果为假，则表示索引可能不完整。
indcheckxmin: 如果为真，则查询不能使用这个索引，直到这个pg_index行的xmin值低于它们的TransactionXmin事件视界。
indisready: 如果为真，则表示索引当前准备好接受插入操作。
indislive: 如果为假，则表示索引正在被删除，应该被忽略。
indisreplident: 如果为真，则表示这个索引被选为“副本身份”。
indkey: 这是一个数组，表示哪些表列被这个索引索引。例如，值1 3意味着第一列和第三列构成了索引项。
indcollation: 对于索引键的每一列，包含用于索引的排序规则的OID，或者如果列不是可排序的数据类型，则为零。
indclass: 对于索引键的每一列，包含要使用的运算符类的OID。
indoption: 这是一个数组，存储每列的标志位。
indexprs: 非简单列引用的索引属性的表达式树。
indpred: 部分索引谓词的表达式树。如果不是部分索引，则为NULL。

这些列提供了索引的详细信息，包括它如何构建、它的特性（如是否唯一、是否为主键等），以及它如何与表的其他部分交互。

pg_class

pg_class目录：在PostgreSQL数据库中，pg_class是一个系统目录表，它用来描述数据库中的各种对象。
描述的对象：pg_class目录描述的对象包括但不限于：
- 表：数据库中存储数据的表格。
- 索引：帮助提高查询效率的数据结构，虽然索引本身也在pg_index目录中有更详细的描述。
- 序列：用于生成连续的数值，虽然序列也在pg_sequence目录中有更详细的描述。
- 视图：基于SQL查询的虚拟表。
- 物化视图：存储查询结果的物理表，可以提高复杂查询的效率。
- 复合类型：由多个字段组成的用户自定义数据类型。
- TOAST表：用于存储大型字段值的表。
relkind：这是一个列，用于指示pg_class中记录的对象的类型。
“relations”：在这段描述中，当提到所有这些类型的对象时，统称为“relations”。
列的适用性：并不是pg_class目录表中的所有列都适用于所有类型的“relations”。这意味着某些列可能只对特定类型的数据库对象有意义。

简而言之，pg_class目录表是用来存储和描述数据库中各种类似于表的对象的元数据，包括表、索引、序列、视图等，而relkind列用于标识这些对象的具体类型。

pg_class是PostgreSQL中用于存储关系（如表、索引、视图等）的元数据的系统表。以下是对其中一些关键列的解释：

oid: 每行的唯一标识符。
relname: 关系的名称，例如表、索引或视图的名称。
relnamespace: 包含此关系的命名空间的OID。
reltype: 如果是表，这是对应于该表行类型的数据类型的OID；对于索引、序列和toast表，这个值为零。
reloftype: 对于类型化表，这是基础复合类型的OID；对于其他关系，这个值为零。
relowner: 关系的所有者。
relam: 如果是表或索引，这是使用的访问方法（如堆、B树、哈希等）；否则为零。
relfilenode: 关系在磁盘上的文件名；零表示这是一个“映射”关系，其磁盘文件名由低级状态决定。
reltablespace: 存储此关系的表空间。如果为零，则意味着使用数据库的默认表空间。
relpages: 表在磁盘上的表示大小（以BLCKSZ大小的页为单位）。这只是估计值，由VACUUM、ANALYZE和一些DDL命令（如CREATE INDEX）更新。
reltuples: 表中活动行的数量。这也是估计值，更新方式同上。如果表从未被VACUUM或ANALYZE处理过，这个值是-1，表示行数未知。
relallvisible: 在表的可见性映射中被标记为全部可见的页数。这也是估计值，更新方式同上。
reltoastrelid: 与此表相关联的TOAST表的OID，如果没有则为零。TOAST表用于存储大型属性。
relhasindex: 如果这是一个表，并且它有（或最近有）任何索引，则为真。
relisshared: 如果这个表在集群的所有数据库中共享，则为真。
relpersistence: 表示表/序列的持久性，p=永久，u=未记录，t=临时。
relkind: 表示关系的类型，如普通表、索引、序列、视图等。
relnatts: 关系中的用户列数量（不包括系统列）。
relchecks: 表上的CHECK约束数量。
relhasrules: 如果表有（或曾经有）规则，则为真。
relhastriggers: 如果表有（或曾经有）触发器，则为真。
relhassubclass: 如果表或索引有（或曾经有）任何继承的子类或分区，则为真。
relrowsecurity: 如果表启用了行级安全，则为真。
relforcerowsecurity: 如果启用了行级安全（当启用时），也将适用于表的所有者，则为真。
relispopulated: 如果关系是填充的（除了某些物化视图外，所有关系都是如此）。
relreplident: 用于形成行的“复制身份”的列。
relispartition: 如果表或索引是分区，则为真。
relrewrite: 在需要表重写的DDL操作期间正在写入的新关系，包含原始关系的OID；否则为零。
relfrozenxid: 所有在此之前的事务ID都已被替换为此表中的永久（“冻结”）事务ID。这用于跟踪表是否需要被VACUUM处理以防止事务ID环绕或允许pg_xact收缩。
relminmxid: 所有在此之前的多事务ID都已被替换为此表中的事务ID。这用于跟踪表是否需要被VACUUM处理以防止多事务ID环绕或允许pg_multixact收缩。
relacl: 访问权限。
reloptions: 访问方法特定的选项，格式为“关键字=值”的字符串。
relpartbound: 如果表是分区的，这是分区界限的内部表示。

pg_stat_activity

“pg_stat_activity” 视图会为每个服务器进程提供一行数据，显示与该进程当前活动相关的信息。
这段内容描述的是数据库系统中后端（backend）的一些属性和状态信息。下面是对这些属性的简要解释：

datid: 数据库的OID（对象标识符）。
datname: 后端连接到的数据库名称。
pid: 后端的进程ID。
leader_pid: 如果这个进程是并行查询工作进程，则为并行组领导者的进程ID；如果是并行应用工作进程，则为领导者应用工作进程的进程ID。如果为NULL，则表示该进程是并行组领导者或领导者应用工作进程，或者不参与任何并行操作。
usesysid: 登录到这个后端的用户的OID。
usename: 登录到这个后端的用户名。
application_name: 连接到这个后端的应用程序名称。
client_addr: 连接到这个后端的客户端的IP地址。如果这个字段为空，表示客户端通过服务器机器上的Unix套接字连接，或者这是一个内部进程，如自动清理（autovacuum）。
client_hostname: 通过client_addr的反向DNS查找报告的已连接客户端的主机名。这个字段仅在IP连接时非空，并且仅当启用了log_hostname时。
client_port: 客户端用于与这个后端通信的TCP端口号，如果使用Unix套接字则为-1。如果这个字段为空，表示这是一个内部服务器进程。
backend_start: 这个进程启动的时间。对于客户端后端，这是客户端连接到服务器的时间。
xact_start: 这个进程当前事务开始的时间，如果没有事务处于活动状态，则为null。如果当前查询是其事务的第一个，则此列等于query_start列。
query_start: 当前活动查询开始的时间，或者如果状态不是活动，则为上一个查询开始的时间。
state_change: 状态最后一次更改的时间。
wait_event_type: 如果后端正在等待任何事件，则为该事件的类型；否则为NULL。
wait_event: 如果后端当前正在等待，则为等待事件的名称；否则为NULL。
state: 这个后端的当前总体状态。可能的值包括：
- active: 当前有正在执行的查询。
- idle: 后端正在等待新的客户端命令,空闲状态。
- idle in transaction: 后端处于事务中，但当前没有执行查询。
- idle in transaction (aborted): 这个状态与idle in transaction相似，但事务中的一个语句导致了错误。
- fastpath function call: 后端正在执行快速路径函数。
- disabled: 如果在这个后端禁用了track_activities，则报告此状态。
backend_xid: 这个后端的顶级事务标识符，如果有的话。
backend_xmin: 当前后端的xmin范围。
query_id: 这个后端最近一次查询的标识符。如果状态是活动，则此字段显示当前正在执行的查询的标识符。在所有其他状态中，它显示最后执行的查询的标识符。默认情况下，不计算查询标识符，所以除非启用了compute_query_id参数或配置了计算查询标识符的第三方模块，否则此字段将为null。
query: 这个后端最近一次查询的文本。如果状态是活动，则此字段显示当前正在执行的查询。在所有其他状态中，它显示最后执行的查询。默认情况下，查询文本在1024字节处被截断；这个值可以通过track_activity_query_size参数更改。
backend_type: 当前后端的类型。可能的类型包括自动清理启动器、自动清理工作器、逻辑复制启动器、逻辑复制工作器、并行工作器、后台写入器、客户端后端、检查点器、归档器、独立后端、启动、walreceiver、walsender和walwriter。此外，通过扩展注册的后台工作器可能有其他类型。