PostgreSQL的视图pg_stat_database

PostgreSQL的视图pg_stat_database

pg_stat_database 是 PostgreSQL 中的一个系统视图，用于提供与数据库相关的统计信息。这个视图包含了多个有用的指标，可以帮助数据库管理员了解数据库的使用情况和性能。

以下是 pg_stat_database 视图的主要列和其含义：

列名	类型	描述
datid	OID	数据库的 OID。
datname	name	数据库的名字。
numbackends	integer	当前连接到这个数据库的会话数。
xact_commit	bigint	事务提交的次数。
xact_rollback	bigint	事务回滚的次数。
blks_read	bigint	从磁盘读取的数据块数。
blks_hit	bigint	缓存命中的数据块数。
tup_returned	bigint	通过扫描返回的行数。
tup_fetched	bigint	通过索引查找到的行数。
tup_inserted	bigint	插入的行数。
tup_updated	bigint	更新的行数。
tup_deleted	bigint	删除的行数。
conflicts	bigint	由于产生冲突然后被删除的事务数（仅在主备架构中有用）。
temp_files	bigint	创建的临时文件数。
temp_bytes	bigint	创建的临时文件占用的字节数。
deadlocks	bigint	检测到的死锁数。
blk_read_time	double	数据块读取的总时间，单位是毫秒。
blk_write_time	double	数据块写入的总时间，单位是毫秒。
stats_reset	timestamp	最近一次重置这些统计数据的时间。

示例查询

查看所有数据库的基本统计信息

SELECT 
    datname, 
    numbackends, 
    xact_commit, 
    xact_rollback, 
    blks_read, 
    blks_hit 
FROM 
    pg_stat_database;

查看某一个具体数据库的详细统计信息

SELECT * FROM pg_stat_database WHERE datname = 'your_database_name';

计算缓存命中率

SELECT 
    datname,
    blks_hit, 
    blks_read, 
    100 * blks_hit / (blks_hit + blks_read) AS hit_ratio 
FROM 
    pg_stat_database;

查看数据库中所有事务的提交和回滚次数

SELECT 
    datname, 
    xact_commit, 
    xact_rollback 
FROM 
    pg_stat_database;

重置统计信息

你可以使用 pg_stat_reset() 或 pg_stat_reset_single_database() 函数来重置统计信息，例如：

-- 重置所有数据库统计信息
SELECT pg_stat_reset();

-- 重置单个数据库的统计信息
SELECT pg_stat_reset_single_database(oid);

-- oid 可以通过以下命令取得
SELECT oid FROM pg_database WHERE datname = 'your_database_name';

通过这些方法，你可以获取数据库的广泛统计信息，帮助进行性能分析、调优和监控。