列 / 行
......我不需要在整个操作中维护事务完整性,因为我知道我正在更改的列在更新期间不会被写入或读取。
PostgreSQL的MVCC模型中的任何UPDATE
都会写入整行的新版本。如果并发事务更改同一行的任何列,则会出现耗时的并发问题。详见手册。 知道并发事务不会触及相同的列可以避免某些可能的复杂情况,但不能避免其他情况。
索引
为避免偏离主题的讨论,让我们假设35百万列的所有状态值当前都设置为相同的(非空)值,从而使索引无用。
当更新整个表(或其主要部分)时,Postgres从不使用索引。当需要读取所有或大多数行时,顺序扫描更快。相反:索引维护意味着对UPDATE
的额外成本。
性能
例如,假设我有一个名为“orders”的表,其中包含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操作不可见。表中的后续插入和更新操作将为该列存储空值。因此,删除列很快,但不会立即减少表的磁盘大小,因为已删除列所占用的空间不会被回收。随着现有行的更新,该空间将逐渐回收。
确保没有对象依赖于该列(外键约束、索引、视图等)。您需要删除/重新创建这些对象。除此之外,在系统目录表pg_attribute
上进行微小的操作即可完成任务。需要对表进行独占锁定,这可能会对重负载产生问题。(正如Buurman在他的评论中强调的那样。)如果没有其他选择,该操作只需要几毫秒。
如果您有一个要保留的列默认值,请在单独的命令中添加它。在同一命令中执行会立即将其应用于所有行。请参见:
要实际应用默认值,请考虑分批处理:
通用解决方案
更新:自Postgres 11以来,您可以使用PROCEDURE
来实现此目的,其中您可以包含事务控制语句,如COMMIT
。请参见:
dblink
已在另一个回答中提到。它允许在隐式独立连接中访问“远程”Postgres数据库。“远程”数据库可以是当前数据库,从而实现“自主事务”:函数在“远程”数据库中编写的内容已提交,不能回滚。
这使得可以运行单个函数,以小部分方式更新大表格,并且每个部分都会单独提交。避免为非常多的行建立事务开销,更重要的是,在每个部分之后释放锁定。这允许并发操作继续进行,几乎没有延迟,并使死锁的可能性更小。
如果您没有并发访问,则这几乎没有用 - 除了避免异常后的ROLLBACK
。对于这种情况,请考虑SAVEPOINT
。
免责声明
首先,大量的小事务实际上是更昂贵的。这仅对大型表格有意义。最佳选择取决于许多因素。
如果您不确定自己在做什么:
单个事务是安全的方法。为了使其正常工作,表上的并发操作必须相互配合。例如:并发写操作可能会将某行移动到已经处理过的分区中。或者并发读操作可能会看到不一致的中间状态。
你已经被警告了。
逐步说明
首先需要安装附加模块 dblink:
使用 dblink 建立连接非常依赖于您的 DB 集群设置和现有的安全策略。这可能有些棘手。相关的后续答案提供了更多如何使用 dblink 进行连接的信息:
创建一个
FOREIGN SERVER
和一个
USER MAPPING
,按照指示进行操作,以简化和优化连接(除非您已经有了一个)。假设存在一个带有或不带有一些间隔的
serial PRIMARY KEY
。
CREATE OR REPLACE FUNCTION f_update_in_steps()
RETURNS void AS
$func$
DECLARE
_step int;
_cur int;
_max int;
BEGIN
SELECT INTO _cur, _max min(order_id), max(order_id) FROM orders;
_step := ((_max - _cur) / 100) + 1;
PERFORM dblink_connect('myserver');
FOR i IN 0..200 LOOP
PERFORM dblink_exec(
$$UPDATE public.orders
SET status = 'foo'
WHERE order_id >= $$ || _cur || $$
AND order_id < $$ || _cur + _step || $$
AND status IS DISTINCT FROM 'foo'$$);
_cur := _cur + _step;
EXIT WHEN _cur > _max;
END LOOP;
PERFORM dblink_disconnect();
END
$func$ LANGUAGE plpgsql;
调用:
SELECT f_update_in_steps();
您可以根据需要对任何部分进行参数化:表名、列名、值等,但一定要对标识符进行清理以避免 SQL 注入:
避免空 UPDATE: