KingbaseES空间爆满预警？用这几个SQL函数精准定位‘磁盘刺客’
KingbaseES空间爆满预警？用这几个SQL函数精准定位‘磁盘刺客’
当你收到KingbaseES数据库的磁盘空间告警邮件时，第一反应可能是"哪个表占用了这么多空间？"但真正的问题往往比想象中复杂——可能是未清理的日志表、膨胀的索引，甚至是长期积累的临时文件。本文将带你像侦探破案一样，从宏观到微观层层剖析，用SQL函数精准定位那些吞噬磁盘空间的"隐形刺客"。
1. 全局扫描：数据库级别的空间诊断
接到空间告警后，首先要确定问题是否真的出在数据库本身。KingbaseES提供了几个关键函数帮助我们快速评估整体情况：
-- 查看所有数据库大小（按降序排列） SELECT datname AS 数据库名, pg_size_pretty(pg_database_size(datname)) AS 占用空间 FROM pg_database ORDER BY pg_database_size(datname) DESC;这个查询会返回类似下面的结果：
| 数据库名 | 占用空间 |
|---|---|
| prod_db | 48 GB |
| test_db | 12 GB |
| temp_db | 2 GB |
关键观察点：
- 如果单个数据库异常膨胀，可以集中排查该库
- 注意
template0和template1等系统数据库的大小变化，异常增长可能预示配置问题
提示：
pg_database_size()返回的是字节数，使用pg_size_pretty()会自动转换为易读的MB/GB单位
2. 深度剖析：表级别的空间占用分析
锁定目标数据库后，下一步是找出具体的"空间大户"。以下查询可以显示指定模式下所有表的大小排名：
-- 查看指定模式下所有表的大小（包含索引） SELECT schemaname AS 模式名, relname AS 表名, pg_size_pretty(pg_total_relation_size(relid)) AS 总大小, pg_size_pretty(pg_relation_size(relid)) AS 表数据大小, pg_size_pretty(pg_indexes_size(relid)) AS 索引大小, n_live_tup AS 行数 FROM pg_stat_user_tables WHERE schemaname = 'public' ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;典型输出示例：
| 模式名 | 表名 | 总大小 | 表数据大小 | 索引大小 | 行数 |
|---|---|---|---|---|---|
| public | audit_log | 28 GB | 22 GB | 6 GB | 450万 |
| public | user_data | 15 GB | 10 GB | 5 GB | 120万 |
| public | temp_data | 8 GB | 8 GB | 0 MB | 0 |
分析维度：
- 表数据 vs 索引比例：1:1通常是健康状态，索引过大可能需优化
- 行数与空间占比：百万行占28GB？可能包含大字段或未压缩数据
- 零行临时表：可能是遗留的临时表未清理
3. 隐藏杀手：特殊对象的空间回收
有些"空间刺客"藏得更深，需要特殊手段才能发现：
3.1 膨胀的TOAST表
大字段（如TEXT、JSONB）会被存储在TOAST表中，可能悄悄占用大量空间：
-- 检查TOAST表大小 SELECT relname AS 主表名, pg_size_pretty(pg_relation_size(reltoastrelid)) AS TOAST大小 FROM pg_class WHERE relkind = 'r' AND pg_relation_size(reltoastrelid) > 0 ORDER BY pg_relation_size(reltoastrelid) DESC LIMIT 5;3.2 未清理的临时文件
长时间运行的查询可能遗留临时文件：
-- 查看当前临时文件使用情况 SELECT datname AS 数据库名, temp_files AS 临时文件数, pg_size_pretty(temp_bytes) AS 临时空间 FROM pg_stat_database;3.3 索引膨胀问题
过度索引或未优化的索引可能成为空间黑洞：
-- 查找重复/冗余索引 SELECT indrelid::regclass AS 表名, array_agg(indexrelid::regclass) AS 重复索引 FROM pg_index GROUP BY indrelid, indkey HAVING COUNT(*) > 1;4. 实战解决方案：空间回收与优化
定位问题后，下面是几种有效的空间回收策略：
4.1 针对大表的处理方案
分区表策略：
-- 将大表按时间分区 CREATE TABLE audit_log ( id BIGSERIAL, event_time TIMESTAMP, details JSONB ) PARTITION BY RANGE (event_time); -- 创建每月分区 CREATE TABLE audit_log_202301 PARTITION OF audit_log FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');数据归档流程：
- 创建归档表结构
- 迁移旧数据：
INSERT INTO archive_table SELECT * FROM main_table WHERE created_at < '2022-01-01' - 删除原表数据：
DELETE FROM main_table WHERE created_at < '2022-01-01' - 执行
VACUUM FULL main_table
4.2 索引优化技巧
重建膨胀索引：
-- 单个索引重建 REINDEX INDEX CONCURRENTLY idx_large_table_column; -- 整表索引重建 REINDEX TABLE CONCURRENTLY large_table;索引大小监控表：
CREATE TABLE index_size_history AS SELECT now() AS check_time, schemaname, indexname, pg_size_pretty(pg_relation_size(indexname::regclass)) AS size FROM pg_indexes;4.3 自动化维护方案
创建定期维护脚本：
#!/bin/bash # 每周维护脚本 psql -U postgres -d mydb <<EOF VACUUM ANALYZE; REINDEX TABLE CONCURRENTLY large_table; SELECT pg_rotate_logfile(); EOF设置监控预警：
-- 创建空间使用预警视图 CREATE VIEW space_alert AS SELECT schemaname, relname, pg_size_pretty(pg_total_relation_size(relid)) AS size, CASE WHEN pg_total_relation_size(relid) > 1073741824 THEN 'CRITICAL' WHEN pg_total_relation_size(relid) > 536870912 THEN 'WARNING' ELSE 'NORMAL' END AS status FROM pg_stat_user_tables ORDER BY pg_total_relation_size(relid) DESC;在实际运维中，我们发现最常出现空间问题的往往是三类表：没有归档策略的日志表、包含大JSON字段的业务表，以及缺乏维护的历史数据表。定期执行pg_repack可以在不影响业务的情况下重组表结构，而设置自动化的autovacuum参数调整能预防空间膨胀问题。