我在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时，这也将简化情况，因为我们可以截断表(将磁盘上的表大小归零，我们甚至不必清理空间即可)

如果我们要重设整个表，而不仅仅是重设几行，通常只需在创建所有选择的表时CREATE TABLE bar即可(在非常大的数据集上，而不是在常规表上)，这样会更快(但要复制，0 F​​ROM foo)，然后交换表并删除旧表。显然，在执行此操作时，我们需要确保没有任何内容插入到原始表中。我们还需要重新创建该索引。

编辑：一个简单的改进，以避免在复制14 GB时锁定表：

lock ;
create a new table, bar;
swap tables so that all writes go to bar;
unlock;
create table baz as select from foo;
drop foo;
create the index on baz;
lock;
insert into baz from bar;
swap tables;
unlock;
drop bar;

(让我们在进行复制时进行写操作，并在事后将其插入)。

这是伪代码。我们将需要400MB(用于ints)或者800MB(用于bigints)临时文件(如果出现问题，可以使用zlib对其进行压缩)。一张桌子大约需要扫描100次以获取真空。但这不会使表膨胀超过1％(任何时候最多不超过1000000条死行)。我们也可以将较少的扫描交易换成更多的表格。

// write all ids to temporary file in disk order                
// no where clause will ensure disk order
$file = tmpfile();
for $id, $replicated in query("select id, replicated from table") {
        if ( $replicated<>0 ) {
                write($file,&$id,sizeof($id));
        }
}

// prepare an update query
query("prepare set_replicated_0(bigint) as
        update table set replicated=0 where id=?");

// reread this file, launch prepared query and every 1000000 updates commit
// and vacuum a table
rewind($file);
$counter = 0;
query("start transaction");
while read($file,&$id,sizeof($id)) {
        query("execute set_replicated_0($id)");
        $counter++;
        if ( $counter % 1000000 == 0 ) {
                query("commit");
                query("vacuum table");
                query("start transaction");
        }
}
query("commit");
query("vacuum table");
close($file);

虽然我们不可能解决空间使用的问题(这是暂时的，直到出现真空)，但我们实际上可以在时钟时间方面加快处理速度。 PostgreSQL使用MVCC的事实意味着我们应该能够做到这一点，而不会出现与新插入的行相关的任何问题。 select创建表将解决一些性能问题，但不允许继续使用该表，并且占用的空间也一样。只需抛开索引，然后重建索引，然后进行清理即可。

drop index replication_flag;
update big_table set replicated=0;
create index replication_flag on big_table btree(ID) WHERE replicated=0;
vacuum full analyze big_table;

我认为最好将Postgres更改为8.X版。原因可能是Postgres的版本太低。也可以在下面尝试此查询。希望对我们有所帮助。

UPDATE table1 SET name = table2.value
FROM table2 
WHERE table1.id = table2.id;

我想你需要做的是
一种。将2000条记录的PK值复制到具有相同标准限制的临时表中，依此类推。
b。选择相同的2000条记录，并按原样在游标中执行必要的操作。
C。如果成功，请对临时表中的记录运行单个更新查询。清除临时表并再次运行步骤a。
d。如果不成功，请清除临时表而不运行更新查询。
简单，高效，可靠。
问候，
韩国电信