PostgreSQL 关于字段类型的修改 谣言与止谣

以下文章来源于AustinDatabases ，作者carol11

PostgreSQL 在9.2 之前是要面临一个指责，就是在更改字段类型的时候带来的不堪，假象你有100万行的数据，其中一个字段是varchar(20) ,你想将其更改为 varhcar(30), 这可能就要造成一个灾难，熟悉postgresql 原理的人们，马上就想到，可能要生成一个“新表”了。导致Postgres重写表的每一行，这可能是一个非常昂贵的操作(就磁盘I/O和挂钟时间而言），MYSQL 早期的版本也没有好到哪里去，可是这对难兄难弟，都会成长。

PostgreSQL 在9.2 之后修改字段的大小，例如 varchar(20) ---> varchar(30) 返回修改仅仅是一瞬间的事情。

所以现在如果还有人说，PG修改字段的大小太差劲，那我到是觉得活在上世纪的 someone 可以清理一下内存了，终归新的东西是要不断学习的，你去看看现在的MYSQL 8 如果你的知识还保留在 MYSQL 5.5 ，那你一定也需要更新了。

那问题1 如果你还在使用PG 9.2 之前的版本，并继续受到这个问题的缠绕，怎么办

1 升级数据库版本 （当然这个说法估计支持的声音不少，但实际上做的人不多，因为牵扯的资源和人太多，没有人愿意在没有占有这个数据库系统的开发或者第三方开发商的支持下去做这个事情）

2 建议将字段更换为text字段，（或者经常需要变动的文字的字段），ALTER TABLE test ALTER COLUMN puzzle TYPE text;ALTER TABLE test ADD CONSTRAINT checksum_lengthCHECK (LENGTH(puzzle) <= 32);我们先看看这个方法合适吗,这个方法当然合适，字段的扩充可以换个思路，我们可以给的无限，然后后面通过约束限制一下，这样DBA 和开发其实都开心。

当然也有人说，你加完约束，系统的性能会受到影响，来来来我们做一个测试，插入1百万的数据，仅仅需要6秒多.

当然这并不是本期主要的话题，本期的主要话题是

这里要澄清的是，不是所有的PG 的 Alter Column type 操作都要进行重建表的操作（这里先不牵扯索引的事情）

这就是今天要进行测试的表，PG的版本 PG 12.2

测试如下

1 name 的类型从 char 变为 varchar 在变成 text

2 将上面的变化在变回来。将整形从小变大 从大变小，将日期类型进行变化

这些都是需要重写的

说完这些可能还有些人有疑问

1 添加一个字段呢，添加一个带默认值的字段呢

2 删除一个字段呢

3 更改一个字段的名字呢

结果是这些都不需要重写，另外在PG11 已经解决了关于 默认值的问题，这个问题，其实在有的商业数据库到很新的版本还是一个问题。

最后是关于索引的问题，这里PG 建立索引尽量要使用

CREATE INDEX CONCURRENTLY idx_add_c on type_change (add_c);

根据PG 的原理来说，我们在建立索引如果不使用 concurrently 参数则建立索引时表要 获取一个 access exclusive 的锁，而如果我们使用了 concurrently 则我们会获得一个 share update exclusive 的锁。所以使用了concurrently 则会允许在索引建立的同时继续读取数据和写入数据，当然也有一些副作用，今天就不说了，这个 concurrently 其实也可以找一期说一下，也是有点意思。