PostgreSQL重建索引 vs 删除并创建

Question

这些命令有什么区别：

reindex database my_db

并且

drop index my_index;
CREATE INDEX index1 ON schema1.table1 USING btree (table1_key);

您在数据库中为每个索引执行第二条命令的位置是哪里？

-----编辑-----

有一个答案指出，这两个命令具有不同的锁定行为。除了不同的锁定之外，这些命令在如何操作数据库方面是否有任何区别？例如，如果我复制了一个虚拟机并执行了命令A（重新索引），然后又复制了一份虚拟机，还原了原始副本的虚拟机，执行了命令B（删除和创建所有索引），然后再次复制，那么经过A和B之后的数据库是否相同？如果不同，会有什么原因导致差异？

- zelinka

---

根据您的编辑 - 没有像您所建议的那样记录的行为，因此确定的唯一方法是执行您自己的测试。 - Nick

一个可能成为dba.SE的候选人。 - Erwin Brandstetter

Answer 1

它们略有不同。 REINDEX 锁定写操作，但不锁定读操作。 DROP INDEX 锁定写和读操作，然后 CREATE INDEX 仅锁定写操作。

REINDEX 类似于删除并重新创建索引，因为索引内容将从头开始重建。但是，锁定方面的考虑却相当不同。REINDEX 锁定索引所属表的读操作，但不锁定写操作。它还在正在处理的特定索引上获取排他锁，这将阻止试图使用该索引的读操作。相反，DROP INDEX 短暂地对父表获取排他锁，阻止写操作和读操作。随后的 CREATE INDEX 锁定写操作，但不锁定读操作；由于索引不存在，因此没有读操作会尝试使用它，这意味着不会有阻塞，但读操作可能被迫进入昂贵的顺序扫描。

来源：https://www.postgresql.org/docs/9.5/static/sql-reindex.html（注释部分）

- Nick

---

此外，您可以使用“concurrently”选项创建索引，然后删除旧索引并将新索引重命名为旧索引。这样做会增加构建时间，但几乎不会影响在线重建的最小锁定时间。另一个drop/create的重要优势是，您可以使用它来更改索引位置，而reindex只能重新构建索引。 - Vao Tsun