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

文章目录

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

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

-- PostgreSQL 10.5
CREATE 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 on
ALTER TABLE t1 ADD COLUMN c3 text DEFAULT 'abc';
ALTER TABLE
Time: 1218.268 ms (00:01.218)

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

接下来使用 PostgreSQL 11 执行同样的操作：

-- PostgreSQL 11.0
CREATE 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 on
ALTER TABLE t1 ADD COLUMN c3 text DEFAULT 'abc';
ALTER TABLE
Time: 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, btree (attrelid, 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 属性将会被清除，因为系统不再需要这些值。

更多请见：http://www.mark-to-win.com/tutorial/51637.html