PostgreSQL表增加/刪除欄位是否會重寫表
一、表新增欄位
1.PostgreSQL10版本及以下
新增不帶預設值的列
PostgreSQL 10 版本前表新增不帶預設值的列不需要重寫表，只需要更新資料字典，因此能瞬間執行。如果不帶預設值，則會填充空值。
新增帶預設值的列
如果新增的欄位帶預設值，則需要重寫表。表越大，執行時間越長。 重寫表會對錶加Access Exclusive鎖，期間整張表是無法訪問的。
如果是生產環境下給大表新增帶 Default 值的欄位可能影響比較大，通常先新增不帶 Default值的欄位，然後寫函式批次重新整理新增欄位的預設值。也可以在業務量較低的時候或者申請割接視窗停業務一次性完成帶DEFAUL值欄位的新增。除此之外，如果有必要，可以清理一下可能堵塞DDL的長事務或者後臺任務(例如暫時關閉這張表上的autovacuum)。
2.PostgreSQL11版本及以上
PostgreSQL 11 版本這方面進一步增強，表新增帶非空預設值的欄位不再需要重寫表，Release 中的說明如下：
Release中的說明
Allow ALTER TABLE to add a column with a non-null default without a table rewrite
主要的實現方法是：在系統表 pg_catalog.pg_attribute 中新增了兩個欄位：atthasmissing 和 attmissingval。新增列的預設值會記錄到attmissingval，並且對應的atthasmissing會被設定為true。查詢時如果tuple中不包含對應的列，則會返回attmissingval的值。
但是如果表之後因為其他的操作導致表被重寫，例如vacuum full，則相應的atthasmissing和attmissingval屬性將會被清除。
postgres=# create table t(id int, name varchar(20));
CREATE TABLE
postgres=# insert into t select generate_series(1,10),left(md5(random()::text),20);
INSERT 0 10
postgres=# select attname, attmissingval, atthasmissing FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass;
attname | attmissingval | atthasmissing
---------+---------------+---------------
id | | f
name | | f
(2 rows)
postgres=# alter table t add column test_default int default 1;
ALTER TABLE
postgres=# alter table t add column test_nodefault int;
ALTER TABLE
postgres=# select attname, attmissingval, atthasmissing FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass;
attname | attmissingval | atthasmissing
----------------+---------------+---------------
id | | f
name | | f
test_default | {1} | t
test_nodefault | | f
(4 rows)
二、表刪除欄位
1.刪除欄位分析
刪除一個列時，並不會重建表（逐行掃表重寫），而是將pg_attribute中對應的列的attname欄位修改為… pg.dropped.idx… ，attisdropped標記為true，查詢時會跳過該列。因此， 刪除列操作可以很快完成。
postgres=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
----------------+-----------------------+-----------+----------+---------
id | integer | | |
name | character varying(20) | | |
test_default | integer | | | 1
test_nodefault | integer | | |
postgres=# select attname, attmissingval, atthasmissing FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass;
attname | attmissingval | atthasmissing
----------------+---------------+---------------
id | | f
name | | f
test_default | {1} | t
test_nodefault | | f
(4 rows)
postgres=# alter table t drop column test_nodefault;
ALTER TABLE
postgres=# select attname, attmissingval, atthasmissing FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass;
attname | attmissingval | atthasmissing
------------------------------+---------------+---------------
id | | f
name | | f
test_default | {1} | t
........pg.dropped.4........ | | f
(4 rows)
但是drop後的列，pg_attribute裡的……… pg.dropped.idx… 記錄不會因為做了vacuum full而被移除，這一點和11版本增加帶預設值的列不同。
postgres=# select attname, attmissingval, atthasmissing FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass;
attname | attmissingval | atthasmissing
------------------------------+---------------+---------------
id | | f
name | | f
test_default | {1} | t
........pg.dropped.4........ | | f
(4 rows)
postgres=# vacuum full t;
VACUUM
postgres=# select attname, attmissingval, atthasmissing FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass;
attname | attmissingval | atthasmissing
------------------------------+---------------+---------------
id | | f
name | | f
test_default | | f
........pg.dropped.4........ | | f
(4 rows)
2.vacuum full對刪除欄位的影響
不過做過了vacuum full 操作已經把這列對應的資料清理掉了，即使透過更新系統表讓列恢復，資料也不能恢復了。（vacuum不會導致列資料恢復不了）
想要恢復可能需要根據全量備份加wal日誌做PITR了。
如下是沒做vacuum full和做過了vacuum full的列恢復情況。可以看到未做vacuum full的列可以正常恢復，但是做過了vacuum full的列雖然可以恢復，但是列原來儲存的資料已經不存在了，用NULL填充。
（1）刪除欄位後未做vacuum full
//建立測試表 postgres=# create table t(id int, name varchar(20)); CREATE TABLE postgres=# insert into t select generate_series(1,10),left(md5(random()::text),20); INSERT 0 10 postgres=# select * from t; id | name ----+---------------------- 1 | bcbd19340969fda7c9c4 2 | a9f514a971eae3937def 3 | 20d53f04cce29b1e2984 4 | 912dd222955487de27a7 5 | 5b18d6e2c9b22d34884f 6 | 4c7db5a43de739511864 7 | b2f1b83e98bdfdce8bce 8 | 1fe69e2bec216de50f29 9 | 689ecc14d87ae81fc0d1 10 | 0ba176b240d0875da3e8 (10 rows)
查詢列資訊，attrelid和pg_class.oid關聯，即表的oid。atttypid和pg_type.oid關聯，列型別的oid，1043即對應varchar型別。attisdropped為t則表示該列被刪除且不再有效。一個刪除的列仍然物理存在於表中，但是會被分析器忽略並因此無法透過SQL訪問。
postgres=# select attrelid,attname,atttypid,attnum,attisdropped FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass; attrelid | attname | atttypid | attnum | attisdropped ----------+---------+----------+--------+-------------- 16395 | id | 23 | 1 | f 16395 | name | 1043 | 2 | f (2 rows) postgres=# select * from pg_type where oid=1043; oid | typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typsubscript | typelem | typarray | typinput | typoutput | typreceive | typsend | typmodin | typmodout | typanalyze | typalign | typstorage | typnotnul l | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl ------+---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+--------------+- --------+----------+-----------+------------+-------------+-------------+-----------------+------------------+------------+----------+------------+---------- --+-------------+-----------+----------+--------------+---------------+------------+-------- 1043 | varchar | 11 | 10 | -1 | f | b | S | f | t | , | 0 | - | 0 | 1015 | varcharin | varcharout | varcharrecv | varcharsend | varchartypmodin | varchartypmodout | - | i | x | f | 0 | -1 | 0 | 100 | | | (1 row) //刪除列 postgres=# alter table t drop column name; ALTER TABLE postgres=# select * from t; id ---- 1 2 3 4 5 6 7 8 9 10 (10 rows) postgres=# select attrelid,attname,atttypid,attnum,attisdropped FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass; attrelid | attname | atttypid | attnum | attisdropped ----------+------------------------------+----------+--------+-------------- 16395 | id | 23 | 1 | f 16395 | ........pg.dropped.2........ | 0 | 2 | t (2 rows) //更新系統表，恢復刪除的列 postgres=# update pg_attribute set attname='name',atttypid=1043,attisdropped='f' where attrelid='t'::regclass and attnum=2; UPDATE 1 postgres=# select attrelid,attname,atttypid,attnum,attisdropped FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass; attrelid | attname | atttypid | attnum | attisdropped ----------+---------+----------+--------+-------------- 16395 | id | 23 | 1 | f 16395 | name | 1043 | 2 | f (2 rows) postgres=# select * from t; id | name ----+---------------------- 1 | bcbd19340969fda7c9c4 2 | a9f514a971eae3937def 3 | 20d53f04cce29b1e2984 4 | 912dd222955487de27a7 5 | 5b18d6e2c9b22d34884f 6 | 4c7db5a43de739511864 7 | b2f1b83e98bdfdce8bce 8 | 1fe69e2bec216de50f29 9 | 689ecc14d87ae81fc0d1 10 | 0ba176b240d0875da3e8 (10 rows)
（2）刪除欄位後做過vacuum full
//刪除列 postgres=# alter table t drop column name; ALTER TABLE postgres=# select attrelid,attname,atttypid,attnum,attisdropped FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass; attrelid | attname | atttypid | attnum | attisdropped ----------+------------------------------+----------+--------+-------------- 16395 | id | 23 | 1 | f 16395 | ........pg.dropped.2........ | 0 | 2 | t (2 rows)
可以看到做過了vacuum full後，對應的pg_attribute裡的這條已經刪除的列的資訊依舊沒有清理掉。
postgres=# vacuum full t; VACUUM postgres=# select attrelid,attname,atttypid,attnum,attisdropped FROM pg_attribute WHERE attnum > 0 and attrelid = 't'::regclass; attrelid | attname | atttypid | attnum | attisdropped ----------+------------------------------+----------+--------+-------------- 16395 | id | 23 | 1 | f 16395 | ........pg.dropped.2........ | 0 | 2 | t (2 rows)
//恢復資料 postgres=# update pg_attribute set attname='name',atttypid=1043,attisdropped='f' where attrelid='t'::regclass and attnum=2; UPDATE 1 postgres=# select * from pg_type where oid=1043; oid | typname | typnamespace | typowner | typlen | typbyval | typtype | typcategory | typispreferred | typisdefined | typdelim | typrelid | typsubscript | typelem | typarray | typinput | typoutput | typreceive | typsend | typmodin | typmodout | typanalyze | typalign | typstorage | typnotnul l | typbasetype | typtypmod | typndims | typcollation | typdefaultbin | typdefault | typacl ------+---------+--------------+----------+--------+----------+---------+-------------+----------------+--------------+----------+----------+--------------+- --------+----------+-----------+------------+-------------+-------------+-----------------+------------------+------------+----------+------------+---------- --+-------------+-----------+----------+--------------+---------------+------------+-------- 1043 | varchar | 11 | 10 | -1 | f | b | S | f | t | , | 0 | - | 0 | 1015 | varcharin | varcharout | varcharrecv | varcharsend | varchartypmodin | varchartypmodout | - | i | x | f | 0 | -1 | 0 | 100 | | | (1 row) //做過了vacuum full的列雖然可以恢復，但是列原來儲存的資料已經不存在了，用NULL填充 postgres=# select * from t; id | name ----+------ 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | (10 rows)
三、關於鎖的情況
所有的DDL操作都會鎖表(堵塞讀寫)。
DDL操作有的只需要修改後設資料,這種情況下一般是毫秒級別就可以完成。而有的需要rewrite表的情況,它的執行時間以及鎖的佔用時間,取決於表的大小以及索引的多少。
如果DDL操作未能及時獲取表的排他鎖(例如有其他長事務持有了表的共享鎖), 則DDL的排他鎖已進入等待佇列, 會堵塞其他該表的一切DML和查詢操作。
來自 “ ITPUB部落格 ” ，連結：https://blog.itpub.net/69990629/viewspace-3002512/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle刪除表欄位和oracle表增加欄位Oracle
- [升級需要用到的] mysql更新表 增加、刪除、修改表欄位MySql
- Oracle-給表刪除欄位Oracle
- Oracle 增加修改刪除欄位Oracle
- PostgreSQL刪除表中重複資料SQL
- Oracle表 列欄位的增加、刪除、修改以及重新命名操作sqlOracleSQL
- SQLServer2012刪除表欄位SQLServer
- postgresql分割槽表修改資料表欄位SQL
- 用SQL語句增加刪除修改欄位SQL
- Oracle 增加修改刪除欄位與新增註釋Oracle
- MySQL之資料庫和表的基本操作（建立表、刪除表、向表中新增欄位）MySql資料庫
- postgresql單個表可以有多少欄位SQL
- sql判斷表中是否有此欄位SQL
- Oracle-給表增加欄位和註釋Oracle
- postgresql VACUUM 不會從表中刪除死行的三個原因SQL
- 刪除表裡重複資料
- oracle修改表增加列刪除列修改列Oracle
- MySQL-刪除欄位MySql
- mysql表刪除重複記錄方法MySql
- Excel表格增加和刪除Excel圖表資料Excel
- 表欄位經常要增加的業務怎麼設計表結構
- oracle檢視該使用者的所有表名字、表註釋、欄位名、欄位註釋、是否為空、欄位型別Oracle型別
- sql server 修改欄位名，檢視指定表是否存在SQLServer
- 更改MySql表和欄位區分大小寫MySql
- postgresql表如何去重SQL
- Oracle如何刪除表中重複記錄Oracle
- 查詢刪除表中重複記錄
- mysql 刪除表中重複的資料MySql
- SAP MM 為MB51報表增加查詢欄位
- 查詢/刪除重複的資料(單個欄位和多個欄位條件)
- Oracle 檢視 表屬性 ：“表名(註釋)/列名(註釋)/欄位是否NULL”OracleNull
- SQL新增表欄位SQL
- 在資料庫表中加一個狀態欄位可以代替軟刪除嗎？資料庫
- oracle中判斷欄位是否存在和新增表結構Oracle
- postgresql如何判斷表是否存在SQL
- Oracle-不刪表資料，修改欄位型別Oracle型別
- PostgreSQL技術週刊第9期：PostgreSQL11新特性解讀之新增非空預設值欄位不需要重寫表SQL
- 高效快速刪除Oracle表中重複記錄Oracle