从11大版本以后,PG优化了添加带有默认值的列操作,11版本以前只要添加带有默认值的列,表会被重写,不管默认值是常量还是变量,所以在加字段的时候需要特别注意,重写表的锁是Access Exclusive,最重的锁,整张表是无法访问的。
我们通过实例看下11版本以后的效果,测试环境PG版本为13.6
#创建测试表
CREATE TABLE t_sample AS
SELECT a1::int
FROM generate_series(1, 100000000) AS a1;
#查看表大小,并添加字段a2,可见很快就完成了,因为只涉及表元数据的变更。虽然也涉及到全表锁Access Exclusive,但是时间很短。pg并没有把该字段写到磁盘。
postgres=# \dt+ t_sample
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+----------+-------+----------+-------------+---------+-------------
public | t_sample | table | postgres | permanent | 3457 MB |
(1 row)
postgres=# \timing
postgres=# ALTER TABLE t_sample ADD COLUMN a2 int;
ALTER TABLE
Time: 1002.778 ms (00:01.003)
#重点来了,看下填充默认常量值,这里只测试int和text类型,甚至比没有默认值还快,瞬间完成
postgres=# ALTER TABLE t_sample ADD COLUMN a3 int DEFAULT 10;
ALTER TABLE
Time: 10.353 ms
postgres=# ALTER TABLE t_sample ADD COLUMN a4 text DEFAULT 'dazuiba';
ALTER TABLE
Time: 4.298 ms
#再看下表大小,确实没变化,因为只改了元数据
postgres=# \dt+ t_sample
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+----------+-------+----------+-------------+---------+-------------
public | t_sample | table | postgres | permanent | 3458 MB |
#而且可以正确展示数据
postgres=# select * from t_sample limit 10;
a1 | a2 | a3 | a4
----+----+----+---------
1 | | 10 | dazuiba
2 | | 10 | dazuiba
3 | | 10 | dazuiba
4 | | 10 | dazuiba
5 | | 10 | dazuiba
6 | | 10 | dazuiba
7 | | 10 | dazuiba
8 | | 10 | dazuiba
9 | | 10 | dazuiba
10 | | 10 | dazuiba
(10 rows)
#所以这些新加的字段并没有真正的写数据到磁盘,只是改变了表的元数据。
#添加一个可变的默认值,则需要重写表和索引
postgres=# ALTER TABLE t_sample ADD COLUMN a5 int DEFAULT random()*1000;
ALTER TABLE
Time: 83613.973 ms (01:23.614)
#另外在更改数据类型的时候,也会重写表,如int改为bigint就会重写表
postgres=# alter table t_sample alter COLUMN a5 type bigint;
ALTER TABLE
Time: 109285.026 ms (01:49.285)
#删除字段也是很快的,因为只是标记改字段已删除,物理还存在,并没有释放空间,如下:
postgres=# \dt+ t_sample
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+----------+-------+----------+-------------+---------+-------------
public | t_sample | table | postgres | permanent | 4976 MB |
(1 row)
postgres=# alter table t_sample drop COLUMN a5;
ALTER TABLE
Time: 2.350 ms
postgres=# \dt+ t_sample
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+----------+-------+----------+-------------+---------+-------------
public | t_sample | table | postgres | permanent | 4976 MB |
(1 row)
postgres=# select * from t_sample limit 5;
a1 | a2 | a3 | a4
----+----+----+---------
1 | | 10 | dazuiba
2 | | 10 | dazuiba
3 | | 10 | dazuiba
4 | | 10 | dazuiba
5 | | 10 | dazuiba
(5 rows)
Time: 1.374 ms
#所以删除字段的时候,可以放心删,除非该表的并发访问极高,获取不到锁。
附上修改字段类型是否需要重写表的规则,如果新旧字段类型是二进制兼容的,从小到大修改,则不需要重表,如果从大往小改,则需要重写表。
varchar(x) to varchar(y) when y>=x. It works too if going from varchar(x) to varchar or text (no size limitation) 不需要重写
numeric(x,z) to numeric(y,z) when y>=x, or to numeric without specifier 但是当标度z发生变化时,不管y有没有变化,都会重写表
varbit(x) to varbit(y) when y>=x, or to varbit without specifier 不需要重写
timestamp(x) to timestamp(y) when y>=x or timestamp without specifier 不需要重写
timestamptz(x) to timestamptz(y) when y>=x or timestamptz without specifier 不需要重写
interval(x) to interval(y) when y>=x or interval without specifier 不需要重写
timestamp to text、varchar、varchar(n),char(n),需要重写
timestamp(x) to text、varchar、varchar(n)、char(n),n>=x,需要重写
text to char、char(x)、varchar(n),需要重写
text to varchar,不需要重写
numeric(x) to numeric(y),y>=x,不需要重写
numeric(x) to numeric,不需要重写
numeric(x,y) to numeric,不需要重写
int to bigint 需要重写
alter table xx add column xx serial/bigserial,需要重写
#对于分区表,修改索引列,有所不同,字段长度由小改大,虽然所有子表不会重写,但是所有索引会重写!
参考:
https://www.postgresql.org/docs/13/sql-altertable.html https://www.cybertec-postgresql.com/en/postgresql-alter-table-add-column-done-right/
https://mp.weixin.qq.com/s/3sW9a63VRq0C51DlESy55A
https://mp.weixin.qq.com/s/g8gEDPIfF3kFDf_32CBEmg