详述PG修改字段类型不锁表的原理 step by step

上期演示了PostgreSQL的零停机进行字段类型的转换，使用了pgroll 100万的数据，在每秒update的情况下，修改表中的字段类型 INT - BIGINT BIGINT- INT，做到了零停机。

那么原理是什么pgroll是一个基于什么原理的工作模式，这里有三个词，影子列，触发器，版本化视图，通过这三个组合，这里在操作中，他分为四个步骤，通过每个阶段持有轻量级锁来确保业务的全程可用。

我们下面把它整体的工作过程进行拆解。

1 创建影子列和触发器

-- 1. 创建影子列（允许 NULL，仅修改元数据，毫秒级完成）
ALTER TABLE public.users
  ADD COLUMN c1_pgroll_new bigint,
  ADD COLUMN c2_pgroll_new bigint;

-- 2. 创建双向触发器
CREATE TRIGGER pgroll_trigger_users
  BEFORE INSERT OR UPDATE ON public.users
  FOR EACH ROW EXECUTE FUNCTION pgroll_sync();

pgroll创建了与要改变的列字段类型一样的列，作为承接新的数据的容器。并添加触发器，对新插入的数据和进行UPDATE的数据进行跟踪和插入。

此时创建的影子列是允许 NULL,不会触发表重写，只修改了系统目录的 pg_attribute，耗时会非常的短。

在此阶段不会锁表，业务是无感知的。

2 数据同步阶段，此时数据在旧的列中写入数据同时也在新的行中通过触发器将数据写入到新的行，更新也是同步到新的行中。此时系统如果更新大量的数据，或大量的插入数据，请注意你的系统的性能的消耗。

3 处理之前的数据，在进行操作中需要注意的是之前的数据，举例在我们新建列之前有100万的数据，那么pgroll将通过分批搬用+立即提交的方式来进行数据老的数据的提交。

-- 伪代码：分批搬迁逻辑
DECLARE
 batch_size INT := 1000;
 rows_copied INT := 0;
BEGIN
 LOOP
   -- 每批只锁 1000 行，拷贝后立即提交释放锁
   UPDATE public.users
   SET c1_pgroll_new = c1::bigint,
       c2_pgroll_new = c2::bigint
   WHERE id IN (
     SELECT id FROM public.users
     WHERE c1_pgroll_new IS NULL
     LIMIT batch_size
   );

   GET DIAGNOSTICS rows_copied = ROW_COUNT;
   EXIT WHEN rows_copied = 0;

   -- 每批提交事务，释放所有锁
   COMMIT;

   -- 短暂休眠，让业务 UPDATE 优先通过
   PERFORM pg_sleep(0.01);
 END LOOP;
END;

通过上面的伪代码我们模拟操作的过程，每一次操作都会将原来的只更新的新的列中，同时会限制每次更新的额行数和中间的间隔。

这里有个部分需要说清楚，1分批每次只锁定少量行，不会阻塞其他的事务，立即提交，在没批完成后释放所，让业务UPDATE有机会通过，此工作还在每次操作pgroll中进行了休眠，保证了业务的优先，每批次耗时 < 10ms ，对业务来说避免了ACCESS EXCLUSIVE的锁。

4 这是整体工作中最后一关，切换在确认数据全量也完成后，系统会在低峰期，创建一个新的schema 和视图。

-- pgroll 自动创建的新 schema
CREATE SCHEMA public_0002_users_swap_type;

-- 在新 schema 下创建视图，映射影子列
CREATE VIEW public_0002_users_swap_type.users AS
  SELECT
    id,
    c1_pgroll_new AS c1,   -- 旧列被"替换"为影子列
    c2_pgroll_new A
    S c2
  FROM public.users;
  SET search_path TO public_0002_users_swap_type;

这里才是切换的分界线，新的应用在这套操作后，都连接到新的架构上，读取的是新字段上的数据，而老的应用继续连接老的schema，对于应用这是两个版本。当确定所有的连接都切换到新的版本后，pgroll complete --postgres-url "..."

pgroll会赶快执行

-- 1. 删除旧列
ALTER TABLE public.users DROP COLUMN c1, DROP COLUMN c2;

-- 2. 重命名影子列为正式列名
ALTER TABLE public.users
  RENAME COLUMN c1_pgroll_new TO c1,
  RENAME COLUMN c2_pgroll_new TO c2;

-- 3. 清理触发器
DROP TRIGGER pgroll_trigger_users ON public.users;

-- 4. 更新 pgroll 内部版本记录

最后的过程会非常的快，清理老的字段，留下新的字段，并将字段的名字进行修改。这里唯一的锁较大的部分就是 DROP COLUMN

而pgroll中提到的秒级回滚，是建立在影子列的基础上行

pgroll 只需要做两件事：

删除新创建的影子列（数据还在旧列里，完好无损）

删除触发器

这是相对我们直接ALTER的操作来说的，如果是ALTER的操作则执行就无法进行撤回了。

最后我们来kank pgroll中的complete的重要性

pgroll 执行完 pgroll migrate 之后迁移其实并没有真正结束。此时数据库正处于“新旧双写”的中间态。如果长期不执行 pgroll complete，触发器会一直运行，导致每次 UPDATE 都要写两份数据，数据库性能会下降，存储空间会翻倍。测试验证通过后，务必执行命令结束迁移。

在我们上期中操作会存在以下的风险

1 数据溢出风险（Varchar -> Timestamp / Bigint -> Int） 你这次做的是 Bigint 互换 Int。如果原表 c2（Bigint）里有大于 21 亿（2147483647）的数据，当它被缩减为 Int 时，pgroll 在搬迁历史数据时会直接报 “integer out of range” 错误导致迁移中断。同理，varchar 转 timestamp 如果格式不标准（如包含“未知”等脏数据），也会引发中断。

2 锁超时保护（--lock-timeout） pgroll 的 DDL 操作虽然快，但在高并发的 Rocky Linux 生产环境上，如果某个瞬间表上有大查询，pgroll 去创建触发器时依然可能需要排队。pgroll 默认设置了 --lock-timeout 500（500毫秒），一旦拿不到锁会立刻退出，绝对不阻塞线上业务，这是一个非常安全的细节设计。

写到这里，pgroll是目前针对PG进行字段类型修改中可以用到的比较好的一个工具，但他的一些设置比较复杂，也劝退了一些使用者。

图片