在线修改PG字段类型

如何在线修改PostgreSQL中的字段类型？一种通用方法
By 冯若航（@Vonng） |
Tags:

场景

在数据库的生命周期中，有一类需求是很常见的，修改字段类型。例如：

  • 使用INT作为主键，结果发现业务红红火火，INT32的21亿序号不够用了，想要升级为BIGINT
  • 使用BIGINT存身份证号，结果发现里面有个X需要改为TEXT类型。
  • 使用FLOAT存放货币，发现精度丢失，想要修改为Decimal
  • 使用TEXT存储JSON字段，想用到PostgreSQL的JSON特性，修改为JSONB类型。

那么，如何应对这种需求呢？

常规操作

通常来说，ALTER TABLE可以用来修改字段类型。

ALTER TABLE tbl_name ALTER col_name TYPE new_type USING expression;

修改字段类型通常会重写整个表。作为一个特例，如果修改后的类型与之前是二进制兼容的，则可以跳过表重写的过程，但是如果列上有索引，索引还是需要重建的。二进制兼容的转换可以使用以下查询列出。

SELECT t1.typname AS from, t2.typname AS To
FROM pg_cast c
         join pg_type t1 on c.castsource = t1.oid
         join pg_type t2 on c.casttarget = t2.oid
where c.castmethod = 'b';

刨除PostgreSQL内部的类型，二进制兼容的类型转换如下所示

text     → varchar 
xml      → varchar 
xml      → text    
cidr     → inet    
varchar  → text    
bit      → varbit  
varbit   → bit

常见的二进制兼容类型转换基本就是这两种：

  • varchar(n1) → varchar(n2) （n2 ≥ n1）（比较常用，扩大长度约束不会重写，缩小会重写）

  • varchar ↔ text （同义转换，基本没啥用）

也就是说，其他的类型转换，都会涉及到表的重写。大表的重写是很慢的，从几分钟到十几小时都有可能。一旦发生重写，表上就会有AccessExclusiveLock，阻止一切并发访问。

如果是一个玩具数据库，或者业务还没上线，或者业务根本不在乎停机多久，那么整表重写的方式当然是没有问题的。但绝大多数时候，业务根本不可能接受这样的停机时间。所以，我们需要一种在线升级的办法。在不停机的情况完成字段类型的改造。

基本思路

在线改列的基本原理如下：

  • 创建一个新的临时列，使用新的类型

  • 旧列的数据同步至新的临时列

    • 存量同步：分批更新
    • 增量同步：更新触发器

  • 处理列依赖：索引

  • 执行切换

    • 处理列以来：约束，默认值，分区，继承，触发器

    • 通过列重命名的方式完成新旧列切换

在线改造的问题在于锁粒度拆分，将原来一次长期重锁操作，等效替代为多个瞬时轻锁操作。

原来ALTER TYPE重写过程中，会加上AccessExclusiveLock，阻止一切并发访问，持续时间几分钟到几天。

  • 添加新列：瞬间完成：AccessExclusiveLock
  • 同步新列-增量：创建触发器，瞬间完成，锁级别低。
  • 同步新列-存量：分批次UPDATE，少量多次，每次都能快速完成，锁级别低。
  • 新旧切换：锁表，瞬间完成。

让我们用pgbench的默认用例来说明在线改列的基本原理。假设我们希望在pgbench_accounts有访问的情况下修改abalance字段类型，从INT修改为BIGINT，那么应该如何处理呢？

  1. 首先，为pgbench_accounts创建一个名为abalance_tmp，类型为BIGINT的新列。
  2. 编写并创建列同步触发器，触发器会在每一行被插入或更新前，使用旧列abalance同步到

详情如下所示：

-- 操作目标：升级 pgbench_accounts 表普通列 abalance 类型：INT -> BIGINT

-- 添加新列：abalance_tmp BIGINT
ALTER TABLE pgbench_accounts ADD COLUMN abalance_tmp BIGINT;

-- 创建触发器函数：保持新列数据与旧列同步
CREATE OR REPLACE FUNCTION public.sync_pgbench_accounts_abalance() RETURNS TRIGGER AS $$
BEGIN NEW.abalance_tmp = NEW.abalance; RETURN NEW;END;
$$ LANGUAGE 'plpgsql';

-- 完成整表更新，分批更新的方式见下
UPDATE pgbench_accounts SET abalance_tmp = abalance; -- 不要在大表上运行这个

-- 创建触发器
CREATE TRIGGER tg_sync_pgbench_accounts_abalance BEFORE INSERT OR UPDATE ON pgbench_accounts
    FOR EACH ROW EXECUTE FUNCTION sync_pgbench_accounts_abalance();

-- 完成列的新旧切换，这时候数据同步方向变化 旧列数据与新列保持同步
BEGIN;
LOCK TABLE pgbench_accounts IN EXCLUSIVE MODE;
ALTER TABLE pgbench_accounts DISABLE TRIGGER tg_sync_pgbench_accounts_abalance;
ALTER TABLE pgbench_accounts RENAME COLUMN abalance TO abalance_old;
ALTER TABLE pgbench_accounts RENAME COLUMN abalance_tmp TO abalance;
ALTER TABLE pgbench_accounts RENAME COLUMN abalance_old TO abalance_tmp;
ALTER TABLE pgbench_accounts ENABLE TRIGGER tg_sync_pgbench_accounts_abalance;
COMMIT;

-- 确认数据完整性
SELECT count(*) FROM pgbench_accounts WHERE abalance_new != abalance;

-- 清理触发器与函数
DROP FUNCTION IF EXISTS sync_pgbench_accounts_abalance();
DROP TRIGGER tg_sync_pgbench_accounts_abalance ON pgbench_accounts;

注意事项

  1. ALTER TABLE的MVCC安全性
  2. 列上如果有约束？（PrimaryKey、ForeignKey，Unique，NotNULL）
  3. 列上如果有索引？
  4. ALTER TABLE导致的主从复制延迟
Last modified 2024-05-22: adjust blog structure (99d80185)