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

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net


文章目錄

在 PostgreSQL 11 之前，為表增加一個包含非空默認值的字段，將會導(dǎo)致表數(shù)據(jù)的重新寫入，為每一行添加該字段，并且填充默認值。如果針對大表執(zhí)行這種操作，將會非常耗時。

以下是一個 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)

在我們的環(huán)境中，為表 t1 （一百萬行數(shù)據(jù)）添加字段 c3 大約需要 1.2 s 的時間。對于千萬級別甚至上億級別的表，添加這種字段將會非常緩慢。

接下來使用 PostgreSQL 11 執(zhí)行同樣的操作：

-- 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 的時間。如果數(shù)據(jù)量更大，性能提升就更明顯。

這個改進的原理就是在表 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 表對應(yīng)行的 attmissingval 列中，并且將 atthasmissing 的值設(shè)置為 true。因此不需要重寫表。

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

新增字段的默認值不一定需要是一個靜態(tài)的表達式。它可以是任何非易變的表達式，例如 CURRENT_TIMESTAMP。但是，易變表達式（例如 random()）仍然會導(dǎo)致表的重寫。對于表中已經(jīng)存在的行，查詢時直接返回 attmissingval 屬性的值。插入新的數(shù)據(jù)行（包括更新已有的行）時，使用用戶提供的值或者默認值進行填充，查詢時不需要使用 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)

一旦該表被重寫（例如執(zhí)行 VACUUM FULL table 操作），相應(yīng)的 atthasmissing 和 attmissingval 屬性將會被清除，因為系統(tǒng)不再需要這些值。

VACUUM FULL t1;
VACUUM
Time: 2222.956 ms (00:02.223)

SELECT attmissingval, atthasmissing
  FROM pg_attribute
 WHERE attrelid = 't1'::regclass
   AND attname = 'c3';
 attmissingval | atthasmissing
---------------+---------------
               | f
(1 row)