怎么防止postgres数据膨胀

最新推荐文章于 2024-10-06 23:02:37 发布

weixin_34362991

最新推荐文章于 2024-10-06 23:02:37 发布

阅读量135

点赞数

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/u/2339776/blog/467258

本文详细介绍了如何通过配置autovacuum、调整触发阈值、增加工作内存、优化事务设计、使用大块数据、定期回收表以及选择合适的开发工具来提升PostgreSQL数据库的性能。包括设置autovacuum参数、避免长事务、关闭不必要的垃圾回收操作、调整autovacuum_naptime参数等关键点。

2019独角兽企业重金招聘Python工程师标准>>>

复制与德哥的博客地址为：http://blog.163.com/digoal@126/blog/static/1638770402015329115636287/

1. 一定要开启autovacuum。

2. 提高系统的IO能力，越高越好。

3. 调整触发阈值，让触发阈值和记录数匹配。调小autovacuum_vacuum_scale_factor和autovacuum_analyze_scale_factor。比如我想在有1万条垃圾记录后就触发垃圾回收，那么对于一个1000万的表来说，我应该把 autovacuum_vacuum_scale_factor调到千分之一即0.001，而 autovacuum_analyze_scale_factor应该调到0.0005。

4. 增加autovacuum_max_workers，同时增加autovacuum_work_mem，同时增加系统内存。

例如对于有大量表需要频繁更新的数据库集群，可以将 autovacuum_max_workers调整为与CPU核数一致，并将 autovacuum_work_mem调整为2GB，同时需要确保系统预留的内存大于 autovacuum_max_workers* autovacuum_work_mem。

5. 应用程序设计时，尽量避免持有事务Exclusive锁的长事务（DDL,DML都会持有事务Exclusive锁）

6. 对于IO没有问题的系统，关闭autovacuum_vacuum_cost_delay。

7. 调整autovacuum_naptime参数到最低，如果还是唤醒时间太长，可以调整代码中的限制，例如改为1毫秒：

#define MIN_AUTOVAC_SLEEPTIME 1.0 /* milliseconds */

8. 应用程序设计时，避免使用大批量的更新，删除操作，可以切分为多个事务进行。

9. 使用大的数据块，对于现代的硬件水平，32KB是比较好的选择，fillfactor实际上不需要太关注，100就可以了，调低它其实没有必要，因为数据库总是有垃圾，也就是说每个块在被更新后实际上都不可能是满的。

10. 万一真的膨胀了，可以通过table rewrite来回收(如vacuum full, cluster)，但是需要迟排他锁。建议使用pg_reorg或者pg_repack来回收，实际上用到了交换 filenode可以缩短需要持有排他锁的时间。

转载于:https://my.oschina.net/u/2339776/blog/467258