## 1. 为什么需要关注PostgreSQL空间占用？ 在数据库运维过程中，空间管理是DBA和开发者的必修课。当你的PostgreSQL实例运行一段时间后，会发现磁盘空间消耗越来越快。上周还能正常运行的报表系统，这周突然因为磁盘不足而崩溃；开发环境明明数据量不大，但数据库目录却占用了上百GB空间——这些场景我都亲身经历过。 PostgreSQL不像某些商业数据库提供直观的图形化空间分析工具，但通过系统视图和函数，我们能获取更精确的空间数据。掌握这些方法可以帮你： - 及时发现异常增长的表或索引 - 规划合理的存储扩容方案 - 优化VACUUM策略 - 评估分区表的分区键合理性 > 提示：生产环境建议每周定期检查空间使用情况，特别是频繁更新的表。我曾经遇到过一个日志表在3天内膨胀了200GB，就是因为没有配置合理的自动清理策略。 ## 2. 数据库级空间分析 ### 2.1 查看整个数据库占用 最基础的查询是使用`pg_database_size()`函数。这个函数返回的是字节数，通常我们需要转换成更易读的格式： ```sql SELECT datname AS database_name, pg_size_pretty(pg_database_size(datname)) AS size FROM pg_database ORDER BY pg_database_size(datname) DESC;这个查询会列出所有数据库按大小降序排列的结果。几个关键点需要注意：
template0和template1是模板数据库，通常占用较小- 结果包含TOAST存储的空间（后面会详细解释）
- 实际磁盘占用会比查询结果略大，因为还包括：
- WAL日志
- 临时文件
- 预写日志(pg_wal)
2.2 计算数据库真实增长趋势
单纯看当前大小不够，我们需要历史数据对比。可以创建定期任务记录空间数据：
CREATE TABLE db_size_history ( record_time TIMESTAMP, db_name TEXT, size_bytes BIGINT ); -- 每天执行一次 INSERT INTO db_size_history SELECT now(), datname, pg_database_size(datname) FROM pg_database;通过这个历史表，可以用窗口函数计算增长率：
WITH growth AS ( SELECT db_name, record_time, size_bytes, LAG(size_bytes) OVER (PARTITION BY db_name ORDER BY record_time) AS prev_size FROM db_size_history ) SELECT db_name, record_time, pg_size_pretty(size_bytes) AS current_size, CASE WHEN prev_size > 0 THEN ROUND(100.0 * (size_bytes - prev_size) / prev_size, 2) ELSE NULL END AS growth_percent FROM growth ORDER BY record_time DESC;3. 表级空间分析
3.1 基础表空间查询
pg_relation_size()系列函数是分析表空间的核心工具。最常用的组合是：
SELECT table_schema, table_name, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"')) AS total_size, pg_size_pretty(pg_relation_size('"' || table_schema || '"."' || table_name || '"')) AS table_size, pg_size_pretty(pg_indexes_size('"' || table_schema || '"."' || table_name || '"')) AS indexes_size, pg_size_pretty(pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') - pg_relation_size('"' || table_schema || '"."' || table_name || '"') - pg_indexes_size('"' || table_schema || '"."' || table_name || '"')) AS toast_size FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') AND table_type = 'BASE TABLE' ORDER BY pg_total_relation_size('"' || table_schema || '"."' || table_name || '"') DESC;这个查询展示了几个关键指标：
- total_size: 表+索引+TOAST的总大小
- table_size: 仅表数据的大小
- indexes_size: 所有关联索引的大小
- toast_size: TOAST存储的特殊数据
注意：TOAST(The Oversized-Attribute Storage Technique)是PostgreSQL存储大字段的机制。当字段值超过2KB时，PostgreSQL会自动将其压缩并存储到TOAST表中。这就是为什么有时表看起来不大，但TOAST占用很多空间。
3.2 深入分析表空间组成
对于特别大的表，我们需要更细致的分析。PostgreSQL的pageinspect扩展能帮我们看到更底层的数据：
CREATE EXTENSION IF NOT EXISTS pageinspect; -- 查看表的填充率 SELECT avg(round(100*(1-((free_space)/8192.0)),2)) AS avg_page_fill_percent FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace CROSS JOIN generate_series(0, (pg_relation_size(c.oid)/8192)::int) AS page CROSS JOIN heap_page_items(get_raw_page(c.oid::regclass::text, page)) WHERE n.nspname = 'public' AND c.relname = 'your_large_table';这个查询会显示表中每个页面的填充率。如果平均值低于70%，说明有大量空间浪费，可能需要VACUUM FULL或调整fillfactor参数。
4. 索引空间优化
4.1 索引空间分析
索引膨胀是常见问题。检查所有索引的大小和效率：
SELECT schemaname, tablename, indexname, pg_size_pretty(pg_relation_size(schemaname || '.' || indexname::text)) AS index_size, idx_scan AS scans_since_start FROM pg_stat_user_indexes JOIN pg_indexes USING (schemaname, tablename, indexname) ORDER BY pg_relation_size(schemaname || '.' || indexname::text) DESC;重点关注：
- 扫描次数极少的大索引（可能无用）
- 大小超过表数据50%的索引
- 重复索引（相同列的不同组合）
4.2 索引优化实战案例
我曾优化过一个电商平台的数据库，发现一个商品表有11个索引，总大小是表数据的3倍。通过以下步骤优化：
- 使用pg_qualstats识别实际使用的索引条件
- 合并部分单列索引为复合索引
- 删除6个月内未被使用的索引
- 对UUID主键启用hash索引替代默认的btree
优化后索引空间减少了68%，写入性能提升了40%。
5. 高级技巧与问题排查
5.1 识别空间泄漏
有时空间会被"幽灵"占用——即磁盘显示已用空间但PostgreSQL不显示。常见原因：
- 未提交的大事务导致的行版本保留
- 失败的CREATE INDEX CONCURRENTLY操作
- 被取消的批量导入
检查方法：
SELECT nspname || '.' || relname AS relation, pg_size_pretty(pg_relation_size(c.oid)) AS visible_size, pg_size_pretty(pg_table_size(c.oid) - pg_relation_size(c.oid)) AS extra_size FROM pg_class c LEFT JOIN pg_namespace n ON n.oid = c.relnamespace WHERE pg_table_size(c.oid) > pg_relation_size(c.oid) AND c.relkind = 'r' ORDER BY (pg_table_size(c.oid) - pg_relation_size(c.oid)) DESC;5.2 自动维护策略
建议创建定期维护任务：
-- 每周分析空间增长 CREATE OR REPLACE FUNCTION log_space_usage() RETURNS void AS $$ BEGIN INSERT INTO space_usage_log SELECT now(), schemaname, tablename, pg_total_relation_size(schemaname || '.' || tablename) FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); END; $$ LANGUAGE plpgsql; -- 设置定时任务（需要pg_cron扩展） SELECT cron.schedule('0 3 * * 6', 'SELECT log_space_usage()');5.3 常见问题解决方案
问题1：VACUUM后空间不释放？
- 原因：PostgreSQL默认只将空间标记为可重用，不归还操作系统
- 解决方案：使用VACUUM FULL或pg_repack
问题2：TOAST异常增长？
- 检查点：
SELECT attname, avg(width) AS avg_width, sum(length(toastval) > 2000) AS toast_rows FROM pg_attribute JOIN pg_stat_all_tables ON pg_attribute.attrelid = relid CROSS JOIN LATERAL (SELECT (row_to_json(t)::jsonb ->> attname) AS toastval FROM your_table t LIMIT 1000) s WHERE relname = 'your_table' GROUP BY attname; - 优化方案：考虑压缩或外部存储大字段
问题3：索引比表还大？
- 典型场景：UUID主键、GIN索引、未优化的JSONB索引
- 优化方案：
-- 对于低基数列考虑BRIN索引 CREATE INDEX idx_orders_created_brin ON orders USING brin(created_at); -- 对UUID主键使用hash索引 CREATE INDEX idx_items_id_hash ON items USING hash(id);
在实际运维中，我发现空间问题往往不是单一原因导致的。建议建立完整的监控体系，记录历史趋势，才能及时发现异常模式。对于特别关键的系统，可以考虑使用pg_statsinfo或pgBadger进行更全面的分析。
