sql - 有效地更新非常大的PostgreSQL数据库表

我在PostgresQL中有一个非常大的数据库表，并且有一个类似“复制”的列。每个新行都开始未复制，以后将由后台程序复制到另一行。该表上有一个部分索引“btree(ID)WHEREplicated = 0”。后台程序最多选择2000个条目(LIMIT 2000)，对其进行处理，然后使用2000个准备好的sql命令在一个事务中提交更改。

现在，我要给用户一个选项来重置此复制值的问题，然后将其全部设为零。

更新表集已复制= 0；

不可能:

这需要很多时间

由于MVCC

，它复制了表格的大小

在一个事务中完成:它要么失败，要么通过。

在这种情况下，我实际上不需要事务处理功能:如果系统出现故障，它将仅处理其中的一部分。

其他几个问题:
做一个

update set replicated=0 where id >10000 and id<20000

也是不好的:它对整个表进行顺序扫描，这太慢了。
如果不这样做，它仍然会很慢，因为它会进行过多的搜索。

我真正需要的是一种遍历所有行，对其进行更改且不受大型交易约束的方法。

奇怪的是

UPDATE table
  SET replicated=0
WHERE ID in (SELECT id from table WHERE replicated= LIMIT 10000)

速度也很慢，尽管这应该是一件好事:以DISK顺序浏览表格...

(请注意，在这种情况下，还有一个索引涵盖了此内容)

(PostgresQL无法使用像Mysql这样的更新LIMIT)

顺便说一句:真正的问题更加复杂，我们在这里谈论的是已经部署的嵌入式系统，因此很难更改远程模式。
不幸的是它是PostgresQL 7.4。

我正在谈论的行数例如90000000。数据库的大小可以是几十个千兆字节。

数据库本身仅包含5个表，一个表非常大。
但这并不是一个坏的设计，因为这些嵌入式盒只能在一种实体上运行，而不是ERP系统或类似的系统!

有任何想法吗？

最佳答案

如何添加新表来存储此复制值(以及将每个记录链接到主表的主键)。然后，您只需为每个复制项添加一条记录，然后删除记录以删除复制标志。 (或者反之亦然-每个未复制记录的记录，这取决于常见情况)。

当您要将它们全部重新设置为0时，这也将简化情况，因为您可以截断表(这会将磁盘上的表大小归零，您甚至不必抽空来释放空间)。

关于sql - 有效地更新非常大的PostgreSQL数据库表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/112249/