PostgreSQL ，我们在给表加列时，如果不设置列的默认值，不需要rewrite table，只是对元数据进行修改。

但是如果需要设置新增列的默认值，那么必须rewrite table。

PostgreSQL 11，新特性，在元数据中新增了2列(attmissingval, atthasmissing)，当新增stable或immutable表达式（非volatile）作为默认值时，在ALTER TABLE时，即刻评估表达式的值作为常量，存入新增列在pg_attribute元数据中的attmissingval字段中，同时将atthasmissing标记为true.

Allow ALTER TABLE to add a column with a non-null default without a table rewrite (Andrew Dunstan, Serge Rielau)

https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16828d5c0273b4fe5f10f42588005f16b415b2d8

Fast ALTER TABLE ADD COLUMN with a non-NULL default  
Currently adding a column to a table with a non-NULL default results in  
a rewrite of the table. For large tables this can be both expensive and  
disruptive. This patch removes the need for the rewrite as long as the  
default value is not volatile. The default expression is evaluated at  
the time of the ALTER TABLE and the result stored in a new column  
(attmissingval) in pg_attribute, and a new column (atthasmissing) is set  
to true. Any existing row when fetched will be supplied with the  
attmissingval. New rows will have the supplied value or the default and  
so will never need the attmissingval.  
Any time the table is rewritten all the atthasmissing and attmissingval  
settings for the attributes are cleared, as they are no longer needed.  
The most visible code change from this is in heap_attisnull, which  
acquires a third TupleDesc argument, allowing it to detect a missing  
value if there is one. In many cases where it is known that there will  
not be any (e.g.  catalog relations) NULL can be passed for this  
argument.  
Andrew Dunstan, heavily modified from an original patch from Serge  
Rielau.  
Reviewed by Tom Lane, Andres Freund, Tomas Vondra and David Rowley.  
Discussion: https://postgr.es/m/[email protected]  
新增stable或immutable表达式（非volatile）作为默认值时，在ALTER TABLE时，即刻评估表达式的值作为常量，存入新增列在pg_attribute元数据中的attmissingval字段中，同时将atthasmissing标记为true. 结合tuple head的t_infomask掩码以及null bitmaps，可以实现输出时是否使用元数据中存储的默认值。
 
未来如果对新增字段进行更新、或者写入新记录时带上非默认值，则新值会直接写入TUPLE中。
 
heap tuple : t_infomask 掩码
 
src/include/access/htup_details.h
 
 * information stored in t_infomask:  
#define HEAP_HASNULL                    0x0001  /* has null attribute(s) */  
#define HEAP_HASVARWIDTH                0x0002  /* has variable-width attribute(s) */  
#define HEAP_HASEXTERNAL                0x0004  /* has external stored attribute(s) */  
#define HEAP_HASOID                             0x0008  /* has an object-id field */  
#define HEAP_XMAX_KEYSHR_LOCK   0x0010  /* xmax is a key-shared locker */  
#define HEAP_COMBOCID                   0x0020  /* t_cid is a combo cid */  
#define HEAP_XMAX_EXCL_LOCK             0x0040  /* xmax is exclusive locker */  
#define HEAP_XMAX_LOCK_ONLY             0x0080  /* xmax, if valid, is only a locker */  
.........  
 * BITMAPLEN(NATTS) -  
 *              Computes size of null bitmap given number of data columns.  
#define BITMAPLEN(NATTS)        (((int)(NATTS) + 7) / 8)  
   1 /*-------------------------------------------------------------------------  
   3  * tupdesc_details.h  
   4  *    POSTGRES tuple descriptor definitions we can't include everywhere  
  10  * src/include/access/tupdesc_details.h  
  11  *  
  12  *-------------------------------------------------------------------------  
  13  */  
  15 #ifndef TUPDESC_DETAILS_H  
  16 #define TUPDESC_DETAILS_H  
  18 /*  
  19  * Structure used to represent value to be used when the attribute is not  
  20  * present at all in a tuple, i.e. when the column was created after the tuple  
  21  */  
  23 typedef struct attrMissing  
  25     bool        ammissingPresent;   /* true if non-NULL missing value exists */  
  26     Datum       ammissing;      /* value when attribute is missing */  
  27 } AttrMissing;  
  29 #endif                          /* TUPDESC_DETAILS_H */  
 PostgreSQL 11测试
 
1、创建测试表，新增若干数据（5.6GB）
 
postgres=# create unlogged table aaa(id int, info text, crt_time timestamp);  
CREATE TABLE  
Time: 6.259 ms  
postgres=# insert into aaa select generate_series(1,1000000),repeat(md5(random()::text),10), now();  
INSERT 0 1000000  
Time: 2151.531 ms (00:02.152)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 1000000  
Time: 1235.480 ms (00:01.235)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 2000000  
Time: 2688.409 ms (00:02.688)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 4000000  
Time: 4782.437 ms (00:04.782)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 8000000  
Time: 11367.010 ms (00:11.367)  
postgres=# \dt+ aaa  
                    List of relations  
 Schema | Name | Type  |  Owner   |  Size   | Description   
--------+------+-------+----------+---------+-------------  
 public | aaa  | table | postgres | 5618 MB |   
(1 row)  
2、新增字段，并添加默认值，由于只需要修改元数据，瞬间完成。
 
postgres=# alter table aaa add column c1 text default 'digoal';  
ALTER TABLE  
Time: 3.013 ms  
3、查看元数据表，可以看到，atthasmissing=true, attmissingval=我们设置的默认值。
 
postgres=# \x  
Expanded display is on.  
postgres=# select * from pg_attribute where attrelid='aaa'::regclass and attname='c1';  
-[ RECORD 1 ]-+---------  
attrelid      | 99498  
attname       | c1  
atttypid      | 25  
attstattarget | -1  
attlen        | -1  
attnum        | 4  
attndims      | 0  
attcacheoff   | -1  
atttypmod     | -1  
attbyval      | f  
attstorage    | x  
attalign      | i  
attnotnull    | f  
atthasdef     | t  
atthasmissing | t  
attidentity   |   
attisdropped  | f  
attislocal    | t  
attinhcount   | 0  
attcollation  | 100  
attacl        |   
attoptions    |   
attfdwoptions |   
attmissingval | {digoal}  
Time: 0.470 ms  
4、查看数据，默认值会从元数据表中获取。
 
postgres=# select id,c1 from aaa limit 10;  
 id |   c1     
----+--------  
  1 | digoal  
  2 | digoal  
  3 | digoal  
  4 | digoal  
  5 | digoal  
  6 | digoal  
  7 | digoal  
  8 | digoal  
  9 | digoal  
 10 | digoal  
(10 rows)  
Time: 0.510 ms  
5、写入新值，更新老值，符合最终要求
 
postgres=# insert into aaa values (0,'test',now(),null);  
INSERT 0 1  
Time: 0.487 ms  
postgres=# insert into aaa values (-1,'test',now(),'new');  
INSERT 0 1  
Time: 0.415 ms  
postgres=# update aaa set c1='hello digoal' where id=1;  
UPDATE 16  
Time: 2010.873 ms (00:02.011)  
postgres=# select id,c1 from aaa where id in (0,-1,1);  
 id |      c1        
----+--------------  
 -1 | new  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
  1 | hello digoal  
(18 rows)  
Time: 1033.896 ms (00:01.034)  
 PostgreSQL 低版本测试
 
postgres=# create unlogged table aaa(id int, info text, crt_time timestamp);  
CREATE TABLE  
Time: 2.930 ms  
postgres=# insert into aaa select generate_series(1,1000000),repeat(md5(random()::text),10), now();  
INSERT 0 1000000  
Time: 2992.188 ms (00:02.992)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 1000000  
Time: 1521.732 ms (00:01.522)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 2000000  
Time: 2781.948 ms (00:02.782)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 4000000  
Time: 5565.887 ms (00:05.566)  
postgres=# insert into aaa select * from aaa;  
INSERT 0 8000000  
Time: 10903.184 ms (00:10.903)  
postgres=# \dt+ aaa  
                    List of relations  
 Schema | Name | Type  |  Owner   |  Size   | Description   
--------+------+-------+----------+---------+-------------  
 public | aaa  | table | postgres | 5683 MB |   
(1 row)  
postgres=# alter table aaa add column c1 text default 'digoal';  
ALTER TABLE  
Time: 22484.222 ms (00:22.484)  
PostgreSQL 11 终于可以愉快的加字段加默认值了。
 
但是依旧需要注意DDL lock, 所以建议还是要设置LOCK_TIME再执行DDL。
 
postgres=# set lock_timeout ='1s';  
alter table ....;  
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=16828d5c0273b4fe5f10f42588005f16b415b2d8