PostgreSQL在线修改数据类型

修改大表中列的数据类型几乎总是一件痛苦的事情。由于alter table语句获得的排他锁，它可能会锁定整个表的读写。本文中，我们将探讨如何以最小的影响、最少的锁来执行这样的操作。它适用于任何数据类型；让我们以int到bigint的变化为例。

 

示范用例

假设我们有一个有许多列的表。其中一列的类型是整型（int，4字节），其值随着时间的推移而递增。一段时间后，我们开始在日志中收到error: integer out of range的信息，这意味着我们试图插入的值大于整数限制（最大值2147483647）。简单的方法是将其类型更改为大整数（bigint，8字节）。下面是我们如何几乎 "在线"完成这一操作的方法。

 

首先，我们需要创建一个使用新数据类型的列。新创建的列是空列，所以应该在几毫秒内完成。

alter table big_table add column order_id_tmp bigint;

 

为了确保没有产生大量的锁，我们可以写个脚本，如果命令执行时间过长，可以将其做超时处理。比如：

cat <<EOF > alter.sql
SET statement_timeout = 100;
ALTER TABLE big_table ADD COLUMN order_id_tmp bigint;
EOF

while true
do
    date
    psql -qX -v ON_ERROR_STOP=1 -f alter.sql && break
    sleep 1
done

statement_timeout将终止运行超过100毫秒的alter table命令，由于psql参数中添加了on_error_stop，其进程将以错误退出。因此，"break"在超时后将不会被执行，因为&&操作符在执行下一个命令前希望第一个命令的退出代码为0，即运行成功。

 

接下来，我们将创建一个函数和一个触发器，用于将新插入和更新记录中的order_id值复制到order_id_tmp中。

CREATE FUNCTION order_id_bigint() RETURNS TRIGGER AS $BODY$
BEGIN
NEW.order_id_tmp=NEW.order_id;
RETURN NEW;
END
$BODY$ LANGUAGE PLPGSQL;

CREATE TRIGGER order_id_update_or_insert
BEFORE INSERT OR UPDATE ON big_table
FOR EACH ROW EXECUTE PROCEDURE order_id_bigint();

然后我们就可以向新列填充数据了。对于较大的表，我们可以创建一个支持表来帮助完成这一工作。它应该包含主键，在本例中是big_table的列 "ID"，用于记录所有新的bigint列为空的记录。

CREATE TABLE public.temp_order_id_bigint(
    id bigint NOT NULL
);
CREATE INDEX id_sorted ON public.temp_order_id_bigint USING btree(id ASC) ;
INSERT INTO public.temp_order_id_bigint
SELECT id FROM big_table
WHERE (order_id IS NOT NULL AND order_id_tmp IS NULL);

 

由于MVCC（多版本并发控制）机制和autovacuum的工作原理，Postgres不喜欢大规模更新，因此我们将分块填充数据。更新会一次性更新所有的元组，这将导致巨大的膨胀，表的大小也会增加一倍。

START TRANSACTION;
UPDATE public.big_table
SET
    order_id_tmp = order_id
WHERE id IN (
    SELECT id
    FROM public.temp_order_id_bigint
    ORDER BY id ASC
    LIMIT 5000
);
delete from public.temp_order_id_bigint where id in (
    SELECT id
    FROM public.temp_order_id_bigint
    ORDER BY id ASC
    LIMIT 5000
);
COMMIT;

循环运行上述事务，直到临时表temp_order_id_bigint 为空。

 

使用最适合你的LIMIT；根据行的大小，你可以使用更低或更高的值来达到更好的效果。在我的测试案例中，5000是最佳值，每5k行在不到300ms的时间内完成。

 

数据填充完成后，我们就可以执行列切换了。如前所述，我们可以使用与while循环类似的方法。这将确保不会等待锁和阻塞其他会话太长时间。

BEGIN;
LOCK TABLE big_table IN SHARE ROW EXCLUSIVE MODE;
DROP TRIGGER order_id_update_or_insert ON big_table;
ALTER TABLE big_table RENAME COLUMN order_id TO order_id_old;
ALTER TABLE big_table RENAME COLUMN order_id_tmp TO order_id;
COMMIT;

看，将列从int改为bigint，锁定时间不到一秒钟。由于所有的准备工作、数据填充等，该操作本身可能比仅仅更改现有列的数据类型花费更长的时间。但我们避免了使用标准方法所需的停机时间或维护窗口。