PostgreSQL 11 新特性解讀 : 新增非空預設值欄位不需要重寫表

PostgreSQL · 發表 2018-12-13 09:36:55

摘要： PostgreSQL 10 版本前表新增不帶預設值的DDL不需要重寫表，只需要更新資料字典，因此DDL能瞬間執行，如下: ALTER TABLE table_name ADD COLUMN flag text; 如果新增的欄位帶預設值，則需要重寫表，表越大，執行時間越長，如下。 A...

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 FUNCTIONadd_column() RETURNS INTEGERAS$BODY$
DECLARE
column_nametext;
default_valuetext;
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表大小依然沒有變化。

參考

ofollow,noindex" target="_blank">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