logo标签列表

如何批量更新 PostgreSQL 中 5500 万条记录中的单个列

sqlpostgresqlplpgsql
8

我想更新Postgres表的一列。由于记录数约为5500万条，因此我们需要将其分批更新，每次更新10000条记录。 注意：我们想要更新所有行，但不想锁定我们的表。

我正在尝试以下查询 -

Update account set name = Some name where id between 1 and 10000

如何针对每10000条记录更新一次循环？

欢迎任何建议和帮助。

PostgreSQL 10.5

4个回答
24

不要一次性提交所有更改（或者像其他答案中建议的那样，55百万次），我宁愿尝试将更新行拆分成小批次，例如你建议的10k条记录。在PL/pgSQL中，可以使用关键字BY以给定步长迭代遍历集合。因此，您可以在一个匿名代码块中执行批量更新，如下所示：

PostgreSQL 11+

DO $$ 
DECLARE 
  page int := 10000;
  min_id bigint; max_id bigint;
BEGIN
  SELECT max(id),min(id) INTO max_id,min_id FROM account;
  FOR j IN min_id..max_id BY page LOOP 
    UPDATE account SET name = 'your magic goes here'
    WHERE id >= j AND id < j+page;
    COMMIT;            
  END LOOP;
END; $$;
  • 您可能需要调整WHERE子句以避免不必要的重叠。

测试

数据样本，包含1051行以及连续的ID：

CREATE TABLE account (id int, name text);
INSERT INTO account VALUES(generate_series(0,1050),'untouched record..');

正在执行匿名代码块...

DO $$ 
DECLARE 
  page int := 100;
  min_id bigint; max_id bigint;
BEGIN
  SELECT max(id),min(id) INTO max_id,min_id FROM account;
  FOR j IN min_id..max_id BY page LOOP 
    UPDATE account SET name = now() ||' -> UPDATED ' || j  || ' to ' || j+page
    WHERE id >= j AND id < j+page;
    RAISE INFO 'committing data from % to % at %', j,j+page,now();
    COMMIT;            
  END LOOP;
END; $$;
    
INFO:  committing data from 0 to 100 at 2021-04-14 17:35:42.059025+02
INFO:  committing data from 100 to 200 at 2021-04-14 17:35:42.070274+02
INFO:  committing data from 200 to 300 at 2021-04-14 17:35:42.07806+02
INFO:  committing data from 300 to 400 at 2021-04-14 17:35:42.087201+02
INFO:  committing data from 400 to 500 at 2021-04-14 17:35:42.096548+02
INFO:  committing data from 500 to 600 at 2021-04-14 17:35:42.105876+02
INFO:  committing data from 600 to 700 at 2021-04-14 17:35:42.114514+02
INFO:  committing data from 700 to 800 at 2021-04-14 17:35:42.121946+02
INFO:  committing data from 800 to 900 at 2021-04-14 17:35:42.12897+02
INFO:  committing data from 900 to 1000 at 2021-04-14 17:35:42.134388+02
INFO:  committing data from 1000 to 1100 at 2021-04-14 17:35:42.13951+02

..你可以批量更新行。为了更清楚地说明我的观点，以下查询按更新时间分组计算记录数：

SELECT DISTINCT ON (name) name, count(id)
FROM account 
GROUP BY name ORDER BY name;

                         name                         | count 
------------------------------------------------------+-------
 2021-04-14 17:35:42.059025+02 -> UPDATED 0 to 100    |   100
 2021-04-14 17:35:42.070274+02 -> UPDATED 100 to 200  |   100
 2021-04-14 17:35:42.07806+02 -> UPDATED 200 to 300   |   100
 2021-04-14 17:35:42.087201+02 -> UPDATED 300 to 400  |   100
 2021-04-14 17:35:42.096548+02 -> UPDATED 400 to 500  |   100
 2021-04-14 17:35:42.105876+02 -> UPDATED 500 to 600  |   100
 2021-04-14 17:35:42.114514+02 -> UPDATED 600 to 700  |   100
 2021-04-14 17:35:42.121946+02 -> UPDATED 700 to 800  |   100
 2021-04-14 17:35:42.12897+02 -> UPDATED 800 to 900   |   100
 2021-04-14 17:35:42.134388+02 -> UPDATED 900 to 1000 |   100
 2021-04-14 17:35:42.13951+02 -> UPDATED 1000 to 1100 |    51

演示: db<>fiddle
@codegeek 这很奇怪。您使用的是哪个postgres版本？您是否正在与同一事务中的其他查询一起运行代码块？这将引发异常！请参见我的db<>fiddle，我从其他语句中分离出了do块。它必须能够工作 :-D - Jim Jones
@JimJones，我喜欢你的回答，但我差点炸掉了数据库，你的查询有一个错别字，应该是AND id < j+page而不是AND j < j+page。 - Kamil Dziedzic
我发现的一个问题是进程占用了整个内存：114522 postgres 20 0 98G 33.4G 33.4G R 99.1 10.3 3:24.90。不知道为什么会这样... - Kamil Dziedzic
@KamilDziedzic 在 where 子句中的列是否正确索引？ - Jim Jones
1
是的，主键。不确定问题出在哪里，现在我也不打算去烦恼了，因为我已经完成了更新。你的查询非常有用。再次感谢！ - Kamil Dziedzic
显示剩余4条评论
2
您可以使用一个 过程 （从版本 11 开始可用），并逐个执行，例如：
CREATE or replace PROCEDURE do_update()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..55000000 -- 55 million, or whatever number you need
    LOOP 

        Update account set name = Some name where id = i;
        COMMIT;
        
        RAISE INFO 'id: %', i;
    END LOOP;
END;
$$;

CALL do_update();
0

设置测试环境：

DROP TABLE IF EXISTS account;
CREATE TABLE account(id integer, name text);

INSERT INTO account
VALUES (1, 'jonas'),(10002, 'petras');

更新脚本：

DO $$
DECLARE
  _id integer;
  _min_id integer;
  _max_id integer; 
  _batch_size integer = 10000;
BEGIN
  SELECT 
    MIN(id),
    MAX(id)
  INTO
    _min_id,
    _max_id
  FROM
    account;

  _id := _min_id;

  LOOP
    UPDATE account SET
      name = 'Some name' 
    WHERE id >=_id 
      AND id < _id + _batch_size;

    COMMIT;

    _id := _id + _batch_size;
    IF _id > _max_id THEN
      EXIT;
    END IF;
  END LOOP;
END;
$$;
0

但我们不想锁定我们的表。

在许多情况下都是有道理的，但您没有透露您的实际设置。您是否需要锁定？是否存在并发写入活动？如果没有，是否有足够的存储空间来写入另一个表的副本？然后，在后台构建一个新的原始更新表可能更可取，然后切换并删除旧表。请参见：

假设表中存在并发写入活动。您不希望太长时间阻塞太多写入活动。您希望重用死元组以保持表膨胀和索引膨胀。因此，分批更新是有意义的。您必须在批次之间进行COMMIT（和VACUUM），以便可以重用死元组占用的空间。并将写入分散到整个表中，以允许连续事务在同一块中产生和消耗死元组。

在Postgres 11或更高版本中，可以在DO语句中的过程或匿名代码块中使用事务控制语句（如COMMIT）。其他答案提供了使用该方法的解决方案。

autovacuum 应该使用激进的设置运行，及时释放死元组以便重用。或者在一定时间间隔内手动运行 VACUUM - 但是这不能（目前）在事务上下文中运行（仅作为单个命令），因此在 PL/pgSQL 循环中不可能实现。

Postgres 10 或更早版本

代码块中不允许进行事务控制。但是我们可以使用 dblink 模拟自主事务。请参见：

可能看起来像：

DO
$do$
DECLARE
   _cur  int := 0;  -- just start with 0 unless min is far off
   _step int := 10000;  -- batch size
   _max  CONSTANT int := (SELECT max(id) FROM account);  -- max id
   _val  CONSTANT text := 'SOME name';
BEGIN
   -- as superuser, or you must also provide the password for the current role;
   PERFORM dblink_connect('dbname=' || current_database());  -- current db

   LOOP
      RAISE NOTICE '%', _cur;
      PERFORM dblink_exec(  -- committed implicitly!
         $$
         UPDATE account
         SET    name = 'SOME name'
         WHERE  id BETWEEN _cur AND _cur + _step   -- gaps don't matter unless huge
         AND    name IS DISTINCT FROM 'SOME name'  -- avoid empty updates
         $$);

      
      _cur := _cur + _step;
      EXIT WHEN _cur > _max;          -- stop when done
   END LOOP;

   PERFORM dblink_disconnect();
END
$do$;

我还添加了另一个谓词：

     AND    name IS DISTINCT FROM 'SOME name'  -- avoid empty updates

为避免在行已经具有新名称的空更新时产生成本。只有当可能发生这种情况时才有用。详见:

您可能希望进一步拆分它，并在其间运行 VACUUM。并且您可能希望使用一些其他的选择列而不是id（一个不是聚集的）以便获得整个表格的广泛分布。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接