更新超过1.2亿行的PostgreSQL表时磁盘空间和性能问题

Question

我有一个表，大约大小为85 in，大约有1.2亿行(在PostgreSQL (12.8)数据库中)。

我需要使用update查询更新所有120万行，以便使用regexp_replace函数消除列的所有行中存在的某些字符。另外一个限制因素是，在运行update操作时，考虑到表复制的必要性，我没有太多的磁盘空间(大约20 gb)。但这对我来说是次要的问题。目前，主要问题是运行过程中此操作的速度缓慢，更重要的是，一旦运行完成，表的大小就会增加。这种尺寸的增加给我留下了其他的选择，除了在桌面上运行一个完全真空之外，由于上述磁盘空间的限制，这是我无法做的事情。

我还应该补充一点，这个表是索引的(使用不同的方法，包括btree)，并且还有一个主键。我也尝试在索引上创建集群，但是没有什么改变。为了减少运行时间，我只从主表中选择了2000万行，并试图处理该表，但是这也没有帮助。也涉及到我上面提到的完全真空的必要性，即使我试图通过应用where条件来更新表，每次运行时只更新一个较小的部分，这也会导致尺寸的增长，这意味着我必须在运行后运行完全真空，因为正常真空无助于将大小缩小到运行前的大小。

Answer 1

使用WHERE子句批量更新表应该有效，但是您需要在每一批之间对表进行真空。这样，一个更新所淘汰的空间就可以在下一个更新中被重用。您应该确保没有任何长期存在的快照(事务)，因为这些快照将强制保留过时的元组，以防旧快照希望看到它们。

如果您在中间连续运行批处理，那么您将取决于自动真空启动的时间和运行所需的时间。通过在批次之间进行手动真空，您可以确定它何时运行以及何时完成。