PostgreSQL 11 新特性之快速增加包含非空默认值的列
作者: 不剪发的Tony老师
毕业于北京航空航天大学,十多年数据库管理与开发经验,目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址:https://tonydong.blog.csdn.net
文章目录
在 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 属性将会被清除,因为系统不再需要这些值。
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)