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

我在PostgresQL中有一个非常大的数据库表和一个像"复制"的列.每个新行开始都是未复制的,稍后将通过后台程序复制到另一个东西.该表上有一个部分索引"btree(ID)WHERE replicated = 0".后台程序最多选择2000个条目(LIMIT 2000),对它们起作用,然后使用2000个准备好的sql-commands在一个事务中提交更改.

现在的问题是我想给用户一个重置这个复制值的选项,让它再次为零.

更新表集已复制= 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顺序浏览表...

(注意,在那种情况下,还有一个覆盖这个的索引)

(像Mysql这样的更新LIMIT不适用于PostgresQL)

BTW:真正的问题更复杂,我们在谈论已经部署的嵌入式系统,因此远程架构更改很困难,但遗憾的是它可能是PostgresQL 7.4.

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

数据库本身只包含5个表,一个是非常大的表.但这不是一个糟糕的设计,因为这些嵌入式盒子只能运行一种实体,它不是ERP系统或类似的东西!

有任何想法吗？

如何添加新表来存储此复制值(以及将每个记录链接到主表的主键).然后,您只需为每个复制项添加一条记录,并删除记录以删除复制的标记.(或者反过来说 - 每个非复制记录的记录,取决于常见情况).

当你想要将它们全部设置为0时,这也会简化这种情况,因为你可以截断表(它将磁盘上的表大小归零,你甚至不需要真空来释放空间)