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

… I don’t need the transactional integrity to be maintained across
the entire operation,because I know that the column I’m changing is
not going to be written to or read during the update.

PostgreSQL’s MVCC model中的任何UPDATE写入一个新版本的整行。如果并发事务更改同一行的任何列，则会出现耗时的并发问题。 Details in the manual.知道同一列不会被并发事务触及，避免了一些可能的复杂情况，但不是其他。

指数

To avoid being diverted to an offtopic discussion,let’s assume that
all the values of status for the 35 million columns are currently set
to the same (non-null) value,thus rendering an index useless.

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

性能

For example,let’s say I have a table called “orders” with 35 million
rows,and I want to do this:

06000

我理解你正在寻求一个更一般的解决方案(见下文)。但是为了解决实际的问题：这可以在毫秒级处理，不管表大小：

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

Per documentation:

When a column is added with ADD COLUMN,all existing rows in the table
are initialized with the column’s default value (NULL if no DEFAULT
clause is specified).

和：

The DROP COLUMN form does not physically remove the column,but simply
makes it invisible to SQL operations. Subsequent insert and update
operations in the table will store a null value for the column. Thus,
dropping a column is quick but it will not immediately reduce the
on-disk size of your table,as the space occupied by the dropped
column is not reclaimed. The space will be reclaimed over time as
existing rows are updated. (These statements do not apply when
dropping the system oid column; that is done with an immediate rewrite.)

根据列(外键约束，索引，视图，…)，确保没有对象。你需要删除/重新创建那些。除此之外，系统目录表pg_attribute上的小操作执行作业。需要在表上的独占锁，这可能是重并发负载的问题。因为它只需要几毫秒，你应该还是很好。

如果您有要保留的列默认值，请将其添加回单独的命令。在相同的命令中执行它将立即应用于所有行，使效果无效。
按照链接，阅读手册中的注释。

一般解决方案

dblink在另一个答案中提到。它允许在隐式单独连接中访问“远程”Postgres数据库。 “远程”数据库可以是当前的数据库，从而实现“自治事务”：在“远程”数据库中写入的功能被提交并且不能回滚。

这允许运行单个函数来更新较小部分中的大表，并且单独提交每个部分。避免为非常大量的行创建事务开销，更重要的是，在每个部分之后释放锁。这允许并发操作没有太多延迟地进行，并且使得死锁不太可能。

如果没有并发访问，这几乎没有用 – 除了在异常后避免ROLLBACK。也考虑SAVEPOINT的情况。

免责声明

首先，许多小交易实际上更昂贵。这只适用于大表。甜蜜点取决于许多因素。

如果你不确定你在做什么：单个事务是安全的方法。为了正常工作，在表上的并发操作必须发挥。例如：并发写可以将一行移动到应该已经处理的分区。或者并发读取可以看到不一致的中间状态。你被警告了。

分步说明

需要首先安装附加模块dblink：

> How to use (install) dblink in PostgreSQL?

使用dblink设置连接非常依赖于DB集群的设置和安全策略。这可能很棘手。相关更多回答与更多如何连接dblink：

> Persistent inserts in a UDF even if the function aborts

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

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注入：

> Table name as a PostgreSQL function parameter

关于避免空UPDATE：

> How do I (or can I) SELECT DISTINCT on multiple columns?