由 John Doe 十二月 24, 2024
摘要：在本文中，我们将了解插入时发生唯一约束冲突会导致 PostgreSQL 产生膨胀。
目录
介绍
因为唯一性约束而插入失败，给表和索引带来的膨胀问题，在互联网上已经非常出名了。然而，这些讨论有时缺乏一个清晰、实际的例子，通过测量来说明影响。尽管这个问题很熟悉，但我们仍然经常会在实际的应用程序中看到这样的设计模式，或者更确切地说，这种反模式。开发者通常依赖唯一性约束，来防止将重复值插入表中。虽然这种方法简单明了、用途广泛且通常被认为是有效的，但在 PostgreSQL 中，不幸的是，由于唯一约束冲突而失败的插入，总是会给表和索引带来膨胀。在高流量系统上，这种不必要的膨胀会显著增加磁盘 I/O 和 autovacuum 运行的频率。在本文中，我们旨在再次强调这个问题，并提供一个带有测量的简单示例来说明它。我们提供了简单的改进建议，以帮助缓解此问题，并减少 autovacuum 工作负载和磁盘 I/O。
防止重复的两种方法
在 PostgreSQL 中，有两种主要的方法使用唯一性约束，来防止出现重复值：
1. 标准插入命令 (INSERT INTO table)
通常的 INSERT INTO table 命令，尝试将数据直接插入到表中。如果插入会出现重复值，则会失败并显示 “duplicate key value violates unique constraint” 错误。由于该命令没有指定任何重复检查，因此 PostgreSQL 会在内部立即插入新行，然后才开始更新索引。当它遇到唯一索引冲突时，它会引发错误并删除新添加的行。索引更新的顺序由其关系 ID 决定，因此索引膨胀的程度取决于索引的创建顺序。如果不断地发生 “unique constraint violation” 错误，表和一些索引都会累积已删除的记录，从而产生膨胀，并且由此产生的写入操作会增加磁盘 I/O，而不会获得任何有用的结果。
2. 冲突感知插入 (INSERT INTO table … ON CONFLICT DO NOTHING)
INSERT INTO table ON CONFLICT DO NOTHING 命令的行为不太一样。由于它指定了可能会发生冲突，因此 PostgreSQL 在尝试插入数据之前，会首先检查可能的重复项。如果找到重复项，PostgreSQL 将执行指定的操作（在本例中为 “DO NOTHING”），并且不会发生错误。此子句是在 PostgreSQL 9.5 中引入的，但是某些应用程序要么还在较旧的 PostgreSQL 版本上运行，要么在升级数据库时保留了旧代码。因此，这种冲突处理选项，通常并未得到充分利用。
测试示例
为了能够进行测试，我们必须使用 “autovacuum=off” 启动 PostgreSQL。否则，在实例大部分空闲的情况下，autovacuum 会立即处理膨胀的对象，并且无法捕获统计信息。让我们来创建一个简单的测试示例，其中包含了多个索引：
CREATE TABLE IF NOT EXISTS test_unique_constraints(
id serial primary key,
unique_text_key text,
unique_integer_key integer,
some_other_bigint_column bigint,
some_other_text_column text);
CREATE INDEX test_unique_constraints_some_other_bigint_column_idx ON test_unique_constraints (some_other_bigint_column );
CREATE INDEX test_unique_constraints_some_other_text_column_idx ON test_unique_constraints (some_other_text_column );
CREATE INDEX test_unique_constraints_unique_text_key_unique_integer_key__idx ON test_unique_constraints (unique_text_key, unique_integer_key, some_other_bigint_column );
CREATE UNIQUE test_unique_constraints_unique_integer_key_idx INDEX ON test_unique_constraints (unique_text_key );
CREATE UNIQUE test_unique_constraints_unique_text_key_idx INDEX ON test_unique_constraints (unique_integer_key );
现在，我们用唯一的数据来填充此表：
DO $$
BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO test_unique_constraints
(unique_text_key, unique_integer_key, some_other_bigint_column, some_other_text_column)
VALUES (i::text, i, i, i::text);
END LOOP;
END;
$$;
在第二步中，我们使用一个简单的 Python 脚本连接到数据库，尝试插入冲突的数据，并在出现错误后关闭会话。首先，它发送了 10000 个与 “test_unique_constraints_unique_int_key_idx” 索引冲突的 INSERT 语句，然后是另外 10000 个与 “test_unique_constraints_unique_text_key_idx” 索引冲突的 INSERT 语句。整个测试在几十秒内完成，之后，我们使用 “pgstattuple” 扩展来检查所有对象。下面的查询在单次输出中列出所有的对象：
WITH maintable AS (SELECT oid, relname FROM pg_class WHERE relname = 'test_unique_constraints')
SELECT m.oid as relid, m.relname as relation, s.*
FROM maintable m
JOIN LATERAL (SELECT * FROM pgstattuple(m.oid)) s ON true
UNION ALL
SELECT i.indexrelid as relid, indexrelid::regclass::text as relation, s.*
FROM pg_index i
JOIN LATERAL (SELECT * FROM pgstattuple(i.indexrelid)) s ON true
WHERE i.indrelid::regclass::text = 'test_unique_constraints'
ORDER BY relid;
观察结果
在多次运行整个测试后，我们观察到以下现象：
- 主表 “test_unique_constraints” 始终有 1000 个活动元组，和 20000 个额外的死元组，导致表中大约有 85% 的死元组。
- 主键上的索引始终显示有 21000 个元组，而不知道其中 20000 个元组在主表中已被标记为已删除。
- 其他的非唯一性索引，在不同的运行中显示不同的结果，范围在 3000 到 21000 条记录之间。具体数字取决于脚本为基础列生成的值的分布。我们测试了重复值和完全唯一的值。重复值会导致索引中的记录较少，完全唯一的值会导致这些索引中出现完整计数为 21000 条记录。
- 在所有测试中唯一性索引都反复显示元组数仅在 1000 到 1400 之间。“unique_text_key” 上的唯一索引总会在输出中显示一些死元组。对这些数字的精确解释，需要更深入地检查这些关系和 pgstattuple 函数的代码，这超出了本文的范围。但这里也反映出了一些小量的膨胀。
- pgstattuple 函数报告的数字，其准确性存在某些疑问，尽管从文档得出的结论似乎是，元组级别的数字应该是精确的。
- 随后进行的手动 VACUUM，确认了主表中有 20000 条死记录，并从主键索引中删除了 54 个页面，以及从其他索引中删除了多达几十个页面。如上所述，每次运行中的不同数字，取决于这些关系中的元组总数。
- 每次插入失败还会增加事务 ID，从而增加了数据库的事务年龄。
以下是在运行测试后，上面显示的查询的一个示例输出，该查询对所有列使用了唯一值。正如我们所看到的，由于插入失败而给非唯一性索引带来的膨胀量可能很大。
relid | relation | table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count | dead_tuple_len | dead_tuple_percent | free_space | free_percent
-------+-----------------------------------------------------------------+-----------+-------------+-----------+---------------+------------------+----------------+--------------------+------------+--------------
16418 | test_unique_constraints | 1269760 | 1000 | 51893 | 4.09 | 20000 | 1080000 | 85.06 | 5420 | 0.43
16424 | test_unique_constraints_pkey | 491520 | 21000 | 336000 | 68.36 | 0 | 0 | 0 | 51444 | 10.47
16426 | test_unique_constraints_some_other_bigint_column_idx | 581632 | 16396 | 326536 | 56.14 | 0 | 0 | 0 | 168732 | 29.01
16427 | test_unique_constraints_some_other_text_column_idx | 516096 | 16815 | 327176 | 63.39 | 0 | 0 | 0 | 101392 | 19.65
16428 | test_unique_constraints_unique_text_key_unique_integer_key__idx | 1015808 | 21000 | 584088 | 57.5 | 0 | 0 | 0 | 323548 | 31.85
16429 | test_unique_constraints_unique_text_key_idx | 57344 | 1263 | 20208 | 35.24 | 2 | 32 | 0.06 | 15360 | 26.79
16430 | test_unique_constraints_unique_integer_key_idx | 40960 | 1000 | 16000 | 39.06 | 0 | 0 | 0 | 4404 | 10.75
(7 rows)
在第二个测试中，我们修改了脚本，在 INSERT 命令中包含了 ON CONFLICT DO NOTHING 子句，并重复这两个测试。这一次，插入不再出现错误；相反，它们只返回 “INSERT 0 0”，表示未插入任何记录。在这个测试后的检查显示，事务 ID 的增长很小，都是由后台进程引起的。尝试插入冲突数据不会导致事务 ID（XID）增加，因为 PostgreSQL 首先仅启动虚拟事务来检查冲突，并且由于发现了冲突，它会中止事务，而无需分配新的 XID。“pgstattuple” 的输出确认了，所有对象都仅包含活动数据，这次没有死元组。
总结
如前所述，每次插入失败都会使底层的表和某些索引膨胀，并增加事务 ID，因为每次失败的插入都发生在单独的事务中。因此，autovacuum 被迫更频繁地运行，从而消耗宝贵的系统资源。因此，那些还只依赖于没有 ON CONFLICT 条件的普通 INSERT 命令的应用程序，应当考虑检视这样的实现。但和通常一样，最终决定应基于每个应用程序的具体情况。