如何在PostgreSQL上安全地重新索引主键？

Question

我们有一个包含主键索引膨胀的大表。我们不断地对该表归档旧记录。

我们通过同时重建索引并删除旧索引的方式重新索引其他列，以避免干扰生产流量。但是对于主键来说，这是不可能的，因为依赖它的外键太多，至少根据我们所尝试的。

如何安全地重新索引主键而不阻塞对表上的DML语句？

- froi

---

你想将PK序列重置为较小的数字，还是想刷新PK的基础唯一索引？ - jlandercy

reindex 函数有什么问题吗？ - user330315

@jlandercy 不是重置主键计数的问题，而是仅仅移除主键索引上的冗余数据。 - froi

然后创建一个新的唯一索引（并发）。当完成时，删除旧的 PK 并使用新创建的索引创建一个新的 PK。在事务中执行索引与 PK 的“交换”，以避免无效数据。 - user330315

在交换过程中，如何解决外键依赖关系？目前，由于这个原因，它不允许我仅删除主键。我必须重新创建所有的外键，然后才能进行交换吗？ - froi

Answer 1

REINDEX CONCURRENTLY似乎也可以使用。我在我的数据库上尝试了一下，没有收到任何错误。

REINDEX INDEX CONCURRENTLY <indexname>;

我认为它可能类似于@jlandercy在他的答案中描述的内容。在重建索引时，我看到了一个后缀为_ccnew的索引，并且现有的索引也完好无损。最终，我猜测在删除旧索引后，该索引被重命名为原始索引，并最终在我的表上看到了唯一的主索引。

我正在使用postgresv12.7。

- amit_saxena

---

CONCURRENTLY 选项自 V12 版本开始提供，早期版本没有此功能。 - EAmez

Answer 2

您可以使用pg_repack来完成这个任务。 pg_repack是一个PostgreSQL扩展程序，可以让您从表和索引中删除膨胀，并可选择恢复聚簇索引的物理顺序。

在整个过程中，它不会持有排他锁。尽管它仍然会执行一些锁定操作，但这应该只需要很短的时间。您可以在此处查看详细信息：https://reorg.github.io/pg_repack/ 要对索引执行重新打包操作，您可以尝试：

pg_repack -t table_name --only-indexes

- len

Answer 3

简述

只需使用索引名称将其重新索引即可，像其他索引一样：reindex

REINDEX INDEX <indexname>;

MCVE

让我们创建一个带有主键约束且同时也是索引的表：

Original Answer翻译成："最初的回答"

CREATE TABLE test(
    Id BIGSERIAL PRIMARY KEY
);

看目录，我们可以看到约束名称为：

Original Answer

SELECT conname FROM pg_constraint WHERE conname LIKE 'test%';
-- "test_pkey"

Having the name of the index, we can reindex it:

REINDEX INDEX test_pkey;

你可以在创建时固定约束名称：

同时，你也可以在创建时固定约束名称：

CREATE TABLE test(
    Id BIGSERIAL NOT NULL
);
ALTER TABLE test ADD CONSTRAINT myconstraint PRIMARY KEY(Id);

如果必须处理并发问题，那么请使用方法a_horse_with_no_name建议的方式，同时创建唯一索引。最初的回答。

-- Ensure Uniqueness while recreating the Primary Key:
CREATE UNIQUE INDEX CONCURRENTLY tempindex ON test USING btree(Id);
-- Drop PK:
ALTER TABLE test DROP CONSTRAINT myconstraint;
-- Recreate PK:
ALTER TABLE test ADD CONSTRAINT myconstraint PRIMARY KEY(Id);
-- Drop redundant Index:
DROP INDEX tempindex;

检查索引是否存在：

SELECT * FROM pg_index WHERE indexrelid::regclass = 'tempindex'::regclass

- jlandercy

---

你可以执行以下操作：ALTER TABLE test DROP CONSTRAINT myconstraint, ADD CONSTRAINT myconstraint PRIMARY KEY(Id) using index tempindex;。这是事务性的，不需要手动drop index。 - user158037

能否同时重新索引主键？ - Leonardo