PostgreSQL索引更新是否原子？并发场景下unique index列赋值问题咨询
1. 索引更新的原子性问题
首先明确回答：PostgreSQL中的索引更新操作是具备原子性的，它和对应的表行修改操作绑定在同一个事务上下文里，完全遵循ACID特性。
具体来说，当你执行UPDATE/INSERT/DELETE这类会修改索引列的语句时，PostgreSQL会把表行的变更和索引的变更作为同一个事务的一部分处理——要么所有变更（表行+索引）都成功提交，要么全部回滚。比如你更新带有唯一索引的number列时，绝不会出现表行已修改但索引没更新，或者索引更新了但表行没变化的情况，这种原子性也是唯一约束能生效的核心保障。
2. 并发自增场景的问题与优化方案
你的场景里，用max(number)+重试的方式实现自增，在高并发下会遇到严重的竞态问题：多个worker会同时查到相同的max(number)值，然后试图给不同的NULL行设置同一个数值，导致大量唯一性约束冲突，重试成本极高，尤其是待处理行数达数千时，性能会非常差。
下面给你两种更高效的解决方案：
方案一：使用序列+触发器（推荐，无竞态）
这种方式利用PostgreSQL的序列（Sequence）生成唯一自增值，序列的nextval()是原子操作，并发调用绝不会产生重复值，完美避免冲突。
步骤如下：
- 创建表和唯一索引（符合你的需求）：
CREATE TABLE target_table ( id SERIAL PRIMARY KEY, -- 示例主键，可替换为你的实际主键列 number INTEGER UNIQUE );
- 创建对应序列：
CREATE SEQUENCE target_table_number_seq;
- 编写触发器函数，当
number为NULL时自动赋值序列值：
CREATE OR REPLACE FUNCTION auto_set_number() RETURNS TRIGGER AS $$ BEGIN -- 仅当原number为NULL时才自动赋值 IF OLD.number IS NULL THEN NEW.number := nextval('target_table_number_seq'); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql;
- 绑定触发器到表：
CREATE TRIGGER trigger_auto_set_number BEFORE UPDATE ON target_table FOR EACH ROW WHEN (OLD.number IS NULL) EXECUTE FUNCTION auto_set_number();
之后每个worker只需要执行简单的更新语句即可，无需自己查max或处理冲突：
-- 每个worker每次处理一行，也可去掉LIMIT批量处理 UPDATE target_table SET number = DEFAULT WHERE number IS NULL LIMIT 1;
这种方式完全没有竞态，性能最优，唯一需要注意的是：序列值不会回滚（如果事务失败，序列值会跳过），如果你的业务要求number必须严格连续无间隙，这个方案可能不适用——但严格连续在高并发场景下本身就很难实现，通常业务也不需要绝对连续。
方案二：基于现有max值的无竞态更新（适合需要严格连续的场景）
如果必须基于当前max(number)+1生成值，且要求连续，可以用UPDATE ... RETURNING结合行锁避免竞态：
UPDATE target_table SET number = (SELECT COALESCE(max(number), 0) + 1 FROM target_table) WHERE number IS NULL LIMIT 1 RETURNING *;
当多个worker同时执行这条语句时，PostgreSQL会自动处理行锁：只有一个worker能成功锁定并更新一行，其他worker会返回0行（或等待锁释放，取决于事务隔离级别）。此时worker只需判断返回行数，如果是0就再次尝试即可。
不过这种方式性能不如序列方案，因为每次都要执行max(number)查询（虽然有唯一索引，这个查询很快，但还是比nextval()慢），且表越大，max(number)的开销会逐渐增加。
总结
- PostgreSQL索引更新是原子性的，和表行变更绑定在同一事务中；
- 高并发下不要用
max(number)+重试的方式实现自增，推荐用序列+触发器的方案，既高效又无冲突； - 如果必须依赖现有max值，用
UPDATE ... RETURNING的方式可以避免竞态，但性能稍差。
内容的提问来源于stack exchange，提问作者atcruice
