PostgreSQL 清理一个大表

Question

我有 Postgres 9.4.7，我有一个大表 ~100M 行和 20 列。表查询是每分钟 1.5k 次选择、150 次插入和 300 次更新，但没有删除。这是我的 autovacuum 配置:

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

在我的案例中，数据库几乎总是处于不断清理的状态。当一个吸尘 session 结束时，另一个吸尘 session 开始。

所以主要问题:有没有一种常用的方法来清理大表？

这里还有一些其他问题。

标准 vacuum 不扫描整个表，'analyze' 只扫描 30k 行。所以在相同的负载下我应该有一个恒定的执行时间，是真的吗？我真的需要分析表吗？频繁的“分析”是否可以对大型表的查询计划进行任何有用的更改？

Answer 1

真空

VACUUM reclaims storage occupied by dead tuples.

所以它只改变受影响的页面，但它会扫描整个表格。

这与您可能称之为“标准真空”的东西有关。现在如果你有 9.6，那么

VACUUM will skip pages based on the visibility map

分析

ANALYZE 扫描的数据量 depends关于表大小和default_statistics_target set per instance或每张 table - 它本身不是 30K:

For large tables, ANALYZE takes a random sample of the table contents, rather than examining every row... change slightly each time ANALYZE is run, even if the actual table contents did not change. This might result in small changes in the planner's estimated costs shown by EXPLAIN.

所以如果你想要 EXPLAIN 运行 smth 这样的更稳定的结果

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

或者增加 default_statistics_target，否则太频繁的分析有更多机会改变计划。

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