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;

这个查询会返回类似下面的结果：

关键观察点：

• 如果单个数据库异常膨胀，可以集中排查该库
• 注意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;

典型输出示例：

分析维度：

• 表数据 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');

数据归档流程：

1. 创建归档表结构
2. 迁移旧数据：INSERT INTO archive_table SELECT * FROM main_table WHERE created_at < '2022-01-01'
3. 删除原表数据：DELETE FROM main_table WHERE created_at < '2022-01-01'
4. 执行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参数调整能预防空间膨胀问题。