PostgreSQL-14中如何限制Schema（模式）的存储空间不超过20GB？

一、表设计层面优化 #

• 消除冗余字段：避免存储可通过计算、关联得到的重复数据，用视图替代冗余字段的持久化存储，减少不必要的空间占用。
• 拆分大表：针对数据量较大的表，使用PostgreSQL 14的声明式分区（按时间、业务ID等维度），将数据分散到多个分区表中，便于单独管理和清理历史分区。
• 精简索引：仅保留业务必需的查询索引，定期通过pg_stat_user_indexes查看索引使用率，删除长期未被使用的冗余索引（索引通常会占用原表1-2倍的空间）。

二、数据类型精细化选择 #

• 选用最小适配类型：优先使用smallint（范围足够时替代integer）、date/timestamp（替代字符串存储日期时间）、numeric(p,s)（替代大精度数值类型）等占用更小空间的数据类型。
• 谨慎使用非关系型存储：避免用jsonb存储可拆解为关系表的结构化数据；确需使用时，仅存储必要字段，剔除冗余内容。
• 规避大字段存储：不在表中直接存储大二进制文件（如图片、视频），可将这类数据存到外部文件系统，表中仅存储文件路径或引用ID。

三、存储参数配置优化 #

• 启用TOAST压缩：PostgreSQL默认对大字段启用TOAST存储，可通过ALTER TABLE your_table SET (toast.compression = 'lz4')（需编译时支持lz4，否则用默认pglz）提升压缩率，减少大字段的空间占用。
• 调整填充因子：对于只读或更新频率低的表，设置ALTER TABLE your_table SET (fillfactor = 100)最大化空间利用率；对于频繁更新的表，设置fillfactor = 70-80减少页面膨胀。
• 开启自动清理：确保autovacuum进程正常运行，配置ALTER TABLE your_table SET (autovacuum_vacuum_scale_factor = 0.05, autovacuum_analyze_scale_factor = 0.02)，及时清理死元组和过期TOAST数据。

四、数据生命周期管理 #

• 定时清理过期数据：用pg_cron（需安装扩展）定时执行清理任务，例如：

  DELETE FROM your_schema.log_table WHERE create_time < now() - interval '30 days';
  

  业务低峰期执行，避免影响正常业务。
• 归档历史数据：将超过保留期限的冷数据导出为CSV/Parquet文件，存储到外部介质（如云存储），然后删除库中对应数据；需查询时可通过外部表临时访问。
• 定期清理表膨胀：在业务低峰期执行VACUUM FULL your_schema.your_table（注意锁表），或使用pg_repack（无锁）清理膨胀的表空间；用pgstattuple扩展查看表膨胀率：

  SELECT schemaname, tablename, tuple_percent, dead_tuple_percent FROM pgstattuple('your_schema.your_table');
  

五、监控与预警机制 #

• 定期监控Schema大小：执行以下SQL查询目标Schema的总占用空间：

  SELECT schemaname, pg_size_pretty(sum(pg_total_relation_size(quote_ident(tablename)))) AS total_size
  FROM pg_stat_user_tables 
  WHERE schemaname = 'your_target_schema'
  GROUP BY schemaname;
  

• 设置空间预警：通过定时任务监控Schema大小，当占用量接近18GB（预留2GB缓冲）时，触发邮件或系统告警，及时介入处理。
• 跟踪索引与表的空间占用：用pg_indexes_size和pg_table_size函数分别查看索引和表的单独占用空间，定位空间消耗大户。

六、额外小技巧 #

• 使用UNLOGGED表：对于无需持久化的临时数据（如日志、中间计算结果），创建UNLOGGED TABLE，这类表不写入WAL日志，空间占用更低且写入更快（注意：数据库崩溃后数据会丢失）。
• 清理临时对象：及时删除Schema中的测试表、临时表，避免闲置对象占用空间；临时表可指定存储到单独的临时Schema。

内容的提问来源于stack exchange，提问作者Raju