中文
devexamples

PostgreSQL Partial Index Soft Delete

Index only live rows with a partial index over soft-deleted tables, keeping hot queries small and vacuum friendly.

SqlData Integrity

Code

snippet
-- 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;

Related Examples