PostgreSQL 11 新特性之快速增加包含非空默认值的列_sql

概述在 PostgreSQL 11 之前，为表增加一个包含非空默认值的字段，将会导致表数据的重新写入，为每一行添加该字段，并且填充默认值。如果针对大表执行这种 *** 作，将会非常耗时。PostgreSQL 11 表 pg_catalog.pg_attribute 中添加两个字段：atthasmissing 和 attmissingval。为表增加一个包含非空默认值的字段不需要重写表。

文章目录

在 Postgresql 11 之前，为表增加一个包含非空默认值的字段，将会导致表数据的重新写入，为每一行添加该字段，并且填充默认值。如果针对大表执行这种 *** 作，将会非常耗时。

以下是一个 Postgresql 10 中添加字段（包含非空默认值）的示例：

-- Postgresql 10.5CREATE table t1 (c1 INT PRIMARY KEY, c2 TEXT);INSERT INTO t1 (c1, c2)SELECT seq, 'test'|| seq::TEXT             from generate_serIEs ( 1, 1000000 ) seq;\timing onALTER table t1 ADD ColUMN c3 text DEFAulT 'abc';ALTER tableTime: 1218.268 ms (00:01.218)

在我们的环境中，为表 t1 （一百万行数据）添加字段 c3 大约需要 1.2 s 的时间。对于千万级别甚至上亿级别的表，添加这种字段将会非常缓慢。

接下来使用 Postgresql 11 执行同样的 *** 作：

-- Postgresql 11.0CREATE table t1 (c1 INT PRIMARY KEY, 1000000 ) seq;\timing onALTER table t1 ADD ColUMN c3 text DEFAulT 'abc';ALTER tableTime: 7.392 ms

可以看到，Postgresql 11 中添加字段只需要 7 ms 的时间。如果数据量更大，性能提升就更明显。

这个改进的原理就是在表 pg_catalog.pg_attribute 中添加两个字段：atthasmissing 和 attmissingval。

\d pg_catalog.pg_attribute              table "pg_catalog.pg_attribute"    Column     |   Type    | Collation | Nullable | Default ---------------+-----------+-----------+----------+--------- attrelID      | oID       |           | not null |  attname       | name      |           | not null |  atttypID      | oID       |           | not null |  attstattarget | integer   |           | not null |  attlen        | smallint  |           | not null |  attnum        | smallint  |           | not null |  attndims      | integer   |           | not null |  attcacheoff   | integer   |           | not null |  atttypmod     | integer   |           | not null |  attbyval      | boolean   |           | not null |  attstorage    | "char"    |           | not null |  attalign      | "char"    |           | not null |  attnotnull    | boolean   |           | not null |  atthasdef     | boolean   |           | not null |  atthasmissing | boolean   |           | not null |  attIDentity   | "char"    |           | not null |  attisdropped  | boolean   |           | not null |  attislocal    | boolean   |           | not null |  attinhcount   | integer   |           | not null |  attcollation  | oID       |           | not null |  attacl        | aclitem[] |           |          |  attoptions    | text[]    |           |          |  attfDWoptions | text[]    |           |          |  attmissingval | anyarray  |           |          | Indexes:    "pg_attribute_relID_attnam_index" UNIQUE, btree (attrelID, attname)    "pg_attribute_relID_attnum_index" UNIQUE, attnum)

使用 ALTER table 语句添加字段的时候，如果指定了（非 VOLATILE）默认值，Postgresql 将该值存储到 pg_attribute 表对应行的 attmissingval 列中，并且将 atthasmissing 的值设置为 true。因此不需要重写表。

SELECT attmissingval, atthasmissing   FROM pg_attribute  WHERE attrelID = 't1'::regclass    AND attname = 'c3'; attmissingval | atthasmissing ---------------+--------------- {abc}         | t(1 row)

新增字段的默认值不一定需要是一个静态的表达式。它可以是任何非易变的表达式，例如 CURRENT_TIMESTAMP。但是，易变表达式（例如 random()）仍然会导致表的重写。对于表中已经存在的行，查询时直接返回 attmissingval 属性的值。插入新的数据行（包括更新已有的行）时，使用用户提供的值或者默认值进行填充，查询时不需要使用 attmissingval。

SELECT * FROM t1 WHERE c1 = 1; c1 |  c2   | c3  ----+-------+-----  1 | test1 | abc(1 row)INSERT INTO t1(c1, c2, c3) VALUES (0, 'zero', NulL);SELECT * FROM t1 WHERE c1 = 0; c1 |  c2  | c3 ----+------+----  0 | zero | (1 row)

一旦该表被重写（例如执行 VACUUM FulL table *** 作），相应的 atthasmissing 和 attmissingval 属性将会被清除，因为系统不再需要这些值。

VACUUM FulL t1;VACUUMTime: 2222.956 ms (00:02.223)SELECT attmissingval, atthasmissing   FROM pg_attribute  WHERE attrelID = 't1'::regclass    AND attname = 'c3'; attmissingval | atthasmissing ---------------+---------------               | f(1 row)