如何在PostgreSQL中进行大规模的非阻塞更新操作？

11 浏览
0 Comments

如何在PostgreSQL中进行大规模的非阻塞更新操作？

我想在PostgreSQL的一张表上进行大规模更新，但我不需要整个操作维持事务的一致性，因为我知道在更新期间，我要更改的列不会被写入或读取。我想知道在psql控制台中是否有一种简单的方法可以加速这些类型的操作。

例如，假设我有一张名为"orders"的表，有3500万行，我想执行以下操作：

UPDATE orders SET status = null;

为了避免偏离主题的讨论，让我们假设这3500万行中的所有status值都当前设置为相同的（非空）值，因此索引无效。

这个语句的问题在于它需要很长时间才能生效（仅因为锁定），并且在整个更新完成之前，所有更改的行都会被锁定。这个更新可能需要5个小时，而类似于以下语句：

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

可能只需要1分钟。在3500万行中，将上述操作分成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中实现我想要的方法？

0

3 答案

0 Comments

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

问题的原因：

1. 在PostgreSQL的MVCC模型中，任何UPDATE操作都会写入整个行的新版本。如果并发事务更改同一行的任何列，就会出现耗时的并发问题。

2. 当更新整个表（或其大部分）时，Postgres永远不会使用索引。当需要读取所有或大部分行时，顺序扫描更快。相反，索引维护对于UPDATE操作而言是额外的开销。

解决方法：

1. 如果并发事务不会触及相同的列，可以避免一些潜在的问题，但并非全部。

2. 对于更新整个表的情况，可以使用ALTER TABLE命令来优化操作。可以通过删除并重新添加列的方式来更新行，而无需进行整个表的重写。这个操作只需要几毫秒的时间。

3. 对于更一般的解决方案，可以使用Postgres 11引入的PROCEDURE来执行大规模的非阻塞更新。PROCEDURE允许在函数中包含事务控制语句，如COMMIT。

4. 另一种解决方法是使用dblink模块，它允许在隐式的单独连接中访问“远程”的Postgres数据库。通过将函数写入“远程”数据库中，可以将大表的更新分解为较小的部分并分别提交。这样可以避免为大量行建立事务开销，并且在每个部分之后释放锁定，减少死锁的可能性。

步骤：

1. 首先需要安装dblink模块。

2. 使用dblink设置连接。

3. 创建一个FOREIGN SERVER和一个USER MAPPING来简化和优化连接。

4. 创建一个函数，将大表的更新分解为较小的步骤，并使用dblink执行每个步骤的更新操作。

5. 调用该函数来执行更新。

注意事项：

1. 大量的小事务实际上更加昂贵，这种方法只适用于大表。

2. 如果不确定操作的安全性，请使用单个事务来执行更新。这样可以确保并发操作的正确性。

3. 在进行ALTER TABLE操作时，可能会对整个表施加独占锁定，这可能会导致操作等待锁定的时间较长，阻塞其他访问操作。

以上是关于如何在PostgreSQL中执行大规模的非阻塞更新的原因和解决方法的总结。通过使用适当的方法，可以高效地处理大规模数据更新的问题。

0
0 Comments

问题的出现原因是PostgreSQL使用MVCC（多版本并发控制），因此避免了任何锁定，如果只有一个写入者，任意数量的并发读取者可以在表上工作，并且不会有任何锁定。如果实际上需要5个小时，那么肯定是因为其他原因（例如，你声称没有并发写入，但实际上却有）。

解决方法之一是在更新之前获取表锁，排除任何其他类型的操作干扰。然后，您应该能够在没有任何干扰的情况下完成此更新。但是，这种方法可能会导致系统暂停直到更新完成，而且每次想要执行这种更新时都需要编写脚本，这是一种繁琐的方法。

另外两种解决方法是：

1. 使用批量更新（Bulk Update）：将要更新的数据分成较小的批次，每次更新一批数据，以减少单次更新的时间。

2. 使用并行更新（Parallel Update）：将要更新的数据分成多个并行任务，每个任务负责更新一部分数据，通过并行处理来提高更新速度。

这两种解决方法不会阻止系统正常运行，且可以显著提高更新速度。但是，这些方法可能需要编写一些额外的代码来实现。

0
0 Comments

问题的出现原因：

在PostgreSQL中进行大规模非阻塞更新时，可能会遇到性能问题和延迟的情况。

解决方法：

可以将需要进行大规模非阻塞更新的列委派到另一个表中，通过以下步骤实现：

1. 创建一个新的表，用于存储需要进行大规模非阻塞更新的列。

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

2. 当需要将某个列的值设置为NULL时，可以通过快速截断(truncate)操作来实现即时更新。

   truncate order_status;

通过上述方法，可以有效地解决在PostgreSQL中进行大规模非阻塞更新时的性能问题和延迟情况。

0