如果您需要
更新 “成百万个”活跃使用的PostgreSQL表中
的大量记录 ，该怎么做（绝对不是）—初始化新字段的值或更正现有记录中的错误？ 同时节省您的时间，不会因停机而损失公司资金。
准备测试数据：
CREATE TABLE tbl(k text, v integer); INSERT INTO tbl SELECT chr(ascii('a'::text) + (random() * 26)::integer) k , (random() * 100)::integer v FROM generate_series(1, 1000000) i;
假设我们只想对k在'q'..'z'范围内的所有记录将v的值增加1。
但是，在开始实验之前，我们将保存原始数据集，以便每次都能获得“干净”的结果：
CREATE TABLE _tbl AS TABLE tbl;
更新：一劳永逸，一劳永逸
立即想到的最简单的选择是“一次更新”进行所有操作：
UPDATE tbl SET v = v + 1 WHERE k BETWEEN 'q' AND 'z';
[看explain.tensor.ru]看起来，在完全“短”线上的操作相当简单，耗时超过2.5秒。 而且，如果您的表达方式更加复杂，则该行会更真实，记录更多，甚至会有一些触发器介入-时间可能不会增加到几分钟，而会增加到几个小时。 假设您准备好等待，并且系统的其余部分是否已连接到该基础（如果它具有活动的OLTP负载）？
问题在于，一旦UPDATE到达特定记录，它将立即
阻止它直到执行结束 。 如果他希望与同一记录同时进行并行启动的“现场”更新，则他仍将
“挂在钩子上”等待更新请求
的块 ，并将下垂直到工作结束。
©
wumo.com/wumo最糟糕的情况是Web系统，其中需要根据需要创建与数据库的连接-毕竟，这种“悬而未决的”连接会累积起来，如果您不为此建立单独的防御机制，则会吞噬数据库和客户端的资源。
分割交易
通常，如果所有事情都在一个请求中完成，那么一切都不是很好。 是的，即使我们将一个大的UPDATE分成几个小的UPDATE，但让它们全部
在一个事务中工作，但锁定的问题将保持不变，因为可变记录将被锁定直到整个事务结束。
因此，我们需要将一项大交易分成几笔。 为此，我们既可以使用外部手段，编写某种脚本来生成单独的事务，也可以利用数据库本身可以为我们提供的机会。
通话和交易管理
从PostgreSQL 11开始，
可以在程序代码内
直接管理事务 ：
在CALL命令调用的过程以及匿名代码块（在DO命令中）中，您可以通过执行COMMIT和ROLLBACK来完成事务。 通过这些命令完成事务后，将自动启动新的事务。
但是此版本并不是所有人都可以使用，因此使用CALL有其局限性。 因此，我们将尝试在没有外部手段的情况下解决问题，从而使其能够在所有当前版本上运行，甚至只需对服务器本身进行最少的更改即可-从而无需编译和重新启动任何程序。
出于同样的原因，我们将不考虑
通过pg_background组织
自主交易的选择 。
在“内部”管理连接
PostgreSQL过去一直使用不同的方法来
模拟自主事务 ，并通过其他过程语言或标准
dblink模块生成单独的其他连接。 后者的优点是，默认情况下，它包含在大多数发行版中，并且只需一个命令即可在数据库中激活它：
CREATE EXTENSION dblink;
“ ...带来了许多很多令人恶心的孩子”
但是，在创建dblink绑定之前，我们首先要弄清楚“常规开发人员”如何将需要更新的大型数据集分解为小的数据集。
天真LIMIT ... OFFSET
第一个想法是进行“分页”搜索：通过在每个新请求中增加OFFSET，
“让我们每次都选择下一个千条记录 ” ：
UPDATE tbl T SET v = Tv + 1 FROM ( SELECT k , v FROM tbl WHERE k BETWEEN 'q' AND 'z' ORDER BY
在测试此解决方案的性能之前，我们将还原数据集：
TRUNCATE TABLE tbl; INSERT INTO tbl TABLE _tbl;
正如我们在上述计划中所看到的，我们将需要更新大约38.4万条记录。 因此，让我们立即看看如何在末尾进行更新-
在1000个条目的第300次迭代区域 ：
[看explain.tensor.ru]哦……在
整个1K记录的末尾更新样本将花费我们几乎与
整个原始版本一样的时间！
这不是我们的选择。 如果迭代次数少且偏移值较小，仍然可以使用它。 因为数据库的
LIMIT X OFFSET Y等效于“
减去/选择/形成第一个X + Y记录，然后将第一个Y扔到垃圾箱 ”，这对于大的Y值看起来是悲惨的。
实际上，这种方法
根本不能应用 ！ 我们不仅要依赖更新的值来进行选择，而且还冒着跳过部分记录的风险，如果具有相同键的块到达页面边界，则有可能更新另一部分两次：
在此示例中，我们更新了绿色记录两次，而从未更新红色记录。 仅仅因为排序键的值相同，在这样的块中记录本身的顺序就不固定。
可悲的ORDER BY ... LIMIT
让我们稍微修改一下任务-添加一个新字段，将值v + 1写入其中：
ALTER TABLE tbl ADD COLUMN x integer;
请注意，这种设计几乎可以立即生效，而无需重写整个表格。 但是，如果您添加DEFAULT值，则-仅从
第11版开始 。
已经经历了痛苦的经历，让我们立即创建一个索引，其中仅保留未初始化的条目：
CREATE INDEX CONCURRENTLY ON tbl(k, v) WHERE x IS NULL;
CONCURRENTLY索引不会阻止对表的读写工作，尽管它甚至会缓慢滚动到庞大的数据集上。
现在的想法是
“让我们每次都只从头几千条记录中选择索引” ：
UPDATE tbl T SET x = Tv + 1 FROM ( SELECT k, v FROM tbl WHERE k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1000
[看explain.tensor.ru]现在已经好多了-每笔交易的持续时间现在缩短了大约6倍。
但是，让我们再次看看第200次迭代的计划将变成什么：
Update on tbl t (actual time=530.591..530.591 rows=0 loops=1) Buffers: shared hit=789337 read=1 dirtied=1
时间再次恶化（尽管只有25％），缓冲区增加了-但是为什么呢？
事实是，
PostgreSQL中的
MVCC在索引中留下了“死灵”-已经更新的记录的版本，现在不再适合该索引。 也就是说，在第200次迭代中仅获取前1000条记录，我们
仍然会扫描 ，即使稍后，我们也会丢弃之前已更改的199K版本的元组。
如果我们需要的迭代次数不是数百次，而是数十万次，那么每次执行后续查询时，降级都会更加明显。
按细分更新
实际上，为什么我们如此重视“ 1000条记录”的值？ 毕竟，我们
没有理由选择正好1000或其他特定数字。 我们只是想将整个数据集“切”为一些（不一定相等）
不相交的部分 -因此，让我们将现有索引用于其预期目的。
索引对（k，v）非常适合我们的任务。 让我们构建一个查询，以便它可以基于最后处理的对：
WITH kv AS ( SELECT k, v FROM tbl WHERE (k, v) > ($1, $2) AND k BETWEEN 'q' AND 'z' AND x IS NULL ORDER BY k, v LIMIT 1 ) , upd AS ( UPDATE tbl T SET x = Tv + 1 WHERE (Tk, Tv) = (TABLE kv) AND Tx IS NULL RETURNING k, v ) TABLE upd LIMIT 1;
在第一次迭代中，将查询参数设置为
“零”值（''，0）就足够了，对于下一次迭代，我们将获取
上一个查询的
结果 。
[看explain.tensor.ru]事务/锁定时间小于一毫秒，迭代次数不会降低性能，不需要对表中的所有数据进行全面的初步扫描。 太好了！
使用dblink放置最终版本 DO $$ DECLARE k text = ''; v integer = 0; BEGIN PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
此方法的另一个优点是可以随时中断该脚本的执行，然后从所需位置恢复执行。
UPDATE中的复杂计算
当您需要从链接表中计算出一些值时，我将单独提到这种情况，即分配值的计算很困难。
花在计算上的时间也增加了交易的持续时间。 因此，最好的选择是在UPDATE之外
进行计算这些值的过程 。
例如，我们要用具有相同值（k，v）的记录数填充新字段x。 让我们创建一个“临时”表，该表的生成没有施加其他锁：
CREATE TABLE tmp AS SELECT k, v, count(*) x FROM tbl GROUP BY 1, 2; CREATE INDEX ON tmp(k, v);
现在，我们可以根据此表根据上述模型进行迭代，以更新目标：
UPDATE tbl T SET x = Sx FROM tmp S WHERE (Tk, Tv) = (Sk, Sv) AND (Sk, Sv) = ($1, $2);
如您所见，不需要复杂的计算。
只记得稍后删除辅助表。