小开

Postgres 使用 MVCC (多版本并发控制) ，因此如果你是唯一的编写者，就可以避免任何锁定，任何数量的并发读取器都可以在表上工作，而且不会有任何锁定。

因此，如果真的需要5h，那么肯定是出于不同的原因(例如，您的做具有并发写操作，这与您声称没有的说法相反)。

小开

我绝不是一个 DBA，但是一个经常需要更新3500万行的数据库设计可能会有问题。

一个简单的 WHERE status IS NOT NULL可能会大大加快速度(如果你有状态索引的话)——不知道实际的用例，我假设如果这是经常运行的，3500万行中的大部分可能已经有一个空状态。

然而，你可以通过 LOOP 语句在查询中进行循环:

CREATE OR REPLACE FUNCTION nullstatus(count INTEGER) RETURNS integer AS $$
DECLARE
i INTEGER := 0;
BEGIN
FOR i IN 0..(count/1000 + 1) LOOP
UPDATE orders SET status = null WHERE (order_id > (i*1000) and order_id <((i+1)*1000));
RAISE NOTICE 'Count: % and i: %', count,i;
END LOOP;
RETURN 1;
END;
$$ LANGUAGE plpgsql;

然后，它可以通过类似于下面这样的操作来运行:

SELECT nullstatus(35000000);

您可能希望选择行计数，但是要注意，确切的行计数可能会花费很多时间。PostgreSQL wiki 有一篇关于慢速计数以及如何避免它的文章。

另外，RAISE NOTICE 部分只是用来跟踪脚本的执行情况。如果你没有监视这些通知，或者根本不在乎，最好把它们放在一边。

小开

首先，您确定需要更新所有行吗？

也许有些行已经有 status NULL 了？

如果是这样，那么:

UPDATE orders SET status = null WHERE status is not null;

至于分区更改-这在纯 sql 中是不可能的。所有更新都在单个事务中。

在“纯 sql”中实现这一点的一种可能方法是安装 dblink，使用 dblink 连接到同一个数据库，然后通过 dblink 发布大量更新，但是对于这样一个简单的任务来说，这似乎有些过头了。

通常只需添加适当的 where就可以解决这个问题。如果没有，就手动分区。写剧本太麻烦了——你通常可以用一句简单的俏皮话:

perl -e '
for (my $i = 0; $i <= 3500000; $i += 1000) {
printf "UPDATE orders SET status = null WHERE status is not null
and order_id between %u and %u;\n",
$i, $i+999
}
'

为了便于阅读，我在这里包装了几行，通常是一行。上面命令的输出可以直接提供给 psql:

perl -e '...' | psql -U ... -d ...

或者首先输入 file，然后输入 psql (以防以后需要这个文件) :

perl -e '...' > updates.partitioned.sql
psql -U ... -d ... -f updates.partitioned.sql

小开

您应该将此列委托给另一个表，如下所示:

create table order_status (
order_id int not null references orders(order_id) primary key,
status int not null
);

那么设置 status = NULL 的操作将是即时的:

truncate order_status;

小开

你确定是因为上锁吗？我不这么认为，还有很多其他可能的原因。为了找到答案，你可以尝试只做锁定。试试这个: 开始现在选择() ; 从订单中选择 * 进行更新; 现在选择() ; 后退;

为了理解到底发生了什么，你应该首先运行一个解释(解释更新订单设置状态...)和/或解释分析。也许您会发现您没有足够的内存来高效地执行 UPDATE。如果是这样，那么 SET work _ mem TO‘ xxxMB’; 可能是一个简单的解决方案。

另外，跟踪 PostgreSQL 日志以查看是否发生了一些与性能相关的问题。

小开

我会用 CTAS:

begin;
create table T as select col1, col2, ..., <new value>, colN from orders;
drop table orders;
alter table T rename to orders;
commit;

小开

列/行

我不需要交易的完整性整个操作，因为我知道我要改变的列在更新过程中不会被写入或读取。

PostgreSQL 的 MVCC 模型中的任何 UPDATE都编写整行的新版本。如果并发事务更改同一行的任何列，就会出现耗时的并发问题。知道同样的专栏不会被并发事务触及，可以避免一些可能出现的并发症，但不能避免其他的并发症。

索引

为了避免转移话题，让我们假设当前设置了3500万列的所有 status 值转换为相同(非空)值，从而使索引无用。

当更新 < strong > 整个表 (或它的主要部分) Postgres 永远不要使用索引。当必须读取所有或大多数行时，顺序扫描会更快。相反: 索引维护意味着 UPDATE的额外成本。

表演

例如，假设我有一个名为“ order”的表，其中包含3500万个订单我想这样做:

UPDATE orders SET status = null;

我明白你的目标是一个更一般的解决方案(见下文)。但是为了解决 实际问题的问题: 这可以在 一件事毫秒中处理，不管表大小如何:

ALTER TABLE orders DROP column status
, ADD  column status text;

手册(直到 Postgres 10) :

使用 ADD COLUMN添加列时，表中的所有现有行用列的默认值(如果没有 DEFAULT，则为 NULL)初始化子句)。如果没有 DEFAULT子句，这仅仅是元数据更改[ ... ]

手册(自 Postgres 11之后) :

当列与 ADD COLUMN和非易失性 DEFAULT一起添加时则在执行语句时计算默认值以及存储在表的元数据中的结果如果未指定 DEFAULT，则为所有现有行的列设置, 使用 NULL。在这两种情况下都不需要重写表。

添加具有易失性 DEFAULT的列或更改现有列将要求整个表及其索引为重写

还有:

DROP COLUMN表单并不从物理上删除该列，但是只是使它对 SQL 操作不可见表中的 update 操作将为该列存储空值。因此，删除列很快，但不会立即减少表的磁盘大小，即被删除的列不会被回收。随着时间的推移，空间将被回收为更新现有行。

确保没有依赖于列的对象(外键约束、索引、视图、 ...)。您需要删除/重新创建这些。除此之外，系统目录表 pg_attribute上的小操作可以完成这项工作。需要一个表上的 独家锁定，这可能是一个问题的重并发负载。(就像 Buurman 在他的评论中强调的那样。)除此之外，操作只需要几毫秒。

如果要保留某个列的默认值，请将其添加回 在一个单独的命令。在同一个命令中执行此操作将立即将其应用于所有行。参见:

添加没有表锁的新列？

要实际应用默认值，可以考虑分批应用:

PostgreSQL 是否优化添加具有非 NULL DEFAULT 的列？

通用解决方案

在另一个答案中提到了 dblink 。它允许以隐式的独立连接访问“远程”Postgres 数据库。“远程”数据库可以是当前的数据库，从而实现 “自主交易”: 函数在“远程”数据库中写入的内容已提交，不能回滚。

这允许运行一个单独的函数，该函数用较小的部分更新一个较大的表，并且每个部分都是单独提交的。避免为非常大数量的行建立事务开销，更重要的是，在每个部分之后释放锁。这允许并发操作在没有太多延迟的情况下进行，并降低了死锁的可能性。

如果您没有并发访问权限，那么这几乎没有用处——除了在异常发生后避免使用 ROLLBACK。在这种情况下还要考虑 SAVEPOINT。

免责声明

首先，许多小型交易实际上更昂贵。这个 < strong > 只适用于大表 。最佳点取决于许多因素。

如果你不确定你在做什么: 单个事务是安全的方法。为了使其正常工作，桌面上的并发操作必须配合使用。例如: 并发的写作可以将一行移动到一个假定已经处理过的分区。或者并发读取可以看到不一致的中间状态。我已经警告过你了。

分步说明

需要首先安装额外的模块 dblink:

如何在 PostgreSQL 中使用(安装) dblink？

使用 dblink 设置连接在很大程度上取决于 DB 集群的设置和适当的安全策略。可能有点棘手。相关后续答案与更多 如何连接到 dblink:

即使函数中止，也要在 UDF 中进行持久性插入

按照指示创建一个 FOREIGN SERVER和一个 USER MAPPING，以简化和简化连接(除非您已经有了一个)。
假设一个 serial PRIMARY KEY有或没有一些间隙。

CREATE OR REPLACE FUNCTION f_update_in_steps()
RETURNS void AS
$func$
DECLARE
_step int;   -- size of step
_cur  int;   -- current ID (starting with minimum)
_max  int;   -- maximum ID
BEGIN
SELECT INTO _cur, _max  min(order_id), max(order_id) FROM orders;
-- 100 slices (steps) hard coded
_step := ((_max - _cur) / 100) + 1;  -- rounded, possibly a bit too small
-- +1 to avoid endless loop for 0
PERFORM dblink_connect('myserver');  -- your foreign server as instructed above


FOR i IN 0..200 LOOP                 -- 200 >> 100 to make sure we exceed _max
PERFORM dblink_exec(
$$UPDATE public.orders
SET    status = 'foo'
WHERE  order_id >= $$ || _cur || $$
AND    order_id <  $$ || _cur + _step || $$
AND    status IS DISTINCT FROM 'foo'$$);  -- avoid empty update


_cur := _cur + _step;


EXIT WHEN _cur > _max;            -- stop when done (never loop till 200)
END LOOP;


PERFORM dblink_disconnect();
END
$func$  LANGUAGE plpgsql;

电话:

SELECT f_update_in_steps();

您可以根据您的需要对任何部分进行参数化: 表名、列名、值，... ... 只要确保清除标识符以避免 SQL 注入:

表名作为 PostgreSQL 函数参数

避免空白的更新:

如何(或可以)在多列上选择 DISTINCT？

小开

一些没有被提及的选择:

使用新桌子技巧。在这种情况下，您可能需要编写一些触发器来处理它，这样对原始表的更改也会传播到表副本，类似这样的事情... ... (Percona是一个触发器方式的例子)。另一种选择可能是“创建一个新列，然后用它替换旧列”恶作剧，以避免锁(不清楚是否有助于提高速度)。

可能计算出最大 ID，然后生成“所有您需要的查询”，并将它们作为一个单独的查询(如 update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ...)传递给它们，然后它可能不会执行那么多的锁定操作，但仍然是全部 SQL，尽管您在执行这些操作之前确实有额外的逻辑: (

小开

PostgreSQL 版本11使用快速 ALTERTABLE 添加列，默认值为非空特性为您自动处理此问题。如果可能，请升级到版本11。

本博客文章中提供了一个解释。

如何在 PostgreSQL 中执行大型非阻塞更新？

列/行

索引

表演

通用解决方案

免责声明

分步说明