PostgreSQL 部分索引与软删除

示例说明

软删除表里九成以上可能是已删除的历史行——全量索引把这些死数据全背着，每次查询都在放大体积里捞小集合。部分索引的 WHERE 谓词把索引内容限定在活行：体积随热数据缩放，B 树更浅查找更快，VACUUM 回收死行的负担也更小。唯一约束同理：同标题唯一只约束未删除文档。

技术要点

• 索引谓词限定只收活行
• 查询条件须与谓词精确匹配
• 唯一性按活行集合生效

代码解读

两个索引都以 deleted_at IS NULL 为谓词：复合索引服务按所有者查活跃文档的主路径，排序键内建让 ORDER BY 免排序；唯一索引把软删除的老标题从约束里排除——重新创建同名文档不会被自己删除过的行卡住。归档查询明确不匹配谓词，自动走其他计划。

使用建议

部分匹配的谓词不会被采用——写查询时逐字对齐索引条件。定期物理归档已删除行防表无限膨胀。谓词变动需要重建索引。

代码示例

-- PostgreSQL 软删除表：业务查询永远附加 deleted_at IS NULL
CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  owner_id bigint NOT NULL,
  title text NOT NULL,
  deleted_at timestamptz
);

-- 部分索引只覆盖活行：体积与热数据成正比
CREATE INDEX idx_documents_owner_live
  ON documents (owner_id, created_at DESC)
  WHERE deleted_at IS NULL;

-- 命中部分索引：条件必须与索引谓词精确匹配
SELECT id, title
FROM documents
WHERE owner_id = 42
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 10;

-- 归档查询走另一条路：不占热索引的空间
SELECT id, deleted_at
FROM documents
WHERE owner_id = 42
  AND deleted_at IS NOT NULL
ORDER BY deleted_at DESC
LIMIT 100;

-- 唯一性也按活行生效：同 owner 的活文档标题唯一
CREATE UNIQUE INDEX idx_documents_title_live
  ON documents (owner_id, title)
  WHERE deleted_at IS NULL;