Postgresql中如何执行ALTER TABLE加列操作不阻塞其他语句？

问题根因 #

ALTER TABLE ADD COLUMN 需要获取表的ACCESS EXCLUSIVE最高级锁，该锁必须等待所有已持有该表锁的事务全部结束才能申请成功，且锁等待过程中会阻塞所有后续对该表的读写请求。你场景中的业务侧持续产生的短查询会不断持有表锁，导致DDL请求长时间排队等待无法执行。

可落地解决方案 #

• 新增锁超时限制，分批重试执行DDL
  给单次DDL操作设置短的锁等待超时时间，拿不到锁就自动放弃，不会一直阻塞业务，多试几次总能在业务短请求的间隙拿到锁完成操作。你场景中业务请求耗时都小于10ms，设置200ms的超时时间足够找到执行窗口：

  -- 单次DDL最多等待200ms锁，拿不到就自动失败回滚
  SET lock_timeout = '200ms';
  ALTER TABLE test_table ADD COLUMN test_column integer;
  

  重复执行上述语句，直到返回成功即可，全程不会长时间阻塞业务。

• 利用高版本PostgreSQL优化加速执行
  PostgreSQL 11及以上版本中，新增无默认值列、或者带常量默认值的列都不需要全表重写，仅需修改元数据，只要拿到锁就能在微秒级完成操作，非常适合搭配上述短锁超时方案使用。

• 复杂加列场景分步操作（适用于需要加非空、带默认值列的场景）
  如果是低版本PostgreSQL需要加带默认值的非空列，可以拆分操作避免长锁：

  1. 先执行加可空列操作，不设置默认值，仅修改元数据速度极快
  2. 分批小批量更新历史数据，给旧行补默认值，每次更新控制在1000行以内，避免长事务锁表
  3. 给列设置默认值，再添加非空约束

锁排查辅助命令 #

如果多次重试仍拿不到锁，可以执行以下命令查询当前持有该表锁的事务：

SELECT pid, state, query, now() - query_start AS duration
FROM pg_stat_activity
WHERE datname = current_database()
  AND 'test_table'::regclass = ANY (relations)
ORDER BY duration DESC;

确认是闲置事务后可以执行SELECT pg_terminate_backend(对应pid);手动终止，释放锁资源。

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