AWS RDS PostgreSQL存储用量差异过大问题咨询

可能的差异原因 #

• MVCC未回收空间：PostgreSQL的DELETE/UPDATE操作不会立即释放磁盘空间，旧数据会以死元组形式留存。即使近期无大量写入，若之前有数据变更操作且未执行彻底清理，这部分空间会持续占用磁盘。VACUUM仅清理死元组但不会释放空间给操作系统，只有VACUUM FULL或pg_repack才能回收。
• WAL日志累积：RDS PostgreSQL的WAL日志用于备份恢复、只读副本同步。若备份保留期过长、副本同步延迟高，或开启了逻辑复制但订阅者异常，WAL日志会大量堆积，占用大量存储。
• 临时文件/临时表残留：大型查询生成的临时文件/表，若会话异常终止可能残留；部分复杂查询的临时数据也会临时占用空间，未及时清理。
• 自定义Tablespaces未统计：若创建了非默认的tablespace，pg_database_size不会包含这部分空间，需单独查询。
• RDS系统组件占用：RDS的监控数据、本地日志缓存、系统配置文件等会占用少量空间，但如果占比过高需排查是否有异常日志生成（如大量错误日志）。

存储用量详细拆解方法 #

1. PostgreSQL内部查询各组件大小 #

数据库与表级大小统计

• 查询目标数据库总大小（含数据、索引、TOAST）：

  SELECT pg_size_pretty(pg_database_size('my_database')) AS total_db_size;
  

• 按表拆解大小（区分数据、索引、总占用）：

  SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename))) AS total_table_size,
    pg_size_pretty(pg_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename))) AS data_size,
    pg_size_pretty(pg_indexes_size(quote_ident(schemaname) || '.' || quote_ident(tablename))) AS index_size
  FROM pg_tables
  WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  ORDER BY pg_total_relation_size(quote_ident(schemaname) || '.' || quote_ident(tablename)) DESC;
  

• 查看死元组与未释放空间：

  SELECT 
    schemaname,
    tablename,
    n_dead_tup,
    pg_size_pretty(dead_tuple_len) AS dead_tuple_size,
    pg_size_pretty(heap_blks_free * current_setting('block_size')::integer) AS free_heap_space
  FROM pg_stat_user_tables
  JOIN pg_stat_user_tables_ext USING (schemaname, tablename)
  ORDER BY dead_tuple_len DESC;
  

WAL日志大小查询

在RDS中可通过以下SQL查询当前WAL目录占用（需超级用户权限）：

SELECT pg_size_pretty(sum(size)) AS wal_total_size
FROM pg_ls_dir('pg_wal') AS f
JOIN pg_stat_file('pg_wal/' || f) AS s ON true;

自定义Tablespaces大小

SELECT 
  spcname AS tablespace_name,
  pg_size_pretty(pg_tablespace_size(spcname)) AS tablespace_size
FROM pg_tablespace
WHERE spcname NOT IN ('pg_default', 'pg_global');

临时文件大小

SELECT pg_size_pretty(sum(size)) AS temp_files_total_size
FROM pg_ls_dir('pg_temp') AS f
JOIN pg_stat_file('pg_temp/' || f) AS s ON true;

2. RDS控制台与CLI辅助排查 #

• 查看CloudWatch指标：监控FreeStorageSpace、UsedStorage、WALGenerated、TempDBUsage等指标，分析存储占用趋势。
• 使用AWS CLI查询实例存储详情：

  aws rds describe-db-instances --db-instance-identifier your-instance-id --query 'DBInstances[0].[AllocatedStorage, StorageType, FreeStorageSpace]'
  

针对性解决建议 #

• 回收死元组空间：先执行VACUUM ANALYZE;更新统计信息，若需释放磁盘空间，在维护窗口执行VACUUM FULL;（会锁表），或安装pg_repack扩展进行在线空间回收。
• 清理WAL日志：检查只读副本同步状态，修复延迟问题；调整备份保留期（按需设置，避免过长）；若使用逻辑复制，确保订阅者正常运行。
• 清理临时文件：若临时文件残留，可重启实例（RDS重启会清理临时空间）；优化大型查询以减少临时数据生成。
• 清理自定义Tablespaces：若存在未使用的tablespace，迁移或删除其中数据后释放空间。

内容的提问来源于stack exchange，提问作者Zsámboki Attila