时间戳

首页 > 解决方案 > 如何安全地重新索引 postgres 上的主键？

postgresql - 如何安全地重新索引 postgres 上的主键？

问题描述

我们有一个巨大的表，其中包含主键索引的膨胀。我们不断地将旧记录存档在该表上。

我们通过同时重新创建索引并删除旧索引来重新索引其他列。这是为了避免干扰生产流量。但这对于主键是不可能的，因为有外键依赖于它。至少基于我们已经尝试过的。

在不阻塞表上的 DML 语句的情况下安全地重新索引主键的正确方法是什么？

标签： postgresql

解决方案

TL;博士

只需使用其索引名称将其重新索引为其他索引：

REINDEX INDEX <indexname>;

MCVE

让我们创建一个具有主键约束的表，该约束也是一个索引：

CREATE TABLE test(
    Id BIGSERIAL PRIMARY KEY
);

查看目录，我们看到约束名称：

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

有了索引的名称，我们可以重新索引它：

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

推荐阅读