别再手动算了！KingbaseES数据库和表大小查询的3个实用SQL脚本（附单位换算）
KingbaseES数据库空间管理实战：3个高效查询脚本与自动化技巧
在数据库运维工作中，空间管理是DBA和运维工程师的日常必修课。KingbaseES作为国产数据库的优秀代表，其空间使用情况的精准监控直接影响着系统性能和稳定性。本文将分享三个经过实战检验的SQL脚本，帮你彻底告别手动计算和记忆函数的烦恼，实现数据库空间管理的自动化与可视化。
1. 空间查询基础：理解KingbaseES的存储计量方式
KingbaseES提供了多种内置函数来查询数据库对象的大小，但直接使用原始输出往往不够直观。我们先从基础开始，了解这些函数的工作原理和返回值特性。
sys_database_size()和sys_relation_size()是KingbaseES中最常用的空间查询函数，它们返回的是以字节为单位的整数值。对于现代数据库系统，这个数值通常会非常大，直接阅读很不方便。例如：
SELECT sys_relation_size('large_table'); -- 输出可能是：2147483648KingbaseES提供了sys_size_pretty()函数将这些字节值转换为更易读的格式：
SELECT sys_size_pretty(2147483648); -- 输出：2048 MB单位换算的常见误区：
- 1 KB = 1024 Bytes
- 1 MB = 1024 KB
- 1 GB = 1024 MB
- 1 TB = 1024 GB
注意：存储厂商通常使用十进制单位（1KB=1000Bytes），而数据库系统普遍使用二进制单位，这可能导致容量规划时的细微差异。
2. 开箱即用的三合一脚本集
2.1 数据库级空间概览脚本
这个增强版脚本不仅显示所有数据库的大小，还加入了使用百分比和趋势分析：
SELECT d.datname AS 数据库名, sys_size_pretty(sys_database_size(d.datname)) AS 当前大小, sys_size_pretty(pg_database_size_limit(d.datname)) AS 大小限制, CASE WHEN pg_database_size_limit(d.datname) > 0 THEN ROUND(100 * sys_database_size(d.datname)::numeric / pg_database_size_limit(d.datname), 2) ELSE NULL END AS 使用百分比, sys_size_pretty(sys_database_size(d.datname) - stat.last_size) AS 近期增长, stat.last_check AS 上次检查时间 FROM sys_database d LEFT JOIN database_size_history stat ON d.datname = stat.datname ORDER BY sys_database_size(d.datname) DESC;脚本优势：
- 直观显示数据库大小限制（如果设置）
- 自动计算使用百分比，便于容量预警
- 通过历史记录表对比显示增长量
- 结果按大小降序排列，突出重点数据库
2.2 表级空间分析脚本（含索引）
这个高级查询不仅显示表大小，还包含了关联的索引和TOAST数据：
SELECT schemaname AS 模式名, relname AS 表名, sys_size_pretty(sys_total_relation_size(relid)) AS 总大小, sys_size_pretty(sys_relation_size(relid)) AS 表数据大小, sys_size_pretty(sys_total_relation_size(relid) - sys_relation_size(relid)) AS 索引和TOAST大小, n_live_tup AS 行数, ROUND((sys_relation_size(relid)::numeric / NULLIF(sys_total_relation_size(relid), 0)) * 100, 2) AS 数据占比百分比 FROM sys_stat_user_tables WHERE schemaname NOT LIKE 'pg_%' AND schemaname != 'information_schema' ORDER BY sys_total_relation_size(relid) DESC LIMIT 50;输出解读：
- 总大小：表数据+索引+TOAST的总和
- 表数据大小：仅表数据占用的空间
- 索引和TOAST大小：差值计算得出
- 数据占比百分比：表数据在总大小中的占比，反映索引开销
2.3 自动化空间监控脚本
这个脚本专为定期监控设计，可直接集成到监控系统中：
WITH db_stats AS ( SELECT d.datname, sys_database_size(d.datname) AS size_bytes, sys_database_size(d.datname) - COALESCE(h.last_size, 0) AS growth_bytes, EXTRACT(EPOCH FROM (NOW() - h.last_check)) / 86400 AS days_since_last_check FROM sys_database d LEFT JOIN database_size_history h ON d.datname = h.datname ) INSERT INTO database_size_history (datname, last_size, last_check) SELECT datname, size_bytes, NOW() FROM db_stats ON CONFLICT (datname) DO UPDATE SET last_size = EXCLUDED.last_size, last_check = EXCLUDED.last_check; -- 生成告警信息 SELECT datname AS 数据库名, sys_size_pretty(size_bytes) AS 当前大小, sys_size_pretty(growth_bytes) AS 近期增长量, ROUND(growth_bytes / NULLIF(days_since_last_check * 86400, 0)) AS 每秒增长字节数, CASE WHEN growth_bytes > 1024^3 AND days_since_last_check < 7 THEN '警告: 快速增长' WHEN size_bytes > 0.9 * pg_database_size_limit(datname) THEN '紧急: 接近容量上限' ELSE '正常' END AS 状态 FROM db_stats WHERE growth_bytes > 0 OR size_bytes > 0.8 * pg_database_size_limit(datname);功能特点：
- 自动维护历史记录表
- 计算每日/每周增长趋势
- 智能预警机制
- 可直接接入Prometheus等监控系统
3. 高级技巧：结果格式化与自动化集成
3.1 自定义格式化输出
KingbaseES允许通过自定义函数实现更灵活的格式化：
CREATE OR REPLACE FUNCTION format_storage_size(bytes bigint) RETURNS text AS $$ DECLARE size_text text; BEGIN IF bytes < 1024 THEN size_text := bytes || ' Bytes'; ELSIF bytes < 1048576 THEN size_text := ROUND(bytes::numeric / 1024, 2) || ' KB'; ELSIF bytes < 1073741824 THEN size_text := ROUND(bytes::numeric / 1048576, 2) || ' MB'; ELSIF bytes < 1099511627776 THEN size_text := ROUND(bytes::numeric / 1073741824, 2) || ' GB'; ELSE size_text := ROUND(bytes::numeric / 1099511627776, 2) || ' TB'; END IF; RETURN size_text; END; $$ LANGUAGE plpgsql;使用示例：
SELECT relname, format_storage_size(sys_total_relation_size(relid)) AS 格式化大小 FROM sys_stat_user_tables;3.2 自动化报表生成
结合KingbaseES的COPY命令，可以轻松将查询结果导出为CSV报表：
COPY ( SELECT schemaname, relname, sys_size_pretty(sys_total_relation_size(relid)) AS size, n_live_tup AS rows FROM sys_stat_user_tables ORDER BY sys_total_relation_size(relid) DESC LIMIT 100 ) TO '/var/lib/kingbase/reports/top_tables.csv' WITH CSV HEADER;对于定期报表，可以设置cron作业自动执行：
#!/bin/bash DATE=$(date +%Y%m%d) ksql -U monitor -d mydb -c "COPY (SELECT ...) TO '/var/lib/kingbase/reports/space_report_${DATE}.csv' WITH CSV HEADER"3.3 可视化集成方案
将查询结果与Grafana等可视化工具集成：
- 首先创建一个视图简化查询：
CREATE VIEW database_size_metrics AS SELECT d.datname, sys_database_size(d.datname) AS size_bytes, EXTRACT(EPOCH FROM NOW()) AS timestamp FROM sys_database d;- 在Grafana中使用PostgreSQL数据源配置面板，使用类似SQL查询：
SELECT timestamp AS "time", datname AS "database", size_bytes FROM database_size_metrics WHERE $__timeFilter(timestamp) ORDER BY timestamp;4. 性能优化与疑难解答
4.1 查询性能对比
不同空间查询方法的性能特点：
| 查询方法 | 执行速度 | 精度 | 锁级别 | 适用场景 |
|---|---|---|---|---|
sys_relation_size() | 快 | 高 | 低 | 实时监控 |
sys_total_relation_size() | 中 | 高 | 中 | 全面分析 |
| 统计信息表 | 最快 | 低 | 无 | 概览和大规模筛查 |
提示：对于大型数据库，频繁执行
sys_total_relation_size()可能影响性能，建议在非高峰期执行。
4.2 常见问题排查
问题1：查询结果与实际磁盘使用不符
可能原因：
- 未统计TOAST表空间
- 索引占用空间未计算
- 数据库有大量空闲空间(map)
解决方案：
-- 完整表空间查询 SELECT relname, sys_size_pretty(sys_relation_size(relid)) AS 主表, sys_size_pretty(sys_toast_relation_size(relid)) AS TOAST, sys_size_pretty(sys_indexes_relation_size(relid)) AS 索引, sys_size_pretty(sys_total_relation_size(relid)) AS 总计 FROM sys_stat_user_tables;问题2：查询速度慢
优化方案：
- 在从库上执行空间查询
- 使用统计信息表估算
- 限制查询范围
-- 快速估算方案 SELECT schemaname, relname, sys_size_pretty( (pg_stat_get_live_tuples(relid) * avg_width + 8192)::bigint ) AS 估算大小 FROM sys_stat_user_tables WHERE schemaname = 'public';问题3：自动增长监控不准确
解决方案：
- 确保历史记录表定期更新
- 考虑WAL日志对空间的影响
- 检查自动清理(vacuum)状态
-- 检查自动清理状态 SELECT relname, last_vacuum, last_autovacuum, vacuum_count, autovacuum_count FROM sys_stat_user_tables WHERE n_dead_tup > 1000;