postgresql - Postgres更快的自动真空

我有一个很大的表，400毫米的记录，通常只接收插入。不过，最近我不得不对记录做很多更新来完成一项任务。这会产生很多死元组。我已将全局配置更新为以下内容：

autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_threshold = 10000
autovacuum_vacuum_cost_limit = 2000
autovacuum_max_workers = 6

有了这些设置，我的意图是每当死元组增加到超过10000条记录时，autovacuum就会清除它。
但是，我发现当表忙于其他插入等时，死元组计数不会改变。它保持固定在某个死元组数。只有当数据库活动在晚上变慢时，自动吸尘器才能正常工作。
我需要自动吸尘器全天积极处理死元组。我该怎么做？我需要增加最大工人数吗？
更新：用户@Laurenz Albe建议我运行一些有和没有死元组的性能数字来演示性能差异。
我将提供sql查询和EXPLAIN（ANALYZE，BUFFERS）结果。为了隐私，我更改了表和组密钥的名称。

EXPLAIN (ANALYZE, BUFFERS)
SELECT  ld.upid,
        MAX(ld.lid)
INTO _tt_test_with_dead_tuples
FROM big_table ld
GROUP BY ld.upid;

-->>>大约有1%（383.2毫米中有3.648毫米）的死元组，结果如下。

HashAggregate  (cost=25579746.07..25584552.97 rows=480690 width=8) (actual time=5966760.520..5975279.359 rows=16238417 loops=1)
  Group Key: upid
  Buffers: shared hit=3015376 read=16753169 dirtied=1759802 written=1360458
  ->  Seq Scan on big_table ld  (cost=0.00..23642679.05 rows=387413405 width=8) (actual time=0.024..5593239.148 rows=383753513 loops=1)
        Buffers: shared hit=3015376 read=16753169 dirtied=1759802 written=1360458
Planning time: 2.677 ms
Execution time: 6012319.846 ms

-->>>有0个死元组，结果如下。

HashAggregate  (cost=25558409.48..25562861.52 rows=445204 width=8) (actual time=825662.640..835163.398 rows=16238417 loops=1)
  Group Key: upid
  Buffers: shared hit=15812 read=19753809
  ->  Seq Scan on big_table ld  (cost=0.00..23628813.32 rows=385919232 width=8) (actual time=0.020..533386.128 rows=383753513 loops=1)
        Buffers: shared hit=15812 read=19753809
Planning time: 10.109 ms
Execution time: 843319.731 ms

最佳答案

死元组不是你的问题。
你真正的问题在别的地方，我在下面强调了它。
慢速查询中的顺序扫描：
Buffers: shared hit=3015376 read=16753169 dirtied=1759802 written=1360458
快速查询中的顺序扫描：
Buffers: shared hit=15812 read=19753809
大约有200万个表块包含最近编写或更新的元组。
在编写元组时，PostgreSQL还不知道事务是否会提交或回滚，因此此信息不会存储在元组中。但是，它记录在提交日志中，该日志存储在pg_xact（或pg_clog，具体取决于您的版本）。
现在，第一个读入并读取新写入元组的读取器必须查阅提交日志来判断元组是否存在。为了避免将来读者的麻烦，它在元组中设置所谓的提示位来反映这些信息。
这会改变并因此“弄脏”包含元组的块，如果您有一个小的shared_buffers设置并且用完了可用的缓冲区，那么后端甚至必须将块写入存储器以清理它们并腾出空间。
这篇文章让你的查询变得如此缓慢。
清空表会使问题消失，因为VACUUM不仅可以清理死元组，还可以为您设置提示位（它也是一个读取器！）.
为了验证这一点，在不清空表的情况下再次运行相同的SELECT，您将看到，对于300万个死元组，它将同样快，因为现在提示位都设置好了。
这就是为什么在加载了很多行之后在表上运行VACUUM是一个好主意，即使没有要清理的内容，也可以为第一个读卡器节省大量工作。
想法：增加shared_buffers能改善情况吗？
但是，由于对表进行真空处理解决了这个问题，所以最好使用autovacuum更频繁地设置提示位。
为此，可以将autovacuum_vacuum_scale_factor设置为0，并将autovacuum_vacuum_threshold设置为一个大常量（远远大于10000），这样就不会有太多没有提示位的行。
此外，将autovacuum_vacuum_cost_delay设置为0，以便自动真空快速完成。
不要全局更改这些参数，请使用ALTER TABLE ... SET (...)仅为此表设置它们。

关于postgresql - Postgres更快的自动真空，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/52169722/