在我的 API 中,当存在具有该唯一键的行时,用户可能会发送一个尝试创建新行的请求。
目前,我正在捕获唯一键错误并返回一条消息,指出 X 已存在。但是,首先查找该行(在同一连接上)并且仅在该行不存在时才运行 INSERT 语句是否会更高效?
我的直觉告诉我,从 Postgres 读取错误应该会更有效,但我想确保我正在按照惯用的方式做事。
Postgres 版本为 12
在我的 API 中,当存在具有该唯一键的行时,用户可能会发送一个尝试创建新行的请求。
目前,我正在捕获唯一键错误并返回一条消息,指出 X 已存在。但是,首先查找该行(在同一连接上)并且仅在该行不存在时才运行 INSERT 语句是否会更高效?
我的直觉告诉我,从 Postgres 读取错误应该会更有效,但我想确保我正在按照惯用的方式做事。
Postgres 版本为 12
无论它是否具有更高的性能,如果在查找期间不锁定整个表直到
INSERT
. 在不锁定表的情况下,理论上,某人可以INSERT
在您检查和执行操作之间使用相同的数据密钥INSERT
(即使它们相隔纳秒)。按照这个速度,从整体角度来看,整个系统的性能可能比仅仅依赖唯一键约束要低。如果其他人同时插入重复项,则没有问题:选择不会看到它,但唯一约束仍然会捕获它。不过,您仍然需要复制错误处理代码。但是,如果有人在选择看到重复项后删除了它,那么它就不会被插入。
我运行了 python 基准测试,源代码可在pastebin上找到。这是一个简单的示例,使用仅具有主键和虚拟文本列的表。对于 0..99 范围内的每个 id,它会插入 100 次。只有第一次有效,其余的都会因为唯一性约束而被拒绝。
候选人是:
insert_only:发送插入,然后要么有效,要么不满足唯一约束。
select_then_insert:先选择检查,然后插入。
insert_select 将前两个查询合并为一个,这也消除了竞争条件:
INSERT INTO testins (id,t) SELECT %s,'hello, world' WHERE NOT EXISTS( SELECT FROM testins WHERE id=%s ) RETURNING id
on_conflict 使用 upsert 功能:
INSERT INTO testins (id,t) VALUES (%s,'hello, world') ON CONFLICT (id) 不返回 id
如果插入了行,“RETURNING id”只会返回 id,因此您知道它是插入的。如果查询没有返回任何内容,则意味着存在重复项。
结果(带表大小):
此测试每个插入有 99 个重复项,因此让我们尝试更合理的每次插入 1 个重复项:
没有重复:
在所有情况下,大部分时间都花在连接上的往返和提交事务上。
结论:
直接 INSERT 的问题在于,它仍然将行写入表中,然后尝试将其写入索引中,但在重复时失败,然后回滚事务。这会导致磁盘写入(表和 WAL),并且表会因死行而膨胀,需要 VACUUMing。做所有这些事情解释了性能的微小损失。
如果存在重复行,其他解决方案不会插入该行,这可以避免无用的写入和表膨胀。
对于 postgres 最惯用的说法是 ON CONFLICT。
因此,如果您预计会有大量重复项,即大多数情况下 INSERT 都会失败,并且该查询的流量很高,那么使用 ON CONFLICT 将是有利的。
如果您期望很少有重复项,即大多数情况下 INSERT 都可以工作,那么您可以让它抛出错误。
如果这是一个较大事务的一部分,您不想失败、回滚并再次执行所有工作,那么 ON CONFLICT 可以提供帮助,因为它不会在出现重复时抛出错误。
在 PostgreSQL 中,唯一约束是通过先插入记录,然后在违反约束时回滚来实现的。
如果您的约束被推迟,则暂时也会插入重复的 B 树条目,然后
unique_key_recheck
运行调用的内部触发器来验证新插入的记录是否违反约束。它看起来像这样:
这些结果集中的第二条记录是当约束失败时已回滚的死记录。这些记录使表空间和 WAL 变得混乱。
因此,直接回答您的问题:是的,有些影响整体性能的事情会在您违反约束时发生,而在您不违反约束时不会发生。
这取决于这些约束违规发生的频率。
提前读取记录需要遍历 B 树两次,如果您的错误率非常低(应该如此),那么在很长一段时间内进行一次死条目性能打击可能是值得的,而不是进行检查在每个插入物上。
请注意,在正确设计的系统中,无论您是否提前检查,唯一约束都应该存在。