PostgreSQL清理大表

Question

我有一个Postgres 9.4.7的大表，约100M行和20列。该表每分钟进行1.5k次查询、150次插入和300次更新，但没有删除操作。以下是我的自动清理配置：

autovacuum_analyze_scale_factor 0 autovacuum_analyze_threshold 5000 autovacuum_vacuum_scale_factor 0 autovacuum_vacuum_threshold 5000 autovacuum_max_workers 6 autovacuum_naptime 5s

在我的情况下，数据库几乎总处于不断清理的状态。当一个清理会话结束时，另一个会话就开始了。

所以主要问题是： 有没有通用的方法来清理大表？

以下是其他一些问题。

标准清理不会扫描整个表，只有“分析”会扫描30k行。因此，在相同的负载下，我应该具有恒定的执行时间，这是真的吗？ 我真的需要分析表吗？对于大表，频繁的“分析”是否可以对查询计划产生任何有用的改变？

- Max Selivanov

---

你有9.6吗？如果没有，那么语句“标准的真空不会扫描整个表格”是错误的。 - Vao Tsun

这个问题更有可能在dba.se上得到答案。 - hemp

Answer 1

真空

真空回收被死元组占用的存储空间。

因此，它只会改变受影响的页面，但会扫描整个表。

这就是您可能称之为“标准真空”的内容。如果您使用9.6，则

根据可见性图，真空将跳过页面。

分析

ANALYZE扫描的数据量取决于表的大小和default_statistics_target每个实例或每个表设置，而不是30K本身：

“对于大型表，ANALYZE会随机抽取表中的一部分内容进行分析，而不是检查每一行……即使实际表格内容没有改变，每次运行ANALYZE时也可能会略微改变。这可能会导致EXPLAIN显示的计划程序估计成本有小的变化。

因此，如果您希望EXPLAIN结果更稳定，可以运行如下命令：”

alter table ... alter COLUMN ... set STATISTICS 200;

或者增加 default_statistics_target，否则分析的机会过多会改变计划。

还有一件事 - 你有一个5K阈值。在一个有100000K行的表中，这是0.002% - 对吗？所以比例是0.00002？而默认值是0.2或0.1...这让我想到，也许你的阈值太低了。确实建议更频繁地运行vacuum，但在这里看起来太频繁了。比默认情况下要多一千倍...

- Vao Tsun