postgresql - 如何从经常访问的表中删除多行

Question

我需要删除一个非常大的表(比如 500 万行)的大部分(比如 90%)。该表的另外 10% 经常被读取，但未被写入。

从“Best way to delete millions of rows by ID”中，我收集到我应该删除我正在删除的 90% 上的任何索引，以加快该过程(我用来选择要删除的行的索引除外)。

从“PostgreSQL locking mode”中，我看到此操作将获取整个表的 ROW EXCLUSIVE 锁。但由于我只阅读另外 10%，这应该无关紧要。

那么，用一个命令删除所有内容是否安全(即 DELETE FROM table WHERE delete_flag='t')？我担心如果删除一行失败，触发巨大 回滚，那么它会影响我从表中读取的能力。批量删除会不会更明智？

Answer 1

1. 对于所有行中 90% 的操作，索引通常是无用的。无论哪种方式，顺序扫描都会更快。 (特殊异常(exception)情况适用。)

2. 如果需要允许并发读取，则不能在表上使用排它锁。因此，您也不能在同一事务中删除任何索引。

3. 您可以在单独的事务中删除索引，以将独占锁的持续时间保持在最短。在 Postgres 9.2 或更高版本中，您还可以使用 DROP INDEX CONCURRENTLY ，它只需要最少的锁。稍后使用 CREATE INDEX CONCURRENTLY 在后台重建索引 - 并且只需要一个非常简短的独占锁。

如果您有一个稳定的条件来确定保留的 10%(或更少)的行，我会建议 partial index仅在那些行上获得最佳的两行:

• 读取查询可以随时快速访问表(使用部分索引)。
• 大的 DELETE 根本不会修改部分索引，因为 DELETE 中没有涉及任何行。

CREATE INDEX foo (some_id) WHERE delete_flag = FALSE;

假设 delete_flag 是 boolean。您必须在查询中包含相同的谓词(即使它在逻辑上看起来是多余的)以确保 Postgres 可以使用部分索引。