## 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 常见问题解决方案问题1VACUUM后空间不释放原因PostgreSQL默认只将空间标记为可重用不归还操作系统解决方案使用VACUUM FULL或pg_repack问题2TOAST异常增长检查点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进行更全面的分析。 
长河君 · 2026/11/3 20:40:33 · 阅读 1.2k PostgreSQL空间占用分析与优化实战
## 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 常见问题解决方案问题1VACUUM后空间不释放原因PostgreSQL默认只将空间标记为可重用不归还操作系统解决方案使用VACUUM FULL或pg_repack问题2TOAST异常增长检查点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进行更全面的分析。