PostgreSQL 10 版本前表新增不带默认值的DDL不需要重写表，只需要更新数据字典，因此DDL能瞬间执行，如下:
ALTER TABLE table_name ADD COLUMN flag text;如果新增的字段带默认值，则需要重写表，表越大，执行时间越长，如下。
ALTER TABLE table_name ADD COLUMN flag text DEFAULT 'default values';生产环境下给大表添加带 Default 值的字段将非常吃力，通常分两步进行：
- 第一步: 先添加不带 Default值的字段。
- 第二步: 写函数批量刷新新增字段的默认值。
上述第二步比较麻烦，也可以在业务低谷或申请停服窗口一次性完成带DEFAUL值字段的新增。
PostgreSQL 11 版本这方面进一步增强，表新增带非空默认值的字段不再需要重写表，Release 中的说明如下：
Release中的说明
Allow ALTER TABLE to add a column with a non-null default without a table rewrite
本文分别在 10 版本和 11 版本进行测试。
PostgreSQL 10 版本
创建测试表并插入1000万数据，如下。
[pg10@pghost1 ~]$ psql mydb pguser
psql (10.0)
Type "help" for help.
mydb=> CREATE TABLE t1(id int4, name text);
CREATE TABLE
mydb=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000
mydb=> ANALYZE t1;
ANALYZE查看表的 relfilenode 和 relpages 信息，relfilenode 表示表的物理文件号。
mydb=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
relname | relfilenode | relpages
---------+-------------+----------
t1 | 25672 | 73530
(1 row)新增带默认值的非空字段，如下。
mydb=> \timing
Timing is on.
mydb=> ALTER TABLE t1 ADD COLUMN flag text DEFAULT 'abcdefg';
ALTER TABLE
Time: 15540.002 ms (00:15.540)执行时间较长，需要15秒左右。
表分析后再次查看表的 relfilenode 和 relpages信息
mydb=> ANALYZE t1;
ANALYZE
mydb=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
relname | relfilenode | relpages
---------+-------------+----------
t1 | 25679 | 83334
(1 row)发现 relfilenode 有变化 ，之前的 relfilenode 值为 25672 ，说明表被重写。另一方面 relpages 变大了。
PostgreSQL 11 版本
创建测试表并插入1000万数据，如下。
[pg11@pghost2 ~]$ psql francs francs
psql (11beta3)
Type "help" for help.
francs=> CREATE TABLE t1(id int4, name text);
CREATE TABLE
francs=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000
francs=> ANALYZE t1;
ANALYZE查看表的 relfilenode 和 relpages信息，如下:
francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
relname | relfilenode | relpages
---------+-------------+----------
t1 | 16802 | 73530新增带默认值的非空字段，如下。
francs=> \timing
Timing is on.
francs=> ALTER TABLE t1 ADD COLUMN flag text DEFAULT 'abcdefg';
ALTER TABLE
Time: 40.743 ms执行时间只需要 40 ms，瞬间完成。
表分析后再次查看表的 relfilenode 和 relpages信息
francs=> ANALYZE t1;
ANALYZE
francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
relname | relfilenode | relpages
---------+-------------+----------
t1 | 16802 | 73530
(1 row)发现 relfilenode 没有变化，依然是 16802，同时 relpages 也没有变化。
增加1000字段
PostgreSQL 11 版本给表 t1 增加了一个带默认值的字段后表占用空间没有变化，是不是增加的字段数不够多？接着往下测试，增加1000个带默认值的字段，看看情况如何？
创建测试表并插入1000万测试数据，如下:
francs=> DROP TABLE t1;
DROP TABLE
francs=> CREATE TABLE t1(id int4, name text);
CREATE TABLE
francs=> INSERT INTO t1 (id,name ) SELECT n, n || '_ALTER TABLE TEST ' FROM generate_series (1,10000000) n;
INSERT 0 10000000
francs=> ANALYZE t1;
ANALYZE查看表的 relfilenode 和 relpages信息，如下:
francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
relname | relfilenode | relpages
---------+-------------+----------
t1 | 34187 | 73530
(1 row)查看表大小，如下：
francs=> SELECT pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
574 MB
(1 row)创建函数，此函数用来给表 t1 添加 1000 个带默认值的字段，如下:
CREATE OR REPLACE FUNCTION add_column() RETURNS INTEGER AS $BODY$
DECLARE
column_name text;
default_value text;
BEGIN
default_value:= repeat(md5('1'),10);
FOR i in 1..1000 LOOP
column_name:= 'flag' || i;
EXECUTE
$$
ALTER TABLE t1 ADD COLUMN
$$
|| column_name ||
$$
text default'
$$
|| default_value ||
$$
'
$$
;
END LOOP;
RETURN 1;
END
$BODY$ LANGUAGE 'plpgsql';执行函数，如下：
francs=> SELECT add_column();
add_column
------------
1
(1 row)这时表t1已增加了1000个字段，如下：
francs=> SELECT * FROM t1 LIMIT 1;
-----------------------------------------[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------
id | 1
name | 1_ALTER TABLE TEST
flag1 | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag2 | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag3 | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag4 | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag5 | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag6 | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
flag7 | c4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849bc4ca4238a0b923820dcc509a6f75849b
...省略查看表 relfilenode 和 relpages，没有变化。
francs=> SELECT relname,relfilenode, relpages FROM pg_class WHERE relname='t1';
relname | relfilenode | relpages
---------+-------------+----------
t1 | 34187 | 73530
(1 row)再次确认表大小，依然还是 574MB。
francs=> SELECT pg_size_pretty(pg_relation_size('t1'));
pg_size_pretty
----------------
574 MB
(1 row)从以上看出给表t1增加了1000个带默认值的字段后，t1表大小依然没有变化。
参考
- WAITING FOR POSTGRESQL 11 – FAST ALTER TABLE ADD COLUMN WITH A NON-NULL DEFAULT
- PostgreSQL 11 preview - 添加非空默认值不需要 rewrite table - online add column with default value
新书推荐
最后推荐和张文升共同编写的《PostgreSQL实战》，本书基于PostgreSQL 10 编写，共18章，重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等，涵盖大量实战用例!
购买链接：https://item.jd.com/12405774.html
