我该怎么做在PostgreSQL大无阻塞更新？我该怎么做在PostgreSQL大无阻塞更新？(How

2019-06-02 15:57发布

站内文章 / 前沿技术

56 0

闹够了就滚

女 | 书童

私信

我想要做PostgreSQL中的表大的更新，但我不需要事务的完整性在整个操作中保持，因为我知道，我改变了列不会期间写入或读取更新。我想知道是否有在PSQL控制台一种简单的方法来使这些类型的操作速度更快。

举例来说，假设我有一个表称为“订单”拥有35万行，我想这样做：

UPDATE orders SET status = null;

为了避免被转移到一台offtopic讨论，让我们假设的状态为3500万列中的所有值当前设置为相同的（非空）值，从而使指标无用。

这个说法的问题在于，它需要很长的时间才能生效（仅锁定的原因），所有更改的行锁定，直到整个更新完成。此更新可能需要5小时，而像

UPDATE orders SET status = null WHERE (order_id > 0 and order_id < 1000000);

可能需要1分钟。超过35万行，做上述，并分解成35块将只需要35分钟，救我4小时25分钟。

我甚至可以进一步用脚本（使用伪代码这里）把它分解：

for (i = 0 to 3500) {
  db_operation ("UPDATE orders SET status = null
                 WHERE (order_id >" + (i*1000)"
             + " AND order_id <" + ((i+1)*1000) " +  ")");
}

此操作可能完成只有几分钟，而不是35。

所以这归结为我真正要求。我不想写一个脚本，再用打破操作每一次我想做这样一个大的一次更新。有没有办法来完成我想要完全在SQL是什么？

Answer 1:

行/列

......我不需要事务的完整性在整个操作中保持，因为我知道，我改变了列不会在更新过程中要写入或读取。

任何UPDATE在PostgreSQL的MVCC模式写入整个行的新版本。如果并发事务更改同一行的任何列，耗时并发问题出现。详细的说明书中无。知道了同一列不会并发事务感动避免一些可能出现的并发症，而不是其他。

指数

为了避免被转移到一台offtopic讨论，让我们假设的状态为3500万列中的所有值当前设置为相同的（非空）值，从而使指标无用。

当更新整个表 （或它的主要部分）的Postgres 从来没有使用索引 。顺序扫描速度更快，当全部或大部分行必须阅读。与此相反：索引维护装置的附加成本UPDATE 。

性能

举例来说，假设我有一个表称为“订单”拥有35万行，我想这样做：
 UPDATE orders SET status = null; 

我明白你的目标是一个更通用的解决方案（见下文）。但要解决企业的实际问题问：这可以在问题毫秒来处理，无论台面尺寸：

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

每文档：

当添加一列ADD COLUMN ，表中的所有现有的行初始化为列的默认值（ NULL ，如果没有DEFAULT指定条款）。如果没有DEFAULT子句，这仅仅是一个元数据的变化...

和：

该DROP COLUMN形式不物理删除列，但只是让无形的SQL操作。在表的后续插入和更新操作将存储该列的空值。因此，删除一列快捷，但它不会立即减少磁盘上的大小你的表的，由下降列占据的空间不会回收。该空间将随着时间的推移被开垦为现有行更新。（掉落系统OID列时，这些陈述不适用;这与立即重写完成。）

确保你没有根据列（外键约束，索引，视图，...）对象。你将需要删除/重新创建这些。除非是，在系统目录表微小的操作pg_attribute做的工作。要求在其可以是用于重并发负载的一个问题表的排他锁 。由于只需要几毫秒的时间，你仍然应该罚款。

如果你有你想保留一列默认情况下，将其重新添加在一个单独的命令 。在相同的命令这样做会立即应用到所有的行，排尿效果。然后，您可以在更新现有列批次。按照文件链接，并在手册中阅读笔记。

通用解决方案

dblink已经在另一个答案被提及。它允许访问在隐式分离的连接“远程”的Postgres数据库。 “远程”数据库可以是目前的一个，从而实现“自主交易”：什么样的功能，在“远程” DB致力于写，不能回滚。

这允许运行一个单一的功能，在更小的部分更新一个大表，每个部分分别提交。避免了建立交易开销的行非常大的数字，更重要的是，发布各部分后锁。这允许并发操作，没有太多的拖延地进行，使死锁的可能性较小。

如果没有并发访问，这是很难有用的-除了避免ROLLBACK异常后。还要考虑SAVEPOINT该情况。

放弃

首先，许多小的交易实际上是更加昂贵。这不仅使对大表感 。甜蜜点取决于许多因素。

如果你不知道你在做什么： 一个单一的交易是安全的方法 。为了这个正常工作，对表并发操作有一起玩。例如：并发写入可以移动行，该行本应当是已经处理过的分区。或并发读取可以看到不一致的中介状态。 你被警告了。

一步一步的指示

附加模块DBLINK需要先安装：

如何使用PostgreSQL的（安装）DBLINK？

设置了DBLINK的连接，在很大程度上取决于到位您的数据库集群和安全政策的设置。它可能会非常棘手。相关答案以后有更多如何与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？

Answer 2:

你应该委托该列这样的另一个表：

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

那么你的设置状态= NULL的操作将是瞬间：

truncate order_status;

Answer 3:

Postgres使用MVCC（多版本并发控制），从而避免任何锁定，如果你是唯一的作家; 任意数量的并发的读者可以在桌子上工作，并且不会有任何锁定。

所以，如果真的需要5小时，它必须是一个不同的原因（例如，你确实有并发写入，违背了你的要求，你不）。

Answer 4:

我会用CTAS：

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

Answer 5:

首先的 - 你确定你需要更新的所有行？

也许有些行已经有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 ...

或者先申请，然后给psql（在情况下，你需要的文件以后）：

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

Answer 6:

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

一个简单的WHERE status IS NOT NULL可能会加快东西相当多的（前提是你有状况的指标） -不知道实际使用的情况下，如果这是经常跑我假设，35万行的很大一部分可能已经有一个空的状态。

但是，您可以通过在查询中做出的循环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的维基有有关项目的缓慢计数和如何避免它。

此外，RAISE NOTICE部分只是为了保持轨道上沿远的脚本是怎么样。如果你没有监视通知或不关心，这将是更好地离开它。

Answer 7:

你确定这是因为锁定的？我不这么认为，有众多的其他可能的原因。要找出你总是可以尝试做只是锁定。试试这个：BEGIN; SELECT NOW（）; SELECT * FROM为了FOR UPDATE; SELECT NOW（）; ROLLBACK;

要了解什么是真正发生的事情，你应该运行首先解释（解释UPDATE命令将状态设置...）和/或解释的分析。也许你会发现，你没有足够的内存来有效地执行UPDATE。如果是这样，SET work_mem TO 'XXXMB'; 可能是一个简单的解决方案。

此外，尾部PostgreSQL的日志，看看是否会出现一些性能相关的问题。

Answer 8:

尚未提到的一些选项：

使用新表的把戏。也许你会在你的情况下，做的是写一些触发器来处理它，以便更改原始表也去传播到你的表副本，类似的东西...（ Percona的是，做它的一些例子触发方式）。另一种选择可能是“创建新列，然后替换旧与它” 伎俩，避免锁（不清楚是否有速度帮助）。

可能计算出最大值ID，则生成“你需要的所有查询”，并在将它们作为像单个查询update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ... update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ... update X set Y = NULL where ID < 10000 and ID >= 0; update X set Y = NULL where ID < 20000 and ID > 10000; ...那么它可能不会做尽可能多的锁定，并且仍然是所有的SQL，虽然你有额外的逻辑前面做:(

文章来源: How do I do large non-blocking updates in PostgreSQL?