在postgresql中添加默认值的列，而不添加表级锁定

Question

有这样的问题 - 超过2000万行的表。

当我使用默认值添加新列时 - postgresql 锁定表超过 40 分钟，因此我的应用程序这次停止工作。

所以而不是

ALTER TABLE "test" ADD COLUMN "field" boolean DEFAULT True NOT NULL;

我愿意

ALTER TABLE "test" ADD COLUMN "field" boolean NULL;
ALTER TABLE "test" ALTER COLUMN "field" SET DEFAULT true;

之后，默认情况下每个新行都将为 true，所以现在我需要更新 2000 万当前行。

我分批更新它们：

WITH cte AS (
SELECT id as pk
FROM "test"
WHERE  "field" is null
LIMIT  10000
)
UPDATE "test" table_
SET "field" = true
FROM   cte
WHERE  table_.id = cte.pk

之后我做

ALTER TABLE "test" ALTER COLUMN "field" SET NOT NULL;

一切都很好，但是当我更新行时太慢了。你能给我一些关于提高更新速度的建议吗？

目前它在大约 10000 分钟内更新 2。 我尝试将大小减小到 1000 - 它更好(3.5 分钟为 10000 分钟(，但它仍然会变慢。

我也尝试在更新前创建索引，但它没有给出更好的结果(据我所知，它会提供更好的结果，但是当表格的巨大路径将被更新时(。

Answer 1

您可以使用HOT 加快UPDATEs。这要求更新的列没有索引(在您的情况下很容易(，并且表块中有可用空间(fillfactor<100(。不确定您是否满足第二个要求。

提高批量更新速度的另一件事是增加max_wal_size，以便检查点不经常发生。

也许你可以等几个月的PostgreSQL v11。以下新功能将满足您的需求：

• 允许ALTER TABLE添加具有非空默认值的列而不重写表(Andrew Dunstan，Serge Rielau(