主页 / dba / 问题 / 270627
Accepted
Gabriele D'Onufrio
Asked: 2020-07-09 07:29:58 +0800 CST

更快的 ALTER COLUMN

  • 772

有没有一种方法可以在一张巨大的桌子上的 postgres 12 中执行 ALTER COLUMN 而无需等待一生？

我尝试将字段从 bigint 转换为 smallint ：

ALTER TABLE huge ALTER COLUMN result_code TYPE SMALLINT;

需要8个小时，有没有更聪明的方法？该表具有序列、键和外键

performance postgresql-12

1 个回答

  1. Best Answer

    Laurenz Albe在类似的帖子中回答了这个问题

    这是答案：

    The table has to be rewritten, and you have to wait.

If you have several columns whose data type you want to change, you can use several ALTER COLUMN clauses in a single ALTER TABLE statement and save time that way.

An alternative idea would be to use logical replication: set up an empty copy of the database (pg_dump -s), where your large table is defined with smallint columns. Replicate your database to that database, and switch over as soon as replication has caught up.

    这是问题

    • 2

相关问题