在 Postgres 分区上强制执行约束Postgres 分区提供了显著的性能和维护优势，但跨分区的唯一约束需要包含分区

在 Postgres 分区上强制执行约束

摘要： Postgres 分区提供了显著的性能和维护优势，但跨分区的唯一约束需要包含分区键——这一限制阻止了在诸如 deduplication_id 之类的列上强制执行全局唯一性。本文探讨了使用触发器和专用去重表的实际解决方案，并比较了它们的性能和可扩展性权衡。

原文链接

Postgres 表分区是一项在顺境中像是超级能力的功能。只需定义一个分区键，将数据分割成可管理的块，一切都会顺利进行。还有什么不爱呢？查询计划中的分区裁剪、更小的表、更快的维护、轻松归档旧数据，这些都是便利的盛宴。

然而，当你尝试在不包含分区键的情况下强制执行唯一约束时，Postgres 的反应就像你刚才让它除以零一样。好吧......关于这一点。

没人读的那条规则

Postgres 分区文档在限制部分明确说明了这一点：

要在分区表上创建唯一约束或主键约束，分区键不得包含任何表达式或函数调用，且约束的列必须包含所有分区键列。

再读一遍。约束的列必须包含所有分区键列。不是"应该"。不是"如果能包含就好了"。是"必须"。而且理由令人抓狂地合理：每个分区维护自己的索引，而本地索引只能在其自己的分区内强制执行唯一性。Postgres 没有跨所有分区同时存在的全局索引的概念，因此它没有机制来检查分区 A 中的某个值是否已存在于分区 B 中。

其他数据库引擎（如 Oracle）有在存储层解决此问题的全局索引。Postgres 没有，而且邮件列表上也没有任何添加全局索引的实质性进展。所以我们只能靠自己的设备。

当理论遇到事件管道

考虑一个相当常见（如果有些人为构造）的场景：一个按 identity 列进行范围分区的 events 表。该表包含一个 deduplication_id，应用程序用它来防止重复事件处理。自然，这应该在所有分区上唯一。

CREATE TABLE events (
  id               BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  deduplication_id BIGINT NOT NULL,
  payload          TEXT NOT NULL,
  event_date       TIMESTAMPTZ NOT NULL DEFAULT now()
) PARTITION BY RANGE (id);

CREATE TABLE events_0m
PARTITION OF events
FOR VALUES FROM (1) TO (1000000);

CREATE TABLE events_1m
PARTITION OF events
FOR VALUES FROM (1000000) TO (2000000);

CREATE TABLE events_2m
PARTITION OF events
FOR VALUES FROM (2000000) TO (3000000);

现在尝试在不报错的情况下向该表添加 UNIQUE (deduplication_id)。分区键是 id，而 deduplication_id 不包含它，所以 Postgres 拒绝了这个约束。你可以创建一个关于 (deduplication_id, id) 的组合唯一约束，但这对于去重实际上毫无用处，因为 id 本身已经是唯一的。无论 deduplication_id 值是否重复，每一行都会满足该约束。

这对于日期范围分区尤其痛苦，这可能是最流行的分区策略。按月或周分区是很常见的，但那里没有通用的唯一性策略，只有不同的时间间隔。分区键是为了数据管理而存在的，而不是为了数据完整性，Postgres 无法在帮助的情况下分离这些概念。

那么我们能做些什么来帮助它呢？

暴力破解来救援

如果 Postgres 不会为我们强制执行全局唯一约束，我们能自己实现吗？这毕竟是 Postgres，我们有很多工具可用。例如，触发器正是为这种场景设计的。

最简单的方法是一个 BEFORE INSERT 触发器，它扫描整个分区表集以查找重复项：

CREATE INDEX idx_events_deduplication_id ON events (deduplication_id);

CREATE OR REPLACE FUNCTION f_check_dupes()
RETURNS TRIGGER AS
$$
BEGIN
  PERFORM * FROM events WHERE deduplication_id = NEW.deduplication_id;
  IF FOUND THEN
    RAISE UNIQUE_VIOLATION
          USING MESSAGE = 'Duplicate found: ' || NEW.deduplication_id;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_check_dupes_b_i
BEFORE INSERT ON events
   FOR EACH ROW EXECUTE FUNCTION f_check_dupes();

这里的 deduplication_id 索引至关重要；没有它，每次插入都会触发跨所有分区的灾难性慢的顺序扫描。该索引使跨所有分区进行更优化的索引扫描成为可能。这仍然比理想情况有更多开销，但肯定可以更糟。

让我们向其中插入两百万行，看看会发生什么：

INSERT INTO events (deduplication_id, payload)
SELECT id, format('Hey %s', id)
  FROM generate_series(1, 2000000 - 1) a(id);

没有触发器的情况下，此插入在约 7 秒内完成。有触发器呢？25 秒。这超过了 3 倍的开销，而我们只有三个分区。触发器必须探测每个分区的索引以确认每行不存在重复项。随着分区数量的增加，探测时间也会增加，因为查询规划器需要接触越来越多的分区索引。五十个分区、一百个分区、三百个分区......每个分区都会为每次单独插入增加另一个索引查找。

那么我们的重复检查有效吗？绝对有效：

INSERT INTO events (deduplication_id, payload)
VALUES (50, 'Yay!');

ERROR:  Duplicate found: 50

这很令人满意，但规模化时的性能还有很多需要改进的地方。这是我们能解决的问题吗？

假装直到成功

如果我们将扫描分区表改为维护一个单独的、非分区表，其唯一职责是追踪已存在的 deduplication_id 值，会怎样？然后我们可以利用该表的主键为我们进行唯一性检查，Postgres 通过一次 B 树查找处理所有繁重的工作。

CREATE TABLE event_dedup (
  deduplication_id BIGINT PRIMARY KEY
);

现在触发器从跨分区扫描变为简单插入 event_dedup（去重）表。如果插入违反主键，Postgres 会立即捕获重复项。没有扫描，没有分区探测，没有存在性恐惧：

CREATE OR REPLACE FUNCTION f_check_dupes()
RETURNS TRIGGER AS
$$
BEGIN
  INSERT INTO event_dedup VALUES (NEW.deduplication_id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_check_dupes_b_i
BEFORE INSERT ON events
   FOR EACH ROW EXECUTE FUNCTION f_check_dupes();

但是我们也需要保持去重表的诚实。如果从 events 中删除行，相应的去重条目应该被清理。否则我们会拒绝针对不再存在的值进行未来插入：

CREATE OR REPLACE FUNCTION f_clean_dupes()
RETURNS TRIGGER AS
$$
BEGIN
  DELETE FROM event_dedup WHERE deduplication_id = OLD.deduplication_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER t_clean_dupes_a_d
AFTER DELETE ON events
   FOR EACH ROW EXECUTE FUNCTION f_clean_dupes();

现在让我们运行相同的两百万行插入：

INSERT INTO events (deplication_id, payload)
SELECT id, format('Hey %s', id)
  FROM generate_series(1, 2000000 - 1) a(id);

这次插入在 14 秒内完成，大约是没有任何触发器的 7 秒基线的两倍。这是合理的，因为每次插入现在都会执行一次额外的 B 树插入到去重表。另一方面，这种开销不会因有三个分区还是三百个分区而改变。去重表是一个单一的非分区表，带有单一索引。无论 events 表上存在多少分区，成本都是恒定的。

这个权衡相当有吸引力：为每次插入支付固定的 2 倍开销，以换取分区数量独立性。对于一个在其生命周期内可能增长到数十个或数百个分区的系统，去重表方法显然是更可持续的选择。

而且是的，重复检查仍然有效：

INSERT INTO events (deduplication_id, payload)
VALUES (50, 'Yay!');

ERROR:  duplicate key value violates unique constraint "event_dedup_pkey"
DETAIL:  Key (deduplication_id)=(50) already exists.

更好的是，我们现在得到了 Postgres 自己的约束 violation 消息，包含违规的键值。无需自定义错误格式。

反馈循环

这种方法有一个自然的担忧：去重表本身可能会变得庞大。如果 events 表随时间累积了数十亿行，去重表也会有数十亿条条目。那是维护在单一索引中的大量 B 树。

解决方案几乎是递归地将去重表分区：

CREATE TABLE event_dedup (
  deduplication_id BIGINT PRIMARY KEY
) PARTITION BY HASH (deduplication_id);

CREATE TABLE event_dedup_h0
PARTITION OF event_dedup
FOR VALUES WITH (modulus 3, remainder 0);

CREATE TABLE event_dedup_h1
PARTITION OF event_dedup
FOR VALUES WITH (modulus 3, remainder 1);

CREATE TABLE event_dedup_h2
PARTITION OF event_dedup
FOR VALUES WITH (modulus 3, remainder 2);

哈希分区非常适合这种情况，因为它均匀分布值，Postgres 可以直接裁剪到任何给定 deduplication_id 的正确分区。主键约束在这里有效，因为，分区键正是我们约束的列。在唯一性强制执行表上没有唯一性问题。非常方便。

触发器代码完全不变。Postgres 透明地处理分区路由。我们实际上只使用声明性分区、一个触发器函数和一个纯粹作为优化而存在的表，构建了一个可扩展的唯一性强制执行层。

虚拟化

如果去重 ID 是可以从行内容中派生而不是存储在 events 表中的东西呢？对我们的方法值得再调整一次。假设唯一性保证基于 payload 内容。我们可以使用哈希函数动态生成去重键：

CREATE OR REPLACE FUNCTION f_check_dupes()
RETURNS TRIGGER AS
$$
DECLARE
  -- Undocumented hash functions are fun!
  n_dupe_id BIGINT := hashtextextended(NEW.payload, 0);
BEGIN
  INSERT INTO event_dedup VALUES (n_dupe_id);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

hashtextextended 函数是 Postgres 中那些在常规文档浏览中不会出现的危险角落之一。它产生输入文本的 64 位哈希，提供约 18 quintillion（千的五次方的中文翻译是“京”，所以 18 quintillion 是 18×10^18 = 18E，即约 18 quintillion）的碰撞空间。对于大多数实际目的，这就足够了。对于真正严格要求的去重需求，请坚持使用确定性标识符而不是哈希。

这种方法的美妙之处在于 events 表保持精简。去重表承担存储成本，而且由于它只是一个单一的 BIGINT 列，它非常紧凑。

附带说明

没有解决方案是没有注意事项的，诚实的 intellectual 要求我们承认几点。

首先，触发器增加了复杂性。每个触发器都是存在于数据库而不是应用程序中的业务逻辑片段。有些团队可以接受这一点；其他人则将其视为在牛排上放番茄酱。对将逻辑保留在应用程序层有强烈看法的组织可能会拒绝在这种情况下使用触发器，无论其技术优点如何。

其次，并发值得注意。两种触发器方法都依赖于查看已提交的数据（或当前事务中可见的数据）。在高并发下，两个事务可能同时检查相同的 deduplication_id，都发现没有，然后都继续进行插入。

去重表方法处理这种情况更优雅，因为 INSERT INTO event_dedup 将在主键的底层唯一索引上阻塞，导致第二个事务等待。一旦第一个提交，第二个将获得约束 violation。

基于扫描的触发器在这里更容易受到攻击，因为 PERFORM 可能看不到来自其他事务的未提交行。触发器需要使用更复杂的逻辑，包括 advisory locks 来防止这种情况。

最后，还有更新的问题。如果可以更新 deduplication_id，应该有额外的触发器逻辑来处理这个用例。这是实现细节与业务逻辑蔓延并超出我们演示案例的简单范围的领域。但你明白了。

结束语

那么这让我们在哪里？文档对约束限制诚实地令人耳目一新，但诚实并不能跨分区强制执行唯一性。

暴力触发器在不引起太多关注的情况下有效，但它随分区数量增长而扩展不佳。去重表方法以小的恒定开销换取分区数量的独立性，这在生产系统中几乎是正确的权衡。对于那些想要冒险的人，算法变体通过动态计算去重键完全省去了额外的列。

可悲的是，这些技术都不像原生 UNIQUE 约束那样干净。它们都涉及触发器，这意味着更多的移动部件、更多需要测试的东西、更多需要记录的东西。但它们有效。有时最好的工程不是找到完美的解决方案，而是找到最可容忍的妥协。

把这些技巧放在你的后口袋里。分区仍然是水平扩展大型数据集的最佳方法，唯一性问题在 Postgres 添加全局索引之前不会消失。在此之前，触发器和去重表会做得很好。