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 版本
[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,瞬間完成。
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
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![](https://img.laitimes.com/img/__Qf2AjLwojIjJCLyojI0JCLicWZwpmL0IDO3YmMkhTOxETZ4EjY0IWZkdjYlVDO2EmMhNGZwUGZ3AzYiFGO38CXt92Yu4GZjlGbh5SZslmZxl3Lc9CX6MHc0RHaiojIsJye.jpeg)