一月 28, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中监控大表状态。
目录
哪些是大表？
PostgreSQL 提供了一个专用函数pg_relation_size，用于计算特定表或索引使用的实际磁盘空间。我们只需要提供表名。除了主数据文件之外，还有其他文件（称为分支）可以通过指定第二个可选参数来测算。其中包括可见性映射表（VM）、可用空间映射表（FSM）和无日志表的初始化分支。
下面的基本查询可告诉我们哪 10 个表是最大的：
WITH tables AS (
SELECT current_database() AS dbname, n.nspname AS schema, c.relname,
pg_relation_size(c.oid) AS size
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid AND c.relkind = 'r'
ORDER BY size DESC
LIMIT 10
)
SELECT dbname, schema, relname,
pg_size_pretty(size) AS relsize
FROM tables
WHERE size > pg_size_bytes('1 GB');
这些表按大小降序显示，最多显示 10 个。在本例中，我们查看的是当前数据库中的所有表。
表上累计的统计信息
一个简单的select * from pg_stat_user_tables，可显示用户表的访问统计信息，当前数据库中的每个表都有一行，以及关键列：
- 顺序扫描计数（seq_scan）：
pg_stat_all_tables视图中的seq_scan列，表示对特定表执行过的顺序扫描的总数。大量的顺序扫描可能表明缺少适当的索引，或者没有充分利用索引。 - 索引扫描计数（idx_scan）：在此表上执行过的索引扫描次数。
- 插入行数（n_tup_ins）：已插入的行数。
- 更新行数（n_tup_upd）：已更新的行数（包括 HOT 更新的行）。
- 删除行数（n_tup_ins）：已删除的行数。
- HOT 更新计数（n_tup_hot_upd）：我们可以使用此列数据，对更新频繁的表计算 HOT 更新比率。此视图中的列 n_tup_upd 和 n_tup_hot_upd，表示每个表的普通更新和 HOT 更新的总数。那些具有高 HOT 更新率的表属于“表现好”的表；我们应该注意更改计数高但 HOT 更新速率低或为零的表。对 fillfactor 设置的更改是应对这些表的一般方案，它允许在添加其他行和表扩展时保留可用空间。保留空间的存在可确保在页面内更新行，并大大增加发生 HOT 更新的可能性。
- 碎片行（n_dead_tup）：它显示了数据已删除的死行的大致数量，当我们使用 DELETE 或 UPDATE 等更改操作时，这些死行会被我们的数据源重用。
要在 PostgreSQL 中查找旧表，我们可以运行以下查询：
SELECT
schemaname, relname,
seq_scan, idx_Scan,
last_seq_scan, last_idx_scan,
pg_size_pretty(pg_total_relation_size(relid)) AS table_size
FROM
pg_stat_user_tables
ORDER BY last_seq_scan ASC, last_idx_scan ASC
LIMIT 10;
此查询显示按 上次顺序扫描时间戳（第 5 列） 和 上次索引扫描时间戳（第 6 列） 排序的所有表的列表。order by 子句也可以替换为 seq_scan 和 idx_scan 列，以获取很少使用的表的列表。
大表上的统计信息
您可以使用以下查询，来显示有关对大表的访问统计信息：
WITH large_tables AS (
SELECT c.oid AS relid,
pg_relation_size(c.oid) AS size
FROM pg_class c
WHERE c.relkind = 'r'
ORDER BY size DESC
LIMIT 10
)
SELECT current_database() AS dbname,
stat.schemaname, stat.relname,
pg_size_pretty(large_tables.size) AS relsize,
stat.seq_scan, stat.seq_tup_read, stat.idx_scan, stat.idx_tup_fetch,
stat.n_tup_ins, stat.n_tup_upd, stat.n_tup_del, stat.n_tup_hot_upd,
stat.n_live_tup, stat.n_dead_tup
FROM large_tables
JOIN pg_stat_user_tables AS stat
ON large_tables.relid = stat.relid
WHERE large_tables.size > pg_size_bytes('1 GB');
如果您有一个可以监控 SQL 查询返回的值的系统，那么这很适合它。