PostgreSQL表膨胀问题解决方案与pg_squeeze实践

1. 项目背景：一次真实的PG表膨胀事故复盘

去年双十一大促前夜，我们核心订单表的查询性能突然断崖式下跌，原本50ms的API响应时间飙升到3秒以上。紧急排查发现表体积膨胀到原始大小的7倍，索引扫描效率降低了80%。当时只能硬着头皮在业务高峰期执行VACUUM FULL，导致系统卡顿近20分钟——这个惨痛教训让我下定决心系统化解决PostgreSQL的表膨胀问题。

表膨胀本质是MVCC机制下的空间回收难题。当执行UPDATE时，PG会创建新行版本并标记旧行为"死元组"；DELETE操作则直接标记删除行。虽然AUTOVACUUM会清理这些死元组，但腾出的空间会形成"空洞"，新的插入操作可能无法充分利用这些碎片空间。特别是在以下场景中膨胀尤为严重：

• 高频更新的计数器字段
• 定期清理历史数据的时序表
• 使用SERIALIZABLE隔离级别的事务
• 长时间运行的事务阻塞VACUUM

关键指标：当pg_stat_user_tables中n_dead_tup超过活元组的10%，或pgstattuple检测到膨胀率>30%时就需要干预

2. 主流表膨胀清理方案对比

2.1 原生VACUUM的局限性

常规VACUUM只能标记空间为可复用，无法减少物理文件大小。VACUUM FULL会重写整个表文件，但需要ACCESS EXCLUSIVE锁，相当于：

BEGIN; LOCK TABLE orders IN ACCESS EXCLUSIVE MODE; CREATE TABLE orders_new (LIKE orders); INSERT INTO orders_new SELECT * FROM orders; DROP TABLE orders; ALTER TABLE orders_new RENAME TO orders; COMMIT;

这种操作在TB级表上可能耗时数小时，完全不可接受。

2.2 第三方工具横评

通过对比测试几种主流方案：

实测发现pg_squeeze在阿里云环境表现最优：

1. 通过逻辑解码避免锁表
2. 支持定时任务自动化管理
3. 提供精确的膨胀率检测SQL

3. pg_squeeze深度配置指南

3.1 安装与基础配置

在RDS PostgreSQL上需要先调整参数：

ALTER SYSTEM SET wal_level = logical; ALTER SYSTEM SET shared_preload_libraries = 'pg_squeeze'; -- 重启生效后 CREATE EXTENSION pg_squeeze;

关键参数调优建议：

squeeze.max_retry = 3 # 失败重试次数 squeeze.worker_timeout = '1h' # 单表处理超时 squeeze.compression_level = 6 # ZSTD压缩级别 squeeze.batch_size = '500MB' # 单批次处理量

3.2 智能清理策略设计

针对不同业务特征的表，推荐采用差异化策略：

订单表（高频更新）

INSERT INTO squeeze.tables (tabschema, tabname, schedule, free_space_extra) VALUES ('public', 'orders', ('{30}', '{2}', NULL, NULL, '{1,3,5}'), 10);

• 每周一、三、五凌晨2:30执行
• 保留10%额外空间应对突发写入

用户表（低频更新）

SELECT squeeze.squeeze_table( schema_name => 'public', table_name => 'users', free_space_extra => 5, skip_analysis => true );

• 手动触发时跳过膨胀率检测
• 仅保留5%缓冲空间

4. 生产环境避坑实录

4.1 典型故障案例

案例1：清理过程中触发唯一键冲突

• 现象：worker进程报错"duplicate key violates unique constraint"
• 原因：表缺少有效主键或唯一索引
• 解决：ALTER TABLE ADD UNIQUE (col1,col2)

案例2：长事务阻塞清理

• 现象：pg_stat_activity显示squeeze进程状态为"waiting"
• 排查：SELECT blocked_locks.pid FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE blocked_locks.pid = 12345;
• 处理：终止阻塞事务或调整清理时间

4.2 监控指标体系

建议配置以下Prometheus监控项：

- name: pg_squeeze_status query: | SELECT count(*) FILTER (WHERE status='working') AS active_workers, count(*) FILTER (WHERE status='failed') AS failed_workers FROM squeeze.log WHERE finish_time > NOW() - INTERVAL '1h' - name: table_bloat_ratio query: | SELECT schemaname||'.'||relname AS table, 100*(1 - (pg_relation_size(schemaname||'.'||relname) - pg_relation_size(schemaname||'.'||relname||'_idx'))::float/ pg_total_relation_size(schemaname||'.'||relname)) AS bloat_pct FROM pg_stat_user_tables WHERE n_dead_tup > 1000

5. 进阶优化技巧

5.1 与分区表配合使用

对于按月分区的日志表，可以只清理最近活跃分区：

CREATE OR REPLACE FUNCTION clean_partitions() RETURNS void AS $$ DECLARE part record; BEGIN FOR part IN SELECT nmsp_parent.nspname AS schema_name, parent.relname AS table_name FROM pg_inherits JOIN pg_class parent ON pg_inherits.inhparent = parent.oid JOIN pg_namespace nmsp_parent ON nmsp_parent.oid = parent.relnamespace WHERE parent.relname LIKE 'logs%' LOOP EXECUTE format('SELECT squeeze.squeeze_table(%L, %L)', part.schema_name, part.table_name); END LOOP; END; $$ LANGUAGE plpgsql;

5.2 内存优化配置

在postgresql.conf中添加：

maintenance_work_mem = 1GB # 每个清理进程内存 squeeze.work_mem = 512MB # 排序操作内存 effective_io_concurrency = 8 # 并行IO数

经过半年实践，这套方案已将我们生产环境的平均表膨胀率控制在8%以下，紧急故障处理时间从小时级缩短到分钟级。最关键的是——再也不用半夜爬起来处理VACUUM FULL了。