Postgres单条ALTER TABLE加多个约束比分开执行性能差问题咨询

• 官方文档提到的「合并ALTER TABLE减少表扫描」的优化，仅适用于无需创建索引、仅需单次遍历表即可完成的变更（例如新增带默认值的非空列、修改列类型等）。你本次添加的约束里，主键、唯一约束本质是创建B树索引，外键需要单独扫描关联表校验数据合法性，这两类操作都无法共享单次表扫描的结果，合并操作无法享受到官方提到的优化收益。
• 合并执行时所有约束创建处于同一事务上下文，需要同时持有更重的排他表锁，且多个索引构建任务会竞争有限的内存资源：默认配置下maintenance_work_mem通常只有几十MB，6个索引同时构建时每个任务能分到的内存仅为串行执行的1/6，排序阶段极易触发磁盘溢写，反而会拉长总耗时。
• 外键校验的缓存收益下降：串行添加外键约束时，前一次校验加载到内存中的users表主键索引可以被后一次外键校验复用，合并执行时索引构建和外键校验同时抢占内存缓存，反而会降低缓存命中率。

• 优先调大会话级维护内存：建索引前执行SET maintenance_work_mem = '32GB';（可根据服务器空闲内存调整，最大可给到空闲内存的50%），同时调大并行维护线程数SET max_parallel_maintenance_workers = 4;，单索引构建时可以利用多线程和大内存减少磁盘IO，单条约束添加耗时可下降30%~70%。
• 外键约束按需使用NOT VALID参数：如果能确认导入的数据完全符合外键规则，添加外键时可以先执行ALTER TABLE events ADD CONSTRAINT events_user_id1_fkey FOREIGN KEY (user_id1) REFERENCES users(id) NOT VALID;，之后再单独执行ALTER TABLE events VALIDATE CONSTRAINT events_user_id1_fkey;，该方式可以大幅缩短锁表时间，校验阶段也不会阻塞表的正常读写。
• 生产环境无 downtime 场景可以使用CONCURRENTLY参数构建索引：添加约束时使用ALTER TABLE events ADD CONSTRAINT events_unique_int1_me_key UNIQUE (unique_int1) CONCURRENTLY;，该方式不会持有排他表锁，不影响业务写入，仅会比普通建索引慢20%左右。
• 如果业务逻辑允许，尽量合并重复的索引：例如如果存在多个查询条件可以共用联合索引，就无需创建多个单列唯一索引，减少索引构建和后续维护的开销。

内容的提问来源于stack exchange，提问作者Adam Kamor