没有锁冲突ALTER 却很慢—— 重写表与扫描校验大白话版目标搞懂为什么明明没人抢锁ALTER TABLE还是跑很久。前置阅读pg-table-locks-简明教程.md讲的是「等锁」导致的阻塞本篇讲「拿到锁之后自己干活慢」。一、先分清两件事等锁 ≠ 干活慢ALTER慢有两个完全不同的原因别搞混阶段慢在哪现象① 等锁卡在「开始之前」别人占着表不放ALTER 一直等自己啥也没干② 干活卡在「拿到锁之后」要处理每一行数据ALTER 已经在跑但数据太多处理不完本篇讲的是第 ② 种锁很快就拿到了但拿到之后 ALTER 要对整张表做重活所以慢。而且这期间它一直握着最强的ACCESS EXCLUSIVE锁别的查询照样被堵。二、ALTER 的两种执行代价同样是「改字段」代价可能天差地别类型代价典型例子只改元数据秒级跟表大小无关varchar(100)→varchar(267)扩大、varchar→text要重写表 / 全表扫描和表大小成正比可能几十分钟见下一节改元数据只在系统目录里改一句「这列现在最长 267」一行数据都不用动 → 快。重写表把整张表的每一行都重新写一遍到新文件 → 慢。全表扫描校验把每一行都读一遍检查是否合规 → 慢。三、哪些「改字段」会触发重写 / 扫描慢操作1. 缩小 varchar 长度 / 改成不兼容的类型-- 慢要扫描每一行检查有没有超过 50 的值ALTERTABLEaALTERCOLUMNcolTYPEvarchar(50);-- 慢类型转换整表重写ALTERTABLEaALTERCOLUMNcolTYPEintUSINGcol::int;只要新旧类型的底层存储格式不一样PG 就会重写整表。2. 加带 default 的字段旧版本 PG-- PG 11 之前每一行都要写入 default 值 → 全表重写ALTERTABLEaADDCOLUMNcolvarchar(267)DEFAULTx;PG 11 对「常量 default」做了优化不重写但 default 是函数如now()、uuid()时仍然会重写。3. 加 NOT NULL 约束-- 慢要扫描全表确认没有一行是 nullALTERTABLEaALTERCOLUMNcolSETNOTNULL;4. 加 CHECK 约束 / 外键-- 慢要扫描全表校验已有数据是否都满足约束ALTERTABLEaADDCONSTRAINTchkCHECK(col0);四、除了重写还有这些「非锁」的慢因素即使操作本身不重写也可能因为下面这些原因慢表本身就巨大行数多纯粹是数据量问题。连带重建索引改类型的列上如果有索引索引会被重建。连带处理依赖对象视图、物化视图、外键等依赖该列的对象也可能连带处理。磁盘 IO 瓶颈重写 写一份新表文件磁盘慢就更久。和后台进程抢 IOautovacuum、checkpoint 同时在跑抢占磁盘带宽。重写后遗留清理旧表文件变成垃圾数据后续VACUUM还要再花资源。五、怎么判断你到底卡在「等锁」还是「干活」方法 1看等待事件SELECTpid,state,wait_event_type,wait_event,queryFROMpg_stat_activityWHEREqueryILIKEalter table%;看到的值含义wait_event_type Lock在等锁被别人挡住→ 看上一篇文档wait_event_type IO或空且stateactive在真正干活重写/扫描→ 就是本篇情况方法 2确认是否真的重写了表-- 执行 ALTER 前后各查一次SELECTrelfilenodeFROMpg_classWHERErelnamea;relfilenode是表底层文件的编号。前后值变了 发生了整表重写没变 只改了元数据。六、怎么降低影响能不重写就不重写扩大 varchar 长度、varchar→text这类是安全的秒级操作。避免「缩长度」「改类型」「加 default 函数」除非真的必要。拆分步骤以「加非空带默认值字段」为例避免一次性重写-- 1) 先加可空字段秒级ALTERTABLEaADDCOLUMNcolvarchar(267);-- 2) 分批回填数据每次几千行避免长事务UPDATEaSETcol...WHEREcolISNULLANDidBETWEEN?AND?;-- 3) 数据填完后再加约束此时扫描压力可控ALTERTABLEaALTERCOLUMNcolSETNOTNULL;加索引用 CONCURRENTLYCREATE INDEX CONCURRENTLY不会长时间锁表。挑低峰期执行并配合lock_timeout防止等锁阶段堵住业务。大表变更前先在测试库演练用relfilenode对比法确认会不会重写。七、一句话总结「没锁还慢」 ALTER 拿到锁之后在重写整张表 / 全表扫描校验。慢的是数据处理本身和抢锁无关。只是扩大 varchar 长度却很慢 → 多半是 PG 版本老或该列被索引/视图依赖导致连带重建。是改类型 / 缩长度 / 加 default / 加 NOT NULL→ 那就是预期内的全表操作只能靠「低峰期 拆分步骤」来缓解。
没有锁冲突，ALTER 却很慢？—— 重写表与扫描校验（大白话版）
没有锁冲突ALTER 却很慢—— 重写表与扫描校验大白话版目标搞懂为什么明明没人抢锁ALTER TABLE还是跑很久。前置阅读pg-table-locks-简明教程.md讲的是「等锁」导致的阻塞本篇讲「拿到锁之后自己干活慢」。一、先分清两件事等锁 ≠ 干活慢ALTER慢有两个完全不同的原因别搞混阶段慢在哪现象① 等锁卡在「开始之前」别人占着表不放ALTER 一直等自己啥也没干② 干活卡在「拿到锁之后」要处理每一行数据ALTER 已经在跑但数据太多处理不完本篇讲的是第 ② 种锁很快就拿到了但拿到之后 ALTER 要对整张表做重活所以慢。而且这期间它一直握着最强的ACCESS EXCLUSIVE锁别的查询照样被堵。二、ALTER 的两种执行代价同样是「改字段」代价可能天差地别类型代价典型例子只改元数据秒级跟表大小无关varchar(100)→varchar(267)扩大、varchar→text要重写表 / 全表扫描和表大小成正比可能几十分钟见下一节改元数据只在系统目录里改一句「这列现在最长 267」一行数据都不用动 → 快。重写表把整张表的每一行都重新写一遍到新文件 → 慢。全表扫描校验把每一行都读一遍检查是否合规 → 慢。三、哪些「改字段」会触发重写 / 扫描慢操作1. 缩小 varchar 长度 / 改成不兼容的类型-- 慢要扫描每一行检查有没有超过 50 的值ALTERTABLEaALTERCOLUMNcolTYPEvarchar(50);-- 慢类型转换整表重写ALTERTABLEaALTERCOLUMNcolTYPEintUSINGcol::int;只要新旧类型的底层存储格式不一样PG 就会重写整表。2. 加带 default 的字段旧版本 PG-- PG 11 之前每一行都要写入 default 值 → 全表重写ALTERTABLEaADDCOLUMNcolvarchar(267)DEFAULTx;PG 11 对「常量 default」做了优化不重写但 default 是函数如now()、uuid()时仍然会重写。3. 加 NOT NULL 约束-- 慢要扫描全表确认没有一行是 nullALTERTABLEaALTERCOLUMNcolSETNOTNULL;4. 加 CHECK 约束 / 外键-- 慢要扫描全表校验已有数据是否都满足约束ALTERTABLEaADDCONSTRAINTchkCHECK(col0);四、除了重写还有这些「非锁」的慢因素即使操作本身不重写也可能因为下面这些原因慢表本身就巨大行数多纯粹是数据量问题。连带重建索引改类型的列上如果有索引索引会被重建。连带处理依赖对象视图、物化视图、外键等依赖该列的对象也可能连带处理。磁盘 IO 瓶颈重写 写一份新表文件磁盘慢就更久。和后台进程抢 IOautovacuum、checkpoint 同时在跑抢占磁盘带宽。重写后遗留清理旧表文件变成垃圾数据后续VACUUM还要再花资源。五、怎么判断你到底卡在「等锁」还是「干活」方法 1看等待事件SELECTpid,state,wait_event_type,wait_event,queryFROMpg_stat_activityWHEREqueryILIKEalter table%;看到的值含义wait_event_type Lock在等锁被别人挡住→ 看上一篇文档wait_event_type IO或空且stateactive在真正干活重写/扫描→ 就是本篇情况方法 2确认是否真的重写了表-- 执行 ALTER 前后各查一次SELECTrelfilenodeFROMpg_classWHERErelnamea;relfilenode是表底层文件的编号。前后值变了 发生了整表重写没变 只改了元数据。六、怎么降低影响能不重写就不重写扩大 varchar 长度、varchar→text这类是安全的秒级操作。避免「缩长度」「改类型」「加 default 函数」除非真的必要。拆分步骤以「加非空带默认值字段」为例避免一次性重写-- 1) 先加可空字段秒级ALTERTABLEaADDCOLUMNcolvarchar(267);-- 2) 分批回填数据每次几千行避免长事务UPDATEaSETcol...WHEREcolISNULLANDidBETWEEN?AND?;-- 3) 数据填完后再加约束此时扫描压力可控ALTERTABLEaALTERCOLUMNcolSETNOTNULL;加索引用 CONCURRENTLYCREATE INDEX CONCURRENTLY不会长时间锁表。挑低峰期执行并配合lock_timeout防止等锁阶段堵住业务。大表变更前先在测试库演练用relfilenode对比法确认会不会重写。七、一句话总结「没锁还慢」 ALTER 拿到锁之后在重写整张表 / 全表扫描校验。慢的是数据处理本身和抢锁无关。只是扩大 varchar 长度却很慢 → 多半是 PG 版本老或该列被索引/视图依赖导致连带重建。是改类型 / 缩长度 / 加 default / 加 NOT NULL→ 那就是预期内的全表操作只能靠「低峰期 拆分步骤」来缓解。
全记录：从WebView包壳到原生功能过审实战)
