Skip to content

Slow Execution of ALTER TABLE on Large Table when changing column type

如果你在更改大表的列数据类型时遇到 ALTER TABLE 操作执行缓慢，可以考虑以下替代方法。

🌐 If you encounter slow execution of the ALTER TABLE operation on a large table when changing a column data type, consider the following alternative approach.

替代方法：

  1. 添加一个新列，类型为：ALTER TABLE "table_name" ADD COLUMN "new_column_name" new_data_type;
  2. 将第一列的值复制到第二列：UPDATE "table_name" SET "old_column_name" = "new_column_name"::new_data_type;
  3. 删除旧列：ALTER TABLE "table_name" DROP COLUMN "old_column_name";

为什么使用这种方法？ ALTER TABLE 操作耗时长，是因为表的大小很大。这种分段的方法有助于：

  • 效率：这个过程更高效，因为它避免了长时间的交易。
  • 减少干扰：有计划地迁移数据可以降低对其他操作和用户的影响。

额外建议：

  • 将会话的 statement_timeout 设置为 0，以防止潜在的事务超时。
  • 使用提供的脚本监控当前被阻塞的数据库事务：
1
create view  public.lock_monitor as
2
select
3
  coalesce(
4
    blockingl.relation::regclass::text,
5
    blockingl.locktype
6
  ) as locked_item,
7
  now() - blockeda.query_start as waiting_duration,
8
  [blockeda.pid](http://blockeda.pid/) as blocked_pid,
9
  blockeda.query as blocked_query,
10
  blockedl.mode as blocked_mode,
11
  [blockinga.pid](http://blockinga.pid/) as blocking_pid,
12
  blockinga.query as blocking_query,
13
  blockingl.mode as blocking_mode
14
from
15
  pg_locks blockedl
16
  join pg_stat_activity blockeda on [blockedl.pid](http://blockedl.pid/) = [blockeda.pid](http://blockeda.pid/)
17
  join pg_locks blockingl on (
18
    blockingl.transactionid = blockedl.transactionid
19
    or blockingl.relation = blockedl.relation
20
    and blockingl.locktype = blockedl.locktype
21
  )
22
  and [blockedl.pid](http://blockedl.pid/) <> [blockingl.pid](http://blockingl.pid/)
23
  join pg_stat_activity blockinga on [blockingl.pid](http://blockingl.pid/) = [blockinga.pid](http://blockinga.pid/)
24
  and blockinga.datid = blockeda.datid
25
where
26
  not blockedl.granted
27
  and blockinga.datname = current_database();

注意事项： 在维护窗口或低流量时段执行这些步骤，以将中断降到最低。